Please Help - Form to select criteria to run report/query

[George van Niekerk]

I have 2 tables, which represents region and state info. I created a FORM
with 2 COMBO BOXES(region & state), which CONTROL SOURCE is the tables and
relevant fields i.e. region, state.

Have created a REPORT with subsequent QUERY to retrieve info. How do i use
the FORM to simultaneously select the state and region. I have used
parameters in the CRITERIA fields of the QUERY - which only gave me
individual dailog boxes for each field for which i have specified a
parameter. Any assistance will be appreciated

 

[fredg]

I have 2 tables, which represents region and state info. I created a FORM
with 2 COMBO BOXES(region & state), which CONTROL SOURCE is the tables and
relevant fields i.e. region, state.

Have created a REPORT with subsequent QUERY to retrieve info. How do i use
the FORM to simultaneously select the state and region. I have used
parameters in the CRITERIA fields of the QUERY - which only gave me
individual dailog boxes for each field for which i have specified a
parameter. Any assistance will be appreciated

Create an unbound form.
Add a combo box.
Set the Row Source of the combo box to include the
Region field.
Name the Combo Box 'cboRegion'.
Set it's Bound column to 1.
Set the Column Width property to 1"

Add a second Combo box, just like above, except set it to the State.
Name this combo box "cboState"

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

Me.Visible = False

Name this form 'ParamForm'.

In the Query (theReport's Record Source) [Region] field criteria line
write:
forms!ParamForm!cboRegion


On the same row, in the [State] column criteria line,
write:
forms!ParamForm!cboState

Make sure the datatype of the bound column of each combo box matches
the datatype of the Field in the query.

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 Region and State.
Click the command button and then report will run.
When the report closes, it will close the form.

 

[George van Niekerk]

"fredg" - You are a star!!

Your method of explaining was excellent.

If possible could you assist with the following. I also want to add a combo
box in the same From to select the "weather" type i.e. HOT/ COLD/ MODERATE. I
have the "Weather" field in a database table (populated via users - FORM)
with the following properties:

Data type = text
Display control = Combo Box
Row source type = value list
Row source = "HOT";"COLD;"MODERATE"
Bound comlom = 1

Your further assitance will really be appreciated.
--
George van Niekerk
(e-mail address removed)

I have 2 tables, which represents region and state info. I created a FORM
with 2 COMBO BOXES(region & state), which CONTROL SOURCE is the tables and
relevant fields i.e. region, state.

Have created a REPORT with subsequent QUERY to retrieve info. How do i use
the FORM to simultaneously select the state and region. I have used
parameters in the CRITERIA fields of the QUERY - which only gave me
individual dailog boxes for each field for which i have specified a
parameter. Any assistance will be appreciated

Create an unbound form.
Add a combo box.
Set the Row Source of the combo box to include the
Region field.
Name the Combo Box 'cboRegion'.
Set it's Bound column to 1.
Set the Column Width property to 1"

Add a second Combo box, just like above, except set it to the State.
Name this combo box "cboState"

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

Me.Visible = False

Name this form 'ParamForm'.

In the Query (theReport's Record Source) [Region] field criteria line
write:
forms!ParamForm!cboRegion


On the same row, in the [State] column criteria line,
write:
forms!ParamForm!cboState

Make sure the datatype of the bound column of each combo box matches
the datatype of the Field in the query.

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 Region and State.
Click the command button and then report will run.
When the report closes, it will close the form.

 

[fredg]

"fredg" - You are a star!!

Your method of explaining was excellent.

If possible could you assist with the following. I also want to add a combo
box in the same From to select the "weather" type i.e. HOT/ COLD/ MODERATE. I
have the "Weather" field in a database table (populated via users - FORM)
with the following properties:

Data type = text
Display control = Combo Box
Row source type = value list
Row source = "HOT";"COLD;"MODERATE"
Bound comlom = 1

Your further assitance will really be appreciated.

Add another combo box to the ParamForm.
name it cboWeather

In the query, write:
forms!ParamForm!cboWeather
as criteria (on the same row as the other criteria).

That's all you need do.

 

[George van Niekerk]

Thank u once again "fredg"!

Another challenge if you may.

How can i.
1).Run the report based on a concatenation/ grouping of specific entries in
the region table, linked to a specific table entry in the province table?
When i for example click the "cboprovince" combo box and select the "north
Province" in the "paramform", the "cboregion" combo box will only display the
"north province's" table entries in the drop down list?

2).Instead of using the "*" in the query criteria to select all table
entries in either Province/ region/ weather, can i create a "select all
provinces" in the province table ans "select all regions" in the region
table? How would i be able to direct the query to be able to recognize that
the "all" refers to "*"?

