By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,146 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Limit values in combo box

P: n/a
I have two combo boxes that get their values from two different
tables.

I want to limit the choices in the second combo box by what is
selected in the first.

I tried using SelText (code below) but it does not work. The query
shows nothing. If I use the actual word in the query instead of
[Combo4].[SelText] it works fine..

SELECT Subs.subID, Subs.SubName, Subs.subTrade FROM Subs WHERE
(((Subs.subTrade)=[Forms]![0 TEST]![Combo4].[SelText])) ORDER BY
Subs.SubName;

Any help will be greatly appreciated.
Dec 31 '06 #1
Share this Question
Share on Google+
8 Replies


P: n/a

"ShyGuy" <sh****@shytown.comwrote in message
news:6u********************************@4ax.com...
>I have two combo boxes that get their values from two different
tables.

I want to limit the choices in the second combo box by what is
selected in the first.

I tried using SelText (code below) but it does not work. The query
shows nothing. If I use the actual word in the query instead of
[Combo4].[SelText] it works fine..

SELECT Subs.subID, Subs.SubName, Subs.subTrade FROM Subs WHERE
(((Subs.subTrade)=[Forms]![0 TEST]![Combo4].[SelText])) ORDER BY
Subs.SubName;

Any help will be greatly appreciated.
Try
(((Subs.subTrade)=[Forms]![0 TEST]![Combo4].Column(0)))
Dec 31 '06 #2

P: n/a
On Sun, 31 Dec 2006 15:45:52 +1300, "Jeff Smith"
<No****@not.this.addresswrote:
>(((Subs.subTrade)=[Forms]![0 TEST]![Combo4].Column(0)))

I get an "undefined function" error with this.
Dec 31 '06 #3

P: n/a
JHB
If the value of [Forms]![0 TEST]![Combo4].[SelText] is text then
SELECT Subs.subID, Subs.SubName, Subs.subTrade FROM Subs WHERE
(((Subs.subTrade)=" & "'" & [Forms]![0 TEST]![Combo4].[SelText] & "'" & "))
ORDER BY Subs.SubName;

if the value number then

SELECT Subs.subID, Subs.SubName, Subs.subTrade FROM Subs WHERE
(((Subs.subTrade)=" & "" & [Forms]![0 TEST]![Combo4].[SelText] & "" & "))
ORDER BY Subs.SubName;

Regards
Jørn
"ShyGuy" <sh****@shytown.comskrev i en meddelelse
news:6u********************************@4ax.com...
I have two combo boxes that get their values from two different
tables.

I want to limit the choices in the second combo box by what is
selected in the first.

I tried using SelText (code below) but it does not work. The query
shows nothing. If I use the actual word in the query instead of
[Combo4].[SelText] it works fine..

SELECT Subs.subID, Subs.SubName, Subs.subTrade FROM Subs WHERE
(((Subs.subTrade)=[Forms]![0 TEST]![Combo4].[SelText])) ORDER BY
Subs.SubName;

Any help will be greatly appreciated.

Dec 31 '06 #4

P: n/a
Thanks for the reply. The value of combo4 is text so I tried the
first sql. I get a syntax error in the following section

'" & [Forms]![0 TEST]![Combo4].[SelText] & "'

