469,311 Members | 2,515 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,311 developers. It's quick & easy.

How can I autofil the City and State fields by entering a Zip?

Im trying to create an autofil for the city and state fields of my database by just entering a zip code. I have tried a few different things but it doesnt populate correctly. I haven't had to write programming since college so Im not very up to date on my language and I cant seem to figure it out. If anyone has any ideas, I would really appreciate it.
Feb 27 '07 #1
65 3874
Rabbit
12,516 Expert Mod 8TB
Assuming you have a table that stores City, State, and Zip Code information, then in the After Update event of the Zip Code control:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Zip_AfterUpdate()
  2.    Me.[Name of City control] = DLookup("[Name of City field]", "[Table Name]", "[Name of Zip Code field] = " & Me.Zip)
  3.    Me.[Name of State control] = DLookup("[Name of State field]", "[Table Name]", "[Name of Zip Code field] = " & Me.Zip)
  4. End Sub
Feb 27 '07 #2
Assuming you have a table that stores City, State, and Zip Code information, then in the After Update event of the Zip Code control:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Zip_AfterUpdate()
  2.    Me.[Name of City control] = DLookup("[Name of City field]", "[Table Name]", "[Name of Zip Code field] = " & Me.Zip)
  3.    Me.[Name of State control] = DLookup("[Name of State field]", "[Table Name]", "[Name of Zip Code field] = " & Me.Zip)
  4. End Sub

Thank you for responding. When I did that and then tried to run it I got an error message that says the macro or its macro group doesnt exist or the macro is new but hasnt been saved. What do I do now?
Feb 27 '07 #3
Rabbit
12,516 Expert Mod 8TB
You must be trying to put it in the properties. What you need to do is set the After Update event property to [Event Procedure] and place the code in the form's module in the visual basic editor.
Feb 27 '07 #4
You must be trying to put it in the properties. What you need to do is set the After Update event property to [Event Procedure] and place the code in the form's module in the visual basic editor.
I went to view code and put it there. Is that not correct?
Feb 27 '07 #5
Rabbit
12,516 Expert Mod 8TB
That's correct. But check the Zip Code control's After Update property and make sure that's set to [Event Procedure]
Feb 27 '07 #6
That's correct. But check the Zip Code control's After Update property and make sure that's set to [Event Procedure]
I did that and the same error comes up. This time is says event procedure though. I must be doing something wrong but Im having trouble figuring out what...
Feb 27 '07 #7
Rabbit
12,516 Expert Mod 8TB
Did you change [Name of City control], [Name of State control], etc. to represent what you actually have on your form? I didn't know what was actually on your form so I had to come up with stuff. You need to rename everything in my example.
Feb 27 '07 #8
Did you change [Name of City control], [Name of State control], etc. to represent what you actually have on your form? I didn't know what was actually on your form so I had to come up with stuff. You need to rename everything in my example.
I did do that. Perhaps my names need to be more unique between the control and the table the information is coming from?
Feb 28 '07 #9
Rabbit
12,516 Expert Mod 8TB
Post what you've got and I'll take a look to see if there's anything wrong.
Feb 28 '07 #10
Post what you've got and I'll take a look to see if there's anything wrong.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Zip_AfterUpdate()
  2.    Me.[CITY] = DLookup("[City]", "[zip-code]", "[Zip] = " & Me.ZIP)
  3.    Me.[STATE] = DLookup("[State]", "[zip-code]", "[Zip] = " & Me.ZIP)
  4. End Sub
Mar 1 '07 #11
Rabbit
12,516 Expert Mod 8TB
Expand|Select|Wrap|Line Numbers
  1. Private Sub Zip_AfterUpdate()
  2.    Me.[CITY] = DLookup("[City]", "[zip-code]", "[Zip] = " & Me.ZIP)
  3.    Me.[STATE] = DLookup("[State]", "[zip-code]", "[Zip] = " & Me.ZIP)
  4. End Sub
You forgot to rename the [Zip] field from my code. It should be:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Zip_AfterUpdate()
  2.    Me.[CITY] = DLookup("[City]", "[zip-code]", "[zip-code] = " & Me.[zip-code])
  3.    Me.[STATE] = DLookup("[State]", "[zip-code]", "[zip-code] = " & Me.[zip-code])
  4. End Sub
Mar 1 '07 #12
You forgot to rename the [Zip] field from my code. It should be:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Zip_AfterUpdate()
  2.    Me.[CITY] = DLookup("[City]", "[zip-code]", "[zip-code] = " & Me.[zip-code])
  3.    Me.[STATE] = DLookup("[State]", "[zip-code]", "[zip-code] = " & Me.[zip-code])
  4. End Sub
