Again-automatically entering zipcodes

[Guest]

I have a table named "Zipcodes" with two fields: City and Zip

On my main form, I have a control named "City" with a drop down list box
that derives its contents from the "City" field of the "Zipcodes Table".

After I select the City from the dropdown list, I would like Access to
insert the corresponding zipcode (found in the Zip field of the Zipcodes
Table) in a control on my form named "Zipcode". I assume that if that
zipcode field on my form is bound to my primary table that stores all the
data entered on the form, the inserted zipcode will also be stored in the
primary table once the record is stored.

I can't figure out how do this. I tried writing a macro that would execute
on "On Change" of the City field of my form (once the city was selected from
the dropdown box). This macro reads as follows:

SET VALUE: [Forms]![Main]![zipcode]

EXPRESSIONLookUp("[Zip]",

![Zipcode],[Table!zipcode!City=[Forms!Main!City)])

I would appreciate your suggestions on the simplest way to do this.
Steve

 

[Steve Schapel]

Steve,

You have the syntax of the SetValue arguments wrong.

Since it is on the same form, the Forms! reference in the Item is not
necessary.

![Zipcode] doesn't make sense.

Item: [zipcode]
Expression: DLookUp("[Zip]","[Zipcodes]","[City]='" & [City] & "'")

Another approach would be to make the Column Count property of the City
combobox = 2 so that the Zip field is also included in the combobox's
Row Source. Then your SetValue Expression could be like this...
Expression: [City].[Column](1)

 

[Guest]

Thanks, but I still can't get this to work.. In fact, I can't get ANY
setvalue macro to work to do anything!

I wrote a setvalue macro to set the value of control "Textbox1" to equal the
value of the control "City" and no matter what I change or do, I always get
the following error: "User-defined type not defined". Same thing happens
when I wrote a simple macro to set the value of a textbox to a typed in value
(e.g., "Hello").

Am I doing something wrong?

Steve,

You have the syntax of the SetValue arguments wrong.

Since it is on the same form, the Forms! reference in the Item is not
necessary.

![Zipcode] doesn't make sense.

Item: [zipcode]
Expression: DLookUp("[Zip]","[Zipcodes]","[City]='" & [City] & "'")

Another approach would be to make the Column Count property of the City
combobox = 2 so that the Zip field is also included in the combobox's
Row Source. Then your SetValue Expression could be like this...
Expression: [City].[Column](1)

--
Steve Schapel, Microsoft Access MVP

I have a table named "Zipcodes" with two fields: City and Zip

On my main form, I have a control named "City" with a drop down list box
that derives its contents from the "City" field of the "Zipcodes Table".

After I select the City from the dropdown list, I would like Access to
insert the corresponding zipcode (found in the Zip field of the Zipcodes
Table) in a control on my form named "Zipcode". I assume that if that
zipcode field on my form is bound to my primary table that stores all the
data entered on the form, the inserted zipcode will also be stored in the
primary table once the record is stored.

I can't figure out how do this. I tried writing a macro that would execute
on "On Change" of the City field of my form (once the city was selected from
the dropdown box). This macro reads as follows:

SET VALUE: [Forms]![Main]![zipcode]

EXPRESSIONLookUp("[Zip]",

![Zipcode],[Table!zipcode!City=[Forms!Main!City)])

I would appreciate your suggestions on the simplest way to do this.
Steve

 

[Steve Schapel]

Stephen, can you post back with the exact entries in the Item and
Expression arguments of these SetValue macros you have experimented
with, and also what event you are using to run the macro.? Thanks.

 

[Guest]

Macro Reads:
Set Value: [Forms]![Main]![Box1]
[Forms]![Main]![Combo180]

In the Properties of Combo 180 , I have Macro1 set to execute on "On Exit"
Note..I have also tried to run it on many other events (e.g., on change,
enter, click, etc).

When I select an entry from the dropdown combo box, and hit "Return" or
click on some other control, I get a message "User-defined type not defined"
and the "Action Failed" window appears as follows:
Macro name: Macro1
Condition:True
Action Name: SetValue
Arguments: [Forms]![Main]![Box1], [Forms]![Main]![Combo180]

Note; Both controls are "unbound" and if I start a brand new Access database
with nothing on the form other than the two referenced controls, it works
fine!

 

[Steve Schapel]

Stephen,

Well, I think the After Update event of Combo180 is the most appropriate
one to use. And, as I mentioned before, I would advise against the
circular reference of the form to itself - just set the arguments as...
Item: [Box1]
Expression: [Combo180]

But neither of these points appears to be responsible for the error you
are getting. The fact that it works in a new database would indicate
some other sort of problem on the form itself. I suppose some sort of
curruption is a possibility. Box1 is a textbox, right? And you are not
using it as a subform, right?

Hmmm, sorry, I don't know. If it works on your new form, I would scrap
this database, import the other objects into the new database, and carry
on regardless...