Filter SubReport on load

[Brilstern]

Ok,

My goal is to filter two SubReports within a master report. I have the Master report that is opened by the below:

Private Sub cmdOpenECR_Click()
 
    Dim strFilter As String
    strFilter = "[SSN] = '" & Me.MemberSelect & "'"
        Call DoCmd.OpenReport(ReportName:="rptECR", View:=Access.AcView.acViewReport, wherecondition:=strFilter)
 
End Sub

With the below controls

=[rank] & " " & [lname] & ", " & [fname] & " " & [mi] & "."
="XXX-XX-" & Right([ssn],4) & "/" & [MOS]
=[EAS] & "    Section: " & [Instrument]
Date Checked In
SSN (not visible, used for filter)

data sourced from the below table
tblBAnd Members

Field                      Type
Rank                       Text
FName                      Text
LName                      Text
MI                         Text
SSN                        Text PK
MOS                        Text
EAS                        Date/Time
Instrument                 Text
Date Checked In            Date/Time
Supply Rep                 Text

below are the data sources for the two subreports
tblInstruments

Field                      Type
Category                   Text
Instrument                 Text
Make                       Text
Model                      Text
Serial                     Text PK
Condition                  Text
Location                   Text
Checked In/Out             Text
Date Checked Out/In        Date/Time
Supply Rep Doing Check Out Text
Purchase Year              Date/Time

tblNon Serialized Gear

Field                      Type
Category                   Text
Item Name                  Text
Brand                      Text
Condition                  Text
Checked Out                Text
Location                   Text
Date Checked Out           Date/Time
Supply Rep Doing Check Out Text
Description                Text

The location on both subreport source tables is a Table/Query List expression built from tblBand Members. (=[rank] & " " & [lname] & ", " & [fname] & " " & [mi] & ".")

How do I filter both subreports on open to match the person that the master report is open upped to?

[Seth Schrock]

You could base the subreports on a query that has the criteria of MemberSelect.

[NeoPa]

You need to determine which fields in [tblBand Members] match, and identify, which fields in [tblInstruments] and [tblNon Serialized Gear] (Separate sets for each of course). When you have this information then you go to each of the Subform/Subreport controls defined on the main report and specify the appropriate sets of fields for each side of the link using the LinkMasterFields and LinkChildFields properties.

This ensures that only matching records are ever populated and displayed in the subreports.

PS. Nicely formulated question. If only more members took that much care to prepare their questions properly we could get so much more quickly to the actual answers.

[Brilstern]

NeoPa,

Took me a little playing around. I ended up creating a new query with the location expression in it. That way it had a reference from the SSN to the location. Linked the fields and wala! I appreciate it always!! Spot on Sir.

Sgt B

[NeoPa]

Always a pleasure - especially working with a properly prepared question though :-)

Maybe if I repeat this some more then other members may pick up on it somewhat ;-)

[Brilstern]

Too Funny,

I have another question that follows into this one but I create a new question and link this one. Don't want to highjack my own question...

Sgt B