I don't know why I can't get this to work the way it should. It seems like it should be simple.
I have a form with a combo box that lists client names. There are two text boxes on the form that should show the client's city and state once the client has been selected in the combo box.
What I've been trying is
1. In the txtClientCity Control Source: =DLookUp("[CCity]","tblCli
ientID] = Form![ClientID]")
In the txtClientState Control Source: =DLookUp("[StateCode]","qr
ClientID] = Form![ClientID]")
The above produces the correct results but the information only shows in the text boxes AFTER the record is saved. I'd like it to show as soon as the combo box selection is made.
I've also tried using the same code in the After Update property of the combo box and now I'm trying it in the On Change property.
The problem with the second attempt is that it modifies the City and State text boxes for ALL records instead of the current record being created.
Private Sub cboClientID_Change()
Me.txtClientCity.Value = DLookup("[CCity]", "tblClients", "[ClientID] = Form![ClientID]")
Me.txtClientState.Value = DLookup("[StateCode]", "qryClientStateCode", "[ClientID] = Form![ClientID]")
In case it's pertinent:
The combo box has Control Source ClientID from the form's source tblJobOrders. Combo Box Row Source: SELECT tblClients.ClientID, tblClients.CName FROM tblClients; Bound column: 2 and Column Widths: 0";2"
Can anyone either fix my code or suggest an alternate way of populating two text boxes based on the selection of a combo box on a form? I'd like them to populate as soon as the selection is made in the combo box (instead of doing so after the new record is saved).
Thanks so much!