Adding all to combo

Hi everyone,
How does one add "ALL" to a bound cbo box?

Cbo has two columns, bound to 1
RegionID
RegionName

Any Suggestions?
Thanks
Dave

It's easy enough to add All to the list:
http://www.mvps.org/access/forms/frm0043.htm

What's more difficult is making sense of interpreting it once you get it in
there.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

wrote in message
oups.com...
Hi everyone,
How does one add "ALL" to a bound cbo box?

Cbo has two columns, bound to 1
RegionID
RegionName

Any Suggestions?
Thanks
Dave

On Mar 19, 11:19 pm, "Allen Browne"
wrote:
It's easy enough to add All to the list:
http://www.mvps.org/access/forms/frm0043.htm

What's more difficult is making sense of interpreting it once you get it in
there.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

wrote in message

oups.com...



Hi everyone,
How does one add "ALL" to a bound cbo box?

Cbo has two columns, bound to 1
RegionID
RegionName

Any Suggestions?
Thanks
Dave- Hide quoted text -

- Show quoted text -

Hi and thanks,
You are right on comment but got it!
Thanks again,
Dave

On Mar 19, 11:19 pm, "Allen Browne"
wrote:
It's easy enough to add All to the list:
http://www.mvps.org/access/forms/frm0043.htm

What's more difficult is making sense of interpreting it once you get it in
there.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

wrote in message

oups.com...



Hi everyone,
How does one add "ALL" to a bound cbo box?

Cbo has two columns, bound to 1
RegionID
RegionName

Any Suggestions?
Thanks
Dave- Hide quoted text -

- Show quoted text -

Hi again,
After following the link and reading it over good, I guess your answer
is you cannot do this unless I assign some sort of UID or a choose a
different primary Key for this table?

For example, if your combo's RowSource is this SQL statement
"(AND IT IS)"

SELECT CustomerID, CompanyName FROM Customers ORDER BY CustomerID;

you can then easily add "(All)" as the first choice. Also, if
CustomerID is the bound field but it's width is set to zero (so that
the user only sees CompanyName), you can store a NULL (if the bound
field is not the primary key of the table), or someother value in the
bound field.

SELECT CustomerID, CompanyName FROM Customers UNION Select Null as
AllChoice , "(All)" as Bogus From Customers ORDER BY CustomerID;

tHIS WOULD WORK FINE IN THE EVENT ONE DIDNT HAVE THE PRIMARY KEY
ASSIGNED TO THE BOUND COLUMN. WHAT THIS DOESNT SHOW IS WHAT IF THE
BOUND COLUMN IS THE PRIMARY KEY WHICH IN THIS ISNSTANCE IS MOST LIKELY
TRUE MOST OF THE TIME. IS THERE ANY DOCUMENTATION ANYWHERE THAT WOULD
EXPLAIN THE WHAT IF?
THANKS,
DAVE

If the combo is bound to a primary key (or any required field), then the
attempt to store the Null there is doomed to fail.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

wrote in message
ups.com...
On Mar 19, 11:19 pm, "Allen Browne"
wrote:
It's easy enough to add All to the list:
http://www.mvps.org/access/forms/frm0043.htm

What's more difficult is making sense of interpreting it once you get it
in
there.

wrote in message

oups.com...



Hi everyone,
How does one add "ALL" to a bound cbo box?

Cbo has two columns, bound to 1
RegionID
RegionName

Any Suggestions?
Thanks
Dave- Hide quoted text -

- Show quoted text -

Hi again,
After following the link and reading it over good, I guess your answer
is you cannot do this unless I assign some sort of UID or a choose a
different primary Key for this table?

For example, if your combo's RowSource is this SQL statement
"(AND IT IS)"

SELECT CustomerID, CompanyName FROM Customers ORDER BY CustomerID;

you can then easily add "(All)" as the first choice. Also, if
CustomerID is the bound field but it's width is set to zero (so that
the user only sees CompanyName), you can store a NULL (if the bound
field is not the primary key of the table), or someother value in the
bound field.

SELECT CustomerID, CompanyName FROM Customers UNION Select Null as
AllChoice , "(All)" as Bogus From Customers ORDER BY CustomerID;

tHIS WOULD WORK FINE IN THE EVENT ONE DIDNT HAVE THE PRIMARY KEY
ASSIGNED TO THE BOUND COLUMN. WHAT THIS DOESNT SHOW IS WHAT IF THE
BOUND COLUMN IS THE PRIMARY KEY WHICH IN THIS ISNSTANCE IS MOST LIKELY
TRUE MOST OF THE TIME. IS THERE ANY DOCUMENTATION ANYWHERE THAT WOULD
EXPLAIN THE WHAT IF?
THANKS,
DAVE

How are you using the combo box?

If, for example, you're using it as a parameter in a query, you'd change the
WHERE clause from

WHERE FIeld1 = Forms!MyForm!MyComboBox

to

WHERE (FIeld1 = Forms!MyForm!MyComboBox OR Forms!MyForm!MyComboBox IS NULL)

If you're referring to the combo box in VBA, you use the IsNull function.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


wrote in message
ups.com...