Pardon my ignorance, but i really do appreciate your assistance and would
most probably rely on your expertise again if thats not a problem/
inconvenience for you.

George van Niekerk
(e-mail address removed)

I have 2 tables, which represents region and state info. I created a FORM
with 2 COMBO BOXES(region & state), which CONTROL SOURCE is the tables and
relevant fields i.e. region, state.

Have created a REPORT with subsequent QUERY to retrieve info. How do i use
the FORM to simultaneously select the state and region. I have used
parameters in the CRITERIA fields of the QUERY - which only gave me
individual dailog boxes for each field for which i have specified a
parameter. Any assistance will be appreciated

Create an unbound form.
Add a combo box.
Set the Row Source of the combo box to include the
Region field.
Name the Combo Box 'cboRegion'.
Set it's Bound column to 1.
Set the Column Width property to 1"

Add a second Combo box, just like above, except set it to the State.
Name this combo box "cboState"

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

Me.Visible = False

Name this form 'ParamForm'.

In the Query (theReport's Record Source) [Region] field criteria line
write:
forms!ParamForm!cboRegion


On the same row, in the [State] column criteria line,
write:
forms!ParamForm!cboState

Make sure the datatype of the bound column of each combo box matches
the datatype of the Field in the query.

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 Region and State.
Click the command button and then report will run.
When the report closes, it will close the form.

 

[fredg]

Thank u once again "fredg"!

Another challenge if you may.

How can i.
1).Run the report based on a concatenation/ grouping of specific entries in
the region table, linked to a specific table entry in the province table?
When i for example click the "cboprovince" combo box and select the "north
Province" in the "paramform", the "cboregion" combo box will only display the
"north province's" table entries in the drop down list?

2).Instead of using the "*" in the query criteria to select all table
entries in either Province/ region/ weather, can i create a "select all
provinces" in the province table ans "select all regions" in the region
table? How would i be able to direct the query to be able to recognize that
the "all" refers to "*"?

Pardon my ignorance, but i really do appreciate your assistance and would
most probably rely on your expertise again if thats not a problem/
inconvenience for you.

George van Niekerk
(e-mail address removed)

I have 2 tables, which represents region and state info. I created a FORM
with 2 COMBO BOXES(region & state), which CONTROL SOURCE is the tables and
relevant fields i.e. region, state.

Have created a REPORT with subsequent QUERY to retrieve info. How do i use
the FORM to simultaneously select the state and region. I have used
parameters in the CRITERIA fields of the QUERY - which only gave me
individual dailog boxes for each field for which i have specified a
parameter. Any assistance will be appreciated

Create an unbound form.
Add a combo box.
Set the Row Source of the combo box to include the
Region field.
Name the Combo Box 'cboRegion'.
Set it's Bound column to 1.
Set the Column Width property to 1"

Add a second Combo box, just like above, except set it to the State.
Name this combo box "cboState"

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

Me.Visible = False

Name this form 'ParamForm'.

