Parameters and Combo Box

[knowshowrosegrows]

I want my users to be able to run a report by a date range parameter and be
able to use a drop down to choose a "Provider" name from that field. How do
I write a query that will allow them to do those two things?

 

[fredg]

I want my users to be able to run a report by a date range parameter and be
able to use a drop down to choose a "Provider" name from that field. How do
I write a query that will allow them to do those two things?

Create an unbound form.
Add a combo box.
Set the Row Source of the combo box to include the
ProviderID field and the Provider Name.
Name the Combo Box 'FindProvider'.
Set it's Bound column to 1.
Set it's Column Count property to 2.
Set the Column Width property to 0";1"

Also add 2 unbound text controls.
Set their format to a valid date format.
Name them "StartDate" and "EndDate"

Add a Command Button to the form.
Code the button's click event:

Me.Visible = False

Name this form 'ParamForm'.

In the Query that is the Report's Record Source, on the [ProviderID]
field criteria line write:
forms!ParamForm!FindProvider

As criteria in the query date field write:
Between forms!ParamForm!StartDate and forms!ParamForm!EndDate

Next, code the report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

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

When ready to run the report, open the report.
The form will open and wait for the selection of the Provider and the
entry of the starting and ending dates wanted.
Click the command button and the report will run.
When the report closes, it will close the form.

 

[knowshowrosegrows]

Thanks Fred

I have been noodling with your instructions and gotten pretty far. When I
go into VB and code the DoCmd.OpenForm code I don't get a "acDialog" option.
I get

acDesign
acFrormDS
acFormPivotChart
acFormPivotTable
acNormal
acPreview

I've tried many variations and I am getting an error.

Any suggestions?

--
Thanks

I want my users to be able to run a report by a date range parameter and be
able to use a drop down to choose a "Provider" name from that field. How do
I write a query that will allow them to do those two things?

Create an unbound form.
Add a combo box.
Set the Row Source of the combo box to include the
ProviderID field and the Provider Name.
Name the Combo Box 'FindProvider'.
Set it's Bound column to 1.
Set it's Column Count property to 2.
Set the Column Width property to 0";1"

Also add 2 unbound text controls.
Set their format to a valid date format.
Name them "StartDate" and "EndDate"

Add a Command Button to the form.
Code the button's click event:

Me.Visible = False

Name this form 'ParamForm'.

In the Query that is the Report's Record Source, on the [ProviderID]
field criteria line write:
forms!ParamForm!FindProvider

As criteria in the query date field write:
Between forms!ParamForm!StartDate and forms!ParamForm!EndDate

Next, code the report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

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

When ready to run the report, open the report.
The form will open and wait for the selection of the Provider and the
entry of the starting and ending dates wanted.
Click the command button and the report will run.
When the report closes, it will close the form.

 

[fredg]

Thanks Fred

I have been noodling with your instructions and gotten pretty far. When I
go into VB and code the DoCmd.OpenForm code I don't get a "acDialog" option.
I get

acDesign
acFrormDS
acFormPivotChart
acFormPivotTable
acNormal
acPreview

I've tried many variations and I am getting an error.

Any suggestions?

If you look at my OpenForm code you will notice there are 5 commas.
You must enter each comma.

 

[knowshowrosegrows]

You have been SOOOO helpful. The tools you gave me are working great. Now I
have what I think may be a related question -

I have a form [frmMeasures] where the user inputs a record to a table
[tblUtilization_Measures]. Once they tab out of that form I don't want them
to be able to scroll around in the records, but sometimes they do need to go
back to an established record and edit it.

I have created another form with all the fields [frmUpdate_Measures]. A
query that searches the main table is the record source for the form. I have
created a ParamForm [Update_Param_Form] like you described earlier for them
to use to choose the specific record to update.

I have successfully set it up so when they open the [frmUpdate_Measures]
they get the [Update_Param_Form] and choose the record they want. These
steps will get the query to produce the proper record, but I can't get the
[frmUpdate_Measures] to open to that specific record for them to update.

Any suggestions?

 

[Laurie]

I have been trying to set up a report combo box based on the instructions
below using my field "MgrName". In the row source of the property box I put
the name of my field "MgrName" but the combo box comes up empty. Do I also
need to list each individual managers name in the row source as well?

Thank you
--
Laurie

I want my users to be able to run a report by a date range parameter and be
able to use a drop down to choose a "Provider" name from that field. How do
I write a query that will allow them to do those two things?

Create an unbound form.
Add a combo box.
Set the Row Source of the combo box to include the
ProviderID field and the Provider Name.
Name the Combo Box 'FindProvider'.
Set it's Bound column to 1.
Set it's Column Count property to 2.
Set the Column Width property to 0";1"

Also add 2 unbound text controls.
Set their format to a valid date format.
Name them "StartDate" and "EndDate"

Add a Command Button to the form.
Code the button's click event:

Me.Visible = False

Name this form 'ParamForm'.

In the Query that is the Report's Record Source, on the [ProviderID]
field criteria line write:
forms!ParamForm!FindProvider

As criteria in the query date field write:
Between forms!ParamForm!StartDate and forms!ParamForm!EndDate

Next, code the report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

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

When ready to run the report, open the report.
The form will open and wait for the selection of the Provider and the
entry of the starting and ending dates wanted.
Click the command button and the report will run.
When the report closes, it will close the form.