Opening Form to a specific record (1 Viewer)

David44Coder

Member
Local time
Today, 17:55
Joined
May 20, 2022
Messages
100
Having a bit of trouble opening a Form to a particular record.

First tried
Code:
DoCmd.OpenForm "MyForm", acFormDS, "Station = '" & Form_frmTest!Place & "'"
A field Called "Station" does exist with a record Place but that didn't work. It just opened to the first record.
So in Form Load event I put
Code:
Dim Place As String
Dim r As DAO.Recordset
Place = nz(Me.OpenArgs)
Set r = Me.RecordsetClone
r.FindFirst ("Station like '" & Place & "*'")
'at this pont r.NoMatch is False
DoCmd.GoToRecord acForm, Me, , 7

This also opened to the first records, so I added DoCmd.GoToRecord but can't quite figure what it wants.
Have put "Me" and the Form name and various other criteria but cannot get it to work.
Online searching found the GoToRecord syntax to move to the 7th record but that fails too.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:55
Joined
Feb 28, 2001
Messages
23,210
Form_Load is a good place for that action. However, lose the parentheses around the FindFirst argument. FindFirst is not a function, but using those parentheses makes it look that way to Access.

Then after a successful R.FindFirst, use

Me.Recordset.Bookmark = R.Bookmark

Then for your further edification, take a REALLY good look at what you sent through OpenArgs and what you used as an argument for the .FindFirst.

Hint: After you do the concatenation in the FindFirst argument, how many times does the word "Station" appear?

But if the Bookmark alignment works, you won't need the GoToRecord anyway so that particular line becomes moot.
 

David44Coder

Member
Local time
Today, 17:55
Joined
May 20, 2022
Messages
100
Thanks Doc Man. I removed the brackets and used a different method to Open Args.
The Bookmark is working just fine. When would GoToRecord be useful? I don't really follow that and how might you know the number to use ? is it not supplied by find (is it) ? But for me if didn't go to that number anyway.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:55
Joined
Feb 28, 2001
Messages
23,210
Thanks Doc Man. I removed the brackets and used a different method to Open Args.
The Bookmark is working just fine. When would GoToRecord be useful? I don't really follow that and how might you know the number to use ? is it not supplied by find (is it) ? But for me if didn't go to that number anyway.
When you create a command button using wizards, several of the options build code for you. The one for "New Record" will use GoToRecord with the symbolic value acNew. The one for Navigate to First Record uses GoToRecord with acFirst. There are other examples using acLast, acNext, acPrevious, etc. where you have a predictable record based on things you would find in a standard navigation control. (Which is probably a hint on how the navigation control actually works.)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:55
Joined
Feb 19, 2002
Messages
36,961
Your first attempt was the correct method. We have to figure out why it didn't work. If you are referring to a field on the current form, which is normal, the correct reference is:

Me.Place

Is the field a string or is it numeric? You are showing it s a string but if it is a table level lookup, you are probably confused as to its real data type which is one of the reasons that experts don't use them.
 

Users who are viewing this thread

Top Bottom