Solved

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

Posted on 2009-07-11
3
320 Views
Last Modified: 2013-11-28
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

0
Comment
Question by:SuperSnooper
  • 2
3 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 250 total points
ID: 24833624
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
else
 msgbox "no record found"  ' can be commented out
end if
rs.close
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.


0
 

Author Comment

by:SuperSnooper
ID: 24835658
Peter,
That is exactly what I am Looking for EXCEPT I get a syntax error (missing operator) when run.

David
0
 

Author Comment

by:SuperSnooper
ID: 24835714
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
Else
 MsgBox "no record found"  ' can be commented out
End If
rs.Close
Set rs = Nothing
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now