Subforms not on speaking terms!

[Guest]

I have two subforms on the same form. They use different queries with a
common field. In some cases, SF1 (Donors) shows a record with multiple
endowments, which appear in a listbox. SF2 (Endowments) defaults to the
first endowment in the list, which is fine. But I want to click in the
listbox in SF1 and cause SF2 to go to another of the related endowments.

Clicking the list box calls code like this in a general module (hence the
long Forms references.) I get no error messages, but nothing happens in SF2.
I tried breaking the Parent/Child link, but to no avail. I would appreciate
any suggestions:

Public Sub SetEndwmtPointer(CallBox)
Dim rst As DAO.Recordset
Set rst = Forms!SwitchboardNew!EndwmtSubFrm.Form.Recordset
rst.FindFirst "EnFno" = " & Forms!SwitchboardNew!DonorSubFrm!CallBox & "
'"
Forms!SwitchboardNew!EndwmtSubFrm.Form.Bookmark = rst.Bookmark
Forms!SwitchboardNew!EndwmtSubFrm.Form.Requery
End Sub

Thanks in advance.

 

[Marshall Barton]

I have two subforms on the same form. They use different queries with a
common field. In some cases, SF1 (Donors) shows a record with multiple
endowments, which appear in a listbox. SF2 (Endowments) defaults to the
first endowment in the list, which is fine. But I want to click in the
listbox in SF1 and cause SF2 to go to another of the related endowments.

Clicking the list box calls code like this in a general module (hence the
long Forms references.) I get no error messages, but nothing happens in SF2.
I tried breaking the Parent/Child link, but to no avail. I would appreciate
any suggestions:

Public Sub SetEndwmtPointer(CallBox)
Dim rst As DAO.Recordset
Set rst = Forms!SwitchboardNew!EndwmtSubFrm.Form.Recordset
rst.FindFirst "EnFno" = " & Forms!SwitchboardNew!DonorSubFrm!CallBox & "
'"
Forms!SwitchboardNew!EndwmtSubFrm.Form.Bookmark = rst.Bookmark
Forms!SwitchboardNew!EndwmtSubFrm.Form.Requery
End Sub

The quotes in the FindFirst are incorret. If EnFno is a
numeric type field:
rst.FindFirst "EnFno = " & _
Forms!SwitchboardNew!DonorSubFrm.FORM!CallBox
If it's a Text type field:
rst.FindFirst "EnFno = """ & _
Forms!SwitchboardNew!DonorSubFrm.FORM!CallBox & """"

You should use the form's RecordsetClone instead of its
Recordset.
Set rst =
Forms!SwitchboardNew!EndwmtSubFrm.Form.RecordsetClone

Also, you should note that all versions of Access do not
accept the abbreviated syntax for referencing controls on a
subform. Use the more complete syntax above.

You should check if the FindFirst found something before
setting the Bookmark property.

The Requery at the end of the procedure move the current
record to the first record. Making the rest of the
procedure ineffective.

You can shorten the code and make it easier to read by using
a With statement:

Public Sub SetEndwmtPointer(CallBox)
Dim rst As DAO.Recordset
With Forms!SwitchboardNew
Set rst = !EndwmtSubFrm.Form.RecordsetClone
rst.FindFirst "EnFno = " & !DonorSubFrm.FORM!CallBox
If Not rst.NoMatch Then
!EndwmtSubFrm.Form.Bookmark = rst.Bookmark
End If
End With
Set rst = Nothing
End Sub

 

[Jeff]

I have two subforms on the same form. They use different queries with a
common field. In some cases, SF1 (Donors) shows a record with multiple
endowments, which appear in a listbox. SF2 (Endowments) defaults to the
first endowment in the list, which is fine. But I want to click in the
listbox in SF1 and cause SF2 to go to another of the related endowments.

Clicking the list box calls code like this in a general module (hence the
long Forms references.) I get no error messages, but nothing happens in SF2.
I tried breaking the Parent/Child link, but to no avail. I would appreciate
any suggestions:

Public Sub SetEndwmtPointer(CallBox)
Dim rst As DAO.Recordset
Set rst = Forms!SwitchboardNew!EndwmtSubFrm.Form.Recordset
rst.FindFirst "EnFno" = " & Forms!SwitchboardNew!DonorSubFrm!CallBox & "
'"
Forms!SwitchboardNew!EndwmtSubFrm.Form.Bookmark = rst.Bookmark
Forms!SwitchboardNew!EndwmtSubFrm.Form.Requery
End Sub

Thanks in advance.

Hi David.

I have a similar setup.

On the mainform I have a textbox that contains the linking data (Aluno
Ref) My list on subform1 populates that textbox on the mainform. The
subform2 uses that textbox to filter its records. Works very well.

Subform1 has its own recordsource and no master-child relationship. It
has a field called 'L1AlunoRef'
The textbox (called 'MFAlunoRef') on the mainform has:
=subform1.Formulário.L1AlunoRef as its recordsource.
Subform2 has a master-child link as MFAlunoRef-L2AlunoRef

When I click on the field in subform1, the MFAlunoRef on the Mainform is
updated with that Reference Number. That Number is used to filter the
subform2.

I hope this helps you.

Jeff