Even though the table the information is coming from and the Zip code field have different names?
Mar 1 '07 #13
Rabbit
12,516 Expert Mod 8TB
Post the information about your tables. i.e. The fields and kind of data it holds.
Mar 1 '07 #14
Post the information about your tables. i.e. The fields and kind of data it holds.
Unfotunately I cannot post that information due to security reasons. I am unable to share the content of the database because it is for work. Thank you for your help though.
Mar 5 '07 #15
Rabbit
12,516 Expert Mod 8TB
I didn't mean the actual data, just the names of the fields.
Expand|Select|Wrap|Line Numbers
  1. Tbl_Main
  2. [ID]; PK; Autonumber
  3. [State]; Text
  4. [Zip]; Numeric
Mar 5 '07 #16
Would you need a table that already has all of this relevant information to do something like this? What I mean is, wouldn't you need a table that has the City, State, Zip Code already populated? Not sure if I'm making any sense here.
Mar 5 '07 #17
You probably have a Table with the relevent fields i.e. ZIPId, CITY, STATE, and ZIP already populated with all the data you will need, so, Make your ZIP field on the form a dropdown with four columns referencing your table and set the column widths to 0,0,0,2.5cm so as to show only the zipcode, now people can either type in the zip or select, That done you make the City and State fields = [ZIP].column(1) and [ZIP].column (2) remembering that ZIPId would be column(0) and not required, Don't forget to make the City and State fields Locked to prevent rubbish being entered. You can use the same in Reports printed from your Data. Hope that helps, it always works for me!
Andy
Mar 5 '07 #18
I didn't mean the actual data, just the names of the fields.
Expand|Select|Wrap|Line Numbers
  1. Tbl_Main
  2. [ID]; PK; Autonumber
  3. [State]; Text
  4. [Zip]; Numeric
COUNTER
EBDORMANT OR TRESCHEAT
DATE PROCESSED
YEAR DUE
HOLDER STATE
CODE
CHECK #
LAST ACTIVITY DATE
AMOUNT
UNITS
DDA/SUSPENSE ACCT
CO CODE
PAYEE LAST NAME
TITLE1
MULTI OWNER
CO OWNER LAST
TITLE2
STREET ADDRESS
STREET ADDRESS 2
CITY
STATE
ZIP
SS #
TR-ACCT NUMBER
ADMINISTRATOR
ASSET/TR-ACCT NAME
CUSIP NUMBER
DUE DILIGENCE MEMO
DATE PAID
MEMO
PAID TO CUSTOMER
Mar 6 '07 #19
Rabbit
12,516 Expert Mod 8TB
COUNTER
EBDORMANT OR TRESCHEAT
DATE PROCESSED
YEAR DUE
HOLDER STATE
CODE
CHECK #
LAST ACTIVITY DATE
AMOUNT
UNITS
DDA/SUSPENSE ACCT
CO CODE
PAYEE LAST NAME
TITLE1
MULTI OWNER
CO OWNER LAST
TITLE2
STREET ADDRESS
STREET ADDRESS 2
CITY
STATE
ZIP
SS #
TR-ACCT NUMBER
ADMINISTRATOR
ASSET/TR-ACCT NAME
CUSIP NUMBER
DUE DILIGENCE MEMO
DATE PAID
MEMO
PAID TO CUSTOMER
And is this the table that you want to look up City/State using zip code? If not, give me the information on the lookup table. If it is, then I misassumed that you had a seperate table with every zip code of every city and state.

This lookup will only work insofar as you have a customer from that city/state/zip code. Which should work if you put the code in the After Update event of the Zip Control and you named everything correctly.
Mar 6 '07 #20
And is this the table that you want to look up City/State using zip code? If not, give me the information on the lookup table. If it is, then I misassumed that you had a seperate table with every zip code of every city and state.

This lookup will only work insofar as you have a customer from that city/state/zip code. Which should work if you put the code in the After Update event of the Zip Control and you named everything correctly.
Oh Im sorry, I do have a seperate table for the City State Zip.

The fields are

ZipId
ZipCode
City
State
DefaulyCity
Mar 7 '07 #21
Rabbit
12,516 Expert Mod 8TB
Oh Im sorry, I do have a seperate table for the City State Zip.

The fields are

