Where = ComboBox results

  • Thread starter Thread starter Gary
  • Start date Start date
G

Gary

I have a form I'm using to limit report data prior to
opening a report.

In the record options I have a combobox that is bound to
the Supervisor table, allowing a user to select the
supervisor to print the report for. My problem is, that
when a supervisor is selected in the combo-box, the
results do not appear in the report- it opens with #error#
in the fields, as if the combobox was null.

I will include the line of code for reference. Should be
one line, any line breaks are unintentional.

=====CODE====

DoCmd.OpenReport "SuperRpt",
ReportDest, , "[SuperDetailQry]![SuperNm] = [Forms]!
[RptSelectFrm]![SelSuper]"


In this line, ReportDest is a defined item based on an
option group- RptSelectFrm is the active form, and
SelSuper is the combobox in question.

Any assistance is greatly appreciated!!!!

Gary
 
Gary said:
I have a form I'm using to limit report data prior to
opening a report.

In the record options I have a combobox that is bound to
the Supervisor table, allowing a user to select the
supervisor to print the report for. My problem is, that
when a supervisor is selected in the combo-box, the
results do not appear in the report- it opens with #error#
in the fields, as if the combobox was null.

I will include the line of code for reference. Should be
one line, any line breaks are unintentional.

=====CODE====

DoCmd.OpenReport "SuperRpt",
ReportDest, , "[SuperDetailQry]![SuperNm] = [Forms]!
[RptSelectFrm]![SelSuper]"


In this line, ReportDest is a defined item based on an
option group- RptSelectFrm is the active form, and
SelSuper is the combobox in question.
Click to expand...


If SuperNm is a numeric field:
DoCmd.OpenReport "SuperRpt", ReportDest, , _
"SuperNm =" & Me.SelSuper

If it's a Text field:
DoCmd.OpenReport "SuperRpt", ReportDest, , _
"SuperNm =""" & Me.SelSuper & """"
 
You've got your last quote mark in the wrong place:

DoCmd.OpenReport "SuperRpt",
ReportDest, , "[SuperDetailQry]![SuperNm] = " & [Forms]!
[RptSelectFrm]![SelSuper]

That will put the VALUE of SelSuper into the filter.
 
Thank you both for your responses. The report now limits
to the supervisor selected! This is wonderful!
If I may impose on your generosity, I do have one quick
follow-up question:
Is it possible to limit multiple criteria in one
statement? I would like to limit by both supervisor and
date if that is possible. I currently have both working
seperately using the strings below, and I'm not sure if I
can combine them this way. I will post an example of the
code below. Again, any line breaks are unintentional-
should be one line.

===Code===
DoCmd.OpenReport "SuperRpt",
ReportDest, , "[SuperDetailQry]![SuperNm]=" & "[Forms]!
[RptSelectFrm]![SelSuper]" And "[SuperDetailQry]![ClsStDt]
Between [Forms]![RptSelectFrm]![FromDate] and [Forms]!
[RptSelectFrm]![ToDate]"

I sincerely appreciate your assistance again! The
assistance of this newsgroup has been of tremendous help
on numerous occasions.

Gary
-----Original Message-----
You've got your last quote mark in the wrong place:

DoCmd.OpenReport "SuperRpt",
ReportDest, , "[SuperDetailQry]![SuperNm] = " & [Forms]!
[RptSelectFrm]![SelSuper]

That will put the VALUE of SelSuper into the filter.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org



I have a form I'm using to limit report data prior to
opening a report.

In the record options I have a combobox that is bound to
the Supervisor table, allowing a user to select the
supervisor to print the report for. My problem is, that
when a supervisor is selected in the combo-box, the
results do not appear in the report- it opens with #error#
in the fields, as if the combobox was null.

I will include the line of code for reference. Should be
one line, any line breaks are unintentional.

=====CODE====

DoCmd.OpenReport "SuperRpt",
ReportDest, , "[SuperDetailQry]![SuperNm] = [Forms]!
[RptSelectFrm]![SelSuper]"


In this line, ReportDest is a defined item based on an
option group- RptSelectFrm is the active form, and
SelSuper is the combobox in question.

Any assistance is greatly appreciated!!!!

Gary
Click to expand...


.
Click to expand...
 
Gary said:
Thank you both for your responses. The report now limits
to the supervisor selected! This is wonderful!
If I may impose on your generosity, I do have one quick
follow-up question:
Is it possible to limit multiple criteria in one
statement? I would like to limit by both supervisor and
date if that is possible. I currently have both working
seperately using the strings below, and I'm not sure if I
can combine them this way. I will post an example of the
code below. Again, any line breaks are unintentional-
should be one line.

===Code===
DoCmd.OpenReport "SuperRpt",
ReportDest, , "[SuperDetailQry]![SuperNm]=" & "[Forms]!
[RptSelectFrm]![SelSuper]" And "[SuperDetailQry]![ClsStDt]
Between [Forms]![RptSelectFrm]![FromDate] and [Forms]!
[RptSelectFrm]![ToDate]"
Click to expand...


You really need to get a grip on the Quoting ;-)
and dates need to be enclosed in # signs. Dates are further
complicated by different international standards so you
should format them specifically to US standards.

. . ., "SuperNm=" & Me!SelSuper & " And ClsStDt Between " &
Format(Me!FromDate, "\#m\/d\/yyyy\#") & " And " &Format
Me!ToDate, "\#m\/d\/yyyy\#")
 
Back
Top