Dialog Form for Report Not Working

[Dalan]

I designed a dialog box with a combo to select individual vendors from
a form and its related data for print ouput. Though the dialog box
seems to work okay, I apparently do not have the filtering code behind
the OnOpen Event Procedure of the report structured correctly.
Anyone's assistance will be appreciated, although I would like to stay
on the same track.

Here is what I have done:

1. Created a new vendor query (qryVendorRpt) with only the needed
fields including the combo box (cboVendorsMain) from the main form
(frmMain).

2. Of course, created a new vendor report (rptVendorDetail).

3. Created a Dialog Form (frmVendorDialog) that contains the
(cboVendors) combo box with its RowSource set to the Vendors Table
(tblVendors). Also included on the Dialog Form is Cancel (cmdClose)
button and an OK (cmdOkay) button which is set to Me.Visible = False.

4. Then inserted code behind the OnOpen report Event Procedure. The
code below obviously is not working and needs to be tweaked.
Suggestions are welcomed.

5. I want to be able to choose "All Vendors" as well as optionally
choosing a single one for report output.

DoCmd.OpenForm "frmVendorDialog",,,,,acDialog

Me!Vendors = Forms![frmVendorDialog]![cboVendors]

DoCmd.Close acForm, "frmVendorDialog"

If Me!Vendors <> "All Vendors" Then

DoCmd.ApplyFilter , "[Me!Vendors]='" & Vendors & "'"

End If

A couple of error codes noted - 2448 can't assign a value to this
object, 2450 can't find form and a few more.

Thanks for your assistance.

[Allen Browne]

When you open a form in dialog mode, it pauses the current event until the
form is closed. That means your second line does not execute until after the
dialog form is closed. Once it's closed, the attempt to assign the value in
the form cannot work. Even if it did work, Report_Open is too early to
assign a value to a control.

You need to take another approach. Open the dialog first (not from
Report_Open). It does not need to be in design view. Then in the Click event
of the Ok button on your dialog, perform an OpenReport action. This way you
can use the WhereCondition of the OpenReport action to filter the report.
And if no specific vendor is chosen, don't apply a WhereCondition, so you
get all vendors.

Private Sub cmdOk_Click()
Dim strWhere As String

If Not IsNull(Me.cboVendors Then
strWhere = "Vendors = " & Me.cboVendors
End If

DoCmd.OpenReport "rptVendorDetail", acViewPreview, , strWhere
End Sub
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Dalan" <ot***@safe-mail.net> wrote in message
news:50**************************@posting.google.c om...

I designed a dialog box with a combo to select individual vendors from
a form and its related data for print ouput. Though the dialog box
seems to work okay, I apparently do not have the filtering code behind
the OnOpen Event Procedure of the report structured correctly.
Anyone's assistance will be appreciated, although I would like to stay
on the same track.

Here is what I have done:

1. Created a new vendor query (qryVendorRpt) with only the needed
fields including the combo box (cboVendorsMain) from the main form
(frmMain).

2. Of course, created a new vendor report (rptVendorDetail).

3. Created a Dialog Form (frmVendorDialog) that contains the
(cboVendors) combo box with its RowSource set to the Vendors Table
(tblVendors). Also included on the Dialog Form is Cancel (cmdClose)
button and an OK (cmdOkay) button which is set to Me.Visible = False.

4. Then inserted code behind the OnOpen report Event Procedure. The
code below obviously is not working and needs to be tweaked.
Suggestions are welcomed.

5. I want to be able to choose "All Vendors" as well as optionally
choosing a single one for report output.

DoCmd.OpenForm "frmVendorDialog",,,,,acDialog

Me!Vendors = Forms![frmVendorDialog]![cboVendors]

DoCmd.Close acForm, "frmVendorDialog"

If Me!Vendors <> "All Vendors" Then

DoCmd.ApplyFilter , "[Me!Vendors]='" & Vendors & "'"

End If

A couple of error codes noted - 2448 can't assign a value to this
object, 2450 can't find form and a few more.

Thanks for your assistance.