MS Access questions

Hi, I am fairly new to Access 2000 but know the basics. I have been trying to create a DB with some screens. I have correctly created the DB and am now working on the screens. I am now a bit stuck and need some help with the following issues:

1) I have a couple of tables set up with auto numbers. I note that if you add a new record but then cancel changes the auto number that was allocated is now lost and cannot be used again. Having read through the manuals, this can be fixed by using the database utility "compact and repair". Unforunately, I am only having patial success with this. Some of my tables with auto numbers are compressing correctly so that numbers used on cancelled records are reset and reused but on a couple of my other tables, the sequence is not resetting. Is there anyway of fixing this, or is this an error of Access? I have tried deleting the auto number column and recreating it but to no evale (this does correctly recreate the numbers in sequence but if I cancel a new record addition, these numbers are still not re-sequenced in the compact process). Is there a way of re-sequencing when you create a new record and then cancel the addition, so that the auto number is re-used?

2) When I launch a couple of my screens, I want the system to check a database for values and if none exist, I want the screen to issue a message and close. I am trying to do this with an SQL statement, but cannot return the result of this SQL into the code, to enable me to say something like "If recordcount = 0 then close"

Hopefully these are easy to resolve problems, but any help received will be greatly appreciateds.

Kind Regards

Simon

Question #1

The copact and repair only resets the autonumbering if there is no data in the tables. If there is data in the tables (no such luck). With that said. Most developers usually use the autonumber on tables where the PK is rarely displayed and we only need a unquie field. For incrementing numbering where you may need to reset the number or change the starting number, try this.

Create a seperate table (called numbering or whatever) in this table you will list the current number that is to be used next. The what you do is : every time you need a new number you grad it from this table, stick it in you table, increase the number by 1 and put it back.

This will allow you to reset you numbering, and specify a number other then 0 to start with and reset to.



Question 2

The easiest way is to use ADO or DAO recordsets. if you go to this thread:

http://forums.aspfree.com/showthread.php?t=25153

The is a sampel DB attached there with has some example of DAO and ADO code


The code to add to these examples for use with the code is

If rs..RecordCount = 0 Then

msgbox "No records"

Endif


S-