Update outside Database

[wtrimble]

I have a form in Db1(mainDB) that when a record is added in a specific form (tblsampledatainfo) it needs to be added to a table in DB2(XRFdb) in table (chemistry).

My problem is this, I need to get the ID for the newly updated record in the Chemistry table in DB2 which is an Autonumber. The number isn't always the last one used + 1 because sometimes records are deleted and Numbers are skipped so I have to go in and search for it. I'm not sure the best way to do this. The following code works usually, but sometimes the ID that it collects is from the first record: 1, and not the actual one, which I believe is do to the fact the table hasn't been refreshed since the update has occurred and so the record hasn't been added yet. Is there a better way to do this or do I need to change anything in my code?

Thanks for your help.

If ticker2 = 2 Then
 
STR = "INSERT INTO Chemistry ([SAMPLE DATE],[erosion layer], ... 
IN 'P:\BU Regeneration\XRF Laboratory\Chemistry_2010-08-04.mdb' " _
& "SELECT Date(), qryprojectsamplenumber.erosion,  ....
& "FROM qryProjectSampleNumber " _
& "Where qryprojectsamplenumber.samplenumber = '" & Me.Text49 & "';"
 
DoCmd.SetWarnings off
DoCmd.RunSQL STR
 
MsgBox ("Sample added to XRF's Database")
 
 
' This is where it pulls the ID (ChemID) from the DB2 and puts in the current record
 
Set db = OpenDatabase("P:\BU Regeneration\XRF Laboratory\Chemistry_2010-08-04.mdb")
Set rs = db.OpenRecordset("chemistry", dbOpenDynaset)
rs.FindFirst "[sample number] = '" & Me.Text49 & "'"
xrf = rs![chem id]
Me.XRF_ = xrf
rs.Close
Set rs = Nothing
Set db = Nothing
ticker2 = 4
End If

 

[SOS]

Do a search here for @@Identity. It should give you what you need.

 

[wtrimble]

Uhh this kindof helps.. but not really. Unclear of how to use it.

 

[dragofly]

If the only problemis possibly that the table is not updated you could put rs.update before rs.findfirst

Or what I would do is link the table (using file import data->link table) then use a query with a where clause simular to your findfirst and thus get your id.

 

[wtrimble]

Ah, yes. I think linked table is what I need. Thanks for reminding me about that.