Subform filtering

[Junior]

Using a parent form to select names from a cbo box
and two subforms to edit tables.
Parent child link is SSN
The Tables may have multiple records for an employee
i.e. multiple occurances of the SSN
So i want to further limit the records available in the subforms. -
How would i limit (filter) the subform records so that they match a field in
the cbo box on the main form?

Say the field is [ADate] (a date field) in the 3rd position in the cbo query
statement and there is also an ADate field in the TblA that i want to edit
in the subform.

 

[John Vinson]

Using a parent form to select names from a cbo box
and two subforms to edit tables.
Parent child link is SSN
The Tables may have multiple records for an employee
i.e. multiple occurances of the SSN
So i want to further limit the records available in the subforms. -
How would i limit (filter) the subform records so that they match a field in
the cbo box on the main form?

Say the field is [ADate] (a date field) in the 3rd position in the cbo query
statement and there is also an ADate field in the TblA that i want to edit
in the subform.

One way to do so would be to have a textbox (which could be invisible)
on the mainform, txtADate, with a Control Source of

=cboMyCombo.Column(2)

and use both the SSN and this control as a joint two-field Master Link
Field: e.g.

SSN;txtADate

as the master and

SSN;ADate

as the child link field.

John W. Vinson[MVP]

 

[Allen Browne]

If you always want to filter the subform by the date field, just set these
properties:
LinkMasterFields: [SSN]; [ADate].Column(2)
LinkChildFields: [SSN], [ADate]
Note that Column() is a zero-based property, so the 3rd column is number 2.


If you need to be able to remove the filter, use the AfterUpdate event of
the ADate combo in the main form to set the Filter property of the form in
the subform:

Private Sub ADate_AfterUpdate
Dim strWhere As String
With Me.[Sub1].Form
If IsDate(Me.ADate.Column(2)) Then
strWhere = "[ADate] = " & Format(Me.ADate.Column(2),
"\#mm/dd/yyyy\#")
.Filter = strWhere
.FilterOn = True
Else
.FilterOn = False
End If
End With
End Sub

Note that if the filter both the main form and the subform, Access will get
confused. Details in this article:
Incorrect filtering of forms and reports
at:
http://members.iinet.net.au/~allenbrowne/bug-02.html

 

[Junior]

John and Allen - thanks - worked great

 

[John Vinson]

LinkMasterFields: [SSN]; [ADate].Column(2)

Cool... didn't know you could do that, Allen. Thanks!

John W. Vinson[MVP]

 

[Allen Browne]

Actually, John, you can't do that. :-(

Tested after reading your reply, and it looks like the combo is not loaded
in time to load the subform. It pops up a paramater box each time you move
record.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

LinkMasterFields: [SSN]; [ADate].Column(2)

Cool... didn't know you could do that, Allen. Thanks!

John W. Vinson[MVP]