Hi again,
After following the link and reading it over good, I guess your answer
is you cannot do this unless I assign some sort of UID or a choose a
different primary Key for this table?

For example, if your combo's RowSource is this SQL statement
"(AND IT IS)"

SELECT CustomerID, CompanyName FROM Customers ORDER BY CustomerID;

you can then easily add "(All)" as the first choice. Also, if
CustomerID is the bound field but it's width is set to zero (so that
the user only sees CompanyName), you can store a NULL (if the bound
field is not the primary key of the table), or someother value in the
bound field.

SELECT CustomerID, CompanyName FROM Customers UNION Select Null as
AllChoice , "(All)" as Bogus From Customers ORDER BY CustomerID;

tHIS WOULD WORK FINE IN THE EVENT ONE DIDNT HAVE THE PRIMARY KEY
ASSIGNED TO THE BOUND COLUMN. WHAT THIS DOESNT SHOW IS WHAT IF THE
BOUND COLUMN IS THE PRIMARY KEY WHICH IN THIS ISNSTANCE IS MOST LIKELY
TRUE MOST OF THE TIME. IS THERE ANY DOCUMENTATION ANYWHERE THAT WOULD
EXPLAIN THE WHAT IF?
THANKS,
DAVE

On Mar 20, 5:54 am, "Douglas J. Steele"
wrote:
How are you using the combo box?

If, for example, you're using it as a parameter in a query, you'd change the
WHERE clause from

WHERE FIeld1 = Forms!MyForm!MyComboBox

to

WHERE (FIeld1 = Forms!MyForm!MyComboBox OR Forms!MyForm!MyComboBox IS NULL)

If you're referring to the combo box in VBA, you use the IsNull function.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

wrote in message

ups.com...





Hi again,
After following the link and reading it over good, I guess your answer
is you cannot do this unless I assign some sort of UID or a choose a
different primary Key for this table?

For example, if your combo's RowSource is this SQL statement
"(AND IT IS)"

SELECT CustomerID, CompanyName FROM Customers ORDER BY CustomerID;

you can then easily add "(All)" as the first choice. Also, if
CustomerID is the bound field but it's width is set to zero (so that
the user only sees CompanyName), you can store a NULL (if the bound
field is not the primary key of the table), or someother value in the
bound field.

SELECT CustomerID, CompanyName FROM Customers UNION Select Null as
AllChoice , "(All)" as Bogus From Customers ORDER BY CustomerID;

tHIS WOULD WORK FINE IN THE EVENT ONE DIDNT HAVE THE PRIMARY KEY
ASSIGNED TO THE BOUND COLUMN. WHAT THIS DOESNT SHOW IS WHAT IF THE
BOUND COLUMN IS THE PRIMARY KEY WHICH IN THIS ISNSTANCE IS MOST LIKELY
TRUE MOST OF THE TIME. IS THERE ANY DOCUMENTATION ANYWHERE THAT WOULD
EXPLAIN THE WHAT IF?
THANKS,
DAVE- Hide quoted text -

- Show quoted text -

The control or cbo is table/Query with record source set to Region.
The form is based on a query!

Sorry, you haven't answered the question. How are you using the combo box?
Are you trying to use it for Search purposes, or are you trying to use it to
add records to a table using the combo box as input? If you're trying to use
it for input, giving a choice of All really makes no sense.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


wrote in message
oups.com...

The control or cbo is table/Query with record source set to Region.
The form is based on a query!

On Mar 20, 5:54 am, "Douglas J. Steele"
wrote:
How are you using the combo box?

If, for example, you're using it as a parameter in a query, you'd change
the
WHERE clause from

WHERE FIeld1 = Forms!MyForm!MyComboBox

to

WHERE (FIeld1 = Forms!MyForm!MyComboBox OR Forms!MyForm!MyComboBox IS
NULL)

If you're referring to the combo box in VBA, you use the IsNull function.

wrote in message

ups.com...
Hi again,
After following the link and reading it over good, I guess your answer
is you cannot do this unless I assign some sort of UID or a choose a
different primary Key for this table?

For example, if your combo's RowSource is this SQL statement
"(AND IT IS)"

SELECT CustomerID, CompanyName FROM Customers ORDER BY CustomerID;

you can then easily add "(All)" as the first choice. Also, if
CustomerID is the bound field but it's width is set to zero (so that
the user only sees CompanyName), you can store a NULL (if the bound
field is not the primary key of the table), or someother value in the
bound field.

SELECT CustomerID, CompanyName FROM Customers UNION Select Null as
AllChoice , "(All)" as Bogus From Customers ORDER BY CustomerID;

tHIS WOULD WORK FINE IN THE EVENT ONE DIDNT HAVE THE PRIMARY KEY
ASSIGNED TO THE BOUND COLUMN. WHAT THIS DOESNT SHOW IS WHAT IF THE
BOUND COLUMN IS THE PRIMARY KEY WHICH IN THIS ISNSTANCE IS MOST LIKELY
TRUE MOST OF THE TIME. IS THERE ANY DOCUMENTATION ANYWHERE THAT WOULD
EXPLAIN THE WHAT IF?
THANKS,