Enter Parameter Value

[Mark Cline]

I am having a problem with 'enter parameter value' windows popping up when I
don't want them to. My current setup...

I have a switchboard with buttons to open various reports. The reports are
set so that their "on open" commands open [Forms]![ReportCreator]. On the
ReportCreator form I have two unbound text boxes whose names are used as
criteria in a query to create a date range for the query. I also have an
"Okay" button that sets the visibility property of the form to "no" once
clicked so that a report over the entered date range will appear. I also
have a "Cancel" button that is set to close the ReportCreator form once
clicked. When I click Cancel the ReportCreator form closes but "Enter
Paramter Value" windows still pop up despite my not wanting to open that
particular report. Is there some way I can get the "Enter Parameter Value"
screens to stop popping up when I click "Cancel"? Thanks in advance...

 

[csergent]

Mark,
You might want to try this: Use your forms to enter the parameters by doing
the following, (I will use a dummy form created from scratch):
1. Create a form called frmParameter
2. To keep it simple, let's add a text box: txtValue1
3. Create a table called tblParam
4. For the table fields create fldDate and make it a date data type; for the
second field, name it fldName and make it a text field with 15 characters.
Create a primary key with an autonumber(I just like to do that).
eter
5. Enter a couple of valid dates in the date field and some names.
6. Write down one of these dates.
7. Create a query called qryParameter Use tblParameter as a source and add
the two fields from the table.
8. In the criteria of fldDate, enter: =[Forms]![frmParameter]![txtValue1]
9. Save the query.
10. Open the form and add a button to run qryParameter
11. Enter one of the dates that you entered in the text box and then click
the button that you just added.
12. This will run the query and display the record with the date tha you
just typed in.
13. Once you have verified that this works, you can create a report that
references the query. Note: Don't close the form when you run the report,
instead run the report from the form.
14. This will allow you to use parameters without popups. You can make it
more complex than this, but I wanted to keep it to one variable.

Let me know if you have any questions about these steps; or if you get a
poput when you apply them.

Chris

 

[Mark Cline]

I don't think this will work because I have multiple reports using the same
form. It seems that when I would do things this way I would need an okay
button for each report I have, in this case 8, which makes things messy. I'm
only having popup problems when I click cancel. Thanks.

Mark,
You might want to try this: Use your forms to enter the parameters by doing
the following, (I will use a dummy form created from scratch):
1. Create a form called frmParameter
2. To keep it simple, let's add a text box: txtValue1
3. Create a table called tblParam
4. For the table fields create fldDate and make it a date data type; for the
second field, name it fldName and make it a text field with 15 characters.
Create a primary key with an autonumber(I just like to do that).
eter
5. Enter a couple of valid dates in the date field and some names.
6. Write down one of these dates.
7. Create a query called qryParameter Use tblParameter as a source and add
the two fields from the table.
8. In the criteria of fldDate, enter: =[Forms]![frmParameter]![txtValue1]
9. Save the query.
10. Open the form and add a button to run qryParameter
11. Enter one of the dates that you entered in the text box and then click
the button that you just added.
12. This will run the query and display the record with the date tha you
just typed in.
13. Once you have verified that this works, you can create a report that
references the query. Note: Don't close the form when you run the report,
instead run the report from the form.
14. This will allow you to use parameters without popups. You can make it
more complex than this, but I wanted to keep it to one variable.

Let me know if you have any questions about these steps; or if you get a
poput when you apply them.

Chris

I am having a problem with 'enter parameter value' windows popping up when I
don't want them to. My current setup...

I have a switchboard with buttons to open various reports. The reports are
set so that their "on open" commands open [Forms]![ReportCreator]. On the
ReportCreator form I have two unbound text boxes whose names are used as
criteria in a query to create a date range for the query. I also have an
"Okay" button that sets the visibility property of the form to "no" once
clicked so that a report over the entered date range will appear. I also
have a "Cancel" button that is set to close the ReportCreator form once
clicked. When I click Cancel the ReportCreator form closes but "Enter
Paramter Value" windows still pop up despite my not wanting to open that
particular report. Is there some way I can get the "Enter Parameter Value"
screens to stop popping up when I click "Cancel"? Thanks in advance...

 

[John Spencer]

On Open Event of the report can be cancelled.

So, if you check to see if the form is open you can cancel the report by
setting Cancel = True [Forms]![ReportCreator] is not open.

OR Change the ReportCreator form.
Add a control (invisible) CheckBox_DoIt that the Ok button sets to True and
the Cancel button sets to false.
Change the Cancel button's code to hide the form (just as the OK button
does)
In the On Open code of the report, check the value of
Forms!ReportCreator!CheckBox_DoIt. If it is False then set Cancel = True
and close [Forms]![ReportCreator].

You will need to trap the 2501 error that the cancel generates in your
calling code (from the switchboard).

