UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Source data from list box    
 
   
luvfuzz
post Apr 26 2005, 09:29 AM
Post #1

UtterAccess Veteran
Posts: 461
From: Indiana
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.

I 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.
Go to the top of the page
 
+
strive4peace
post May 8 2005, 10:08 PM
Post #2

UtterAccess VIP
Posts: 20,211
From: Colorado
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


as 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)) & ")"
Go to the top of the page
 
+
luvfuzz
post May 13 2005, 08:05 AM
Post #3

UtterAccess Veteran
Posts: 461
From: Indiana
Thanks for the thorough response. I'll be working through this today.

FYI: I've learned extensively from a calendar that you created.
Go to the top of the page
 
+
luvfuzz
post May 13 2005, 08:25 AM
Post #4

UtterAccess Veteran
Posts: 461
From: Indiana
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));
Go to the top of the page
 
+
luvfuzz
post May 13 2005, 08:33 AM
Post #5

UtterAccess Veteran
Posts: 461
From: Indiana
Well...I'm learning. What I wrote above is incorrect. The mFilter should essentially just be the WHERE statement:

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?
Go to the top of the page
 
+
strive4peace
post May 13 2005, 09:39 AM
Post #6

UtterAccess VIP
Posts: 20,211
From: Colorado
... WITHOUT the word "where" ...

ReportFilter = "[employeeID] IN (5,10,15,20)"
Go to the top of the page
 
+
luvfuzz
post May 13 2005, 01:38 PM
Post #7

UtterAccess Veteran
Posts: 461
From: Indiana
Works AWESOME, thanks!
Go to the top of the page
 
+
strive4peace
post May 13 2005, 01:51 PM
Post #8

UtterAccess VIP
Posts: 20,211
From: Colorado
you're welcome (IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif) happy to help
Go to the top of the page
 
+
« Next Oldest · Access Reports · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 25th May 2013 - 10:22 AM