Storing Newest Autonumber

[PapaMike]

I have a program with six related tables. The primary table uses an autonumber field as the unique key field. The program is designed to prepare Bid Quotations on indivual parts. Occasionally there will be two parts that are nearly identical. Rather than forcing the user to enter information on both parts in all six tables, I created a series of queries that will duplicate a part in all tables with multiple records in some tables with a new part number. The new part number is suppplied by the user. Everything works fine but I need to store the Bid Number (autonumber) of the new part. This new autonumber will then be used to update the remaining tables that use BOTH part number and Bid Number. Thank you for any help.
Michael

 

[JimHorton]

Michael,
This is a classic example of why I and many others discourage the use of Autonumber. You'll never be able to accurately determine the next autonumber--it doesn't increment from the last one in the table, since some may have been subsequently deleted. You could create the primary record first and fetch the autonumber from that--just fetch it before the .update statement if you're doing it in dao or ado.

However, if you're using a query to insert, I know of no way to accurately retrieve the autnumber of the record or records you just created--you can't just get the key greater than the greatest key before the insert--other users may have entered in that time also. For standalone, something like that would work but it's still messy. Bottom line--lose autonumber, create a sequence table, roll your own autonumber and you'll be happier.
--Jim

 

[PapaMike]

Jim,
Thanks for the help. I solved the problem by using the tried and true work around method. After all of the new records are created using the new part number, a button is displayed asking the user to find the master Bid record using the new Part Number they entered and then confirming the find as correct by entering the found autonumber. I then use this user entered confirmation to update all records using the new part number as the finding criteria and the entered Bid number as the update value. Works fine. Thanks again,
Michael