Populate Subreport Controls Based On User Input

[unifyzero]

Problem: On 1 report I’d like to pull up multiple subreports based on user input. My preference would be to have a form where users select 3 options and then open a report which will populate the subreport controls with the appropriate reports based off the user’s input.



I’ve found a few possible solutions on the internet, but honestly they’re over my head and I’m not sure if I can implement them or if they’ll even work. I know very little VBA and no SQL. If someone can explain it to me in children’s terms or give me a sample code that I can tweak, that would be awesome.

I've included a sample of what I have so far. I'm sure that the design is not the best, so I'm willing to accept feedback on it; however, I understand what I currently have and don't want to go too far down the rabbit hole.


Background: I maintain 3 systems which each have multiple configurations. For each configuration different documentation and software is required. I want users to be able to input which configuration they have of each system and then get a report showing what documentation and software they need.

[ranman256]

Your query would be based on the form...
select * from table where [clientID] = forms!frmRpt!cboClient

your report would use this query.

[unifyzero]

ranman256, thank you for your input.

Luckily one of my coworkers was able to figure it out. Here's what we did:

- Created a form with 3 combo boxes to select the configuration of each system.
- Created a table for each combo box to pull values from
- On the form created a button that runs a Select Case for each combo box. The case determines which configuration to select and changed the source of the Subreport control to the appropriate query.

I've included a sample of the code below in case it helps someone in the future.

Private Sub Button_CreateReport_Click()


'Check to ensure combo boxes are populated
If IsEmpty(Combo_PC) Or IsNull(Combo_PC) Then
    MsgBox "Must select a PC version", vbInformation, "PC version missing"
    Combo_PC.SetFocus
ElseIf IsEmpty(Combo_RTR) Or IsNull(Combo_Rtr) Then
    MsgBox "Must select a RTR version", vbInformation, "RTR version missing"
    Combo_RTR.SetFocus
    ElseIf IsEmpty(Combo_PRINTER) Or IsNull(Combo_Printer) Then
        MsgBox "Must select an PRINTER version", vbInformation, "PRINTER version missing"
        Combo_PRINTER.SetFocus
    Else:
   
    DoCmd.OpenReport "CombinedReport", acViewReport
   
    Select Case Combo_PC
        Case "1"
            Report_CombinedReport.PCDocSub.SourceObject = "PCDocReport"
            Report_PCDocReport.RecordSource = "PC3DocQuery"
            Report_CombinedReport.PCSwSub.SourceObject = "PCSwReport"
            Report_PCSWReport.RecordSource = "PC3SwQuery"
        Case "2"
            Report_CombinedReport.PCDocSub.SourceObject = "PCDocReport"
            Report_PCDocReport.RecordSource = "PC4DocQuery"
            Report_CombinedReport.PCSwSub.SourceObject = "PCSwReport"
            Report_PCSWReport.RecordSource = "PC4SwQuery"
        Case "3"
            Report_CombinedReport.PCDocSub.SourceObject = "PCDocReport"
            Report_PCDocReport.RecordSource = "PC5DocQuery"
            Report_CombinedReport.PCSwSub.SourceObject = "PCSwReport"
            Report_PCSWReport.RecordSource = "PC5SwQuery"
    End Select