second combo list based on first

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

second combo list based on first

Author

Topic

MarkC
Starting Member

5 Posts

Posted - 2004-02-02 : 14:22:17

I'm a newby to the world of Access and SQL (coming away from lotus approach and am struggling with combo lists.

I have two SQL tables, one for company address details and another for contacts. It has been set up to allow multiple contacts per address based upon a identity (numeric data type). I have a combo box for selecting the company details and a second for selecting the contact details. I want to restict the second combo box to show just the contacts for the selected company. Based on other info I have read I have but the following statement in my second combo box row source details

SELECT id, title, forename, surname, active, ID2 FROM contactcompanydetails WHERE (id = 'forms!quote!combo1.column(0)')

combo1 relates to my first combo box, quote relates to my form and Column 0 relates to the identity. The problem I have is that when I select the second combo I get the following error

error converting datatype varchar to numeric

Can any one tell me why this is happening?

Yours desparatley

MarkC

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-02-02 : 17:52:05

you should have no single quotes in that statement. also, you may need to execute a requery command on that combo each time the other combo value changes to keep it up to date.

- Jeff

MarkC
Starting Member

5 Posts

Posted - 2004-02-05 : 05:03:23

Thanks for the advice Jeff, but I am now getting a different error. It's saying

Incorrect Syntax near '!'

I have rechecked all the references to the form and they seem to be correct.

quote:
Originally posted by jsmith8858

you should have no single quotes in that statement. also, you may need to execute a requery command on that combo each time the other combo value changes to keep it up to date.

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-02-05 : 08:38:17

try:

forms!quote.combo1.column(0)

- Jeff

MarkC
Starting Member

5 Posts

Posted - 2004-02-05 : 12:31:08

I've just tried your suggestion and that doesn't work either. I get the Incorrect syntax error again. Is there another method I could use in order to get the same result.

Cheers

MarkC

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-02-05 : 12:56:25

post your entire rowsource property.

- Jeff

MarkC
Starting Member

5 Posts

Posted - 2004-02-06 : 04:23:59

Jeff

I've tried the following row Sources

SELECT id, title, forename, surname, active, ID2 FROM contactcompanydetails WHERE (id = forms!quote!combo1.column(0))

SELECT id, title, forename, surname, active, ID2 FROM contactcompanydetails WHERE (id = forms!quote.combo1)

SELECT id, title, forename, surname, active, ID2 FROM contactcompanydetails WHERE (id = forms!quote.combo1.column(0))

SELECT id, title, forename, surname, active, ID2 FROM contactcompanydetails WHERE (id = "forms!quote.combo1.column(0)")

quote:
Originally posted by jsmith8858

post your entire rowsource property.

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-02-06 : 07:58:08

use VB to change the rowsource and then re-query on the CHANGE event of combo1.

i.e., if combo2 should be based on combo1's value, then:


private sub combo1_Changed()   ' the changed event
   combo2.rowsource = "Select ... from ... where id = " & combo1.value
   combo2.requery
end sub

that should work. note that if ID is a string and not a number, you might need a single quote before and after it.

try this out, let me know if it works. that's how I always do this sort of thing.

- Jeff

MarkC
Starting Member

5 Posts

Posted - 2004-02-06 : 11:10:38

That's worked! Thanks for all your help Jeff.

quote:
Originally posted by jsmith8858

use VB to change the rowsource and then re-query on the CHANGE event of combo1.

i.e., if combo2 should be based on combo1's value, then:

private sub combo1_Changed() ' the changed event
combo2.rowsource = "Select ... from ... where id = " & combo1.value
combo2.requery
end sub

that should work. note that if ID is a string and not a number, you might need a single quote before and after it.

try this out, let me know if it works. that's how I always do this sort of thing.

- Jeff

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-02-09 : 06:59:55

Also, rarely used Eval() function can be applied:

WHERE id = Eval("forms!quote!combo1.column(2)")

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-02-09 : 07:40:51

Of course, I meant to use Eval() in Properties Sheet, not in a vba code.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources