How to .GoToRecord on another form

[Dean Slindee]

I have a search form (frmMailingSearch) that displays a grid of rows that is
a subset of the entire table. From that grid, the user may give focus to
the selector box of one row, and then click the Select button. I would then
like to show the row in the detail form, by going directly to it. The
detail form may or may not have been instantiated as yet. I want the detail
form to always have it's recordsource as the full underlying table, not just
the one row that we "goto".



The DoCmd.GoToRecord is not working as I want, because "MailingID" is not
actually an offset value, it's the key value of the row. Should I be using
a different cmd or structuring the offset value differently? This code is
in the frmMailingSearch.cmdSelect_Click event.



Dim frm As Form

Dim booFormFound As Boolean

booFormFound = False

For Each frm In Forms

If frm.Name = "frmMailing" Then

booFormFound = True

Exit For

End If

Next frm

If booFormFound = True Then

DoCmd.GoToRecord acDataForm, "frmMailing", acGoTo, MailingID

Else

DoCmd.OpenForm "frmMailing", acNormal

DoCmd.GoToRecord acDataForm, "frmMailing", acGoTo, MailingID

End If



I have also substituted .FindRecord for .GoToRecord, with passing syntax,
but no resultset. Any better ideas would be appreciated.



Thanks in advance,

Dean S

 

[SteveM]

Since you only want to do this once, you could do it in the OnLoad or OnOpen
events. With your DoCmd.OpenForm - supply the ID in the OpenArgs attribute.

In your form's Onload or OnOpen event use a RecordsetClone and Findfirst()
to locate the bookmark of the record matching the ID in Me.OpenArgs then set
the form's recordset bookmark to the one found in the clone.

'Call form and set to ID 1
DoCmd.OpenForm "myForm",,,,,,1

Private Sub Form_Open()
' Find the record that matches OpenArgs
Dim rs As Recordset

Set rs = Me.RecordsetClone
rs.FindFirst "[myID] = " & Me.OpenArgs)
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Steve

 

[Dean Slindee]

Thanks for the tip. Here's the final code that works great!

DoCmd.OpenForm "frmMailing", , , , , , MailingID

Private Sub Form_Load()

If Not IsNull(Me.OpenArgs) Then

'find the record that matches OpenArgs

Dim rs As ADODB.Recordset

Set rs = Me.RecordsetClone

rs.Find "MailingID = " & Me.OpenArgs

If Not rs.EOF Then

Me.Bookmark = rs.Bookmark

End If

End If

End Sub

Since you only want to do this once, you could do it in the OnLoad or
OnOpen
events. With your DoCmd.OpenForm - supply the ID in the OpenArgs
attribute.

In your form's Onload or OnOpen event use a RecordsetClone and Findfirst()
to locate the bookmark of the record matching the ID in Me.OpenArgs then
set
the form's recordset bookmark to the one found in the clone.

'Call form and set to ID 1
DoCmd.OpenForm "myForm",,,,,,1

Private Sub Form_Open()
' Find the record that matches OpenArgs
Dim rs As Recordset

Set rs = Me.RecordsetClone
rs.FindFirst "[myID] = " & Me.OpenArgs)
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Steve

I have a search form (frmMailingSearch) that displays a grid of rows that
is
a subset of the entire table. From that grid, the user may give focus to
the selector box of one row, and then click the Select button. I would
then
like to show the row in the detail form, by going directly to it. The
detail form may or may not have been instantiated as yet. I want the
detail
form to always have it's recordsource as the full underlying table, not
just
the one row that we "goto".



The DoCmd.GoToRecord is not working as I want, because "MailingID" is not
actually an offset value, it's the key value of the row. Should I be
using
a different cmd or structuring the offset value differently? This code
is
in the frmMailingSearch.cmdSelect_Click event.



Dim frm As Form

Dim booFormFound As Boolean

booFormFound = False

For Each frm In Forms

If frm.Name = "frmMailing" Then

booFormFound = True

Exit For

End If

Next frm

If booFormFound = True Then

DoCmd.GoToRecord acDataForm, "frmMailing", acGoTo, MailingID

Else

DoCmd.OpenForm "frmMailing", acNormal

DoCmd.GoToRecord acDataForm, "frmMailing", acGoTo, MailingID

End If



I have also substituted .FindRecord for .GoToRecord, with passing syntax,
but no resultset. Any better ideas would be appreciated.



Thanks in advance,

Dean S