Link to home
Start Free TrialLog in
Avatar of mbmartin0409
mbmartin0409Flag for United States of America

asked on

Access 2010: synchronized combo boxes; how do display what's saved in field?

I have 3 related fields on a form, City, State, and Zip.  When zip is entered, the the city and state fields auto populate ... that works.  BUT  if the user doesn't actually ENTER a zip on the form, the form displays the last state and ciity that was entered.  If the user copies the displayed zip and pastes it back, then the city and state fields update.   On the form, the zip field is bound to the zip field in the table, but the city and state fields are unbound.  Here's the 'after update' code I have:  
Private Sub ZIP_AfterUpdate()
    Me!City = DLookup("City", "zipcodedatabase", "zip = '" & Me.ZIP & "'")
    Me!State = DLookup("State", "zipcodedatabase", "zip = '" & Me.ZIP & "'")
End Sub

Any ideas?  clearly i'm a NEOPHYTE with VB
Avatar of teedo757
teedo757

Can you set the City and state back to null after the update?
Avatar of mbmartin0409

ASKER

I'm guessing that might do it, but.... how? :-)
Avatar of Rey Obrero (Capricorn1)
clear the City and State textboxes AFTER when you go to new record, using the current event of the form

private sun form_current()

if me.newrecord then
 me.city=""
me.state=""

end if

end sub
<but the city and state fields are unbound.>

where do you use these two Controls? purpose ?
Thanks, Capricorn1 ---   this addition works if the user selects "save and new" --- but when navigating to the 'next' or any other record, the city and state fields are blank.  should I change the 'unbound' properties of those two fields to be bound to the corresponding city and state fields of the table?
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
--> capricorn1:
This form is used to a) enter new records and b) manage/followup on current records.  Zip (and then city/state) are entered when the record is created, and then used for followup actions.   Zip is bound to field 'zip' but currently i have the city and state as 'unbound' - thinking i had to keep them that way to be automatically updated with my dlookup afterupdate event with zip.
Thanks Capricorn1 - You're the BEST!    maybe I should hire you to review my whole project for suggestions and comments.  You've helped me twice now.
* when creating new records, all controls/fields will be blank
* when browsing or editing records, what was previously entered data will be displayed in the form