AutoNumber does not update table 1

[polnichek]

I have a table with a primary index that is an Autonumber field and I need this number to insert into another table as a foreign key. Since the first table does not have the value actually "physically" in its dataset (even though there has been a number generated by the autonumber field) I keep getting an error when I attempt to add a new row to its related table. Is this is a cart before the horse problem because Access does not actually put the number into the table until you close the form? The data entry form for the first table is a bound form and table two does not have a form at all since it functions as a bridge table. I am updating table two via a function call and using an ADO recordset object, when I attempt two update table two I get an access error telling me there is no related record from Table 1 (even though an autonumber has been generated and is visible on the data entry form). If anyone can help me get unstuck I would be very grateful.

Polnichek

 

[Sheco]

Try searching this forum and google for @@identity.

 

[polnichek]

Thanks for the tip. It got me thinking about alternaives to what I was doing. To resolve this issue I scrapped my bound data entry form and created an unbound version which allowed me to control when and how things were entered into the database by using the ADO recordset object. This enabled me to update my bridge table after I updated the first table and capture the autonumber in order to use it as a foreign key to create a new record in the bridge table.
Thanks again to Sheapco for the tip.