Display a specific record on a subform

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

Greetings:

I have 2 different subforms related to a master form. I display these forms
one at a time as I need them by changing the source object for the subform
control (called fsubOpen) on the Master form.

Forms!frmMaster.fsubOpen.SourceObject = strFormName

In most cases, I only need to open the subform without regard to the record
being displayed. However, I recently came across a situation where I wanted
the record id displayed in a text box on subform1 to determine the record
displayed on subform2. When the user clicks a command button, I would like
subform1 to be replaced by subform2 as the source object for fsubopen and to
display the record with the displayed record ID in the textbox txtRecordID.
If this were a standard openForm situation, one could easily apply the
stLinkCriteria, but i don't think that will work for this. Any ideas would be
welcome.
 
FindFirst on the RecordsetClone of the Form in the subform control. Then
match the Bookmark (unless it's NoMatch.)
 
Hi Allen,

Thanks for your response. I have reproduced the code below that I have come
up with based on your suggestion. When I run this code I get an error stating
"invalid bookmark" as it executes this line of the code

"Forms!frmMaster!fsubOpen.Form.Bookmark = rs.Bookmark".

Can you help me see where I've gone astray. Thanks

Notes: 1. fsubOpen is the subform control on frmMaster
2. lbxOrder is a listbox on frmSubForm1
3. cmdOpenDetailsForm is a command button on frmSubForm1

Private Sub cmdOpenDetailsForm_Click()
On Error GoTo Err_cmdOpenDetailsForm_Click

Dim rs As Recordset
Dim strFormName As String

' Find the currently selected record
Set rs = Forms!frmMaster!fsubOpen.Form.RecordsetClone
rs.FindFirst "[idsOrderID] = " & Str(Nz(Me![lbxOrder], 0))

'change the subform controls source object to the correct subform
strFormName = Me.cboOrder.Column(2)
Forms!frmMaster!fsubOpen.SourceObject = strFormName

'set the bookmark on the subform to the correct record
If Not rs.EOF Then Forms!frmMaster!fsubOpen.Form.Bookmark = rs.Bookmark

Exit_cmdOpenDetailsForm_Click:
Exit Sub

Err_cmdOpenDetailsForm_Click:
MsgBox Err.Description
Resume Exit_cmdOpenDetailsForm_Click
End Sub
 
Back
Top