Solved

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

Posted on 2009-07-11
3
327 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

631 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