Latest Combo box question

  • Thread starter Thread starter Eric G
  • Start date Start date
E

Eric G

[Sorry if you read this recently, but I can't find my post from
yesterday. I changed the title to help things out.]

I have a dropdown combo box on a form that displays the nine values of
a table.
Would someone be able to tell me how I can set up the combo box so
that it only displays the first eight of the nine values as choices?

COMBO BOX:
Control source: ReasonID
Row source: SELECT [Reasons].[ReasonID], [Reasons].[Reason] FROM
Reasons;
Column widths: 0";0.7"

TIA Eric
 
Change the Row Source query to exclude the record whose value of ReasonID
you don't want:

SELECT [Reasons].[ReasonID], [Reasons].[Reason] FROM Reasons WHERE
[Reasons].[ReasonID] <> #

Change # to the number that you don't want to be in the dropdown list.
 
Thanks Ken,

That was nice and easy to set up.

Eric

Change the Row Source query to exclude the record whose value of ReasonID
you don't want:

SELECT [Reasons].[ReasonID], [Reasons].[Reason] FROM Reasons WHERE
[Reasons].[ReasonID] <> #

Change # to the number that you don't want to be in the dropdown list.

--
Ken Snell
<MS ACCESS MVP>


Eric G said:
[Sorry if you read this recently, but I can't find my post from
yesterday. I changed the title to help things out.]

I have a dropdown combo box on a form that displays the nine values of
a table.
Would someone be able to tell me how I can set up the combo box so
that it only displays the first eight of the nine values as choices?

COMBO BOX:
Control source: ReasonID
Row source: SELECT [Reasons].[ReasonID], [Reasons].[Reason] FROM
Reasons;
Column widths: 0";0.7"

TIA Eric
Click to expand...
Click to expand...
 
Hi Ken,
Change the Row Source query to exclude the record whose value of ReasonID
you don't want:

SELECT [Reasons].[ReasonID], [Reasons].[Reason] FROM Reasons WHERE
[Reasons].[ReasonID] <> #

Change # to the number that you don't want to be in the dropdown list.
Click to expand...


I'd also like to have two more ReasonID values not showing in the
dropdown list.
I tried the following code but it didn't work:

SELECT [Reasons].[ReasonID], [Reasons].[Reason] FROM Reasons WHERE
[Reasons].[ReasonID] <> 9 AND WHERE
[Reasons].[ReasonID] <> 2 AND WHERE
[Reasons].[ReasonID] <> 3;

It actually removed all choices from the drop-down list and didn't
work at all.
I'd appreciate a tip here.

TIA Eric
 
Try

SELECT ReasonID, Reason FROM Reasons WHERE ReasonID NOT IN(9,3,2)

This assumes that the reasonID is a Numeric value. If Reason is a String
then

SELECT ReasonID, Reason FROM Reasons WHERE ReasonID NOT IN('9','3','2')

You might want to add an order by to get a reasonable sort order for the
combo

ORDER BY Reason


Ron W

Eric G said:
Hi Ken,
Change the Row Source query to exclude the record whose value of ReasonID
you don't want:

SELECT [Reasons].[ReasonID], [Reasons].[Reason] FROM Reasons WHERE
[Reasons].[ReasonID] <> #

Change # to the number that you don't want to be in the dropdown list.
Click to expand...


I'd also like to have two more ReasonID values not showing in the
dropdown list.
I tried the following code but it didn't work:

SELECT [Reasons].[ReasonID], [Reasons].[Reason] FROM Reasons WHERE
[Reasons].[ReasonID] <> 9 AND WHERE
[Reasons].[ReasonID] <> 2 AND WHERE
[Reasons].[ReasonID] <> 3;

It actually removed all choices from the drop-down list and didn't
work at all.
I'd appreciate a tip here.

TIA Eric
Click to expand...
 
Sorry - I missed you post last night!

In addition to Ron's solution, you can use your original approach by
removing the "extra" WHERE words:

SELECT [Reasons].[ReasonID], [Reasons].[Reason] FROM Reasons WHERE
[Reasons].[ReasonID] <> 9 AND
[Reasons].[ReasonID] <> 2 AND
[Reasons].[ReasonID] <> 3;


--

Ken Snell
<MS ACCESS MVP>

Eric G said:
Hi Ken,
Change the Row Source query to exclude the record whose value of ReasonID
you don't want:

SELECT [Reasons].[ReasonID], [Reasons].[Reason] FROM Reasons WHERE
[Reasons].[ReasonID] <> #

Change # to the number that you don't want to be in the dropdown list.
Click to expand...


I'd also like to have two more ReasonID values not showing in the
dropdown list.
I tried the following code but it didn't work:

SELECT [Reasons].[ReasonID], [Reasons].[Reason] FROM Reasons WHERE
[Reasons].[ReasonID] <> 9 AND WHERE
[Reasons].[ReasonID] <> 2 AND WHERE
[Reasons].[ReasonID] <> 3;

It actually removed all choices from the drop-down list and didn't
work at all.
I'd appreciate a tip here.

TIA Eric
Click to expand...
 
"Ken Snell" <[email protected]> wrote:
Click to expand...
Sorry - I missed you post last night!

In addition to Ron's solution, you can use your original approach by
removing the "extra" WHERE words:

SELECT [Reasons].[ReasonID], [Reasons].[Reason] FROM Reasons WHERE
[Reasons].[ReasonID] <> 9 AND
[Reasons].[ReasonID] <> 2 AND
[Reasons].[ReasonID] <> 3;
Click to expand...


Thanks Ken.
I thought about playing around a bit with the Wheres and such.
I'll make a note about this for the future.
Best,
Eric
 
Back
Top