Solved

DoCmd.GoToRecord SYNTAX

Posted on 2014-11-17
4
532 Views
Last Modified: 2014-12-05
Hello ~ I'm attempting to go to a specific record on an open form using:
DoCmd.GoToRecord acDataForm, "frmProject", , "projID =" & ProjLINK

I receive: Run-time error 2498
ProjLINK is an integer
projID  is an auto-number ID and the primary key reflected on frmProject

Clearly I'm missing something here.
Would appreciate illumination!

Many Thanks!
0
Comment
Question by:Chi Is Current
4 Comments
 
LVL 75
ID: 40449142
You might try this:

docmd.GoToRecord acDataForm, "frmProject",acGoTo, "[projID] =" & CLng(me![ProjLINK])


Where is ProjLINK located ?  A field or text box on the Form ?
mx
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40449483
GoToRecord is just for moving up/down the records.

For searching use:

DoCmd.SearchForRecord, "frmProject", acFirst, "projID = " & ProjLINK & ""

/gustav
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 40455772
In Access 2003, there is no SearchForRecord, there is DoCmd.FindRecord
With many records, that becomes BRUTALLY slow.

These days, I'd use
Dim db As Database
Dim rst As DAO.Recordset
Dim strCriteria As String
Dim TheprojID as Long
strCriteria = "projID = " & ProjLINK
    
'finding no match takes forever so
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT projID from WhatEverTable where " & strCriteria & ";", dbOpenDynaset, dbSeeChanges)
If rst.RecordCount = 0 Then
    response = MsgBox("No such projID found.", vbInformation)
Else
    TheprojID = rst!projID
    rst.Close
    strCriteria = "projID  = " & TheJobID
    Set rst = Me.RecordsetClone
    rst.MoveLast
    rst.FindLast strCriteria
    Me.Bookmark = rst.Bookmark
End If

Set rst = Nothing

Open in new window


It performs an order of magnitude faster than DoCmd.FindRecord once you start going north of 10K records
0
 
LVL 2

Author Comment

by:Chi Is Current
ID: 40484320
Hello mx, /gustav & Nick67, thank you ALL for your thoughtful replies.
I am most grateful for your ideas and expertise.

Seems this is not really as much of a syntax question as a strategy question.

I think the best way of displaying the desired record is by using Nick67's method:
    ...
    rst.FindLast strCriteria
    Me.Bookmark = rst.Bookmark

Gotta give Nick67 the points for pure psychic accuracy,
this solution is perfect.

Thank you ~ Jacob
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

726 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