Follow up: Towns and Zip codes

[smackedass]

Hello,

Thanks again for your help. I posted below, here's part of my message:

Specifically: I have a table set up that has people's names, addresses, and
all kinds of other stuff. What I want to do is write code instructing the
Zip Code field to pull an established zip code once it reads the name of the
town (am dealing with approx. 10 towns).

I went to the recommended link, and tried the following code:

Sub Town_OnExit(Cancel As Index)
Dim varZipCode As Integer
var ZipCode = DLookup("Zip code", "tblTown", "Town = Harwich")
If (NotIsNull(varZipCode)) Then Me![02645] = varTown

When I go to debug it tells me: "Variable Not Defined", then shows 'ZipCode'
(from the 3rd line) highlighted.

Again, this definitely is not my area of expertise. From what little I do
know, it looks like the variable is defined. Perhaps it shouldn't be an
integer...if not, what should it be?

Can anyone offer any suggestions as to what I might be doing wrong?

Thanks, again.

smackedass

 

[Douglas J. Steele]

Assuming that by the 3rd line you mean:

var ZipCode = DLookup("Zip code", "tblTown", "Town = Harwich")

the problem would appear to be the space between var and ZipCode. However,
even after you fix that, you'll run into a problem with the DLookup
statement because of the space in the field name Zip Code in tblTown. As
well, since Town is a text field, you need quotes around the value you're
trying to look up.

And you're right that it shouldn't be declared as an integer. Even if the
Zip Code field in tblTown is an integer, if there isn't an entry in the
table for the specific town you're looking up, DLookup will return Null, and
only variants can accept Null values.

Finally, there is not NotIsNull function, only an IsNull function. You need
a space there.

Try:

Sub Town_OnExit(Cancel As Index)
Dim varZipCode As Variant

varZipCode = DLookup("[Zip code]", "tblTown", "Town = 'Harwich'")

If (Not IsNull(varZipCode)) Then Me![02645] = varTown

Alternatively, the last line could be

If (IsNull(varZipCode) = False) Then Me![02645] = varTown


However, where is varTown defined?

 

[Mike Painter]

Hello,

Thanks again for your help. I posted below, here's part of my
message:

Specifically: I have a table set up that has people's names,
addresses, and all kinds of other stuff. What I want to do is write
code instructing the Zip Code field to pull an established zip code
once it reads the name of the town (am dealing with approx. 10 towns).

If there are only ten towns and the town does not have more than one zip
code then there is no need to write any code. Make the town name the key
field and relate the two. If the name is in a listbox or a combo box with
dropdown set (Me!MYCombo.dropdown) in onEnter it's one click to pick the
town and the zip code.
This will work in a slightly different manner even if there is more than one
ZIP per town. No code and minimal typing.

 

[smackedass]

Thanks, again, for all of your help. I don't have it figured out yet, but
I've got plenty to work with.

smackedass