Full Version: Source data from list box
UtterAccess Discussion Forums > Microsoft® Access > Access Reports
I am working with an employee data report which pulls data for every employeeID. I haven't yet tried, but I believe I could make the report pull a single employee based on lbo/cbo.
would like to be able to run the report pulling several specific employees as selected in a lbo on a form. I would think it is some sort of looping through the lbo rows, but I'm lost on how to set the data source for the report.
Thank You.
It would be best to build a filter string for the report (as opposed to imbedding a parameter in a query)--in this way, you can use comboboxes and listboxes to make it easier for the user to choose criteria and you can ignore criteria when it has not been specified...
Here is an example that tests criteria and builds a filter string to use as a parameter in OpenReport
assuming you are in the code behind the ReportMenu form...
   'tell Access you are going to create a variable to hold text
   dim mFilter as string
   'initialize the variable
   mFilter = ""

   'substitute YOUR controlname in here after "Me."
   'we are testing to see if it is filled out
   'if it is, we are going to make mFilter hold the criteria
   If not IsNull(me.text_controlname ) Then
      mfilter = "[TextFieldname]= '" & me.controlname_for_text  & "'"
   end if
   'test the next control
   If not IsNull(me.date_controlname ) Then
       'if we alread have criteria, add AND to specify that and more
      if mfilter  <> "" then mFilter = mFilter & " AND "
      mFilter = mFilter & "[DateFieldname]= #" & me.controlname_for_date  & "#"
   end if

   'test the next control
   If not IsNull(me.numeric_controlname ) Then
       'if we alread have criteria, add AND to specify that and more
      if mfilter  <> "" then mFilter = mFilter & " AND "
      mfilter = mfilter  &  "[NumericFieldname]= " & me.controlname_for_number
   end if

    if len(mfilter) > 0 then  
       DoCmd.OpenReport "ReportName", acViewPreview, , mfilter
       DoCmd.OpenReport "ReportName", acViewPreview

I have used
TextFieldname to show how text needs to be delimited - with single quote marks (you can also use double quote marks
DateFieldname to show that dates need to be delimited with #
NumericFieldname to show that numbers are not delimited
each time, we are testing to see if a filter control is filled out.
If it is, we are going to see if we first need to add AND (if the filter string already says something)
Then we are going to add the criteria for that filter
make sure that the referenced fields are in the underlying recordset for the report.
Since a filter is applied on the recordset, they do not have to be on the report object (unlike a form)
For a Date Range, you would do:
   If not IsNull(me.date1_controlname ) Then
      mFilter = mFilter & "[DateFieldname]>= #" & me.controlname_for_date1  & "#"
   end if

   If not IsNull(me.date2_controlname ) Then
       'if we alread have criteria, add AND to specify that and more
      if mfilter  <> "" then mFilter = mFilter & " AND "
      mFilter = mFilter & "[DateFieldname] <= #" & me.controlname_for_date2  & "#"
   end if

IF you want to also PRINT the criteria on the report
put a LABEL on your PageFooter
name --> Label_Criteria
then, in the OnFormat event of the ReportHeader
   if len(trim(nz(me.filter,""))) > 0 then
      me.Label_Criteria.Caption = me.filter
      me.Label_Criteria.Visible = true
      me.Label_Criteria.Visible = false
   end if

Has you can see, using a form to collect criteria for a report is the way to go. You have a multi-select listbox. You can use the above as a go-by and here is code to process your choices:
    mFilter = "[Field_Or_Control_Name] IN ("
    For Each varItem In me.listbox_controlname.ItemsSelected
        'delete the line that doesn't apply
        'for text
        mFilter = mFilter & "'" & ctl.ItemData(varItem) & "', "
        'for numbers
        mFilter = mFilter & ctl.ItemData(varItem) & ", "
    Next varItem
    'remove comma and space from the end and add a parenthesis
    mFilter = left(mFilter ,len(mFilter )-2)) & ")"
Thanks for the thorough response. I'll be working through this today.
YI: I've learned extensively from a calendar that you created.
So...should the mFilter essentially look like this after it has processed what is on the form? I've just edited my SQL to include the IN....5, 10, 15, 20 are the employeeIDs selected in my list box.
SELECT tbl_employee.employeeID, tbl_employee.lastName, tbl_employee.firstName, tbl_employee.mInitial,_
tbl_employee.shiftID, tbl_employee.supervisorID, tbl_employee.jobTitleID, tbl_employee.payRate,_
tbl_employee.hireDate, tbl_employee.seniorityDate, tbl_employee.phonePrimary,_
tbl_employee.phoneEmergency, tbl_employee.emergencyContactName, tbl_employee.relationshipID,_
tbl_employee.addressLine1, tbl_employee.addressLine2, tbl_employee.city, tbl_employee.state,_
tbl_employee.zipCode, tbl_employee.shiftPreferenceID1, tbl_employee.shiftPreferenceID2,_
tbl_employee.shiftPreferenceID3, tbl_employee.active
FROM tbl_employee
WHERE (((tbl_employee.employeeID) IN (5, 10, 15, 20) AND ((tbl_employee.active)=True));
Well...I'm learning. What I wrote above is incorrect. The mFilter should essentially just be the WHERE statement:
WHERE [employeeID] IN (5,10,15,20)

because we are simply filling in the where condition of the openReport action. The where condition is basically just adding some criteria to the report's existing data source.
Is this a correct statement?
... WITHOUT the word "where" ...
eportFilter = "[employeeID] IN (5,10,15,20)"
Works AWESOME, thanks!
you're welcome wink.gif happy to help
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.