Use Dialog Form to Define Recordsource and Go To Specific Record

[cyclokj]

I'm trying to build a form in Access 2003 which both defines the recordsource at the form's open event and lands on a specific record. I am able to do either one successfully, but not both. I use a dialog form to specify which set of records and which particular record I need. Then, the dialog form runs the following code:


DoCmd.OpenForm frmSomeForm, , , "[somefield] = " & Me.somefieldvalue, , , Me.OpenArgs The somefieldvalue is the record I want to land on. The Me.OpenArgs value is passed to frmSomeForm and processed on that form's open event to determine the recordsource. What happens is that I get the correct recordsource, but am landing on the first record, not the one I want. I have also tried passing both values as a compound OpenArg, but this yields the same result when I open the new form. I have wasted an unbelievable amount of time playing with this and cannot get both things to happen - it's either one or the other. Any ideas? Thank you!

[RuralGuy]

What code are you trying to use to move to a certain record?

[cyclokj]

I have tried using DoCmd.OpenForm frmSomeForm, , , "[somefield] = " & Me.somefieldvalue, , , Me.OpenArgs as mentioned previously. But this does not work. I have also tried using
Set rs = Me.Form.RecordsetClone
rs.FindFirst "SalesInvoiceID = " & somefieldvalue

This code runs at the new form's on load event. I tried running it at the new form's on open event as well and neither way produces the result I need.

[RuralGuy]

Does your form have a default RecordSource? If not then adding a WhereCondition argument will not achieve anything.

[cyclokj]

The form does not have a default recordsource as it gets set by the dialog form. When I initially built the form, it did have a default recordsource and this worked along with the where condition to land on the appropriate record just fine. Problem is that the form was then filtered to only that record. I need to scroll through all of the records which correspond to the conditions set on the dialog form - not just one. That is why I took the default recordsource off of the form and now have it be determined on the form's on open event based on parameters passed from the dialog form.

[RuralGuy]

Maybe this link will give you some ideas: http://www.baldyweb.com/Bookmark.htm

[cyclokj]

I have reviewed that same link from other posts and still cannot achieve the result I'm looking for. At what point/event should the recordset code run? I've tried it on the new form's on open event and also on the new form's on load event. Neither causes the form to land on the selected record - it always lands on the first record. Is there some other event where this code should be running from?

[RuralGuy]

That link code was designed to run in the 1st Form that opens the Next Form. In your case I would leave a generic RecordSource on the 2nd Form that pulls all records and use the OnLoad event and OpenArgs argument to do a .FindFirst.

[cyclokj]

Thank you. I finally got this to work!! As a note for others who may read this post, the new form does not need to have a default record source - it works either way. The thing that was tripping me up was the bookmark portion of the code. I found this example in Microsoft Visual Basic Help - topic RecordsetClone Property:

Sub SupplierID_AfterUpdate()
Dim rst As Recordset
Dim strSearchName As String

Set rst = Me.RecordsetClone
strSearchName = Str(Me!SupplierID)
rst.FindFirst "SupplierID = " & strSearchName
If rst.NoMatch Then
MsgBox "Record not found"
Else
Me.Bookmark = rst.Bookmark
End If
rst.Close
End Sub

Revising this code on the new form's on load event gives the result I was looking for - landing on a specific record within a form where the recordsouce is determined on open.