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

Combo Box dependent on text box

P: 25
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Client_AfterUpdate()
  2.  
  3.  
  4.  
  5.  
  6. With Me![Combo9]
  7. If IsNull(Me!Client) Then
  8. .RowSource = ""
  9. Else
  10. .RowSource = "SELECT [ClientAddress]" & _
  11.              " FROM [ClientDetail]" & _
  12.              " WHERE [ClientName]=" & Me!Client
  13. End If
  14.  
  15. End With
  16.  
  17.  
  18. End Sub

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

Thanks a lot.
Sep 16 '07 #1
Share this Question
Share on Google+
4 Replies


puppydogbuddy
Expert 100+
P: 1,923
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Client_AfterUpdate()
  2.  
  3. With Me![Combo9]
  4. If IsNull(Me!Client) Then
  5. .RowSource = ""
  6. Else
  7. .RowSource = "SELECT [ClientAddress]" & _
  8.              " FROM [ClientDetail]" & _
  9.              " WHERE [ClientName]=" & Me!Client
  10. End If
  11.  
  12. End With
  13.  
  14.  
  15. 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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboClient_AfterUpdate()
  2.  
  3. With Me![cboClient]
  4. If IsNull(Me!txtClient) Then
  5. .RowSource = ""
  6. Else
  7. .RowSource = "SELECT [ClientAddress]" & _
  8.              " FROM [ClientDetail]" & _
  9.              " WHERE [ClientName]=" & Me!txtClient
  10. End If
  11.  
  12. End With
  13.  
  14.  
  15. End Sub
Sep 16 '07 #2

P: 25
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboClient_AfterUpdate()
  2.  
  3. With Me![cboClient]
  4. If IsNull(Me!txtClient) Then
  5. .RowSource = ""
  6. Else
  7. .RowSource = "SELECT [ClientAddress]" & _
  8.              " FROM [ClientDetail]" & _
  9.              " WHERE [ClientName]=" & Me!txtClient
  10. End If
  11.  
  12. End With
  13.  
  14.  
  15. 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
Sep 17 '07 #3

FishVal
Expert 2.5K+
P: 2,653
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.
Expand|Select|Wrap|Line Numbers
  1. .RowSource = "SELECT [ClientAddress]" & _
  2.              " FROM [ClientDetail]" & _
  3.              " WHERE [ClientName]='" & Me!txtClient & "';"
  4.  
Sep 17 '07 #4

P: 25
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.
Expand|Select|Wrap|Line Numbers
  1. .RowSource = "SELECT [ClientAddress]" & _
  2.              " FROM [ClientDetail]" & _
  3.              " WHERE [ClientName]='" & Me!txtClient & "';"
  4.  
Hi ,

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

Expand|Select|Wrap|Line Numbers
  1. 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
Sep 20 '07 #5

Post your reply

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