Adding saved records's ID to another table

[bd528]

Hi all,

I have a form where after saving (clicking "save") for a new record, I want that new record's autonumber ID to be added to a table, only if it doesn't exist in that table.

I know how to add records to a table. Where I'm struggling is getting the saved form's ID, and then efficiently checking if it exists in the table.

Thanks in advance

 

[pbaldy]

If it's a bound form, the autonumber should be available as soon as the record is dirtied. You can get it by referring to the control that contains it from the appropriate event:

Me.TextboxName

You can use a DCount() to see if it exists in the other table:

http://www.mvps.org/access/general/gen0018.htm

 

[bd528]

If it's a bound form, the autonumber should be available as soon as the record is dirtied. You can get it by referring to the control that contains it from the appropriate event:

Me.TextboxName

You can use a DCount() to see if it exists in the other table:

http://www.mvps.org/access/general/gen0018.htm

The form is bound, and even after dirty, whenever I try and get the ID, null is returned.

I assigned a textbox to the ID, and it stays empty.

 

[pbaldy]

The table it's bound to is an Access table? Linked or local?

 

[bd528]

The table it's bound to is an Access table? Linked or local?

It's a linked SharePoint table.

 

[pbaldy]

Oh, all bets are off then. I haven't worked with Sharepoint, but perhaps you need to force a save to get the ID to populate?

If Me.Dirty Then Me.Dirty = False

 

[bd528]

If Me.Dirty Then Me.Dirty = False

That worked - thank you so much!

It slipped my mind to use dcount also - I was using and overly complicated sql query and recordset - so thanks for that too!

 

[pbaldy]

Happy to help!