INSERT and Autonumber

[David S. Mohler]

Hello,

I'm a bit of a novice at using code to manipulate my Access queries.

I have set up a database that has 3 tables:

Jobs
Locations
Jobs_Locations

Jobs_Locations is a junction table. A job can be in many locations, and each
location can have many jobs. Using the junction table, I can look up all
jobs where LocationID = X, or all locations where JobID = Y. This works
fine.

The Jobs and Locations tables each have an autonumber field for the primary
key. These are foreign keys in Jobs_Locations.

When I add a new Job to Jobs using an "INSERT INTO Jobs" query, is there a
way to immediately retrieve the new ID assigned by the Autonumber? That way,
I can immediately update the junction table with the new JobID and specify
each location that applies for that JobID.

How is this normally done?

Thanks,
Dave

 

[Allen Browne]

No. Access (JET) does not give you the value of the AutoNumber from an
Append query.

If you are the only user, you might be game to use DMax() which will return
the highest number assigned so far. However, it would be more reliable to
open a DAO recordset and AddNew (with Update). This lets you get the new
number, which you can then use as the value for the foreign key in the
INSERT statement for the related records.

 

[MikeHatton]

Dave,
I have 2 methods that I use depending on what is required..

The easiest way I found was using the domain agregate function dmax(
this can be used in a query to retrieve the max (or last) autonumbe
used... This is not the most robust method but it works in simpl
situations.

Code
-------------------
dmax("[FieldAutonumber]","Table")
-------------------


The second method is to use the recordset.addnew method rather than th
insert query. This proves far more reliable in multiuser environments
this example shows code for creating a new record from an unbound for
and returning the autonumber to a field on the form...


Code
-------------------

dim rs as recordset
set rs = currentdb.openrecordset("Table")
rs.addnew
rs![Field1] = Form.[field1]
...
rs![Fieldn] = Form.[fieldn]
Form.[NewID] = rs![FieldAutonumber]
rs.update
set rs = nothing