Adding "all" to a Combo box list

[Eka1618]

Hello,

I am tring to add the option <all> to my combo box. This is actually going
to reference a PK in a table that is a numeric field. I can only get the
"all" option to work if it displays in the box as 0 instead of <all>. How can
I make this read as <all> and yet still carry a value of zero? Is it even
possible?

Here is the code that I have:

SELECT DISTINCT tblPattern.PAT_NO
FROM tblPattern

UNION

SELECT 0
FROM tblPattern
ORDER BY tblPattern.PAT_NO;

Thank you for the help in advance!

~Erica~

 

[Klatuu]

It won't work. The data types have to be the same. What you are doing is a
bit unusual in that there is no descriptive field for the user to see. It
seems like it would be difficult for a user to know which PK goes with which
record. But, if they can live with that, then just tell them 0 means All.

 

[Eka1618]

Klatuu,

Thank you for letting me know so I can move on. My users need this field to
be a number field because that is what kind of data their pattern numbers
are. They should be fine with this.

Thanks again!

~Erica~

 

[Douglas J. Steele]

Another options to what Dave suggested is to make a two-column combo box:

SELECT DISTINCT tblPattern.PAT_NO, CStr(tblPattern.PAT_NO) AS Display
FROM tblPattern
UNION
SELECT 0, "All"
FROM tblPattern
ORDER BY tblPattern.PAT_NO;

Bind the combo box to the first column, and only display the second one.

 

[Larry Linson]

Have you considered having another field in your table... the number in
character form, just for display... that way you can display the character
field, including 1, 2, ... <all>, but bind to the numeric field.

Larry Linson
Microsoft Office Access MVP

 

[Eka1618]

Thanks for all the tips guys,

I understand what you all say about having another column, but I am not sure
if I follow what you're all saying about 'binding' one column to the
other...is this something additional I need to do, or is it something that
just happens when you use code like doug's example?

I did not consider having an additional column. The PAT_NO field is actually
apart of a composite PK (not sure if that mean anything here in this
situation), but I'd like to go ahead and try dougs example to see what
happens.

I'll probably post more

~Erica~

 

[Eka1618]

I though I would also post the sample in my query where I use PAT_NO:

AND (tblResults.PAT_NO) IN (Select PAT_NO from tblPattern Where ( PAT_NO =
[forms].[frmCustomResultReport].[cmbPatNo] OR
[forms].[frmCustomResultReport].[cmbPatNo] = 0) )


I have not gotten any errors so far... I just do not understand what you
mean by bind.

Thanks again!

~Erica~

 

[Douglas J. Steele]

Look at the properties of the combo box. Find the properties named Column
Count, Bound Column and Column Widths.

If you change the Row Source to the SQL I suggested, you'd make the Column
Count 2 (because there are now two columns being returned by your SQL), the
Bound Column to 1 (which means that when you query the combo box, it will
give you the value of the first column of the selected row) and the Column
Widths to 0" (or 0";1"). The a value of 0 for the Column Widths property
means that the first column of the combo box won't be displayed. If you
don't specify any other values, the remaining columns will all be given a
default width (which is usually 1 inch)

The SQL you posted in another thread is correct, regardless of whether you
change the combo box or not.

 

[Eka1618]

Thank you for the Help!

I actually figured everything out a little while ago and forgot to tell
you... but everything is working very well!

Thanks Again!

~Erica~

 

[James A. Fortune]

Another options to what Dave suggested is to make a two-column combo box:

SELECT DISTINCT tblPattern.PAT_NO, CStr(tblPattern.PAT_NO) AS Display
FROM tblPattern
UNION
SELECT 0, "All"
FROM tblPattern
ORDER BY tblPattern.PAT_NO;

Bind the combo box to the first column, and only display the second one.

Doug,

That's an excellent suggestion. Depending on how many records are in
tblPattern, it might be noticeably more efficient to use:

SELECT DISTINCT tblPattern.PAT_NO, CStr(tblPattern.PAT_NO) AS Display
FROM tblPattern
UNION
SELECT 0, "All"
FROM tblPattern WHERE PID = 1
ORDER BY tblPattern.PAT_NO;

where PID is the primary key of tblPattern. That way the UNION doesn't
have to eliminate a bunch of 0, "All" duplicates.

James A. Fortune
(e-mail address removed)