Open query based report on Form with matching record

[amycclark]

This is my data:

Table: "Facility Info"
Data in the table: "facility", "city", "date", etc.
Query: "Q Facility"
Report: "R Facility"
Form: "Main Form" is where the data is entered that goes into the "Facility Info" table.

In the "Main Form" there is a dropdown box where I can select the "facility".

I would like to add a button to this form that opens my report "R Facility".
But this report is a collection of all the facilities and I would like it to just report the ones for the facility that I selected from the dropdown box on my "Main Form".

Any help is appreciated!!!
~Amy

 

[pbaldy]

How about

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

 

[amycclark]

This seems to be getting me in the right direction...

I have the below code as an event procedure in the "On Click" properties part of my button. But I get an error says "missing syntax error (missing operator) in query expression 'Facility = XXXX'

Private Sub OpenRptSingle_Click()
On Error GoTo Err_OpenRptSingle_Click

DoCmd.OpenReport "R Facility", , , "Facility = " & Me.Facility

Exit_OpenRptSingle_Click:
Exit Sub
Err_OpenRptSingle_Click:
MsgBox Err.Description
Resume Exit_OpenRptSingle_Click

End Sub


Can you see what I am missing?
~Amy

 

[pbaldy]

What data type is the Facility field? If text, you need the delimiters as noted on the link.

 

[pr2-eugin]

Since Facility is a String, your code should be..

Private Sub OpenRptSingle_Click()
On Error GoTo Err_OpenRptSingle_Click
    DoCmd.OpenReport "R Facility", , , "Facility =[COLOR=Red][B] '[/B][/COLOR]" & Me.Facility[COLOR=Red][B] & "'"[/B][/COLOR]
Exit_OpenRptSingle_Click:
    Exit Sub
Err_OpenRptSingle_Click:
    MsgBox Err.Description
    Resume Exit_OpenRptSingle_Click
End Sub

 

[pbaldy]

I was too slow again?

 

[pr2-eugin]

Don't think so.. You were super fast.. I might have posted probably 10 or 15 seconds after you..

 

[amycclark]

Well thanks Pauls! I am getting close... I pasted below my code revisions... I added the & and single quotes, just as you initially said so now it runs but doesn't open the form, it prints it! oy.

Private Sub OpenRptSingle_Click()
On Error GoTo Err_OpenRptSingle_Click

DoCmd.OpenReport "Enforcement Report", , , "Facility = '" & Me.Facility & "'"

Exit_OpenRptSingle_Click:
Exit Sub
Err_OpenRptSingle_Click:
MsgBox Err.Description
Resume Exit_OpenRptSingle_Click

End Sub

 

[amycclark]

oooh.... I fixed it myself by adding acViewPreview
If you only knew how proud I am of myself for this one thing! lol!
Thanks a bunch!



Private Sub OpenRptSingle_Click()
On Error GoTo Err_OpenRptSingle_Click

DoCmd.OpenReport "Enforcement Report", acViewPreview, , "Facility = '" & Me.Facility & "'"

Exit_OpenRptSingle_Click:
Exit Sub
Err_OpenRptSingle_Click:
MsgBox Err.Description
Resume Exit_OpenRptSingle_Click

End Sub

 

[pbaldy]

Happy to help Amy!