assign the last value of a recordset to a variable 1

[craigorama]

Hi everyone!

I'm trying to assign the last value in a field (location_no) to a variable.

I am using a recordset and trying to get the value of the last recordset.

I can get the recordcount but dont know how to use that to get that value from the cell.

which method/ property will return the value?

my code so far:

Public Sub get_value()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset

Dim strSQL As String
Dim value As Double
Dim lastfield As Double
Dim almostlastfield As Double

Set cnn = CurrentProject.Connection
strSQL = "SELECT location_no from GB_Locations"

Set rs = CreateObject("ADODB.Recordset")

rs.Open strSQL, cnn, 1, 1

If Not rs.EOF Then
lastfield = rs.RecordCount
MsgBox ("There were " & lastfield & " matches.")

Else
MsgBox "No matches."
End If

value = ??????
MsgBox (value)
rs.Close
Set rs = Nothing
Set cnn = Nothing

End Sub

 

[PHV]

...
If Not rs.EOF Then
lastfield = rs.RecordCount
MsgBox ("There were " & lastfield & " matches.")
rs.MoveLast
MsgBox "Last value=" & rs!location_no
Else
MsgBox "No matches."
End If
rs.Close
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[craigorama]

thanks!

the next task is to update a field in a table with an incremental number starting at the value. where value = rs!location_no.

ie if value is 548 i need the first record of Location_Number field to be 549 and +1 to each row.

is there a way to do this with an sql update stmt? or is it a matter of using a recordset?