Having trouble with missing records

[Deco01]

I would like some help on how to deal with missing records that have occured
due to me setting a field to required and then not inputting required field,
electing to stupidly close the database and it now has 6 records missing in
my table. I cannot get htem back so am looking for assistance. If i create
a temporary table, setting the autonumber to 9430, the number which the
missing records start from and then saving this temporary table, will I be
allowed to append these records and will the data be inserted into my real
table in the correct position, ie being sandwiched between the records 9429
and 9437???

Any assistance would be much appreciated as I need to have these records
available to the staff for searching for jobs and invoice details for these
records??

please help

Deco

 

[Jerry Whittle]

1. Create a new table; however, just make a number (long integer) field to
match the other table's autonumber field. Type in the exact data that you
want to see. Then you can use an append query in append the records as long
as you don't violate any other constraints.

2. >>> table in the correct position <<<
Records have no position in a table. In theory you could open a table and
see all the records in a nice order. Close the table then reopen it only to
find the records all jumbled up. To make matters worse, you can NOT depend on
an autonumber to increment to the next larger number. Sometimes they don't
plus there's ways to 'burn up' a number in an autonumber field. The only way
to maintain records in a specific order is by ordering them on some field in
a form, report, or query.

Personally I like using a Date/Time field with a default value of Now() to
order records. The one caveat is that you can't add more than one record per
second for this to be bulletproof.

 

['69 Camaro]

Hi, Deco.

If you're relying on AutoNumbers to be sequential, then you shouldn't be
using an AutoNumber column. You should be relying on a function that
calculates the next number. Please see the following Web page for some code
samples of how to do this:

http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.