Assigning the value in recordset field to a variable

[Baby Face Lee]

Hi there everyone
Can you tell me how I go about the following:
I want to put the value of the last primary key number in a variable so I
did the following code:

Dim lngID As Long
Dim rs As Recordset

Set rs = Me.RecordsetClone

lngID = rs.FindLast "ID"

The last line tells me the syntax is wrong but I don't know what I should
do. It's OK to just have rs.FindLast "ID" on it's own but I want to assign
the value in the ID field to the lngID variable.
Can you perhaps explain where I'm going wrong?
Many thanks,
Lee

 

[Marshall Barton]

Hi there everyone
Can you tell me how I go about the following:
I want to put the value of the last primary key number in a variable so I
did the following code:

Dim lngID As Long
Dim rs As Recordset

Set rs = Me.RecordsetClone

lngID = rs.FindLast "ID"

The last line tells me the syntax is wrong but I don't know what I should
do. It's OK to just have rs.FindLast "ID" on it's own but I want to assign
the value in the ID field to the lngID variable.

First, Findxxx expects an expression to search for.
Findlast will find (set the recordset's current record) the
last (as specified in the recordset's Order By clause)
record where the expression is true. From where I sit,
that's a completely different operation from what I think
you want to do.

Most likely, you want to find the largest ID value in the
table, not the form's recordset. If so, here's a common way
to do that:

lngID = DMax("ID", "thetable")

If that's not what you want to do (even if it seems to
work), please post back with more details about what you're
trying to accomplish.

 

[Baby Face Lee]

Thanks Marshall. I'm so used to only using the Lookup and Count domain
aggregate functions I'd completely forgot about the Max one. Sorry to have
troubled you.
Kind regards,

Lee

 

[Marshall Barton]

No trouble.

But, if you're using that to generate a sequential PK, I am
a little cautious about recommending that approach. For
instance, if your app may have multiple users, then that
approach does not guarantee that two records would not get
the same key.