list box report and list box filter

[thatlem]

I have two list boxes on a form, one to drive the report selection and one to filter the data. I have the following statement:

Private Sub GraphListBox_AfterUpdate()
stDocName = Me.GraphListBox
stDocName2 = Me.FacilityUnitListBox
DoCmd.OpenReport stDocName, [acViewPreview], , "FacilityUnit =" & stDocName2
End Sub

The GraphListBox refers to the report and FacilityUnitListBox refers to the unit to filter the report.

The correct report pulls and the filter recognizes the FacilityUnitListBox selection correctly, but I keep getting a syntax error for the filter section of the DoCmd. Have tried variations on the syntax but can't seem to lock it down. The above is the closest I get and is exactly as defined in the Access help. It works if I hard code the facility unit as: "FacilityUnit = 3A" but need the staff to pick from a list for a broader selection.

Any suggestions, it's driving me nuts!!

 

[pbaldy]

For a text value:

DoCmd.OpenReport stDocName, acViewPreview, , "FacilityUnit ='" & stDocName2 & "'"

 

[thatlem]

sorry, that doesn't work.

It pulls back a blank report, as if the filter did not recognize the FacilityUnit. Am not quite sure I understand your use of the quotes. Are they correct?

 

[pbaldy]

They should be. I can't see how it could work if you hard code it like:

FacilityUnit = 3A

because text values are required to be surrounded by quotes, like:

FacilityUnit = '3A'

What is the data type of FacilityUnit in the table? This may also help:

http://www.baldyweb.com/wherecondition.htm

 

[thatlem]

yes, my bad....

filter is "FacilityUnit = '3A'"

field is a text field running off a query.

 

[pbaldy]

Have you set a breakpoint to make sure of what the listbox values are when the code runs? The syntax I posted should be correct for a text value.

 

[thatlem]

am not sure what you mean by breakpoint? or how to define it.

With the code as defined
stDocName2 = Me.FacilityUnitListBox

it does correctly identify the unit highlighted when run, as it will pop up in the error message, and you can hover over the code in the DoCmd line and see the correct selection. So assume that it is choosing the unit correctly. Was just getting syntax errors.

Would I need to define an afterupdate field or similar. I tried to put two afterupdate commands in but the system did not like that....

 

[pbaldy]

Sounds like the variable contains the appropriate value. Can you post the db?

 

[thatlem]

Sorry, can't easily do that as it's on the order of 70 meg and has confidential materials involved.