Report Question using Listbox value

  • Thread starter Thread starter Kevin
  • Start date Start date
K

Kevin

I have a report which I generate automatically from an
OnDblClick event in a list box. I have code which
determines which item in the list is selected etc, and
assigns that value to a variable declared in the
procedure. The code to to determine the selected value
works perfectly and all is right with the world, but...I
need to use that value when I generate a report. I am
currently generating the report using a filter. The
reports record source is one of my underlying tables and I
was hoping I could just open the report filtered. to get
just the information I want. The filter is a string value
which is a sql string. I assemble it as follows:

myvalue=.itemdata(itemselected) '**This is sutocode and
Again, this works, the correct value is assigned.

rptfilter="Select whatever from mytable where
mytable.fieldname='" & myvalue & "';"

DoCmd.OpenReport "MyRptName", acViewNormal, rptfilter

I inserted a debug.print myvalue statement just below the
sqlstring and above the docmd command and the assembled
value of rptfilter is correct. If I place that exact
string (as shown in the debug window) into a query in the
query builder and run it, the data I expect is presented
in the query results. When I run this from code as I would
normally, I get all records in the table on which the
report is based, unfiltered. What is going on? Since the
value selected is not static say afterupdate as it is in a
dropdown list or text field, I can't just grab the current
value of the field, I have to determine the value at the
time my code runs.

Thanks in advance!

Kevin

P.S. I can't thank everyone enough, especially the MVPs
who dedicate so much time to answering our questions and
helping us solve problems. This would be a lot tougher
without your assistance!

Thanks again!
 
You're using the FilterName instead of the WhereClause
argument. I think what you're trying to do would be best
done using the WhereClause. That's a way to feed a "WHERE"
clause (without the word WHERE) to a report to get
filtered results.

For example - try this:

myvalue=.itemdata(itemselected)
mywhereclause = "FieldName='" & myvalue & "'"

DoCmd.OpenReport "MyRptName", acViewNormal, ,mywhereclause
 
Back
Top