Passing Parameters

  • Thread starter Thread starter Erna Henderson
  • Start date Start date
E

Erna Henderson

I have a form with a multi select list box, two date range
boxes, and a preview report command button. I want to
pass the one or more items selected in the multi select
list box along with the from and to date ranges to my
query which opens the report. Here is my code to get the
names from the listbox concatenated into the variable
strSql. How do I get the strSql information to my query?
Any help would be appreciated. Thank you.

Private Sub PreviewReport_Click()
On Error GoTo Err_PreviewReport_Click

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSql As String
Dim stDocName As String

Set frm = Form!frmAgentSelectionForm
Set ctl = frm!MultiSelectListBox
strSql = "Select * from Customer Service where
[Rep]="
For Each varItem In ctl.ItemsSelected
strSql = strSql & ctl.ItemData(varItem) & " OR
[Rep]="
Next varItem

strSql = Left$(strSql, Len(strSql) - 12)

stDocName = "Agent Average Report"
DoCmd.OpenReport stDocName, acPreview

Exit_PreviewReport_Click:
Exit Sub

Err_PreviewReport_Click:
MsgBox Err.Description
Resume Exit_PreviewReport_Click

End Sub
 
Try...

Currentdb().QueryDefs("QueryName").SQL = strSql

Where QueryName is the name of the query on which the
report is based.
 
You are almost there!

What you have so for is EXCELLENT!

What you do is remove ALL the conditions from the query. This makes your
query very clean.

Now what we will do is "pass" the conditions to the report. This means you
can get rid of all the junk forms reference stuff in your query.

So, for the report..remove all the conditions in the reports sol. Keep it
nice an clean.

We now simply send the conditions WHEN WE OPEN the report!

So, try the following (note that if this code is on the same form as the
muti-select box..then you don't need the long forms ref name, but can use
the control directly..so try::



ctl As Control
Dim varItem As Variant
Dim strSql As String
Dim stDocName As String

Set ctl = MultiSelectListBox
For Each varItem In ctl.ItemsSelected
if strSql <> "" then
strSql = strSql & ","
endif
strSql = strSql & ctl.ItemData(varItem)
Next varItem

if strSql <> "" then
strSql = "[Rep] in (" & strSql & ")"
end if

Note that so far, that is code will work if the user selects NONE of the
listbox choices (this is good!). So, you might want to place a bit of text
on the side explain to the user that if you want "all", then the user can
select no values from the listbox (see my screen shots latter for a example
of is idea). Also, in the above case if Rep is NOT a number field, then we
must surround each value with quotes. So, if rep is a text field, then we to
change the above listbox code line:

from:
strSql = strSql & ctl.ItemData(varItem)
to
strSql = strSql & "'" & ctl.ItemData(varItem) & "'"

So, I am surround each value with single quotes..and you must do this ONLY
if rep is a string value.

' now, lets setup our start, and end date conditsions.

if strSql <> "" then
strSql = " and "
endif

strSql = strSql & "AgentDate between #" & format(me.dtStart,"mm/dd/yyyy")
& "#" & _
"and #" & format(me.dtEnd,"mm/dd/yyyy") & "#"

stDocName = "Agent Average Report"
DoCmd.OpenReport stDocName, acPreview,,strSql

Notice for dates..we must surround the values with #.

The other thing to note here is that the strSql is now just a valid sql
"where" clause without the word "where". You are now free to build the query
for the report..but no forms refs for the dates, or even the listbox is now
required. Here is some report screens that use the exact type of code above.

http://www.attcanada.net/~kallal.msn/ridesrpt/ridesrpt.html
 
Back
Top