I think if you actually test the Change event for the combo box you will
find that it actually occurs after the contents of the combo box have
changed. I did test the code I gave the user, and it does work in the
Change event.
If you will notice in my original post that I did qualify it with "One
approach." If you want to offer another "approach" to the user, that is
great.
This response is supplied "as is" without any representations or warranties.
David said:
Nancy:
I don't know all of your specifics (like is the state located in Table B?),
so I'll make some general suggestions. One approach would be to use the
Change event of the combo box and then either use a query or the DLookup
function to find the state value. For example, if you have a textbox to
hold the state value on the form, the change event might look like:
Private Sub cmbZip_Change()
txtState = DLookup("[State]", "TableB", "Zip=" &
CLng(Forms!MyFormName!cmbZip))
End Sub
This assumes the state field is in the same table as the zip code.
David, the value of the combo box has not been updated at
the time of the Change event, the AfterUpdate event is the
preferred event for this kind of thing.
Actually, if the state is in the same table as the zip code,
then the DLookup can be avoided by including the state field
as a hidden(?) column in the combo box's RowSource query.
Then the state text box could be set using:
txtState = cmbZip.Column(1)
(assuming that the zip is in the first column and the state
is in the second column).