mbmartin0409
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
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
Can you set the City and state back to null after the update?
ASKER
I'm guessing that might do it, but.... how? :-)
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
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 ?
where do you use these two Controls? purpose ?
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
--> 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.
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.
ASKER
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
* when browsing or editing records, what was previously entered data will be displayed in the form