I tried removing a couple of quotes and it accepted the code but it
did not work. ;-(

Any ideas?

Thank you.

On Sun, 31 Dec 2006 09:07:20 +0100, "JHB" <jo*******@tdcadsl.dk>
wrote:
>If the value of [Forms]![0 TEST]![Combo4].[SelText] is text then
SELECT Subs.subID, Subs.SubName, Subs.subTrade FROM Subs WHERE
(((Subs.subTrade)=" & "'" & [Forms]![0 TEST]![Combo4].[SelText] & "'" & "))
ORDER BY Subs.SubName;

if the value number then

SELECT Subs.subID, Subs.SubName, Subs.subTrade FROM Subs WHERE
(((Subs.subTrade)=" & "" & [Forms]![0 TEST]![Combo4].[SelText] & "" & "))
ORDER BY Subs.SubName;

Regards
Jørn
"ShyGuy" <sh****@shytown.comskrev i en meddelelse
news:6u********************************@4ax.com.. .
>I have two combo boxes that get their values from two different
tables.

I want to limit the choices in the second combo box by what is
selected in the first.

I tried using SelText (code below) but it does not work. The query
shows nothing. If I use the actual word in the query instead of
[Combo4].[SelText] it works fine..

SELECT Subs.subID, Subs.SubName, Subs.subTrade FROM Subs WHERE
(((Subs.subTrade)=[Forms]![0 TEST]![Combo4].[SelText])) ORDER BY
Subs.SubName;

Any help will be greatly appreciated.
Dec 31 '06 #5

P: n/a
"ShyGuy" <sh****@shytown.comwrote in message
news:l0********************************@4ax.com...
Thanks for the reply. The value of combo4 is text so I tried the
first sql. I get a syntax error in the following section

'" & [Forms]![0 TEST]![Combo4].[SelText] & "'

I tried removing a couple of quotes and it accepted the code but it
did not work. ;-(

Any ideas?
Try wrapping it in Eval()

Eval([Forms]![0 TEST]![Combo4].[SelText])

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Dec 31 '06 #6

P: n/a
ShyGuy wrote:
Thanks for the reply. The value of combo4 is text so I tried the
first sql. I get a syntax error in the following section

'" & [Forms]![0 TEST]![Combo4].[SelText] & "'

I tried removing a couple of quotes and it accepted the code but it
did not work. ;-(

Any ideas?

Thank you.
SelText is always string. It may look like a bird in a cage but it's a
string. Access may coerce it into something else if it thinks you want
it to, but it's a string.

So is Column(x).

There is no SelText until a selection is made. So if you are referring
to SelText before a selection is made, an error is likely to occur. I
can't really think of a good use for SelText.

It's likely to be more efficient to load the second combo after the
first has been updated, else you may have problems when there is no
value for second combo to use.

This Forms![FormName] etc etc etc syntax is pathetic. Why does MS
continue with this abomination?

I suggest that in your first (controlling) combo AfterUpdate (in the
form module) you load the Second Combo.

This is an example using tables from northwind.

Private Sub Combo0_AfterUpdate()
If Len(Combo0.Column(0)) 0 Then
With Combo2
.RowSource = "SELECT * FROM ORDERS WHERE CustomerID='" &
Combo0.Column(0) & "'"
.Requery
End With
End If
End Sub

'" -is a single quote followed by a double quote
"'"-is double single double.

Jan 1 '07 #7

P: n/a
On 31 Dec 2006 16:38:16 -0800, "Lyle Fairfield"
<ly***********@aim.comwrote:
>ShyGuy wrote:
>Thanks for the reply. The value of combo4 is text so I tried the
first sql. I get a syntax error in the following section

'" & [Forms]![0 TEST]![Combo4].[SelText] & "'

I tried removing a couple of quotes and it accepted the code but it
did not work. ;-(

Any ideas?

Thank you.

SelText is always string. It may look like a bird in a cage but it's a
string. Access may coerce it into something else if it thinks you want
it to, but it's a string.

So is Column(x).

There is no SelText until a selection is made. So if you are referring
to SelText before a selection is made, an error is likely to occur. I
can't really think of a good use for SelText.

It's likely to be more efficient to load the second combo after the
first has been updated, else you may have problems when there is no
value for second combo to use.

This Forms![FormName] etc etc etc syntax is pathetic. Why does MS
continue with this abomination?

I suggest that in your first (controlling) combo AfterUpdate (in the
form module) you load the Second Combo.

This is an example using tables from northwind.

Private Sub Combo0_AfterUpdate()
If Len(Combo0.Column(0)) 0 Then
With Combo2
.RowSource = "SELECT * FROM ORDERS WHERE CustomerID='" &
Combo0.Column(0) & "'"
.Requery
End With
End If
End Sub

'" -is a single quote followed by a double quote
"'"-is double single double.

Thank you. This works great. Is there a way to select a different
column with the "Select *" ?
Jan 1 '07 #8

P: n/a
On Sun, 31 Dec 2006 22:53:56 GMT, "Rick Brandt"
<ri*********@hotmail.comwrote:
>"ShyGuy" <sh****@shytown.comwrote in message
news:l0********************************@4ax.com.. .
>Thanks for the reply. The value of combo4 is text so I tried the
first sql. I get a syntax error in the following section

'" & [Forms]![0 TEST]![Combo4].[SelText] & "'

I tried removing a couple of quotes and it accepted the code but it
did not work. ;-(

Any ideas?

Try wrapping it in Eval()

Eval([Forms]![0 TEST]![Combo4].[SelText])
I get a too comlicated error with the eval.
Jan 1 '07 #9

This discussion thread is closed

Replies have been disabled for this discussion.