Full Version: Source data from list box
UtterAccess Discussion Forums > Microsoft® Access > Access Reports
luvfuzz
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.
strive4peace
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...
CODE
   '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
   else
       DoCmd.OpenReport "ReportName", acViewPreview
   endif

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:
CODE
   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
CODE
   if len(trim(nz(me.filter,""))) > 0 then
      me.Label_Criteria.Caption = me.filter
      me.Label_Criteria.Visible = true
   else
      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:
CODE
    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)) & ")"
luvfuzz
Thanks for the thorough response. I'll be working through this today.
YI: I've learned extensively from a calendar that you created.
luvfuzz
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.
CODE
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));
luvfuzz
Well...I'm learning. What I wrote above is incorrect. The mFilter should essentially just be the WHERE statement:
!--c1-->
CODE
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?
strive4peace
... WITHOUT the word "where" ...
eportFilter = "[employeeID] IN (5,10,15,20)"
luvfuzz
Works AWESOME, thanks!
strive4peace
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.