Cascading Combo box.

[shreyansghia]

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.

Private Sub Combo0_AfterUpdate()


With Me![Combo8]
If IsNull(Me!Combo0) Then
.RowSource = " "
Else
.RowSource = "SELECT [Address] " & _
             "FROM TblTEST 2 " & _
             "WHERE [Company ID]=" & Me!Combo0
End If
Call .Requery
End With
End Sub

The above does nt work for me.

Could anyone please tell me where am i going wrong.

Thanks a lot.

Regards ,
Shreyans

[JConsulting]

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.

Private Sub Combo0_AfterUpdate()


With Me![Combo8]
If IsNull(Me!Combo0) Then
.RowSource = " "
Else
.RowSource = "SELECT [Address] " & _
             "FROM TblTEST 2 " & _
             "WHERE [Company ID]=" & Me!Combo0
End If
Call .Requery
End With
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.ht ml

[shreyansghia]

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:

Private Sub Combo24_AfterUpdate()
With Me![Combo30]
If IsNull(Me!Combo24) Then
.RowSource = ""
Else
.RowSource = "SELECT [Address]" & _
             " FROM TblTEST 2" & _
             " WHERE [Company ID]=" & Me!Combo24
End If

End With
End Sub

It still does not work. Just FOI i am using Access 2007.

Could u please help me out here .

Thanks

[Stwange]

Hello,

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

Below is the code:

Private Sub Combo24_AfterUpdate()
With Me![Combo30]
If IsNull(Me!Combo24) Then
.RowSource = ""
Else
.RowSource = "SELECT [Address]" & _
             " FROM TblTEST 2" & _
             " WHERE [Company ID]=" & Me!Combo24
End If

End With
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:

.RowSource = "SELECT [Address] FROM [TblTEST 2] WHERE [Company ID] =" & Me!Combo24 & ";"

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

 [Company ID] = '" & Me!Combo24 & "';"

[shreyansghia]

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:

.RowSource = "SELECT [Address] FROM [TblTEST 2] WHERE [Company ID] =" & Me!Combo24 & ";"

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

 [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 :-(

[Stwange]

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.

[shreyansghia]

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 .

[shreyansghia]

--------------------

I ve mailed you at: [email removed]

Thanks

[JConsulting]

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