passing a variable from a form to a report filter

[Shift838]

Can a variable to passed from a form to the filter on a report via code? If so how can this be done?

I beleive on report open it will need to be:

me.filteron = true
me.filter = ?????

I think I may have the syntex wrong on the ???? I have
!Forms......

thanks,

chris

 

[Hmadyson]

You can definitely refer to a form field in a query by saying Forms!formName!object.value, I am assuming that you can use the filter to open the report, by saying

Docmd.OpenReport ReportName, acViewpreview,,WhereCond

The argument before the Where Cond is for Filter, but WhereCond works better and faster since it limits the report to just what you wanted. In the code you should figure out what the value of the object is

set frm = Forms("FormName")
filter = "Whatever=" & frm.controls("ControlName").value
then pass that filter to the report above.

 

[Shift838]

When I use it this way:

Forms!frmname!listbox.value

it does not pass the value, it gives me a prompt to input the value when I try to execute the report.

 

[Hmadyson]

you can only call it that way in a query, via code you need to do it the way I showed.

 

[Shift838]

No i need to set it on the main form that is has the command button to execute the report?

 

[Shift838]

Ok, I got it working with 1 list box via your coding suggestions. what about 2 listboxes to apply two different filters.

 

[johnbucks]

I have been trying to do a very similar thing, and it never seems to work - what am I doing wrong?

my code:
WhereCrit = "T_BookHrs.UserID= " & Forms!F_BookHrs!UserID_TB
stDocName = "R_BookedHrs"
DoCmd.OpenReport stDocName, acViewPreview,,WhereCrit

In the Watches window I get WhereCrit = "T_BookHrs.UserID=321839" which I think looks fine

But when the report opens it still asks me for the UserID and even when I do enter the UserID it seems to ignore it and I get a report with data from all users.

Any suggestions appreciated, John

 

[Hmadyson]

johnbucks, look at the query that is behind the report. It sounds like UserID is a parameter and not a field. The query for the form should be something simple with just all of the fields that you want. Then the where criteria limits the data to a smaller subset.

Chris, you just need to change your criteria to
ListA='xxx' and ListB='yyy'

 

[johnbucks]

Thanks for the reply.

Actually I solved the problem - it was that the query I was using for the report did not have the "visible" checkbox marked for my UserID field.

The query worked OK on its own, but it needed this field to be visible before the report filter would see it.

Thanks again

John