ZipId
ZipCode
City
State
DefaulyCity
Assuming that the above table is named [zip-code]:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Zip_AfterUpdate()
  2.    Me.[CITY] = DLookup("[City]", "[zip-code]", "[ZipCode] = " & Me.ZIP)
  3.    Me.[STATE] = DLookup("[State]", "[zip-code]", "[ZipCode] = " & Me.ZIP)
  4. End Sub
Mar 7 '07 #22
Assuming that the above table is named [zip-code]:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Zip_AfterUpdate()
  2.    Me.[CITY] = DLookup("[City]", "[zip-code]", "[ZipCode] = " & Me.ZIP)
  3.    Me.[STATE] = DLookup("[State]", "[zip-code]", "[ZipCode] = " & Me.ZIP)
  4. End Sub
Now it says Run-time error '3464':
data type mismatch in criteria expression.

Does that mean I have labels wrong?
Mar 7 '07 #23
Rabbit
12,516 Expert Mod 8TB
Now it says Run-time error '3464':
data type mismatch in criteria expression.

Does that mean I have labels wrong?
That most likely means your zip code field is text.

Try:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Zip_AfterUpdate()
  2.    Me.[CITY] = DLookup("[City]", "[zip-code]", "[ZipCode] = '" & Me.ZIP & "'")
  3.    Me.[STATE] = DLookup("[State]", "[zip-code]", "[ZipCode] = '" & Me.ZIP & "'")
  4. End Sub
Mar 7 '07 #24
That most likely means your zip code field is text.

Try:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Zip_AfterUpdate()
  2.    Me.[CITY] = DLookup("[City]", "[zip-code]", "[ZipCode] = '" & Me.ZIP & "'")
  3.    Me.[STATE] = DLookup("[State]", "[zip-code]", "[ZipCode] = '" & Me.ZIP & "'")
  4. End Sub
You were right. Thank you so much for your help, I really appreciate it.
Mar 9 '07 #25
Rabbit
12,516 Expert Mod 8TB
Not a problem, good luck.
Mar 9 '07 #26
Not a problem, good luck.
I actually have one more question. It finally works and what I now would like to do is have it only enter the City and State fields if it doesnt recognize the zip code. i.e. if there is a zip code that isnt in my database, I want to be able to enter it manually. Otherwise, I want it to skip right over the City and State fields. Is that possible?
Mar 12 '07 #27
Rabbit
12,516 Expert Mod 8TB
I actually have one more question. It finally works and what I now would like to do is have it only enter the City and State fields if it doesnt recognize the zip code. i.e. if there is a zip code that isnt in my database, I want to be able to enter it manually. Otherwise, I want it to skip right over the City and State fields. Is that possible?
Yes, this is possible. You just set the focus to a different control depending on whether or not City or State is a null.
Expand|Select|Wrap|Line Numbers
  1. If IsNull(Me.City) Then
  2.      Me.City.SetFocus
  3. Else
  4.      Me.[Other Control Name].SetFocus
  5. End If
Mar 12 '07 #28
Yes, this is possible. You just set the focus to a different control depending on whether or not City or State is a null.
Expand|Select|Wrap|Line Numbers
  1. If IsNull(Me.City) Then
  2.      Me.City.SetFocus
  3. Else
  4.      Me.[Other Control Name].SetFocus
  5. End If
Im not sure I understand what I would use as the [other control name]. Is it something I can just choose or would there be a name from my table I should use?
Mar 12 '07 #29
Rabbit
12,516 Expert Mod 8TB
Im not sure I understand what I would use as the [other control name]. Is it something I can just choose or would there be a name from my table I should use?
It's something you would choose. Whatever the next textbox or whatever you want them to go to instead of city/state.
Mar 12 '07 #30
It's something you would choose. Whatever the next textbox or whatever you want them to go to instead of city/state.
Oh ok. Thank you, you have been such a great help!
Mar 12 '07 #31
Rabbit
12,516 Expert Mod 8TB
Not a problem, good luck.
Mar 12 '07 #32
Not a problem, good luck.
I was wondering if you could help me out again. I am basically trying to do the same thing again so I took the code you have written for me and adjusted it. I must not have done it correctly though because it keeps giving me an error. I couldn't find anything else like it on the sight so I thought Id ask.

Expand|Select|Wrap|Line Numbers
  1. Private Sub CUSIP_NUMBER_AfterUpdate()
  2.     Me.[ASSET/TR-ACCT_NUMBER] = DLookup("[ASSET]", "[ASSET/CUSIP]", "[CUSIP] = " & Me.[CUSIP_NUMBER])
  3. End Sub
