Solved

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

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

790 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