Parameter dialogue instead of my form problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I'm using Access 2003

I've a query which feeds a report and i'm using a form with a drop-down
allowing a user to select a criteria to filter the report.

In query design view, I have added [Forms]![YourFormName]![FormObjectName]
to the Criteria field.

When I run this query, i expect to see the 'YourFormName' pop-up, but
instead I get the 'enter parameter value' dialogue, with the title
'[Forms]![YourFormName]![FormObjectName] '.

I'm struggling to figure out why. Any ideas where i'm going wrong?

Thank you. Paul
 
Paulc said:
Hi, I'm using Access 2003

I've a query which feeds a report and i'm using a form with a
drop-down allowing a user to select a criteria to filter the report.

In query design view, I have added
[Forms]![YourFormName]![FormObjectName] to the Criteria field.

When I run this query, i expect to see the 'YourFormName' pop-up, but
instead I get the 'enter parameter value' dialogue, with the title
'[Forms]![YourFormName]![FormObjectName] '.

I'm struggling to figure out why. Any ideas where i'm going wrong?

Thank you. Paul
Click to expand...

That form reference will not cause the form to open. It needs to be open BEFORE
the query is executed.

The standard way is to provide the form where the user makes a selection and
then presses a button to open the report.
 
Thanks, that makes sense.

In form CategoryListFrm, dropdown named CategoryNameList contains the
category names I wish to provide as report filter options. The click event
for the button is:

Private Sub Command2_Click()
Me.Visible = False
DoCmd.OpenReport "0607 Category by Commodity Full", acViewPreview
End Sub

and the line in my query is [Form]![CategoryListFrm]![CategoryListName]

It seems when i run my form the CategoryListName selection is not being
passed to the query as when I type the exact category name into the criteria
field the report filters accordingly. Currently the report shows everything
in preview if I run from my form.

Are there any clues on my method that may help solve?

Thanks, Paul.

Rick Brandt said:
Paulc said:
Hi, I'm using Access 2003

I've a query which feeds a report and i'm using a form with a
drop-down allowing a user to select a criteria to filter the report.

In query design view, I have added
[Forms]![YourFormName]![FormObjectName] to the Criteria field.

When I run this query, i expect to see the 'YourFormName' pop-up, but
instead I get the 'enter parameter value' dialogue, with the title
'[Forms]![YourFormName]![FormObjectName] '.

I'm struggling to figure out why. Any ideas where i'm going wrong?

Thank you. Paul
Click to expand...

That form reference will not cause the form to open. It needs to be open BEFORE
the query is executed.

The standard way is to provide the form where the user makes a selection and
then presses a button to open the report.
Click to expand...
 
Paulc said:
Thanks, that makes sense.

In form CategoryListFrm, dropdown named CategoryNameList contains the
category names I wish to provide as report filter options. The click
event for the button is:

Private Sub Command2_Click()
Me.Visible = False
DoCmd.OpenReport "0607 Category by Commodity Full", acViewPreview
End Sub

and the line in my query is
[Form]![CategoryListFrm]![CategoryListName]
Click to expand...

That needs to be [Forms] not [Form].

(note the "s")
 
Paul:

The simplest way is, as Rick said, to open the form first and then open the
report with a button on the form. You can if you wish set the report up to
automatically open the form, however, by including code in the report's Open
event procedure:

Const FORMNOTOPEN = 2450
Dim frm As Form

On Error Resume Next
Set frm = Forms!YourFormName
If Err = FORMNOTOPEN Then
DoCmd.OpenForm "YourFormName", OpenArgs:=Me.Name
Cancel = True
Else
If Err <> 0 Then
' unknown error
MsgBox Err.Description, vbExclamation, "Error"
End If
End If

If you wish you can close the form in the report's Close event procedure with:

DoCmd.Close acForm, "YourFormName"

As the Click event procedure for the button on the form use:

Const REPORTCANCELLED = 2501

On Error Resume Next
DoCmd.OpenReport Me.OpenArgs, acViewPreview
Select Case Err.Number
Case 0
' no error so do nothing
Case REPORTCANCELLED
' anticipated error so do nothing
Case Else
'unknown error so inform user
MsgBox Err.Description
End Select

Because the report passes its name to the form as the OpenArgs property this
form can be used with more than one report which takes the same parameter.
Note that the form's code handles the error which would occur if the opening
of the report is cancelled. This is to cater for reports whose opening is
cancelled in their NoData event procedure if there is nothing to report. If
you open the report directly in code from elsewhere in your application you'd
also need to handle this error as the code in the report's Open event
procedure cancels the opening of the report if the dialogue form is not open,
so say you want to open the report from a button on a form which provides a
choice of available reports you'd use code the same as the above but instead
of Me.OpenArgs you'd put the name of the report or more likely a reference to
a control on the form in which you select the report.

Ken Sheridan
Stafford, England
 
Thank you for your reply Ken.
I shall explore this shortly.

Paul.

Ken Sheridan said:
Paul:

The simplest way is, as Rick said, to open the form first and then open the
report with a button on the form. You can if you wish set the report up to
automatically open the form, however, by including code in the report's Open
event procedure:

Const FORMNOTOPEN = 2450
Dim frm As Form

On Error Resume Next
Set frm = Forms!YourFormName
If Err = FORMNOTOPEN Then
DoCmd.OpenForm "YourFormName", OpenArgs:=Me.Name
Cancel = True
Else
If Err <> 0 Then
' unknown error
MsgBox Err.Description, vbExclamation, "Error"
End If
End If

If you wish you can close the form in the report's Close event procedure with:

DoCmd.Close acForm, "YourFormName"

As the Click event procedure for the button on the form use:

Const REPORTCANCELLED = 2501

On Error Resume Next
DoCmd.OpenReport Me.OpenArgs, acViewPreview
Select Case Err.Number
Case 0
' no error so do nothing
Case REPORTCANCELLED
' anticipated error so do nothing
Case Else
'unknown error so inform user
MsgBox Err.Description
End Select

Because the report passes its name to the form as the OpenArgs property this
form can be used with more than one report which takes the same parameter.
Note that the form's code handles the error which would occur if the opening
of the report is cancelled. This is to cater for reports whose opening is
cancelled in their NoData event procedure if there is nothing to report. If
you open the report directly in code from elsewhere in your application you'd
also need to handle this error as the code in the report's Open event
procedure cancels the opening of the report if the dialogue form is not open,
so say you want to open the report from a button on a form which provides a
choice of available reports you'd use code the same as the above but instead
of Me.OpenArgs you'd put the name of the report or more likely a reference to
a control on the form in which you select the report.

Ken Sheridan
Stafford, England

Paulc said:
Hi, I'm using Access 2003

I've a query which feeds a report and i'm using a form with a drop-down
allowing a user to select a criteria to filter the report.

In query design view, I have added [Forms]![YourFormName]![FormObjectName]
to the Criteria field.

When I run this query, i expect to see the 'YourFormName' pop-up, but
instead I get the 'enter parameter value' dialogue, with the title
'[Forms]![YourFormName]![FormObjectName] '.

I'm struggling to figure out why. Any ideas where i'm going wrong?

Thank you. Paul
Click to expand...
Click to expand...
 
Back
Top