lookup last record in table

[ray147]

hi,
i have a form where an SQL Insert query is running to append Table1 with a new row. Just afterwards, I would like to update a corresponding record in Table2 with the primary key field of the 'just inserrted' row in Table 1.

I know that I need to use an SQL Update query and know how to reference the WHERE field as you can see below...what i dunno is how to identify the last record primary key field ...

DoCmd.RunSQL _
"UPDATE Bookings" & _
" SET [DespRef] = " & ??? what ??? & _
", [Status] = 20" & _
" WHERE [BookingRef] = " & Forms!xCreate_Despatch1("BookingRef" & k) & ";"

Any ideas?

 

[ray147]

no ideas here guys?!
i can't figure it out!

 

[Bat17]

I dont have Access to hand at the moment but assuming that the "last record primary key field " is numeric and ascending then you should be able to use the Dmax() function, Look it up in help for the syntax.

Peter

 

[ray147]

tnx bat!

that works gr8! sometimes thinks are so simple, but you just can;t get across the right stuff

for ppl who read this thread, function looks as follows:

DMax("[DespatchRef]", "Despatches")

The above reads the maximum 'DespatchRef' value from table 'Despatches'