This is how I changed the code. I have a table that has the Asset and Cusip on it with a name of asset/cusip and on the form I have Asset/Tr-Acct number and Cusip number. Unfortunatly I cannot change the labels on the form to make them less wordy due to certian security measures placed on the Table. If it matters not every asset on the table has a cusip.
Mar 13 '07 #33
Rabbit
12,516 Expert Mod 8TB
General syntax of DLookup is:
Expand|Select|Wrap|Line Numbers
  1. DLookup("[Field Name]", "[Table Name]", "Where Statement")
Is it returning anything at all? What is it doing wrong? Is CusID a number or text?
Mar 13 '07 #34
General syntax of DLookup is:
Expand|Select|Wrap|Line Numbers
  1. DLookup("[Field Name]", "[Table Name]", "Where Statement")
Is it returning anything at all? What is it doing wrong? Is CusID a number or text?
It returns;
data type mismatch in criteria expression.
Which means I have labeled something incorrectly right?
The CusID is text because there are sometimes letters in it so as far as I know I cannot change the field to a number.
Mar 13 '07 #35
Rabbit
12,516 Expert Mod 8TB
Expand|Select|Wrap|Line Numbers
  1. Private Sub CUSIP_NUMBER_AfterUpdate()
  2.     Me.[ASSET/TR-ACCT_NUMBER] = DLookup("[ASSET]", "[ASSET/CUSIP]", "[CUSIP] = '" & Me.[CUSIP_NUMBER] & "'")
  3. End Sub
You have to surround text with quotes. No quotes means it's a number or a field.
Mar 13 '07 #36
Expand|Select|Wrap|Line Numbers
  1. Private Sub CUSIP_NUMBER_AfterUpdate()
  2.     Me.[ASSET/TR-ACCT_NUMBER] = DLookup("[ASSET]", "[ASSET/CUSIP]", "[CUSIP] = '" & Me.[CUSIP_NUMBER] & "'")
  3. End Sub
You have to surround text with quotes. No quotes means it's a number or a field.
Ok I did that and now it gives me this error;
Microsoft Office Access can't find the field 'I' referred to in your expression.
Mar 13 '07 #37
Ok I did that and now it gives me this error;
Microsoft Office Access can't find the field 'I' referred to in your expression.
I'm sorry if I'm not doing something correctly. I would greatly appreciate at least some kind of response since it has been two days since my last post and I have not been able to figure it out on my own. Thank you.
Mar 15 '07 #38
Rabbit
12,516 Expert Mod 8TB
I have no idea why it's doing that. As long as you are referring to everything correctly, it should work.

Let's see if any of the experts can shed a light on this.
Mar 15 '07 #39
I have no idea why it's doing that. As long as you are referring to everything correctly, it should work.

Let's see if any of the experts can shed a light on this.
Ok, thank you for responding. I seem to be hitting a wall here. The zip code part works great so I would think this would work just as well.
Mar 15 '07 #40
Ok, thank you for responding. I seem to be hitting a wall here. The zip code part works great so I would think this would work just as well.
Does anyone know why this code is not working?

Private Sub CUSIP_NUMBER_AfterUpdate()
Me.[ASSET/TR-ACCT_NUMBER] = DLookup("[ASSET]", "[ASSET/CUSIP]", "[CUSIP] = '" & Me.[CUSIP_NUMBER] & "'")
End Sub
Mar 19 '07 #41
nico5038
3,080 Expert 2GB
Does anyone know why this code is not working?

Private Sub CUSIP_NUMBER_AfterUpdate()
Me.[ASSET/TR-ACCT_NUMBER] = DLookup("[ASSET]", "[ASSET/CUSIP]", "[CUSIP] = '" & Me.[CUSIP_NUMBER] & "'")
End Sub
1) Make sure CUSIP is a text field in the table, when it's a number use:
Me.[ASSET/TR-ACCT_NUMBER] = DLookup("[ASSET]", "[ASSET/CUSIP]", "[CUSIP] = " & Me.[CUSIP_NUMBER] )

2) Add:

Me.Refresh

Before the End sub to make sure the result will show.

Nic;o)
Mar 19 '07 #42
Rabbit
12,516 Expert Mod 8TB
1) Make sure CUSIP is a text field in the table, when it's a number use:
Me.[ASSET/TR-ACCT_NUMBER] = DLookup("[ASSET]", "[ASSET/CUSIP]", "[CUSIP] = " & Me.[CUSIP_NUMBER] )

