Multiselect list box as query criteria?

**jforbes** · Jul 20 '16, 08:10 PM

This looks very similar to Need help with combo box loop/open report

I think you are attempting to approach this task by thinking a bit to rigidly. Like a list of things you need to do while using Access to get all these PDF's created. Instead, you could look at it as a bunch of building blocks that you assemble to allow you to create what you need.

1) The first Building Block would be a Report that Stands on it's own. I would re-write your Report so that when it is ran, it returns all the Employees, with accurate data for each Employee's sub-report(s). The Sub-reports would Link to the Main Reports using LinkMasteFields and LinkChildFields . This would mean, removing the Form References from the Query's criteria. ... The Query would have no Criteria, at least not any to limit the Employees returned.

2) The Second Block would be to rework the cmdExportToPDF method so that it Filters the Report to the EmployeeID selected in cmbReportEmploy ee. It would look something like this:

Code:

DoCmd.OpenReport "Letter Report", acViewPreview, , "EmployeeID="Me.cmbReportEmployee.Value , acWindowNormal

How this works... When the Report is opened, it runs the Query, but before it runs the Query, it looks at the supplied Where Condition and if there is anything there, it appends it to the Query. The end result is that the Report is opened and limited to just the Employee that was selected on the ReportOpener Form.

3) I would then create a Building Block that performs your export to PDF. This is just an Example, and it may have some syntax errors:

Code:

Public Function exportEmployeeLetter(Byref EmployeeID AS Long) AS Boolean

   ' Probably should put in some Error Handling
   ' Especially since you are performing some File I/O

   Dim sFirstName AS String
   Dim sLastName AS String

   exportEmployeeLeter = False

   sFirstName = DFirst("FirstName", "Employee", "EmployeeID=" EmployeeID)
   sLastName = DFirst("LastName", "Employee", "EmployeeID=" EmployeeID)
   
   If Len(sFirstName) > 0 and Len(sLastName) > 0 Then
      DoCmd.OpenReport "Letter Report", acViewPreview, , "EmployeeID"=EmployeeID  , acWindowNormal
      DoCmd.OutputTo acOutputReport, "Letter Report", "PDFFormat(*.pdf)", "C:\Users\username\Documents\Stock Letter Reports\" + sFirstName & "_" & sLastName & ".pdf", False, "", , acExportQualityPrint
      DoCmd.Close acReport, "Letter Report", acSaveNo
   End If

   exportEmployeeLeter = True

Now you can call exportEmployeeL etter() from anywhere in your Database and it will create the PDF

4) You can write a single Export routine that would look like this:

Code:

Private Sub cmdExportToPDF_Click()
   Call exportEmployeeLetter(Me.cmbReportEmployee.Value)
End Sub

5) You can then either Modify your ReportOpener Form to use a Multi-select ListBox as you and Phil have discussed, or create a loop based on a couple TextBoxes (or ComboBoxes), with a StartEmployeeID and EndEmployeeID. Honestly, I would use the Start and End Employee TextBoxes (or ComboBoxes). It's kind of old school, but it would be easy to create, use and maintain. Or you could do Both. The Start and End thing would look something like this:

Code:

Private Sub cmdExportAllToPDF_Click

   Dim lStart AS Long
   Dim lEnd AS Long
   Dim lCount AS Long

   lStart = Nz(Me.cmbStartEmployeeID.Value, 0)
   lEnd = Nz(Me.cmbEndEmployeeID.Value, 0)

   if lStart  > 0 And lEnd > 0 Then
      For lCount = lStart To lEnd
         Call exportEmployeeLetter(lCount)
      Next lCount
   End If

End Sub

This is just an option, but it's how I would approach it.

Multiselect list box as query criteria?

Multiselect list box as query criteria?

Comment