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

Cascading Combo box.

P: 25
Hello ,

I am trying to ve a cascading combo box in my form. I ve 2 Combo Boxes. Combo0 is based on the table TEST 1 and combo8 is based on table TEST 2.

TEST 1 has Company ID (Primary Key) , Company Name .

TEST2 has Address , Company ID

I ve linked the 2 tables through company ID.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo0_AfterUpdate()
  2.  
  3.  
  4. With Me![Combo8]
  5. If IsNull(Me!Combo0) Then
  6. .RowSource = " "
  7. Else
  8. .RowSource = "SELECT [Address] " & _
  9.              "FROM TblTEST 2 " & _
  10.              "WHERE [Company ID]=" & Me!Combo0
  11. End If
  12. Call .Requery
  13. End With
  14. End Sub
The above does nt work for me.

Could anyone please tell me where am i going wrong.

Thanks a lot.

Regards ,
Shreyans
Sep 2 '07 #1
Share this Question
Share on Google+
8 Replies


JConsulting
Expert 100+
P: 603
Hello ,

I am trying to ve a cascading combo box in my form. I ve 2 Combo Boxes. Combo0 is based on the table TEST 1 and combo8 is based on table TEST 2.

TEST 1 has Company ID (Primary Key) , Company Name .

TEST2 has Address , Company ID

I ve linked the 2 tables through company ID.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo0_AfterUpdate()
  2.  
  3.  
  4. With Me![Combo8]
  5. If IsNull(Me!Combo0) Then
  6. .RowSource = " "
  7. Else
  8. .RowSource = "SELECT [Address] " & _
  9.              "FROM TblTEST 2 " & _
  10.              "WHERE [Company ID]=" & Me!Combo0
  11. End If
  12. Call .Requery
  13. End With
  14. End Sub
The above does nt work for me.

Could anyone please tell me where am i going wrong.

Thanks a lot.

Regards ,
Shreyans

See this link...it's the same thing.

http://www.thescripts.com/forum/thread701767.html
Sep 3 '07 #2

P: 25
See this link...it's the same thing.

http://www.thescripts.com/forum/thread701767.html
Hello,

I incorporated the changes as suggested by you at the above link.

Below is the code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo24_AfterUpdate()
  2. With Me![Combo30]
  3. If IsNull(Me!Combo24) Then
  4. .RowSource = ""
  5. Else
  6. .RowSource = "SELECT [Address]" & _
  7.              " FROM TblTEST 2" & _
  8.              " WHERE [Company ID]=" & Me!Combo24
  9. End If
  10.  
  11. End With
  12. End Sub
It still does not work. Just FOI i am using Access 2007.

Could u please help me out here .

Thanks
Sep 3 '07 #3

Expert 100+
P: 126
Hello,

I incorporated the changes as suggested by you at the above link.

Below is the code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo24_AfterUpdate()
  2. With Me![Combo30]
  3. If IsNull(Me!Combo24) Then
  4. .RowSource = ""
  5. Else
  6. .RowSource = "SELECT [Address]" & _
  7.              " FROM TblTEST 2" & _
  8.              " WHERE [Company ID]=" & Me!Combo24
  9. End If
  10.  
  11. End With
  12. End Sub
It still does not work. Just FOI i am using Access 2007.

Could u please help me out here .

Thanks
Although you didn't give the error message, I think the problem is your table name. Ideally, there shouldn't be spaces in the name, eg. tblTEST_2, but in this case, using square brackets should do the trick:
Expand|Select|Wrap|Line Numbers
  1. .RowSource = "SELECT [Address] FROM [TblTEST 2] WHERE [Company ID] =" & Me!Combo24 & ";"
  2.  
NOTE: If Company ID is not stored as a number (regardless of whether it is numeric or not), you need ' ' (or " " but these need escape characters) either side of the WHERE, so it would be
Expand|Select|Wrap|Line Numbers
  1.  [Company ID] = '" & Me!Combo24 & "';"
Sep 3 '07 #4

P: 25
Although you didn't give the error message, I think the problem is your table name. Ideally, there shouldn't be spaces in the name, eg. tblTEST_2, but in this case, using square brackets should do the trick:
Expand|Select|Wrap|Line Numbers
  1. .RowSource = "SELECT [Address] FROM [TblTEST 2] WHERE [Company ID] =" & Me!Combo24 & ";"
  2.  
NOTE: If Company ID is not stored as a number (regardless of whether it is numeric or not), you need ' ' (or " " but these need escape characters) either side of the WHERE, so it would be
Expand|Select|Wrap|Line Numbers
  1.  [Company ID] = '" & Me!Combo24 & "';"
Hello ,

Thanks for the quick response.

I ve stored Company ID has number.

As regards the space in "TEST 2" ; i enclosed it with the square. However , i still didnt work . So i tried removing the space i.e TEST2.
But the error mesg that i get always is that

'SELECT [Address] FROM TblTEST2 WHERE [Company ID]=1' specified on this form does not exists.

Its my third day on this and still cant get this solved :-(
Sep 3 '07 #5

Expert 100+
P: 126
Hello ,

Thanks for the quick response.

I ve stored Company ID has number.

As regards the space in "TEST 2" ; i enclosed it with the square. However , i still didnt work . So i tried removing the space i.e TEST2.
But the error mesg that i get always is that

'SELECT [Address] FROM TblTEST2 WHERE [Company ID]=1' specified on this form does not exists.

Its my third day on this and still cant get this solved :-(
The table name needs to match the actual name of your table exactly.
Put a semi-colon at the end of the rowsource, and ensure that the RowSourceType of this combobox is set to Table/Query at design time.
Sep 3 '07 #6

P: 25
The table name needs to match the actual name of your table exactly.
Put a semi-colon at the end of the rowsource, and ensure that the RowSourceType of this combobox is set to Table/Query at design time.
Yes , its exactly as what u ve said .
for combo 24
RowSource = SELECT [TEST1].[Company ID], [TEST1].[Company Name] FROM TEST1;

RowSourceType= Table/query.

Can i mail you my db .
Sep 3 '07 #7

P: 25
--------------------
I ve mailed you at: [email removed]

Thanks
Sep 3 '07 #8

JConsulting
Expert 100+
P: 603
Yes , its exactly as what u ve said .
for combo 24
RowSource = SELECT [TEST1].[Company ID], [TEST1].[Company Name] FROM TEST1;

RowSourceType= Table/query.

Can i mail you my db .

Do you by chance have ' marks in some of your company names?

Example: O'Hara's Nightclub

??
J
Sep 3 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.