I don't think this will work because I have multiple reports using the same
form. It seems that when I would do things this way I would need an okay
button for each report I have, in this case 8, which makes things messy.
I'm
only having popup problems when I click cancel. Thanks.

Mark,
You might want to try this: Use your forms to enter the parameters by
doing
the following, (I will use a dummy form created from scratch):
1. Create a form called frmParameter
2. To keep it simple, let's add a text box: txtValue1
3. Create a table called tblParam
4. For the table fields create fldDate and make it a date data type; for
the
second field, name it fldName and make it a text field with 15
characters.
Create a primary key with an autonumber(I just like to do that).
eter
5. Enter a couple of valid dates in the date field and some names.
6. Write down one of these dates.
7. Create a query called qryParameter Use tblParameter as a source and
add
the two fields from the table.
8. In the criteria of fldDate, enter: =[Forms]![frmParameter]![txtValue1]
9. Save the query.
10. Open the form and add a button to run qryParameter
11. Enter one of the dates that you entered in the text box and then
click
the button that you just added.
12. This will run the query and display the record with the date tha you
just typed in.
13. Once you have verified that this works, you can create a report that
references the query. Note: Don't close the form when you run the report,
instead run the report from the form.
14. This will allow you to use parameters without popups. You can make it
more complex than this, but I wanted to keep it to one variable.

Let me know if you have any questions about these steps; or if you get a
poput when you apply them.

Chris

I am having a problem with 'enter parameter value' windows popping up
when I
don't want them to. My current setup...

I have a switchboard with buttons to open various reports. The reports
are
set so that their "on open" commands open [Forms]![ReportCreator]. On
the
ReportCreator form I have two unbound text boxes whose names are used
as
criteria in a query to create a date range for the query. I also have
an
"Okay" button that sets the visibility property of the form to "no"
once
clicked so that a report over the entered date range will appear. I
also
have a "Cancel" button that is set to close the ReportCreator form once
clicked. When I click Cancel the ReportCreator form closes but "Enter
Paramter Value" windows still pop up despite my not wanting to open
that
particular report. Is there some way I can get the "Enter Parameter
Value"
screens to stop popping up when I click "Cancel"? Thanks in advance...

 

[Ken Sheridan]

The following is an example of another approach to doing this sort of thing,
in this case filtering a report by LastName from a dialogue form. The
report's RecordSource is a query without parameters as the report is filtered
by means of its Filter property.

In the report's Open event procedure:

Private Sub Report_Open(Cancel As Integer)

Const FORMNOTOPEN = 2450
Dim frm As Form

On Error Resume Next
Set frm = Forms!frmNameDlg
If Err = FORMNOTOPEN Then
DoCmd.OpenForm "frmNameDlg", OpenArgs:=Me.Name
Cancel = True
Else
If Err <> 0 Then
' unknown error
MsgBox Err.Description, vbExclamation, "Error"
Else
Me.Filter = "LastName= """ & frm.txtLastName & """"
Me.FilterOn = True
DoCmd.Close acForm, frm.Name
End If
End If

End Sub

In the Click event of the command button on the form to open the report:

Private Sub cmdOpenReport_Click()

DoCmd.OpenReport Me.OpenArgs, acViewPreview

End Sub

Closing the dialogue form with a Cancel button, or in any other way, is fine
because no attempt to open the report is then made by the form.

All you'd need to do in your case, apart from changing the form name in the
code, is to amend the line which sets the report's Filter property to
something like:

Me.Filter = "MyDate >= # " & Format(frm.txtStartDate,"mm/dd/yyyy") & "# " & _
"And MyDate < # " & Format(frm.txtStartDate,"mm/dd/yyyy") & "# +1"

Using the above means of defining a date range is more reliable than a
BETWEEN….AND operation as it allows for rows with dates on the last day of
the range which might contain a non-zero time-of-day element, which sometimes
happens inadvertently (the usual culprit is the inappropriate use of the
Now() function as a default value). The formatting of the date values
internationalizes the application because date literals in Access must be in
US short date format or an otherwise internationally unambiguous format.

Ken Sheridan
Stafford, England

I am having a problem with 'enter parameter value' windows popping up when I
don't want them to. My current setup...

I have a switchboard with buttons to open various reports. The reports are
set so that their "on open" commands open [Forms]![ReportCreator]. On the
ReportCreator form I have two unbound text boxes whose names are used as
criteria in a query to create a date range for the query. I also have an
"Okay" button that sets the visibility property of the form to "no" once
clicked so that a report over the entered date range will appear. I also
have a "Cancel" button that is set to close the ReportCreator form once
clicked. When I click Cancel the ReportCreator form closes but "Enter
Paramter Value" windows still pop up despite my not wanting to open that
particular report. Is there some way I can get the "Enter Parameter Value"
screens to stop popping up when I click "Cancel"? Thanks in advance...