Default on a prompt

D

DanG

I have a form that sets up filters for a report. One is a dropdown box of
the different offices that information is being tracked on. What is the best
way to add a choice to the drop down which says Nationwide and encompasses
all the data from all the offices combined.
 
A

Allen Browne

The simplest way is to teach people to leave the combo blank if they do not
wish to filter on that field. You then ignore the boxes where the user did
not enter anything when you build the WhereCondition for OpenReport.

There's an example here of building up such a string here:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
The example applies the results to the Filter of the form, but it's exactly
the same for the WhereCondition of OpenReport.

If you prefer, you can use a UNION query to add a literal value to the
appropropriate column of the combo, and then ignore that special value when
you build your WhereCondition string. For an example, see:
Adding "All" to a listbox or combobox
at:
http://www.mvps.org/access/forms/frm0043.htm
 
D

DanG

I am trying to add the (All) listing to the drop down as noted in one of your
links. I get it to appear in the drop down, but it wont let me choose it.
There is not about not being able to do it when the primary key of a table is
involved. In this case the CenterID is a primary field. Any suggestions?
 
A

Allen Browne

Clear the ControlSource property of the combo.

If the combo is bound to a field, and the field is a foreign key with
relational integrity, then you will not be able to choose a value that is
not in the RowSource table.
 
D

DanG

The Control Source is clear on the combo box.

What else should I try?

PS - Thank you for helping me.
 
A

Allen Browne

If the combo is unbound, I'm not sure what you mean.
Are you saying you can select any other value, but not the "All"?

What is the data type of the bound field in the combo's RowSource?

Is there anything in the Format property of the combo?

Does it not actually select the All, or do you get stuck there an unable to
move on?

Is there any error message?
 
D

DanG

Bound Column is the CenterID (autonumber), I tried switching Bound Column to
CenterName, which allowed me to choose 'All' but that only got me to the
point of "error executing this command" when I tried to run the report. I
also have filters for Date range. Those work as long as I am picking an
individual center name. (The report shows up in triplicate, which I was
going to post under separate thread after the current issues were fixed).
 
A

Allen Browne

Dan, there is not enough info there to know what's going on.

Take it one step at a time. I take it the combo's RowSource is a UNION
query. When you view the query, you probably see the column left-aligned
(like text) rather than right-aligned (like a number) because of the text in
this column.

Now how are you trying to apply this? Are you using code behind the button,
or a macro? Are you building a WhereCondition string for OpenReport? Or are
you trying to refer to the combo in the the criteria of the query the report
is based on?
 
D

DanG

the info on this combo box is ---->
Rowsource: SELECT CenterID, CenterName FROM tblCenterList UNION Select Null
as AllChoice , "(All)" as Bogus From tblCenterList
ORDER BY CenterID;

Bound Column: 1
Column Count: 2
Column Widths: 0";1"
Limit to List: Yes
AutoExpand: Yes
Enabled: Yes
Locked: No

the event procedure behind the 'Run Report' button is this:
Private Sub Command4_Click()
Me.Visible = False
End Sub

Report Open and Close Event Procedures are ----->

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "ParamForm", , , , , acDialog

End Sub


Private Sub Report_Close()
DoCmd.Close acForm, "Paraform"
End Sub
 
A

Allen Browne

Thanks, Dan. I understand now.

The article at the MVPs site suggests using Null as the key value for the
UNION. This is the hidden column that shows the "(All"), so when you select
"(All)", Access recognises the combo value is Null, but Null doesn't match
anything, so it straightaway disconnects it from the "(All)" and just shows
a blank combo. Yes: I can see that happening.

A better solution would be to create another little table table with 2
fields:
ID Number primary key
TheText Text unique, and required.
Save as (say) tblAll4Combo.
Enter just one record, using -1 for the ID, and (All) for the text field.

Now try a RowSource of:
SELECT CenterID,
CenterName
FROM tblCenterList
UNION ALL
Select ID AS CenterID,
TheText AS CenterName
FROM tblCenterList
ORDER BY CenterID;
You should now be able to select the (All) option in the combo.

Now you will have the job of convincing the query to feed all values to the
report when -1 is chosen:

