Select "All" in Combo Box

C

channell

Hell, I have a question regarding "All" in a Combo Box. I need to have an
"All" option in my combo box to return all the records on my form. Thanks
for your help!!

I have a form that is Filtered according to what is selected in my ComboBox.
It works perfectly. My Combo Box reads as follows:

SELECT tPRELOADPOSITION.[BELT ID], tPRELOADPOSITION.BELT FROM
tPRELOADPOSITION ORDER BY [BELT];

I have tried this, but to no avail:

SELECT tPRELOADPOSITION.[BELT ID], tPRELOADPOSITION.BELT FROM
tPRELOADPOSITION UNION SELECT ("All") FROM tPRELOADPOSITION ORDER BY [BELT];


There are two columns: 0";1"

I have tried a few different things, and I usually get a syntax error or the
combo box displays nothing. I have read all sorts of articles and websites,
but I am missing something. Thank you for your help! I very much appreciate
it!

-Scott Channell
 
B

BruceM

This article shows several approaches:
http://www.mvps.org/access/forms/frm0043.htm

One potential problem I see with what you have tried is that you are only
adding one field via the Union, but the row source has two fields. You may
end up with something like this:

SELECT tPRELOADPOSITION.[BELT ID], tPRELOADPOSITION.BELT FROM
tPRELOADPOSITION UNION SELECT Null As ChooseAll, "(All)" As PlaceHolder FROM
tPRELOADPOSITION ORDER BY [BELT];

Or maybe in would be SELECT 0 As ChooseAll ...

When something doesn't work it would be best to describe the details rather
than say something like "to no avail". Also, your SQL seems to be based on
a specific suggestion or article, so it would be good to provide the source
of what you tried rather than saying you read "all sorts" of things.
 
C

channell

Bruce,

Thank you for your input. I will be more specific:

If I input the information the first way you provided and it retured a
syntax error:

(Missing operator) in query expression '[PRELOAD POSITION] = '.

I got this a few times and it takes me to my VBA to debug the filter I have
which is:
Private Sub Combo52_AfterUpdate()
Me.Filter = "[PRELOAD POSITION] = " & Me.Combo52
Me.FilterOn = True
End Sub

