Capture ID's From an Append query

[CanWest]

Ok this is a bit of a weird one. I have an append query that appends records from one table to another. The second table's Primary key is an autonumber field. I need to capture the ID's that are created when the append is done.

Any suggestions would be greatly appreciated.

 

[jdraw]

Ok this is a bit of a weird one. I have an append query that appends records from one table to another. The second table's Primary key is an autonumber field. I need to capture the ID's that are created when the append is done.

Any suggestions would be greatly appreciated.

After the Append is completed, do a select on the newly created record in the proper table. Something along this layout should work -- you'll need the proper table name and idfield name

Select S.* from secondTable as S where
idfield = (Select MAX(idfield) from SecondTable)


good luck

 

[CanWest]

Select S.* from secondTable as S where
idfield = (Select MAX(idfield) from SecondTable)


good luck

Would this not get me only the one id that was at the end of the append. There are between 40 and 50 records appended each time and the number is never the same

 

[John Big Booty]

Once you've appended your x records and captured all the ID's what will you then be doing with them?

 

[John Big Booty]

You could add a new filed to your second table in the form of a check box. The default for this would be unchecked. as part of your append query you could set all new (appended) records as checked and then easily identify the new records by the fact that they are checked. Then set the check to no once you have collected and processed those new records.

 

[CanWest]

Once you've appended your x records and captured all the ID's what will you then be doing with them?

Using them in a delete query if an invoice has to be regenerated. Long story but I inherited this database that was originally and sql db gated to access....

 

[John Big Booty]

I'd probably go with adding the Yes/no field to your second table, should make things nice and easy.

 

[boblarson]

One way would be to use an extra table with just a single field (long integer) and then you would clear that table, do an select for just the ID's from the table you WILL be appending the data to and then use that table as an exclusion (see my Quick Tutorial here) to find out what new ID's are in the data table you are wanting.

 

[boblarson]

Oh and that way you don't have to modify your real tables.

 

[vbaInet]

Regarding getting the newly created IDs, I would be inclined to use a recordset's LastModified property.

 

[CanWest]

Thanks everyone for your input. By using dmax before and after the append I was able to come up with the valuse I needed.

 

[boblarson]

Thanks everyone for your input. By using dmax before and after the append I was able to come up with the valuse I needed.

Just a word of caution -

Autonumbers are NOT guaranteed to give you incremental numbers nor are they required to be positive. You may have it currently set to increment but Access doesn't always stick to that. So it may be risky to assume that it will always give you the right answer by relying on the Max of the ID.

It is more likely than not that it will be okay, but you should be aware of the possibility that is won't.