Autonumber Identification 2

[nag9127]

I have a form (the first form) with data pulled from a table that is being used to create records in a different table (the second table). I am using a SQL insert command to place values from my first form into the fields in the second table. After the new record is created in the second table I am closing the first form and opening a form bound to the second table with the new record to permit additional editing and/or processing of that record. The field through which I need to identify the data to retrieve from the second table is an autonumber field which does not exist in the first form or the first table. It is created automatically through the insertion of the new record. What is the best technique for identifying that autonumber value so that I can bring the record up in my editing form automatically as soon as my first form closes and without any user input?

 

[nag9127]

As a followup to my question, with an autonumber field is there some kind of autonumber value that is maintained by the system that is available to grab and assign to a variable? I could then manually increment the variable and know what number to pull my data from in the second table.

 

[nag9127]

FYI, this is Access 2000!

 

[ProgramError]

How do you open your second form?

Assuming the record you wish to look at was the last you entered, then put docmd.gotorecord aclast in the onlaod event of the second form.


Ian Mayor (UK)
Program Error
Always make your words short and sweet. Because you never know when you may have to eat them.

 

[nag9127]

That's a good solution and I can do that. I just wanted to try to eliminate any possibility of the wrong record being retrieved due to multiple users inserting multiple records at the same time. Granted that this sequence of events is more or less instantaneous (it's actually part of the same event procedure) and retrieving the wrong record is extremely unlikely, maybe even impossible, I still wanted to be able to retrieve the record by its actual ID (the autonumber field) if that could be done. That would eliminate any error possibilities. You never know for sure how the software may handle more or less concurrent data entry. Thanks for your reply!

 

[TheAceMan1]

How are ya tania20 . . .

Any sort order would upset the listing as well.

[blue] . . . due to multiple users inserting multiple records at the same time.[/blue]

Woooaaaa! Be sure to have a look below:

Why AutoNumber shouldn't be used in MultiUser databases (And How to ge the Unique Number)

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[KenReay]

Hi

Hmm, have done this before but cannot quite remember how try a search for %Identity%, I think that will give you a pointer to how to do it

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[ProgramError]

Don't use autonumbers for the second table, instead create a unique ID (maybe with ID = dmax("ID", "table")+1 ) at the time of saving the record and use that as the ID (take note of Aceman1's link). Once you have this ID you can open the second form using.

Docmd.openform "formname",,,"ID = '" & [savedID] & "'"

and this will show only the record with Identical ID

Ian Mayor (UK)
Program Error
Always make your words short and sweet. Because you never know when you may have to eat them.

 

[nag9127]

Actually, I could leave the ID field alone and just create a new field in the table that pulls an incrementing number from a second table (I would have code to make the value in this second table field increase by 1 as each number is used). I am using that technique to create new part numbers as items are added. Then I would just recall the record using this field.

 

[KenReay]

Hi

Found the links I used to do this

http://msdn2.microsoft.com/en-us/library/aa140015(office.10).aspx

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[TheAceMan1]

nag9127 . . .

. . . and the link I provided?

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Spruill]

How can i get the autonumber before the record is commited to the database?