• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 654
  • Last Modified:

Autofill the city and state field in an Access 2007 subform by selecting a zip code

I want the [Zip] field in my subform (AddrDtl_frm) to autofill the [AddrDtCity] and [AddrDtCity] fields in the same subform based on the [Zip] selected in the combobox in the subform. The main form's recordsource is Addr_tbl.  The subform's recordsource is AddrDtl_tbl.  The subform and main form are linked through the [Addr] field.  Previous code worked great when I had a single form using one record source.  I'm sure I'm messing up the paths.  

Tables
Addr_Tbl
  Addr

AddrDtl_tbl
  AddrDtlLocTyp
  AddrDtlLocNum
  AddrDtCity
  AddrDtSt
  Zip
  Addr (Linked to Addr_tbl . Addr)

 Addr-tbl.doc
Private Sub Zip_AfterUpdate()
Dim varAddrDtCity, VarAddrDtSt As Variant
varAddrDtCity = DLookup("[AddrDtCity]", "Zip_tbl", "[ZipID]=Forms![Addr_Frm]![AddrDtl_frm].Form![ZipID] =" & Me!Zip)
 If (Not IsNull(varAddrDtCity)) Then Me![AddrDtCity] = Me!Zip.Column(2)
VarAddrDtSt = DLookup("[AddrDtSt]", "Zip_tbl", "[ZipID]=Forms![Addr_Frm]![AddrDtl_frm].Form![ZipID]=" & Me!Zip)
 If (Not IsNull(VarAddrSt)) Then Me![AddrSt] = Me!Zip.Column(3)

End Sub

*Worked Great when on a single form and one record source
Private Sub Zip_AfterUpdate()
Dim varAddrDtCity, VarAddrDtSt As Variant
varAddrDtCity = DLookup("AddrDtCity", "Zip_tbl", "[ZipID] =" & Me!Zip)
 If (Not IsNull(varAddrDtCity)) Then Me![AddrDtCity] = Me!Zip.Column(2)
VarAddrDtSt = DLookup("AddrDtSt", "Zip_tbl", "[ZipID]=" & Me!Zip)
 If (Not IsNull(VarAddrDtSt)) Then Me![AddrDtSt] = Me!Zip.Column(3)
End Sub

Open in new window

0
marksantelia
Asked:
marksantelia
1 Solution
 
ste5anSenior DeveloperCommented:
hi Mark,

first a comment on your code: It's not possible to declare multiple variables by enumerating them. Thus

Dim lngVal1, lngVal2 As Long

will result in lngVal1 being a Variant and lngVal2 being a Long. So your code runs fine, but you should remember this and declare always every variable, as it makes code better readable.

As you said your messing up the paths: You don't need to use any path at all. So your single form code should work seamlessly.

btw, I think you don't need the DLookup()'s at all as you have already the necessary values loaded in your ZIP combo box...

mfG
--> stefan <--
Private Sub Zip_AfterUpdate()

  If Len(Trim(Me!Zip.Column(2))) > 0 Then 
    Me![AddrDtCity] = Me!Zip.Column(2)
  End If

  If Len(Trim(Me!Zip.Column(3))) > 0 Then 
    Me![AddrDtSt] = Me!Zip.Column(3)
  End If

End Sub

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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