Limit values in combo box

[ShyGuy]

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.subTrad e)=[Forms]![0 TEST]![Combo4].[SelText])) ORDER BY
Subs.SubName;

Any help will be greatly appreciated.

[Jeff Smith]

Re: Limit values in combo box


"ShyGuy" <shyguy@shytown .comwrote in message
news:6u5ep2la3e j1ia5r6v3npecn3 ubnk2hnq4@4ax.c om...

>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.subTrad e)=[Forms]![0 TEST]![Combo4].[SelText])) ORDER BY
Subs.SubName;
>
Any help will be greatly appreciated.

Try
(((Subs.subTrad e)=[Forms]![0 TEST]![Combo4].Column(0)))

[ShyGuy]

Re: Limit values in combo box

On Sun, 31 Dec 2006 15:45:52 +1300, "Jeff Smith"
<NoSpam@not.thi s.addresswrote:

>(((Subs.subTra de)=[Forms]![0 TEST]![Combo4].Column(0)))

I get an "undefined function" error with this.

[JHB]

Re: Limit values in combo box

If the value of [Forms]![0 TEST]![Combo4].[SelText] is text then
SELECT Subs.subID, Subs.SubName, Subs.subTrade FROM Subs WHERE
(((Subs.subTrad e)=" & "'" & [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.subTrad e)=" & "" & [Forms]![0 TEST]![Combo4].[SelText] & "" & "))
ORDER BY Subs.SubName;

Regards
Jørn


"ShyGuy" <shyguy@shytown .comskrev i en meddelelse
news:6u5ep2la3e j1ia5r6v3npecn3 ubnk2hnq4@4ax.c om...

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.subTrad e)=[Forms]![0 TEST]![Combo4].[SelText])) ORDER BY
Subs.SubName;
>
Any help will be greatly appreciated.

[ShyGuy]

Re: Limit values in combo box

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" <joern_bea@tdca dsl.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.subTra de)=" & "'" & [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.subTra de)=" & "" & [Forms]![0 TEST]![Combo4].[SelText] & "" & "))
>ORDER BY Subs.SubName;
>
>Regards
>Jørn
>
>
>"ShyGuy" <shyguy@shytown .comskrev i en meddelelse
>news:6u5ep2la3 ej1ia5r6v3npecn 3ubnk2hnq4@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.subTra de)=[Forms]![0 TEST]![Combo4].[SelText])) ORDER BY
>Subs.SubName ;
>>
>Any help will be greatly appreciated.

>

[Rick Brandt]

Re: Limit values in combo box

"ShyGuy" <shyguy@shytown .comwrote in message
news:l0cgp25nvi 94q6778r11ftnb9 s4nh5is8p@4ax.c om...

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

[Lyle Fairfield]

Re: Limit values in combo box

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_AfterUpd ate()
If Len(Combo0.Colu mn(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.

[ShyGuy]

Re: Limit values in combo box

On 31 Dec 2006 16:38:16 -0800, "Lyle Fairfield"
<lylefairfield@ 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_AfterUpd ate()
If Len(Combo0.Colu mn(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 *" ?

[ShyGuy]

Re: Limit values in combo box

On Sun, 31 Dec 2006 22:53:56 GMT, "Rick Brandt"
<rickbrandt2@ho tmail.comwrote:

>"ShyGuy" <shyguy@shytown .comwrote in message
>news:l0cgp25nv i94q6778r11ftnb 9s4nh5is8p@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.