So, I am not sure what's going on with that. I try the second way you
provided (Changing "Null" to "0", and I select ALL and it returns nothing,
but the other options in the combo box work.

I am pulling work Positions from a table called tDAILYINFO.

tDAILYINFO:
EMPLOYEE ID
WORKDATE
PRELOAD POSITION (FK from tPRELOADPOSITION)
.... and other fields

tPRELOADPOSITION:
BELT ID
BELT

Thanks Bruce




BruceM said:
This article shows several approaches:
http://www.mvps.org/access/forms/frm0043.htm

One potential problem I see with what you have tried is that you are only
adding one field via the Union, but the row source has two fields. You may
end up with something like this:

SELECT tPRELOADPOSITION.[BELT ID], tPRELOADPOSITION.BELT FROM
tPRELOADPOSITION UNION SELECT Null As ChooseAll, "(All)" As PlaceHolder FROM
tPRELOADPOSITION ORDER BY [BELT];

Or maybe in would be SELECT 0 As ChooseAll ...

When something doesn't work it would be best to describe the details rather
than say something like "to no avail". Also, your SQL seems to be based on
a specific suggestion or article, so it would be good to provide the source
of what you tried rather than saying you read "all sorts" of things.

channell said:
Hell, I have a question regarding "All" in a Combo Box. I need to have an
"All" option in my combo box to return all the records on my form. Thanks
for your help!!

I have a form that is Filtered according to what is selected in my
ComboBox.
It works perfectly. My Combo Box reads as follows:

SELECT tPRELOADPOSITION.[BELT ID], tPRELOADPOSITION.BELT FROM
tPRELOADPOSITION ORDER BY [BELT];

I have tried this, but to no avail:

SELECT tPRELOADPOSITION.[BELT ID], tPRELOADPOSITION.BELT FROM
tPRELOADPOSITION UNION SELECT ("All") FROM tPRELOADPOSITION ORDER BY
[BELT];


There are two columns: 0";1"

I have tried a few different things, and I usually get a syntax error or
the
combo box displays nothing. I have read all sorts of articles and
websites,
but I am missing something. Thank you for your help! I very much
appreciate
it!

-Scott Channell
Click to expand...
Click to expand...
 
B

BruceM

You need to allow for the user selecting All. Assuming BeltID is the
primary key, and therefore cannot be null, try using the 0 as I suggested,
but test for it in the After Update code:

Private Sub Combo52_AfterUpdate()
If Me.Combo52 = 0 Then
Me.FilterOn = False
Else
Me.Filter = "[PRELOAD POSITION] = " & Me.Combo52
Me.FilterOn = True
End Sub

This assumes the bound column from the combo box is 1 (the BeltID column).

Consider giving the combo box (or any control) a meaningful name such as
cboChooseBelt. It will make things much easier to identify the relevant
code if your form has several combo boxes.

channell said:
Bruce,

Thank you for your input. I will be more specific:

If I input the information the first way you provided and it retured a
syntax error:

(Missing operator) in query expression '[PRELOAD POSITION] = '.

I got this a few times and it takes me to my VBA to debug the filter I
have
which is:
Private Sub Combo52_AfterUpdate()
Me.Filter = "[PRELOAD POSITION] = " & Me.Combo52
Me.FilterOn = True
End Sub

So, I am not sure what's going on with that. I try the second way you
provided (Changing "Null" to "0", and I select ALL and it returns nothing,
but the other options in the combo box work.

I am pulling work Positions from a table called tDAILYINFO.

tDAILYINFO:
EMPLOYEE ID
WORKDATE
PRELOAD POSITION (FK from tPRELOADPOSITION)
... and other fields

tPRELOADPOSITION:
BELT ID
BELT

Thanks Bruce




BruceM said:
This article shows several approaches:
http://www.mvps.org/access/forms/frm0043.htm

One potential problem I see with what you have tried is that you are only
adding one field via the Union, but the row source has two fields. You
may
end up with something like this:

SELECT tPRELOADPOSITION.[BELT ID], tPRELOADPOSITION.BELT FROM
tPRELOADPOSITION UNION SELECT Null As ChooseAll, "(All)" As PlaceHolder
FROM
tPRELOADPOSITION ORDER BY [BELT];

Or maybe in would be SELECT 0 As ChooseAll ...

When something doesn't work it would be best to describe the details
rather
than say something like "to no avail". Also, your SQL seems to be based
on
a specific suggestion or article, so it would be good to provide the
source
of what you tried rather than saying you read "all sorts" of things.

channell said:
Hell, I have a question regarding "All" in a Combo Box. I need to have
an
"All" option in my combo box to return all the records on my form.
Thanks
for your help!!

I have a form that is Filtered according to what is selected in my
ComboBox.
It works perfectly. My Combo Box reads as follows:

SELECT tPRELOADPOSITION.[BELT ID], tPRELOADPOSITION.BELT FROM
tPRELOADPOSITION ORDER BY [BELT];

I have tried this, but to no avail:

SELECT tPRELOADPOSITION.[BELT ID], tPRELOADPOSITION.BELT FROM
tPRELOADPOSITION UNION SELECT ("All") FROM tPRELOADPOSITION ORDER BY
[BELT];


There are two columns: 0";1"

I have tried a few different things, and I usually get a syntax error
or
the
combo box displays nothing. I have read all sorts of articles and
websites,
but I am missing something. Thank you for your help! I very much
appreciate
it!

-Scott Channell
Click to expand...
Click to expand...
Click to expand...
 
C

channell

Bruce:

It worked perfectly!!

I had to put in "End If" in the VBA, but thank you so much for your help. I
greatly appreciate it!

-Scott Channell


BruceM said:
You need to allow for the user selecting All. Assuming BeltID is the
primary key, and therefore cannot be null, try using the 0 as I suggested,
but test for it in the After Update code:

Private Sub Combo52_AfterUpdate()
If Me.Combo52 = 0 Then
Me.FilterOn = False
Else
Me.Filter = "[PRELOAD POSITION] = " & Me.Combo52
Me.FilterOn = True
End Sub

This assumes the bound column from the combo box is 1 (the BeltID column).

Consider giving the combo box (or any control) a meaningful name such as
cboChooseBelt. It will make things much easier to identify the relevant
code if your form has several combo boxes.

channell said:
Bruce,

Thank you for your input. I will be more specific:

If I input the information the first way you provided and it retured a
syntax error:

(Missing operator) in query expression '[PRELOAD POSITION] = '.

I got this a few times and it takes me to my VBA to debug the filter I
have
which is:
Private Sub Combo52_AfterUpdate()
Me.Filter = "[PRELOAD POSITION] = " & Me.Combo52
Me.FilterOn = True
End Sub

So, I am not sure what's going on with that. I try the second way you
provided (Changing "Null" to "0", and I select ALL and it returns nothing,
but the other options in the combo box work.

I am pulling work Positions from a table called tDAILYINFO.

tDAILYINFO:
EMPLOYEE ID
WORKDATE
PRELOAD POSITION (FK from tPRELOADPOSITION)
... and other fields

tPRELOADPOSITION:
BELT ID
BELT

Thanks Bruce




BruceM said:
This article shows several approaches:
http://www.mvps.org/access/forms/frm0043.htm

One potential problem I see with what you have tried is that you are only
adding one field via the Union, but the row source has two fields. You
may
end up with something like this:

SELECT tPRELOADPOSITION.[BELT ID], tPRELOADPOSITION.BELT FROM
tPRELOADPOSITION UNION SELECT Null As ChooseAll, "(All)" As PlaceHolder
FROM
tPRELOADPOSITION ORDER BY [BELT];

Or maybe in would be SELECT 0 As ChooseAll ...

When something doesn't work it would be best to describe the details
rather
than say something like "to no avail". Also, your SQL seems to be based
on
a specific suggestion or article, so it would be good to provide the
source
of what you tried rather than saying you read "all sorts" of things.

Hell, I have a question regarding "All" in a Combo Box. I need to have
an
"All" option in my combo box to return all the records on my form.
Thanks
for your help!!

I have a form that is Filtered according to what is selected in my
ComboBox.
It works perfectly. My Combo Box reads as follows:

SELECT tPRELOADPOSITION.[BELT ID], tPRELOADPOSITION.BELT FROM
tPRELOADPOSITION ORDER BY [BELT];

I have tried this, but to no avail:

SELECT tPRELOADPOSITION.[BELT ID], tPRELOADPOSITION.BELT FROM
tPRELOADPOSITION UNION SELECT ("All") FROM tPRELOADPOSITION ORDER BY
[BELT];


There are two columns: 0";1"

I have tried a few different things, and I usually get a syntax error
or
the
combo box displays nothing. I have read all sorts of articles and
websites,
but I am missing something. Thank you for your help! I very much
appreciate
it!

-Scott Channell
Click to expand...
Click to expand...
Click to expand...
 
B

BruceM

Oops. Glad you got it working. It's a handy technique.

channell said:
Bruce:

It worked perfectly!!

I had to put in "End If" in the VBA, but thank you so much for your help.
I
greatly appreciate it!

-Scott Channell


BruceM said:
You need to allow for the user selecting All. Assuming BeltID is the
primary key, and therefore cannot be null, try using the 0 as I
suggested,
but test for it in the After Update code:

Private Sub Combo52_AfterUpdate()
If Me.Combo52 = 0 Then
Me.FilterOn = False
Else
Me.Filter = "[PRELOAD POSITION] = " & Me.Combo52
Me.FilterOn = True
End Sub

This assumes the bound column from the combo box is 1 (the BeltID
column).

Consider giving the combo box (or any control) a meaningful name such as
cboChooseBelt. It will make things much easier to identify the relevant
code if your form has several combo boxes.

channell said:
Bruce,

Thank you for your input. I will be more specific:

If I input the information the first way you provided and it retured a
syntax error:

(Missing operator) in query expression '[PRELOAD POSITION] = '.

I got this a few times and it takes me to my VBA to debug the filter I
have
which is:
Private Sub Combo52_AfterUpdate()
Me.Filter = "[PRELOAD POSITION] = " & Me.Combo52
Me.FilterOn = True
End Sub

So, I am not sure what's going on with that. I try the second way you
provided (Changing "Null" to "0", and I select ALL and it returns
nothing,
but the other options in the combo box work.

I am pulling work Positions from a table called tDAILYINFO.

tDAILYINFO:
EMPLOYEE ID
WORKDATE
PRELOAD POSITION (FK from tPRELOADPOSITION)
... and other fields

tPRELOADPOSITION:
BELT ID
BELT

Thanks Bruce




:

This article shows several approaches:
http://www.mvps.org/access/forms/frm0043.htm

One potential problem I see with what you have tried is that you are
only
adding one field via the Union, but the row source has two fields.
You
may
end up with something like this:

SELECT tPRELOADPOSITION.[BELT ID], tPRELOADPOSITION.BELT FROM
tPRELOADPOSITION UNION SELECT Null As ChooseAll, "(All)" As
PlaceHolder
FROM
tPRELOADPOSITION ORDER BY [BELT];

Or maybe in would be SELECT 0 As ChooseAll ...

When something doesn't work it would be best to describe the details
rather
than say something like "to no avail". Also, your SQL seems to be
based
on
a specific suggestion or article, so it would be good to provide the
source
of what you tried rather than saying you read "all sorts" of things.

Hell, I have a question regarding "All" in a Combo Box. I need to
have
an
"All" option in my combo box to return all the records on my form.
Thanks
for your help!!

I have a form that is Filtered according to what is selected in my
ComboBox.
It works perfectly. My Combo Box reads as follows:

SELECT tPRELOADPOSITION.[BELT ID], tPRELOADPOSITION.BELT FROM
tPRELOADPOSITION ORDER BY [BELT];

I have tried this, but to no avail:

SELECT tPRELOADPOSITION.[BELT ID], tPRELOADPOSITION.BELT FROM
tPRELOADPOSITION UNION SELECT ("All") FROM tPRELOADPOSITION ORDER BY
[BELT];


There are two columns: 0";1"

I have tried a few different things, and I usually get a syntax
error
or
the
combo box displays nothing. I have read all sorts of articles and
websites,
but I am missing something. Thank you for your help! I very much
appreciate
it!

-Scott Channell
Click to expand...
Click to expand...
Click to expand...
 

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

Select All Help, One More Time 3
combo box filter 1
Combo Box Sorting 2
Select All For a Multi Select Box 3
Combo Box Question 4
Cascading Dependant combo boxes 0
Combo Box and First Record 5
Combo Box Sync 5

Top