Pull Up detail record along with all table records

[Mona C. via AccessMonster.com]

I have a listpage which is a subform in datasheet view that lists a subset of
records. I have 17 records that show up on this listpage. When the User
double-clicks on the record row, it opens the detail record form to the
record that was clicked. My problem is that it will only filter to that
record. How can I get it to go to a specific record, but then if I want to
search for another record, on this same detail form or click the next record
button, it will pull in the next record.

I guess I need it to filter in order to pull into the detail record the
record clicked on the listpage, but I also want all the records to be
accessible to this form instead of just the one record as it is doing now.

Here's my code:


Private Sub Form_DblClick(Cancel As Integer)
' This double-clicks on the record on the listpage. It should open the form
' to the record clicked, but Need to bring in all records, so they can be
searched later
' within the form.
' Find the record that matches the control.

Dim rs As Object

Set rs = Me.Recordset.Clone

DoCmd.OpenForm "frmResolution", WhereCondition:="TrackingNo =" &
TrackingNo
rs.FindFirst "[TrackingNo] = " & Str(Nz(Me![tblDataEntry.TrackingNo], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

'close the list page after detail form is opened
DoCmd.Close acForm, "frmListPage"

End Sub

 

[John Nurick]

Hi Mona,

Something like this should do it:

1) When you call DoCmd.OpenForm to open the detail form, pass a
WhereCondition that will return all the records currently displayed in
the subform. Typically, this will involve the field that links the
subform to its parent form, and the value of this field in the current
record in the parent form. This gives the detail form access to all the
records in the subform.

2) In the OpenArgs argument of DoCmd.OpenForm, pass the key value of the
current record in the subform.

3) In the detail form's Open event, use the value passed in OpenArgs to
find the matching record in the usual way.

I have a listpage which is a subform in datasheet view that lists a subset of
records. I have 17 records that show up on this listpage. When the User
double-clicks on the record row, it opens the detail record form to the
record that was clicked. My problem is that it will only filter to that
record. How can I get it to go to a specific record, but then if I want to
search for another record, on this same detail form or click the next record
button, it will pull in the next record.

I guess I need it to filter in order to pull into the detail record the
record clicked on the listpage, but I also want all the records to be
accessible to this form instead of just the one record as it is doing now.

Here's my code:


Private Sub Form_DblClick(Cancel As Integer)
' This double-clicks on the record on the listpage. It should open the form
' to the record clicked, but Need to bring in all records, so they can be
searched later
' within the form.
' Find the record that matches the control.

Dim rs As Object

Set rs = Me.Recordset.Clone

DoCmd.OpenForm "frmResolution", WhereCondition:="TrackingNo =" &
TrackingNo
rs.FindFirst "[TrackingNo] = " & Str(Nz(Me![tblDataEntry.TrackingNo], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

'close the list page after detail form is opened
DoCmd.Close acForm, "frmListPage"

End Sub

 

[Mona C. via AccessMonster.com]

My detail from is not opening at all.

I have the following code on my subform list page. Then on “double-click”
I’m using the openArg to pass the trackingno from the subform list page to
the detail form. I want all the records to be available to the detail form,
but I want the form to open up on the record clicked on thes ubform list page:
here’s my code for the subform list page:


Private Sub Form_DblClick(Cancel As Integer)
' Find the record that matches the OpenArgs control and make available all
records on next form.

Dim rs As Object

Set rs = Me.Recordset.Clone
DoCmd.OpenForm "frmResolution", , , , , , "TrackingNo"

'close the list page after detail form is opened
DoCmd.Close acForm, "frmListPage"

End Sub


I’m passing from my ‘trackingno from the subform . The following code is in
my openForm event of the detail form:

Private Sub Form_Open(Cancel As Integer)
Dim Argv() As String
Dim strName As String

If OpenArgs <> "" Then
Argv = OpenArgs
strName = Argv(0)
TrackingNo = strName
End If

End Sub

Hi Mona,

Something like this should do it:

1) When you call DoCmd.OpenForm to open the detail form, pass a
WhereCondition that will return all the records currently displayed in
the subform. Typically, this will involve the field that links the
subform to its parent form, and the value of this field in the current
record in the parent form. This gives the detail form access to all the
records in the subform.

2) In the OpenArgs argument of DoCmd.OpenForm, pass the key value of the
current record in the subform.

3) In the detail form's Open event, use the value passed in OpenArgs to
find the matching record in the usual way.

I have a listpage which is a subform in datasheet view that lists a subset of
records. I have 17 records that show up on this listpage. When the User

[quoted text clipped - 30 lines]

End Sub

 

[John Nurick]

My detail from is not opening at all.

In fact the code you've included below won't even compile.

I have the following code on my subform list page. Then on “double-click”
I’m using the openArg to pass the trackingno from the subform list page to
the detail form. I want all the records to be available to the detail form,
but I want the form to open up on the record clicked on thes ubform list page:
here’s my code for the subform list page:


Private Sub Form_DblClick(Cancel As Integer)
' Find the record that matches the OpenArgs control and make available all
records on next form.

Dim rs As Object

Set rs = Me.Recordset.Clone
DoCmd.OpenForm "frmResolution", , , , , , "TrackingNo"

The previous line passes the literal string
TrackingNo
in OpenArgs. You need to pass the value of TrackingNo for the current
record. You probably need
DoCmd.OpenForm "frmResolution", , , , , , Me.XXX.Value
where XXX is the name of the control that displays the TrackingNo.

'close the list page after detail form is opened
DoCmd.Close acForm, "frmListPage"

End Sub


I’m passing from my ‘trackingno from the subform . The following code is in
my openForm event of the detail form:

Private Sub Form_Open(Cancel As Integer)
Dim Argv() As String
Dim strName As String

If OpenArgs <> "" Then

This next line won't compile because OpenArgs is a string while Argv is
an array (in fact an array of zero elements).

Argv = OpenArgs
strName = Argv(0)
TrackingNo = strName
End If

End Sub

Try something like this (air code):

Private Sub Form_Open(Cancel As Integer)
Dim rs As DAO.RecordSet

Set rs = Me.Recordsetclone

If Len(Me.OpenArgs) > 0 Then
rs.FindFirst "TrackingNo=" & Me.OpenArgs
If not rs.NoMatch Then
Me.Bookmark = rs.Bookmark
End If
End If

End Sub

Hi Mona,

Something like this should do it:

1) When you call DoCmd.OpenForm to open the detail form, pass a
WhereCondition that will return all the records currently displayed in
the subform. Typically, this will involve the field that links the
subform to its parent form, and the value of this field in the current
record in the parent form. This gives the detail form access to all the
records in the subform.

2) In the OpenArgs argument of DoCmd.OpenForm, pass the key value of the
current record in the subform.

3) In the detail form's Open event, use the value passed in OpenArgs to
find the matching record in the usual way.

I have a listpage which is a subform in datasheet view that lists a subset of
records. I have 17 records that show up on this listpage. When the User

[quoted text clipped - 30 lines]

End Sub