Open a Report based on Form contents

[pantscat]

Apologies if this is a common post but I have searched and tried to find an answer to this but I cannot.
Oh.. and hi!


I am trying to add a button to one of my forms so that "onclick" it will open a report and filter it based upon the content of the form.

I have tried to use the following code:

DoCmd.OpenReport "rpt_CC_preview", acViewPreview, , [Subject] = Me.Form![Subject]

... but it does not seem to work properly, it opens the form but does not match the "Subject" fields.

I guess it is a syntax prob but I'm rather new to this sort of thing!

Any advice would be gratefully received,

Ant

 

[Rich]

DoCmd.OpenReport "rpt_CC_preview", acViewPreview, , "[Subject] = " & Me![Subject]

 

[pantscat]

Thanks Rich,

That very nearly works - but now when I click my button an "Enter Parameter Value" box appears...

grateful for any ideas,

ta,

Ant

 

[dcx693]

In this statement:
DoCmd.OpenReport "rpt_CC_preview", acViewPreview, , "[Subject] = " & Me![Subject]
the [Subject] piece refers to the name of a field in the query or table that is the recordsource for the "rpt_CC_preview" report. Make sure the query or table feeding into the report has a field called "Subject". Then, the Me![Subject] refers to a control on your form. The name needs to match the name of the control on your form. Check these.

 

[pantscat]

Checked and double-checked this already - they both match as both the form and the report have the same source table.
I'm a wee bit stumped.


Thanks,

Ant

 

[dcx693]

And Subject is the name of the control on the form?

 

[pantscat]

Yes....

Would that cause some confusion? having a control named identically to a field on the report?

 

[dcx693]

It is possible. I always name my controls something other than the underlying field name. I try to follow the Leszynski/Simonyi/Reddick naming conventions. Basically, if you're using a text box, prepend txt to the control name. So in this case, I'd call the text box "txtSubject". Combo boxes get cbo, list boxes get lst, labels get lbl, tables get tbl, etc....

 

[pantscat]

I have tried changing the name of the text box to txt_subject - but I still have the same problem - it brings up an Enter Parameter Value box and I've just noticed that it is asking for the value for one of the records in the subject field.

I'm not sure if that made perfect sense - the txt_subject text box contains the value "Art" and when I press my button to open the report an "Enter Parameter Value" box appears - see attachment.

 

[dcx693]

(WHACK) <-- The sound of me slapping my forehead.

I'm guessing Subject is a text field? Try this:

DoCmd.OpenReport "rpt_CC_preview", acViewPreview, , "[Subject] = '" & Me![Subject] & "'"

 

[pantscat]

dcx - I LOVE YOU! (in manly back-slapping kind of way...)

It works!

Thank you so much... you have saved me many hours of frustration and beating my head against a rather pointy monitor.

Ant

btw - I don't understand what the & " '" did... and I'd be very curious to find out.

 

[dcx693]

You're welcome. Sorry I didn't realize it sooner, thus the (WHACK!) on my forehead.

Whenever you "pass" a value to VBA, you need to properly "delimit" it, so that it knows how to handle it.

Your original version of the command:
[Subject] = Me.Form![Subject]
literally passed "[Subject] = Me.Form![Subject]" to the report. What you actually wanted was to pass "[Subject]='Art'".

Numeric values do not have delimiters. Thus, if Subject were a numeric field, then:
"[Subject] = " & Me![Subject]
woulld've worked fine.

However, it contained text (string values), and those need to be delimited by quotation marks. Thus, you needed to use:
"[Subject] = '" & Me![Subject] & "'"
which could've also been written out as:
"[Subject] = """ & Me![Subject] & """"
Access interprets two consecutive quotation marks like "" to be a single quotation mark.

 

[pantscat]

Yes... that pretty much makes sense to me.

Thank you ever so much for your help - is very much appreciated!

Ant