Solved

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

Posted on 2009-07-11
3
321 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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.

930 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

10 Experts available now in Live!

Get 1:1 Help Now