Use Form to prompt for report criteria

[jhicsupt]

I have a form that I am using to prompt for report criteria. When I run the
query outside of the form, it works fine - prompting me for both criteria.
However when I run from the form, I get #Error#. Can you see what I am doing
wrong? Thanks in advance.

I have two combo boxes that I have put in my underlying query. In the
fields of the query are:
[Forms]![frmSelection Criteria Form]![OfficeNumber]
[Forms]![frmSelection Criteria Form]![Manager]

***
On the OnClick event is the following:

Private Sub Command6_Click()
On Error GoTo Err_command6_Click

Dim stDocName As String
stDocName = "RptItems"
DoCmd.OpenReport stDocName, acPreview

Exit_command6_Click:
Exit Sub

Err_command6_Click:
MsgBox Err.Description
Resume Exit_command6_Click

End Sub

 

[Ofer Cohen]

Can you post the SQL of the record source of the report ?

Where do you get Error?
In a text box in the report?
Do you get any records display in the report?
Did you run the query seperatly while the form is laded and you chosed
values in the combo's? did the query returned values?

 

[Chuck]

I have a form that I am using to prompt for report criteria. When I run the
query outside of the form, it works fine - prompting me for both criteria.
However when I run from the form, I get #Error#. Can you see what I am doing
wrong? Thanks in advance.

I have two combo boxes that I have put in my underlying query. In the
fields of the query are:
[Forms]![frmSelection Criteria Form]![OfficeNumber]
[Forms]![frmSelection Criteria Form]![Manager]

***
On the OnClick event is the following:

Private Sub Command6_Click()
On Error GoTo Err_command6_Click

Dim stDocName As String
stDocName = "RptItems"
DoCmd.OpenReport stDocName, acPreview

Exit_command6_Click:
Exit Sub

Err_command6_Click:
MsgBox Err.Description
Resume Exit_command6_Click

End Sub

The above code is correct.
You didn't show the criteria in the query.

The criteria for the OfficeNumber and Manager fields in the query must refer
back to the form with the combo boxes.

If the query is not already this way,
set the criteria for [OfficeNumber] to [Forms]![{form name}]![{combo box
name}]
(combo box name is probably something like OfficeNumber_CB)
And
set the criteria for [Manager] to [Forms]![{form name}]![{combo box name}]
(combo box name is probably something like Manager_CB)

Chuck
--

 

[Chuck]

I have a form that I am using to prompt for report criteria. When I run the
query outside of the form, it works fine - prompting me for both criteria.
However when I run from the form, I get #Error#. Can you see what I am doing
wrong? Thanks in advance.

I have two combo boxes that I have put in my underlying query. In the
fields of the query are:
[Forms]![frmSelection Criteria Form]![OfficeNumber]
[Forms]![frmSelection Criteria Form]![Manager]

***
On the OnClick event is the following:

Private Sub Command6_Click()
On Error GoTo Err_command6_Click

Dim stDocName As String
stDocName = "RptItems"
DoCmd.OpenReport stDocName, acPreview

Exit_command6_Click:
Exit Sub

Err_command6_Click:
MsgBox Err.Description
Resume Exit_command6_Click

End Sub

You did show the criteria in the query.
Just change the table field names to the combo box names, which I don't think
should be the same.

Chuck
--

 

[jhicsupt]

OK, it's working now - thanks.

However now I have another problem. On this same query, one of the criteria
has an Iif statement in it. So the report is prompting for the field that is
referenced.

Here's my Iif statement:
MinorFamilyA: IIf([FlagYE]="YE Lines","YE Items",[Minor Family])

I am now being prompted for [FlagYE]

I have a form that I am using to prompt for report criteria. When I run the
query outside of the form, it works fine - prompting me for both criteria.
However when I run from the form, I get #Error#. Can you see what I am doing
wrong? Thanks in advance.

I have two combo boxes that I have put in my underlying query. In the
fields of the query are:
[Forms]![frmSelection Criteria Form]![OfficeNumber]
[Forms]![frmSelection Criteria Form]![Manager]

***
On the OnClick event is the following:

Private Sub Command6_Click()
On Error GoTo Err_command6_Click

Dim stDocName As String
stDocName = "RptItems"
DoCmd.OpenReport stDocName, acPreview

Exit_command6_Click:
Exit Sub

Err_command6_Click:
MsgBox Err.Description
Resume Exit_command6_Click

End Sub

You did show the criteria in the query.
Just change the table field names to the combo box names, which I don't think
should be the same.

Chuck

 

[Chuck]

OK, it's working now - thanks.

However now I have another problem. On this same query, one of the criteria
has an Iif statement in it. So the report is prompting for the field that is
referenced.

Here's my Iif statement:
MinorFamilyA: IIf([FlagYE]="YE Lines","YE Items",[Minor Family])

I am now being prompted for [FlagYE]

Your code indicates that MinorFamilyA is a calculated field in the query and
that [FlagYE] and [MinorFamily] are existing fields in the reference data
source for the query, probably a table.

In the vernacular, your statement says that if the field [FlagYE] = "YE Lines"
{simple text} then set the value of the calculated field MinorFamilyA to "YE
Items" {simple text} else set the value of the calculated field MinorFamilyA
to the value {presumably also simple text} of the [Minor Family] field for the
current record.

If that is what you want, then your code is OK.
However
The above code will also have to be repeated in every row for every OR criteria
you may have. (Within every OR statement in the SQL code)

Spelling counts, so check it.

It's obvious that I'm making a lot of guesses about your query.
It would be helpful if you could post the complete SQL code for the query.
If you are like me and don't write code, open the query in design mode then
click on the little down arrow beside the View Icon and then click on SQL View.
All your entries in the design view will be there. That's what needed to truly
help you.

Chuck
--