Forms:Fill Fields automatically

[Kim]

Some MVP designed this code
can I use it differently and how?


Sub Zip_OnExit(Cancel As Integer)
Dim varState, varCity As Variant
varState = DLookup("State","tblZipCode", "ZipCode =[Zip]")
varCity = DLookup("City","tblZipCode","ZipCode = [Zip]")
If(Not IsNull(varState))Then Me![State]= varState
If(Not IsNull(varState))Then Me![City] = varCity

End Sub


Here is my information:
I have a query turned into a form and a table.

Form name Copy of qry:CANTEST

boxes on form:

NEW CAN#
FY
Transaction Amount


My table name tbl:CANFY2003
Fields:
CAN
RESEARCH
DIVISION
POOL
Notes
I understand that there are other ways to accomplish this
but I want to use code and I'm trying to figure out if I
need to design a box on a form or go into the already
created text box on the form and open up the code window
to design something like above. all I want to do as a
project is to use the above code to look at a form match
the form with a table and add the tables fields to the
form automatically.

 

[Ted Allen]

Hi Kim,

Your post is a little confusing because the code that you
posted is designed to fill in the values of some fields
on a form based on the entry in another field. it would
not add the fields to the form based on fields contained
in a table.

I'm also unsure what you are trying to accomplish, since
Access already has a built in wizard that will add fields
to a form based on table/query selections (which I rarely
use because you just end up redesigning everything
anyway). Also, in design view of a form you can just
drag fields from the field list based on the form's
record source.

Maybe I misunderstood your post, it could be just a
difference of terminology. Please post back with more
info. If you are trying to autofill the values in some
of your fields based on the values in other fields, post
examples of what would be entered and how you would want
to determine the lookup value.

-Ted Allen