2) Add:

Me.Refresh

Before the End sub to make sure the result will show.

Nic;o)
CUSIP is text.

Looking over the older posts brought something to my attention. In one post you said it was CUSID but it's CUSIP everywhere else, is everything spelled correctly?
Mar 19 '07 #43
CUSIP is text.

Looking over the older posts brought something to my attention. In one post you said it was CUSID but it's CUSIP everywhere else, is everything spelled correctly?
I tried the Me.Refresh and that does nothing. I also double checked the spelling on everything and made sure everything was set to text. It still comes up with the same error; Microsoft Office can't find the field "I" referred to in your expression. It must have something to do with the single quotes but I don't know what to do.
Mar 20 '07 #44
MMcCarthy
14,534 Expert Mod 8TB
Using the Me. Check that the spelling of the [ASSET/TR-ACCT_NUMBER] and [CUSIP_NUMBER] are correct by selecting them from the drop down list rather than typing them.

See if that resolves your problem. If not come back.
Mar 20 '07 #45
NeoPa
32,173 Expert Mod 16PB
I tried the Me.Refresh and that does nothing. I also double checked the spelling on everything and made sure everything was set to text. It still comes up with the same error; Microsoft Office can't find the field "I" referred to in your expression. It must have something to do with the single quotes but I don't know what to do.
Firstly, Rabbit.
Sorry to take so long to respond to this. I will get right on it now.

Crayola.
Could you post the exact SQL that you're currently using (that triggers this error). Any QueryDefs (Saved Queries) referred to should also have their SQL posted here. I'm sorry if you've passed this stage in the thread before but I need to focus on the SQL (Which is where your problem lies) and that's hard if you get overburdened with unconnected detail.

.ReQuery() / .Refresh()
A .Refresh() will only regrab info for those records it already knows to exist.
A .requery() will rerun the query again.
The latter will be required (in place of the former) if there are any records added or removed between separate runs.
Mar 20 '07 #46
Firstly, Rabbit.
Sorry to take so long to respond to this. I will get right on it now.

Crayola.
Could you post the exact SQL that you're currently using (that triggers this error). Any QueryDefs (Saved Queries) referred to should also have their SQL posted here. I'm sorry if you've passed this stage in the thread before but I need to focus on the SQL (Which is where your problem lies) and that's hard if you get overburdened with unconnected detail.

.ReQuery() / .Refresh()
A .Refresh() will only regrab info for those records it already knows to exist.
A .requery() will rerun the query again.
The latter will be required (in place of the former) if there are any records added or removed between separate runs.
I am new to this but I believe what you are looking for is this (if Im wrong please correct me);
Expand|Select|Wrap|Line Numbers
  1. SELECT distinct [ASSET/CUSIP].CUSIP
  2. FROM [ASSET/CUSIP]
  3. ORDER BY [ASSET/CUSIP].CUSIP;
That is what is in the Row source field for the Cusip Number.
Mar 20 '07 #47
NeoPa
32,173 Expert Mod 16PB
That would be fine if this control were where the error message were coming from.
I doubt it is, as this SQL looks fine.
Only you can determine what throws up the error but my guess would be the Record Source of the form itself.
Mar 20 '07 #48
That would be fine if this control were where the error message were coming from.
I doubt it is, as this SQL looks fine.
Only you can determine what throws up the error but my guess would be the Record Source of the form itself.
I'm not sure I understand. Could you explain this a little better? The error is saying it can't find "I" but I don't know what that is or how to correct the problem. It is in the code that Rabbit helped me with;
Expand|Select|Wrap|Line Numbers
  1. Private Sub CUSIP_NUMBER_AfterUpdate()
  2.     Me.[ASSET/TR-ACCT_NUMBER] = DLookup("[ASSET]", "[ASSET/CUSIP]", "[CUSIP] = '" & Me.[CUSIP_NUMBER] & "'")
  3. End Sub
and I believe it has something to do with the single quotation marks at the end. The Cusip Number is actually a text field because some have letters in them. Because of this, Rabbit said I needed the single quotes. Is there more to the single quotes or is it in the record source like you say?
Mar 20 '07 #49
nico5038
3,080 Expert 2GB
Hmm, your field naming is risky for A2002/3.
Did you try to use names without the special characters "/" and "-" ?

Nic;o)
Mar 20 '07 #50

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by B. G. Mahesh | last post: by
12 posts views Thread by Bob Bedford | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.