In an Access 2003 db Go to a Specific Record Based on a Field Value

I Have a form that has a field (ID) that is "AutoNumbered".  I want to go to the last record with a specified value in a second field.  Then I want to go to the previous record . The ID field are not in any order so I can't us a query becaued additional field value inputs are necessary.

I know this won't work but basically This is what I want to do:
     Dim IDNumber as String 
     DoCmd GoToRecord, , acLast Where (A_Name) is "Correct_Name" (Obtained from    DLookUP)
     IDNumber= (ID) -1
     DoCmd GoToRecord, , IDNumber

Open in new window

Who is Participating?
peter57rConnect With a Mentor Commented:
I don't really understand what you are trying to do.  If you are trying to locate records based on what you imagine to be their physical location then it can't be done, because the physical sequence is not known.

You can find records using their data values and it is possible that what you mean is you want the max id of records containing a certain value in another field.
That is given by..
DMax("ID", "tablename","secondfield "= somenumber)
or by
DMax("ID", "tablename","secondfield '"= "sometext" & "'")
Then you can subtract 1 from this value and move to that record - if it exists, because using autonumbers can leave gaps.
So the full code would like..

dim rs as dao.recordset
set rs = me.recordsetclone
rs.findfirst "Id=" & DMax("ID", "tablename","secondfield "= somenumber)-1   ' or the text equivalent
if not rs.nomatch then
me.bookmark = rs.bookmark
 msgbox "no record found"  ' can be commented out
end if
set rs = nothing

Of course the record you then move to doesn't necessarily have the same value in the second field as the value you used to find the Max id number.

SuperSnooperAuthor Commented:
That is exactly what I am Looking for EXCEPT I get a syntax error (missing operator) when run.

SuperSnooperAuthor Commented:
Sorry Peter. This is what I am using:

ContMaintCode = DLookup("ContractCode", "CountRecords")
DoCmd.GoToRecord , , acLast
Dim rs As dao.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "Id=" & DMax("ID", "PreventativeMaintenanceList", "Heat_Air_Contractor_TypeMaintCode " = ContMaintCode) - 1
If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark
 MsgBox "no record found"  ' can be commented out
End If
Set rs = Nothing
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.