Using a Union Query and SQL to add ALL to a combo Box

PLCJockey

I've reviewed all the code on this site but I can't get ALL in my combo box. It keeps telling me that the number of columns in the query does not match etc.

Basically, I have a table where a total of 10 fields are being populated with information. I then want to run a query to sort those records based on criteria selected from a form using combo boxes.

I'd appreciate any help. I've not used SQL before taking this on, and not a lot of Access really. Thanks

msquared

Quote:

Originally Posted by PLCJockey

I've reviewed all the code on this site but I can't get ALL in my combo box. It keeps telling me that the number of columns in the query does not match etc.

Basically, I have a table where a total of 10 fields are being populated with information. I then want to run a query to sort those records based on criteria selected from a form using combo boxes.

I'd appreciate any help. I've not used SQL before taking this on, and not a lot of Access really. Thanks

The way to get <All> is something like the following using Customers Table as an example.

Set the combo box row source to ....

Expand|Select|Wrap|Line Numbers

 
SELECT '*' AS CustID, '<All>' AS CustName

UNION

SELECT CustID, CustName

FROM Customers;

Mary

NeoPa

That's probably a better answer than mine and more closely matches the question, but another way to handle the situation is to treat a Null value as <All>.
That way, if nothing is selected they get the whole lot.

PLCJockey

Ideally I would like to be able to use the Null selection as a virtual ALL selection. Is the code much different for this?

msquared

Quote:

Originally Posted by PLCJockey

Ideally I would like to be able to use the Null selection as a virtual ALL selection. Is the code much different for this?

Pretty much the same. Change the RowSource of each combo box to a version of this ...

Expand|Select|Wrap|Line Numbers

 
SELECT Null AS CustID, '<All>' AS CustName

UNION

SELECT CustID, CustName

FROM Customers;

Mary

NeoPa

Quote:

Originally Posted by PLCJockey

Ideally I would like to be able to use the Null selection as a virtual ALL selection. Is the code much different for this?

The beauty of this approach is that you don't really need to do anything.
If the operator simply clears the current value from a ComboBox it will return Null. Your code can work with this.

Using a Union Query and SQL to add ALL to a combo Box

re: Using a Union Query and SQL to add ALL to a combo Box

re: Using a Union Query and SQL to add ALL to a combo Box

re: Using a Union Query and SQL to add ALL to a combo Box

re: Using a Union Query and SQL to add ALL to a combo Box

re: Using a Union Query and SQL to add ALL to a combo Box

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about