Use Frm to Open Rpt Based on Qry

[Jeff]

I have a Report that is based on a query that requests a number to search for
a distinct record. How do I create a form that will contain a label for the
number, the number field to search by, and a command button to Print Preview
based on what is input in the number field?

 

[fredg]

I have a Report that is based on a query that requests a number to search for
a distinct record. How do I create a form that will contain a label for the
number, the number field to search by, and a command button to Print Preview
based on what is input in the number field?

First, create a query that will display the fields you wish to show in
the report.

Second, create a report, using the query as it's record source, that
shows the data you wish to display for ALL records.

Let's assume it is a CustomerID number you need as criteria.

Next, make a new unbound form.
Add a combo box that will show the CustomerID field as well as the
Customer Name field (you can use the Combo Box wizard to do so).
Set the Combo box's Column Count property to 2.
Hide the CustomerID field by setting the Combo box's ColumnWidth
property to 0";1"
Make sure the Combo Box Bound Column is the
CustomerID field.
Name this Combo Box "cboFindName".

Add a command button to the form.
Code the button's Click event:
Me.Visible = False
Name this form "ParamForm"

Go back to the query. As criteria, on the Query's CustomerID field
criteria line write:
forms!ParamForm!cboFindName

Code the Report's Open Event:
DoCmd.OpenForm "ParamForm" , , , , , acDialog

Code the Report's Close event:
DoCmd.Close acForm, "ParamForm"

Run the Report.
The report will open the form.

Find the CustomerName in the combo box.

Click the command button.

The Report will display just those records selected.
When the Report closes it will close the form.

 

[Jeff]

Fred,
I have tried this 10 ways to Tuesday and I can't get it to work.

Here is what I did...

First, I created the qry "qryRMAShippingFormReturnReport"

2nd, I created the rpt using the qry as its record source.

3rd I created an unbound frm with a combo box using RMA# as the row source
as follows: SELECT [qryRMAShippingFormReturnReport].[RMA #] FROM
qryRMAShippingFormReturnReport; and I named the combo box "cboFindRMAReturn"

Here is where I am having the biggest problem, I think!!!
Next, I added a command button named "cmdPreviewRMAReturn" and added code as
follows:

Private Sub cmdPreviewRMAReturn_Click()

Me.Visible = False

End Sub

I named the frm "frmFindRMAReturn"

Next, I added criteria to the RMA# field of the qry as follows:
[forms]![frmFindRMAReturn]![cboFindRMAReturn]

Then, I coded the rpt On Open Event as follows:
Private Sub Report_Open(Cancel As Integer)

DoCmd.OpenForm "frmFindRMAReturn", , , , , acDialog

End Sub

but, when I attempt to code the frm On Close Event, I receive the following
error:
Microsoft Visual Basic
Error accessing file. Network connection may have been lost.

I can't seem to get past this part.

 

[fredg]

On Tue, 18 Nov 2008 07:58:00 -0800, Jeff wrote:

Comments in line, below...

Fred,
I have tried this 10 ways to Tuesday and I can't get it to work.

Here is what I did...

First, I created the qry "qryRMAShippingFormReturnReport"
Good.

2nd, I created the rpt using the qry as its record source.

Good.

3rd I created an unbound frm with a combo box using RMA# as the row source
as follows: SELECT [qryRMAShippingFormReturnReport].[RMA #] FROM
qryRMAShippingFormReturnReport; and I named the combo box "cboFindRMAReturn"

Not so good.

You are using the query (which uses this combo box as criteria) to
fill this combo box with it's rowsource data.

The Combo Box rowsource should be based on whatever table (not this
query) contains the [RMA #] from the drop-down.

Select YourTable.[RMA #] from YourTable order by [RMA #];

Note: In your #3 above, you refer to "RMA#" and "RMA #".
Watch your spelling, as they are not the same. I have used [RMA #].
Also, it's not a good idea to use symbols and/or spaces in field
names. "RMANum" or "RMAno" are just as easy to read as "RMA #" and
won't throw an error if you neglect to use the brackets.

Here is where I am having the biggest problem, I think!!!
Next, I added a command button named "cmdPreviewRMAReturn" and added code as
follows:

Private Sub cmdPreviewRMAReturn_Click()

Me.Visible = False

End Sub

I named the frm "frmFindRMAReturn"
Good.

Next, I added criteria to the RMA# field of the qry as follows:
[forms]![frmFindRMAReturn]![cboFindRMAReturn]
Good.

Then, I coded the rpt On Open Event as follows:
Private Sub Report_Open(Cancel As Integer)

DoCmd.OpenForm "frmFindRMAReturn", , , , , acDialog

End Sub
Good.


but, when I attempt to code the frm On Close Event, I receive the following
error:
Microsoft Visual Basic
Error accessing file. Network connection may have been lost.

I can't seem to get past this part.

Perhaps there is some corruption.
May I suggest that you compact and repair the database. Then try
coding the Report's Close event again.

If that fails. make the changes on the form's combo box rowsource
first.
Then, open the form and select a [RMA #] from the drop-down.
Click the command button then open the query. Does the query display
the correct records? If so, close the query and the form.
Re-create the report.
Try again with the coding on this new report.