- If you are building a WhereCondition for OpenReport, your code will just
ignore the -1 value (i.e. don't filter on this field if the combo is -1.)

- If you are trying to make the query read the combo on your form, you will
need to modify the query so it returns all values when the combo is -1.
Switch the query to SQL View (View menu, in query design), locate the WHERE
clause, and change it so it looks like this:
WHERE (([Forms].[Form1].[Combo2] = -1)
OR ([Forms].[Form1].[Combo2] Is Null)
OR (CenterID = [Forms].[Form1].[Combo2]))

I personally prefer the WhereCondition where this is possible: it's a bit
more work to program, but simpler and more efficient to execute.

Let us know how you go.
 
D

DanG

Hey. Thanks for the information. I got halfway there. I added the new
table. I entered in the rowsource information. I got the drop down to work.
Now this is where I show off how little I actually know about Access.

Where do I put this wherecondition, and how do I even code it. I really
dont know much about coding at all.

Thanks again for all the help you have provided me on these issues.

Allen Browne said:
Thanks, Dan. I understand now.

The article at the MVPs site suggests using Null as the key value for the
UNION. This is the hidden column that shows the "(All"), so when you select
"(All)", Access recognises the combo value is Null, but Null doesn't match
anything, so it straightaway disconnects it from the "(All)" and just shows
a blank combo. Yes: I can see that happening.

A better solution would be to create another little table table with 2
fields:
ID Number primary key
TheText Text unique, and required.
Save as (say) tblAll4Combo.
Enter just one record, using -1 for the ID, and (All) for the text field.

Now try a RowSource of:
SELECT CenterID,
CenterName
FROM tblCenterList
UNION ALL
Select ID AS CenterID,
TheText AS CenterName
FROM tblCenterList
ORDER BY CenterID;
You should now be able to select the (All) option in the combo.

Now you will have the job of convincing the query to feed all values to the
report when -1 is chosen:

