[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 347
  • Last Modified:

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
0
mbmartin0409
Asked:
mbmartin0409
  • 4
  • 4
1 Solution
 
teedo757Commented:
Can you set the City and state back to null after the update?
0
 
mbmartin0409Author Commented:
I'm guessing that might do it, but.... how? :-)
0
 
Rey Obrero (Capricorn1)Commented:
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
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
Rey Obrero (Capricorn1)Commented:
<but the city and state fields are unbound.>

where do you use these two Controls? purpose ?
0
 
mbmartin0409Author Commented:
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?
0
 
Rey Obrero (Capricorn1)Commented:
<should I change the 'unbound' properties of those two fields to be bound to the corresponding city and state fields of the table?>

YES
0
 
mbmartin0409Author Commented:
--> 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.
0
 
mbmartin0409Author Commented:
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.
0
 
Rey Obrero (Capricorn1)Commented:
* 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
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now