469,331 Members | 5,487 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,331 developers. It's quick & easy.

Filter SubReport on load

Brilstern
208 100+
Ok,

My goal is to filter two SubReports within a master report. I have the Master report that is opened by the below:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdOpenECR_Click()
  2.  
  3.     Dim strFilter As String
  4.     strFilter = "[SSN] = '" & Me.MemberSelect & "'"
  5.         Call DoCmd.OpenReport(ReportName:="rptECR", View:=Access.AcView.acViewReport, wherecondition:=strFilter)
  6.  
  7. End Sub
With the below controls
Expand|Select|Wrap|Line Numbers
  1. =[rank] & " " & [lname] & ", " & [fname] & " " & [mi] & "."
  2. ="XXX-XX-" & Right([ssn],4) & "/" & [MOS]
  3. =[EAS] & "    Section: " & [Instrument]
  4. Date Checked In
  5. SSN (not visible, used for filter)
data sourced from the below table
tblBAnd Members
Expand|Select|Wrap|Line Numbers
  1. Field                      Type
  2. Rank                       Text
  3. FName                      Text
  4. LName                      Text
  5. MI                         Text
  6. SSN                        Text PK
  7. MOS                        Text
  8. EAS                        Date/Time
  9. Instrument                 Text
  10. Date Checked In            Date/Time
  11. Supply Rep                 Text
below are the data sources for the two subreports
tblInstruments
Expand|Select|Wrap|Line Numbers
  1. Field                      Type
  2. Category                   Text
  3. Instrument                 Text
  4. Make                       Text
  5. Model                      Text
  6. Serial                     Text PK
  7. Condition                  Text
  8. Location                   Text
  9. Checked In/Out             Text
  10. Date Checked Out/In        Date/Time
  11. Supply Rep Doing Check Out Text
  12. Purchase Year              Date/Time
tblNon Serialized Gear
Expand|Select|Wrap|Line Numbers
  1. Field                      Type
  2. Category                   Text
  3. Item Name                  Text
  4. Brand                      Text
  5. Condition                  Text
  6. Checked Out                Text
  7. Location                   Text
  8. Date Checked Out           Date/Time
  9. Supply Rep Doing Check Out Text
  10. 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?
Jan 27 '12 #1

✓ answered by 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.

5 4953
Seth Schrock
2,962 Expert 2GB
You could base the subreports on a query that has the criteria of MemberSelect.
Jan 27 '12 #2
NeoPa
32,182 Expert Mod 16PB
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.
Jan 27 '12 #3
Brilstern
208 100+
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
Jan 28 '12 #4
NeoPa
32,182 Expert Mod 16PB
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 ;-)
Jan 28 '12 #5
Brilstern
208 100+
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
Jan 28 '12 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by dd_bdlm | last post: by
1 post views Thread by shaqattack1992-newsgroups | last post: by
17 posts views Thread by =?Utf-8?B?SGVyYg==?= | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by haryvincent176 | last post: by
reply views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.