- If you are building a WhereCondition for OpenReport, your code will just
ignore the -1 value (i.e. don't filter on this field if the combo is -1.)

- If you are trying to make the query read the combo on your form, you will
need to modify the query so it returns all values when the combo is -1.
Switch the query to SQL View (View menu, in query design), locate the WHERE
clause, and change it so it looks like this:
WHERE (([Forms].[Form1].[Combo2] = -1)
OR ([Forms].[Form1].[Combo2] Is Null)
OR (CenterID = [Forms].[Form1].[Combo2]))

I personally prefer the WhereCondition where this is possible: it's a bit
more work to program, but simpler and more efficient to execute.

Let us know how you go.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

DanG said:
the info on this combo box is ---->
Rowsource: SELECT CenterID, CenterName FROM tblCenterList UNION Select
Null
as AllChoice , "(All)" as Bogus From tblCenterList
ORDER BY CenterID;

Bound Column: 1
Column Count: 2
Column Widths: 0";1"
Limit to List: Yes
AutoExpand: Yes
Enabled: Yes
Locked: No

the event procedure behind the 'Run Report' button is this:
Private Sub Command4_Click()
Me.Visible = False
End Sub

Report Open and Close Event Procedures are ----->

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "ParamForm", , , , , acDialog

End Sub


Private Sub Report_Close()
DoCmd.Close acForm, "Paraform"
End Sub
Click to expand...
Click to expand...
 
A

Allen Browne

Good: you have the combo working.

The goal here is to filter a report, so you have 2 options. You either put
the WHERE clause in the report's filter, or you build a WhereCondition
string in code and use it for OpenReport.

Option 1: Modify the report's query.
=========================
Presumably you already have a query that supplies the data to the report,
i.e. the one named in the report's RecordSource property. Open this query in
deisgn view. Then use the View menu to switch it to SQL View. The query
statement will look like this:
SELECT ...
FROM ...
WHERE ...
ORDER BY ...
Locate the WHERE clause, and modify it as shown in the last post.

Option 2: Build the WhereCondition string.
==============================
For an example of how to do that, see:
http://allenbrowne.com/casu-15.html
In that case, strWhere is very simple. It just ends up as something like:
ID = 999
In your case, you will need some code that tests if the combo is null, or if
its value is -1, and if so to ignore the field. For other values, you want
to use it as part of the WhereCondition string.

There's a more comprehensive example involving several combos and text boxes
here:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
(In that example, we didn't add '(All)' to the combo: we just assumed the
user left it blank if they wanted all records.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

DanG said:
Hey. Thanks for the information. I got halfway there. I added the new
table. I entered in the rowsource information. I got the drop down to
work.
Now this is where I show off how little I actually know about Access.

Where do I put this wherecondition, and how do I even code it. I really
dont know much about coding at all.

Thanks again for all the help you have provided me on these issues.

Allen Browne said:
Thanks, Dan. I understand now.

The article at the MVPs site suggests using Null as the key value for the
UNION. This is the hidden column that shows the "(All"), so when you
select
"(All)", Access recognises the combo value is Null, but Null doesn't
match
anything, so it straightaway disconnects it from the "(All)" and just
shows
a blank combo. Yes: I can see that happening.

A better solution would be to create another little table table with 2
fields:
ID Number primary key
TheText Text unique, and required.
Save as (say) tblAll4Combo.
Enter just one record, using -1 for the ID, and (All) for the text field.

Now try a RowSource of:
SELECT CenterID,
CenterName
FROM tblCenterList
UNION ALL
Select ID AS CenterID,
TheText AS CenterName
FROM tblCenterList
ORDER BY CenterID;
You should now be able to select the (All) option in the combo.

Now you will have the job of convincing the query to feed all values to
the
report when -1 is chosen:

- If you are building a WhereCondition for OpenReport, your code will
just
ignore the -1 value (i.e. don't filter on this field if the combo is -1.)

- If you are trying to make the query read the combo on your form, you
will
need to modify the query so it returns all values when the combo is -1.
Switch the query to SQL View (View menu, in query design), locate the
WHERE
clause, and change it so it looks like this:
WHERE (([Forms].[Form1].[Combo2] = -1)
OR ([Forms].[Form1].[Combo2] Is Null)
OR (CenterID = [Forms].[Form1].[Combo2]))

I personally prefer the WhereCondition where this is possible: it's a bit
more work to program, but simpler and more efficient to execute.

Let us know how you go.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

DanG said:
the info on this combo box is ---->
Rowsource: SELECT CenterID, CenterName FROM tblCenterList UNION Select
Null
as AllChoice , "(All)" as Bogus From tblCenterList
ORDER BY CenterID;

Bound Column: 1
Column Count: 2
Column Widths: 0";1"
Limit to List: Yes
AutoExpand: Yes
Enabled: Yes
Locked: No

the event procedure behind the 'Run Report' button is this:
Private Sub Command4_Click()
Me.Visible = False
End Sub

Report Open and Close Event Procedures are ----->

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "ParamForm", , , , , acDialog

End Sub


Private Sub Report_Close()
DoCmd.Close acForm, "Paraform"
End Sub

:

Dan, there is not enough info there to know what's going on.

Take it one step at a time. I take it the combo's RowSource is a UNION
query. When you view the query, you probably see the column
left-aligned
(like text) rather than right-aligned (like a number) because of the
text
in
this column.

Now how are you trying to apply this? Are you using code behind the
button,
or a macro? Are you building a WhereCondition string for OpenReport?
Or
are
you trying to refer to the combo in the the criteria of the query the
report
is based on?

Bound Column is the CenterID (autonumber), I tried switching Bound
Column
to
CenterName, which allowed me to choose 'All' but that only got me to
the
point of "error executing this command" when I tried to run the
report.
I
also have filters for Date range. Those work as long as I am
picking
an
individual center name. (The report shows up in triplicate, which I
was
going to post under separate thread after the current issues were
fixed).

:

If the combo is unbound, I'm not sure what you mean.
Are you saying you can select any other value, but not the "All"?

What is the data type of the bound field in the combo's RowSource?

Is there anything in the Format property of the combo?

Does it not actually select the All, or do you get stuck there an
unable
to
move on?

Is there any error message?

The Control Source is clear on the combo box.

What else should I try?

PS - Thank you for helping me.

:

Clear the ControlSource property of the combo.

If the combo is bound to a field, and the field is a foreign key
with
relational integrity, then you will not be able to choose a
value
that
is
not in the RowSource table.

I am trying to add the (All) listing to the drop down as noted
in
one
of
your
links. I get it to appear in the drop down, but it wont let
me
choose
it.
There is not about not being able to do it when the primary
key
of a
table
is
involved. In this case the CenterID is a primary field. Any
suggestions?

:

The simplest way is to teach people to leave the combo blank
if
they
do
not
wish to filter on that field. You then ignore the boxes where
the
user
did
not enter anything when you build the WhereCondition for
OpenReport.

There's an example here of building up such a string here:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
The example applies the results to the Filter of the form,
but
it's
exactly
the same for the WhereCondition of OpenReport.

If you prefer, you can use a UNION query to add a literal
value
to
the
appropropriate column of the combo, and then ignore that
special
value
when
you build your WhereCondition string. For an example, see:
Adding "All" to a listbox or combobox
at:
http://www.mvps.org/access/forms/frm0043.htm

I have a form that sets up filters for a report. One is a
dropdown
box
of
the different offices that information is being tracked on.
What
is
the
best
way to add a choice to the drop down which says Nationwide
and
encompasses
all the data from all the offices combined.
Click to expand...
Click to expand...
Click to expand...
 
D

DanG

Not sure it is just because my brain is fried on this issue, but I cant
figure out how to write it to work for my specific example.
You wrote: In your case, you will need some code that tests if the combo
is null, or if its value is -1, and if so to ignore the field. For other
values, you want
to use it as part of the WhereCondition string.


Can you help me with code language on this. I cant seem to figure anything
out that is actually viable language in code.

Thanks



Allen Browne said:
Good: you have the combo working.

The goal here is to filter a report, so you have 2 options. You either put
the WHERE clause in the report's filter, or you build a WhereCondition
string in code and use it for OpenReport.

Option 1: Modify the report's query.
=========================
Presumably you already have a query that supplies the data to the report,
i.e. the one named in the report's RecordSource property. Open this query in
deisgn view. Then use the View menu to switch it to SQL View. The query
statement will look like this:
SELECT ...
FROM ...
WHERE ...
ORDER BY ...
Locate the WHERE clause, and modify it as shown in the last post.

Option 2: Build the WhereCondition string.
==============================
For an example of how to do that, see:
http://allenbrowne.com/casu-15.html
In that case, strWhere is very simple. It just ends up as something like:
ID = 999
In your case, you will need some code that tests if the combo is null, or if
its value is -1, and if so to ignore the field. For other values, you want
to use it as part of the WhereCondition string.

There's a more comprehensive example involving several combos and text boxes
here:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
(In that example, we didn't add '(All)' to the combo: we just assumed the
user left it blank if they wanted all records.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

DanG said:
Hey. Thanks for the information. I got halfway there. I added the new
table. I entered in the rowsource information. I got the drop down to
work.
Now this is where I show off how little I actually know about Access.

Where do I put this wherecondition, and how do I even code it. I really
dont know much about coding at all.

Thanks again for all the help you have provided me on these issues.

Allen Browne said:
Thanks, Dan. I understand now.

The article at the MVPs site suggests using Null as the key value for the
UNION. This is the hidden column that shows the "(All"), so when you
select
"(All)", Access recognises the combo value is Null, but Null doesn't
match
anything, so it straightaway disconnects it from the "(All)" and just
shows
a blank combo. Yes: I can see that happening.

A better solution would be to create another little table table with 2
fields:
ID Number primary key
TheText Text unique, and required.
Save as (say) tblAll4Combo.
Enter just one record, using -1 for the ID, and (All) for the text field.

Now try a RowSource of:
SELECT CenterID,
CenterName
FROM tblCenterList
UNION ALL
Select ID AS CenterID,
TheText AS CenterName
FROM tblCenterList
ORDER BY CenterID;
You should now be able to select the (All) option in the combo.

Now you will have the job of convincing the query to feed all values to
the
report when -1 is chosen:

- If you are building a WhereCondition for OpenReport, your code will
just
ignore the -1 value (i.e. don't filter on this field if the combo is -1.)

- If you are trying to make the query read the combo on your form, you
will
need to modify the query so it returns all values when the combo is -1.
Switch the query to SQL View (View menu, in query design), locate the
WHERE
clause, and change it so it looks like this:
WHERE (([Forms].[Form1].[Combo2] = -1)
OR ([Forms].[Form1].[Combo2] Is Null)
OR (CenterID = [Forms].[Form1].[Combo2]))

I personally prefer the WhereCondition where this is possible: it's a bit
more work to program, but simpler and more efficient to execute.

Let us know how you go.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

the info on this combo box is ---->
Rowsource: SELECT CenterID, CenterName FROM tblCenterList UNION Select
Null
as AllChoice , "(All)" as Bogus From tblCenterList
ORDER BY CenterID;

Bound Column: 1
Column Count: 2
Column Widths: 0";1"
Limit to List: Yes
AutoExpand: Yes
Enabled: Yes
Locked: No

the event procedure behind the 'Run Report' button is this:
Private Sub Command4_Click()
Me.Visible = False
End Sub

Report Open and Close Event Procedures are ----->

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "ParamForm", , , , , acDialog

End Sub


Private Sub Report_Close()
DoCmd.Close acForm, "Paraform"
End Sub

:

Dan, there is not enough info there to know what's going on.

Take it one step at a time. I take it the combo's RowSource is a UNION
query. When you view the query, you probably see the column
left-aligned
(like text) rather than right-aligned (like a number) because of the
text
in
this column.

Now how are you trying to apply this? Are you using code behind the
button,
or a macro? Are you building a WhereCondition string for OpenReport?
Or
are
you trying to refer to the combo in the the criteria of the query the
report
is based on?

Bound Column is the CenterID (autonumber), I tried switching Bound
Column
to
CenterName, which allowed me to choose 'All' but that only got me to
the
point of "error executing this command" when I tried to run the
report.
I
also have filters for Date range. Those work as long as I am
picking
an
individual center name. (The report shows up in triplicate, which I
was
going to post under separate thread after the current issues were
fixed).

:

If the combo is unbound, I'm not sure what you mean.
Are you saying you can select any other value, but not the "All"?

What is the data type of the bound field in the combo's RowSource?

Is there anything in the Format property of the combo?

Does it not actually select the All, or do you get stuck there an
unable
to
move on?

Is there any error message?

The Control Source is clear on the combo box.

What else should I try?

PS - Thank you for helping me.

:

Clear the ControlSource property of the combo.

If the combo is bound to a field, and the field is a foreign key
with
relational integrity, then you will not be able to choose a
value
that
is
not in the RowSource table.

I am trying to add the (All) listing to the drop down as noted
in
one
of
your
links. I get it to appear in the drop down, but it wont let
me
choose
it.
There is not about not being able to do it when the primary
key
of a
table
is
involved. In this case the CenterID is a primary field. Any
suggestions?

:

The simplest way is to teach people to leave the combo blank
if
they
do
not
wish to filter on that field. You then ignore the boxes where
the
user
did
not enter anything when you build the WhereCondition for
OpenReport.

There's an example here of building up such a string here:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
The example applies the results to the Filter of the form,
but
it's
exactly
the same for the WhereCondition of OpenReport.

If you prefer, you can use a UNION query to add a literal
value
to
the
appropropriate column of the combo, and then ignore that
special
value
when
you build your WhereCondition string. For an example, see:
Adding "All" to a listbox or combobox
at:
http://www.mvps.org/access/forms/frm0043.htm

I have a form that sets up filters for a report. One is a
dropdown
box
of
the different offices that information is being tracked on.
What
is
the
best
way to add a choice to the drop down which says Nationwide
and
encompasses
all the data from all the offices combined.
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

Download the example database from the article.

You will see how it builds a string from the various controls on the form.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Possible to Combine reports to's to a single box? 0
Unclear change tracking process 0
Automation of scanned file storage based on content 0
list or combo box 0
Using a drop-down box to sort data based on seperate columns 2
Page numbering with Merged Document and IF statements 1
Combine Reports 1
Using filtered form data as record source for report 2

Top