form to enter multiple criteria for a report

[Guest]

I'm trying to select the criteria for a report with a form. This is what I
have written in each of the two criteria boxes for the query.
[Forms]![Pre VPI Kir Criteria]![ProgramPicker]
[Forms]![Pre VPI Kir Criteria]![EventPicker]

When i open the report, the form opens up correctly, and I can select the
items i want from the combo box. But, the report is already open and blank.
How do i fix this??

Thanks

 

[Dirk Goldgar]

I'm trying to select the criteria for a report with a form. This is
what I have written in each of the two criteria boxes for the query.
[Forms]![Pre VPI Kir Criteria]![ProgramPicker]
[Forms]![Pre VPI Kir Criteria]![EventPicker]

When i open the report, the form opens up correctly, and I can select
the items i want from the combo box. But, the report is already open
and blank. How do i fix this??

You need to have the form open and completed before the report queries
its recordsource. That won't happen if you just open the form normally
in the report's Open event, as the report will go merrily on its way
while you are filling out the form.

There are two different ways I know of to handle this.

1. Open the form first, rather than the report. Then have a button on
the form to open the report once it has been filled out.

2. In the report's Open event, open the form in dialog mode, so that the
report pauses processing until the form is closed or hidden. On the
form, have an OK button that doesn't close the form, but rather hides it
by setting its Visible property to False. A Cancel button just closes
the form. Code in the report's Open event procedure, after having
opened the form, checks to see if the form is still loaded (If
CurrentProject.AllForms("Pre VPI Kir Criteria").IsLoaded Then ...). If
it is not, the procedure cancels the report by setting the event
procedure's Cancel argument to True.

 

[Guest]

I tried Option number one. But the form still comes out blank. I don't think
the query is recieving the information that it needs

I'm trying to select the criteria for a report with a form. This is
what I have written in each of the two criteria boxes for the query.
[Forms]![Pre VPI Kir Criteria]![ProgramPicker]
[Forms]![Pre VPI Kir Criteria]![EventPicker]

When i open the report, the form opens up correctly, and I can select
the items i want from the combo box. But, the report is already open
and blank. How do i fix this??

You need to have the form open and completed before the report queries
its recordsource. That won't happen if you just open the form normally
in the report's Open event, as the report will go merrily on its way
while you are filling out the form.

There are two different ways I know of to handle this.

1. Open the form first, rather than the report. Then have a button on
the form to open the report once it has been filled out.

2. In the report's Open event, open the form in dialog mode, so that the
report pauses processing until the form is closed or hidden. On the
form, have an OK button that doesn't close the form, but rather hides it
by setting its Visible property to False. A Cancel button just closes
the form. Code in the report's Open event procedure, after having
opened the form, checks to see if the form is still loaded (If
CurrentProject.AllForms("Pre VPI Kir Criteria").IsLoaded Then ...). If
it is not, the procedure cancels the report by setting the event
procedure's Cancel argument to True.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Guest]

There is also a third way which cancels the report if the form isn't open and
opens the form. Here's a simple example using the following code in the
report's Open event procedure:

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"
End If
End If

In the Click event procedure of a button on frmNameDlg:

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, vbExclamation, "Error"
End Select

If the report is opened from elsewhere in code be sure to handle the error
there also:

Const REPORTCANCELLED = 2501

On Error Resume Next
DoCmd.OpenReport "YourReportName", 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

Note that when using two parameters in an AND operation the query both need
to have values. If you want to make them optional so that a user can enter
one, both or neither, test for NULL:

[Forms]![Pre VPI Kir Criteria]![ProgramPicker] OR [Forms]![Pre VPI Kir
Criteria]![ProgramPicker] IS NULL

[Forms]![Pre VPI Kir Criteria]![EventPicker] OR [Forms]![Pre VPI Kir
Criteria]![EventPicker] IS NULL

In SQL this will translate to something like:

SELECT *
FROM SomeTable
WHERE
(SomeField = [Forms]![Pre VPI Kir Criteria]![ProgramPicker]
OR [Forms]![Pre VPI Kir Criteria]![ProgramPicker] IS NULL)
AND
(SomeOtherField = [Forms]![Pre VPI Kir Criteria]![EventPicker]
OR [Forms]![Pre VPI Kir Criteria]![EventPicker] IS NULL);

You'll find Access will shift things around a bit in the design grid after
you've saved the query, but it will work just the same.

Ken Sheridan
Stafford, England

 

[Dirk Goldgar]

I tried Option number one. But the form still comes out blank. I
don't think the query is recieving the information that it needs

Let's see the code you're using, and the SQL of the recordsource query
for the report. By "the form still comes out blank", I assume you mean
the *report* comes out blank.

 

[Guest]

I fixed it. It was an error in my part. Thank you for all of your help.