In the Query (theReport's Record Source) [Region] field criteria line
write:
forms!ParamForm!cboRegion

On the same row, in the [State] column criteria line,
write:
forms!ParamForm!cboState

Make sure the datatype of the bound column of each combo box matches
the datatype of the Field in the query.

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 Region and State.
Click the command button and then report will run.
When the report closes, it will close the form.

George,
As others might have the same questions, and because the purpose of
these newsgroups is to help everyone, these questions should have been
in a new thread.

As posted here, others won't see these new questions because the
subject line refers to a different problem.

Both of these questions have been answered hundreds of times.
Please search
http://www.groups.google.com

Advanced Group Search
for the Exact phrase
"Cascading Combo Box"
and for
"Add All to a combo box"
(without the quotes).
Search in *Access* newsgroups

Groups.Google is a great resource.

 

[jeq214]

Fred, I used this same method on my parameter form, but how would I enable
the report to show records if a few fields aren't filled in. I have a few
parameter fields, but they may not all fill in those fields. They may only
want to see records for a specific company and leave the rest blank. When I
do this, my report is empty. Any suggestions?

Jon

I have 2 tables, which represents region and state info. I created a FORM
with 2 COMBO BOXES(region & state), which CONTROL SOURCE is the tables and
relevant fields i.e. region, state.

Have created a REPORT with subsequent QUERY to retrieve info. How do i use
the FORM to simultaneously select the state and region. I have used
parameters in the CRITERIA fields of the QUERY - which only gave me
individual dailog boxes for each field for which i have specified a
parameter. Any assistance will be appreciated

Create an unbound form.
Add a combo box.
Set the Row Source of the combo box to include the
Region field.
Name the Combo Box 'cboRegion'.
Set it's Bound column to 1.
Set the Column Width property to 1"

Add a second Combo box, just like above, except set it to the State.
Name this combo box "cboState"

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

Me.Visible = False

Name this form 'ParamForm'.

In the Query (theReport's Record Source) [Region] field criteria line
write:
forms!ParamForm!cboRegion


On the same row, in the [State] column criteria line,
write:
forms!ParamForm!cboState

Make sure the datatype of the bound column of each combo box matches
the datatype of the Field in the query.

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 Region and State.
Click the command button and then report will run.
When the report closes, it will close the form.

 

[Al Campagna]

Jeq,
In the query criteria, use...
Like "*" & Forms!ParamForm!cboRegion & "*"
If cboRegion were left empty, all Regions would be returned.
Use that same syntax for any other parameters you might be leaving blank.

--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Fred, I used this same method on my parameter form, but how would I enable
the report to show records if a few fields aren't filled in. I have a few
parameter fields, but they may not all fill in those fields. They may only
want to see records for a specific company and leave the rest blank. When I
do this, my report is empty. Any suggestions?

Jon

I have 2 tables, which represents region and state info. I created a FORM
with 2 COMBO BOXES(region & state), which CONTROL SOURCE is the tables and
relevant fields i.e. region, state.

Have created a REPORT with subsequent QUERY to retrieve info. How do i use
the FORM to simultaneously select the state and region. I have used
parameters in the CRITERIA fields of the QUERY - which only gave me
individual dailog boxes for each field for which i have specified a
parameter. Any assistance will be appreciated

Create an unbound form.
Add a combo box.
Set the Row Source of the combo box to include the
Region field.
Name the Combo Box 'cboRegion'.
Set it's Bound column to 1.
Set the Column Width property to 1"

Add a second Combo box, just like above, except set it to the State.
Name this combo box "cboState"

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

Me.Visible = False

Name this form 'ParamForm'.

In the Query (theReport's Record Source) [Region] field criteria line
write:
forms!ParamForm!cboRegion


On the same row, in the [State] column criteria line,
write:
forms!ParamForm!cboState

Make sure the datatype of the bound column of each combo box matches
the datatype of the Field in the query.

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 Region and State.
Click the command button and then report will run.
When the report closes, it will close the form.

 

[jeq214]

Thanks Al that worked!

One more thing. How would I set that same criteria to this:
Between [forms]![frmParameters]![RecStartDate] And
[forms]![frmParameters]![RecEndDate]

Access gives me a syntax error when I add Like "*" &

Jeq,
In the query criteria, use...
Like "*" & Forms!ParamForm!cboRegion & "*"
If cboRegion were left empty, all Regions would be returned.
Use that same syntax for any other parameters you might be leaving blank.

--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Fred, I used this same method on my parameter form, but how would I enable
the report to show records if a few fields aren't filled in. I have a few
parameter fields, but they may not all fill in those fields. They may only
want to see records for a specific company and leave the rest blank. When I
do this, my report is empty. Any suggestions?

Jon

On Wed, 31 May 2006 07:36:01 -0700, George van Niekerk wrote:

I have 2 tables, which represents region and state info. I created a FORM
with 2 COMBO BOXES(region & state), which CONTROL SOURCE is the tables and
relevant fields i.e. region, state.

Have created a REPORT with subsequent QUERY to retrieve info. How do i use
the FORM to simultaneously select the state and region. I have used
parameters in the CRITERIA fields of the QUERY - which only gave me
individual dailog boxes for each field for which i have specified a
parameter. Any assistance will be appreciated

Create an unbound form.
Add a combo box.
Set the Row Source of the combo box to include the
Region field.
Name the Combo Box 'cboRegion'.
Set it's Bound column to 1.
Set the Column Width property to 1"

Add a second Combo box, just like above, except set it to the State.
Name this combo box "cboState"

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

Me.Visible = False

Name this form 'ParamForm'.

In the Query (theReport's Record Source) [Region] field criteria line
write:
forms!ParamForm!cboRegion


On the same row, in the [State] column criteria line,
write:
forms!ParamForm!cboState

Make sure the datatype of the bound column of each combo box matches
the datatype of the Field in the query.

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 Region and State.
Click the command button and then report will run.
When the report closes, it will close the form.

 

[Al Campagna]

Jeq,
Try...
Between Nz([Forms]![frmParameters]![RecStartDate],#1/1/1800#) And
Nz([Forms]![frmParameter]![RecEndDate],#1/1/2999#)

Also, make sure to do a Refresh just before running the query/report, so that the just
entered values are updated.

--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Thanks Al that worked!

One more thing. How would I set that same criteria to this:
Between [forms]![frmParameters]![RecStartDate] And
[forms]![frmParameters]![RecEndDate]

Access gives me a syntax error when I add Like "*" &

Jeq,
In the query criteria, use...
Like "*" & Forms!ParamForm!cboRegion & "*"
If cboRegion were left empty, all Regions would be returned.
Use that same syntax for any other parameters you might be leaving blank.

--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Fred, I used this same method on my parameter form, but how would I enable
the report to show records if a few fields aren't filled in. I have a few
parameter fields, but they may not all fill in those fields. They may only
want to see records for a specific company and leave the rest blank. When I
do this, my report is empty. Any suggestions?

Jon

:

On Wed, 31 May 2006 07:36:01 -0700, George van Niekerk wrote:

I have 2 tables, which represents region and state info. I created a FORM
with 2 COMBO BOXES(region & state), which CONTROL SOURCE is the tables and
relevant fields i.e. region, state.

Have created a REPORT with subsequent QUERY to retrieve info. How do i use
the FORM to simultaneously select the state and region. I have used
parameters in the CRITERIA fields of the QUERY - which only gave me
individual dailog boxes for each field for which i have specified a
parameter. Any assistance will be appreciated

Create an unbound form.
Add a combo box.
Set the Row Source of the combo box to include the
Region field.
Name the Combo Box 'cboRegion'.
Set it's Bound column to 1.
Set the Column Width property to 1"

Add a second Combo box, just like above, except set it to the State.
Name this combo box "cboState"

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

Me.Visible = False

Name this form 'ParamForm'.

In the Query (theReport's Record Source) [Region] field criteria line
write:
forms!ParamForm!cboRegion


On the same row, in the [State] column criteria line,
write:
forms!ParamForm!cboState

Make sure the datatype of the bound column of each combo box matches
the datatype of the Field in the query.

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 Region and State.
Click the command button and then report will run.
When the report closes, it will close the form.