Opening Form to a specific record

[David44Coder]

Having a bit of trouble opening a Form to a particular record.

First tried

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

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]

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]

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.

 

[pbaldy]

FYI, in your effort you had the wherecondition in the wrong position:

Open a second form to the record

 

[David44Coder]

Thanks, 3 commas needed.

 

[The_Doc_Man]

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]

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.

 

[pbaldy]

Your first attempt was the correct method. We have to figure out why it didn't work.

See post 4.

 

[Pat Hartman]

Skipped right over post #4