Combo Box dependent on text box

[shreyansghia]

Hello ,

I ve a form which is based on a table viz SalesOrder.

1 of the field which Sales Order has is : Client.

I ve 2 more tables, (a) Client which has 1 field viz ClientName , (b) ClientDetails which has 2 fields ClientName and Address. The tables are linked ( the link being client name) . This ofcourse means , a particular client could ve multiple addresses.

Every time a sales order is excuted , the equipments need to be supplied to a particular location as requested by the client.

Thus i ve created a combo box on the form through which i would select the address.

Now what i want is i should get to see only those locations as are pertaining to the relevant Client . The problem here is , it is not 1 combo box dependent on another combo box. The Combo box is dependent on the text box "Client" .

I am using the following After_Update code

Private Sub Client_AfterUpdate()
 
 
 
 
With Me![Combo9]
If IsNull(Me!Client) Then
.RowSource = ""
Else
.RowSource = "SELECT [ClientAddress]" & _
             " FROM [ClientDetail]" & _
             " WHERE [ClientName]=" & Me!Client
End If
 
End With
 
 
End Sub

It doesn't work. Could someone pls guide me here.

Thanks a lot.

[puppydogbuddy]

Hello ,

I ve a form which is based on a table viz SalesOrder.

1 of the field which Sales Order has is : Client.

I ve 2 more tables, (a) Client which has 1 field viz ClientName , (b) ClientDetails which has 2 fields ClientName and Address. The tables are linked ( the link being client name) . This ofcourse means , a particular client could ve multiple addresses.

Every time a sales order is excuted , the equipments need to be supplied to a particular location as requested by the client.

Thus i ve created a combo box on the form through which i would select the address.

Now what i want is i should get to see only those locations as are pertaining to the relevant Client . The problem here is , it is not 1 combo box dependent on another combo box. The Combo box is dependent on the text box "Client" .

I am using the following After_Update code

Private Sub Client_AfterUpdate()
 
With Me![Combo9]
If IsNull(Me!Client) Then
.RowSource = ""
Else
.RowSource = "SELECT [ClientAddress]" & _
             " FROM [ClientDetail]" & _
             " WHERE [ClientName]=" & Me!Client
End If
 
End With
 
 
End Sub

It doesn't work. Could someone pls guide me here.

Thanks a lot.

Your problem is most likely due to ambiguous references when you are referring to different controls. Also, you are not following standard prefixes for Access Objects.

object......... ............... .......prefix
_______________ _____________
table ............... ............... ....tbl
field.......... ............... ...........fld
form ............... ............... .....frm
textbox........ ............... ........txt
combobox....... ............... .....cbo

I've used the standard prefixes+names in place your names. If I have interpreted incorrectly, it is because I misinterpreted an ambiguous name.

Private Sub cboClient_AfterUpdate()
 
With Me![cboClient]
If IsNull(Me!txtClient) Then
.RowSource = ""
Else
.RowSource = "SELECT [ClientAddress]" & _
             " FROM [ClientDetail]" & _
             " WHERE [ClientName]=" & Me!txtClient
End If
 
End With
 
 
End Sub

[shreyansghia]

Your problem is most likely due to ambiguous references when you are referring to different controls. Also, you are not following standard prefixes for Access Objects.

object......... ............... .......prefix
_______________ _____________
table ............... ............... ....tbl
field.......... ............... ...........fld
form ............... ............... .....frm
textbox........ ............... ........txt
combobox....... ............... .....cbo

I've used the standard prefixes+names in place your names. If I have interpreted incorrectly, it is because I misinterpreted an ambiguous name.

Private Sub cboClient_AfterUpdate()
 
With Me![cboClient]
If IsNull(Me!txtClient) Then
.RowSource = ""
Else
.RowSource = "SELECT [ClientAddress]" & _
             " FROM [ClientDetail]" & _
             " WHERE [ClientName]=" & Me!txtClient
End If
 
End With
 
 
End Sub

Hi ,

I tried with ur suggestions. But it doesnt work. FOI i am using MS Access 2007.
Also as per ur code, my texd box and combo box are both named Client. However my combo is combo9 and the text box is Client.

Combo9 is dependent on textbox client.

Is there anything else u can suggest

[FishVal]

Hi, there.

I guess [ClientDetail].[ClientName] is text type table field.
SQL syntax requires string constants to be enclosed in single quotes.
So try the following modification for RowSource string.

.RowSource = "SELECT [ClientAddress]" & _
             " FROM [ClientDetail]" & _
             " WHERE [ClientName]='" & Me!txtClient & "';"
 

[shreyansghia]

Hi, there.

I guess [ClientDetail].[ClientName] is text type table field.
SQL syntax requires string constants to be enclosed in single quotes.
So try the following modification for RowSource string.

.RowSource = "SELECT [ClientAddress]" & _
             " FROM [ClientDetail]" & _
             " WHERE [ClientName]='" & Me!txtClient & "';"
 

Hi ,

Well, it still didnt work . However , i used the following for the RowSource of my combobox

SELECT [ClientDetail].[ClientAddress] FROM ClientDetail WHERE [ClientDetail].[ClientName]=Form!Client; 

It works now , but not in a very efficient manner :-).

Thanks anyways for the help