Message Request Box

  • Thread starter Thread starter Evelyn Williams
  • Start date Start date
E

Evelyn Williams

Thank you so much for your help in getting my report to work...my supervisor
is very pleased.

My report details various vendors who submitted candidates on a particular
date, and the results from their interview, i.e., selected and hired, or
rejected. Now, with this report, 30 pages print at once. How can I get a
message request box to specify one vendor to review as opposed to the whole
report? I did get this to work once, and for the life of me I cannot remember
what I did.

This is what I need: I click on the report, a message box appears, and I
type in the vendor (Account Temps) and only the information for 'Account
Temps' shows in the report.
 
Thank you so much for your help in getting my report to work...my supervisor
is very pleased.

My report details various vendors who submitted candidates on a particular
date, and the results from their interview, i.e., selected and hired, or
rejected. Now, with this report, 30 pages print at once. How can I get a
message request box to specify one vendor to review as opposed to the whole
report? I did get this to work once, and for the life of me I cannot remember
what I did.

This is what I need: I click on the report, a message box appears, and I
type in the vendor (Account Temps) and only the information for 'Account
Temps' shows in the report.
Click to expand...

Use a form to do this.

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, as well
as a starting and ending date range.

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 2 unbound text controls to the form.
Set their Format property to any valid date format.
Name one "StartDate".
Name the other "EndDate".

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

As Criteria on the DateField, write:
Between forms!ParamForm!StartDate and forms!ParamForm!EndDate

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.
Enter the starting and ending dates.
Click the command button.

The Report will display just those records for the Customer selected.
When the Report closes it will close the form.
 
You could do this a couple of ways:

1. In the query that is the RecordSource for your report, you could add a
parameter in the Criteria of the [Vendor] field. The query might look like:

Parameter [Vendor Name?] Text;
SELECT * FROM yourTable
WHERE [Vendor] = [Vendor Name?]

I generally avoid parameter queries because they require that you know
exactly how the field in the WHERE clause is worded.

2. Another method would be to use the Reports OnOpen event to get the name
of the vendor and filter the report based on that. It might look like:

Private Sub Report_Open(Cancel as Integer)

me.filter = "[Vendor] = '" & inputbox("Vendor name?") & "'"
me.filterOn = true

End Sub

This method has the same problem as #1, you have to know "Account Temps" or
all of the other names.

3. My preferred method would be to use a form to select from among the
vendors who presented candidates on a particular day. You could use a combo
box to select the day, and have a list display the names of the vendors that
had candidates that day. Then, you select a vendor from the list, and use a
command button to open the report filtered based upon the item selected in
the list. To do this you would use the command buttons click event, and fill
in the WhereCondition of the OpenReport method. Something like:

Private Sub Report_Click

dim strCriteria as string

strCriteria = "[Vendor] = '" & me.lstVendors & "'"
docmd.openreport "ReportName", acViewPreview,,strCriteria

End Sub
 
Back
Top