469,306 Members | 2,281 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,306 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
65 3873
NeoPa
32,173 Expert Mod 16PB
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?
The "I" error message indicates to me that you have some SQL running somewhere which refers (incorrectly) to "I". This is clearly not the case with the SQL you posted so there must be something somewhere else. You need to find out what is running when this error message occurs. That is the answer to the question I'm asking of you. That is the SQL we need to see. Give me the SQL & I'm confident I can explain what's wrong.
Rabbit is absolutely right about the single-quotes at the end. The explanation he gave is also correct. If you have a string (text) field then he has referenced it perfectly.
Mar 21 '07 #51
The "I" error message indicates to me that you have some SQL running somewhere which refers (incorrectly) to "I". This is clearly not the case with the SQL you posted so there must be something somewhere else. You need to find out what is running when this error message occurs. That is the answer to the question I'm asking of you. That is the SQL we need to see. Give me the SQL & I'm confident I can explain what's wrong.
Rabbit is absolutely right about the single-quotes at the end. The explanation he gave is also correct. If you have a string (text) field then he has referenced it perfectly.
Ok, so if it is somewhere in one of my SQLs, is there a way so I can see all that I have? I looked at all that I know I have and I could not find "I" referenced in anything. I didn't know if maybe I did something with out realizing it though. Im sorry if I seem difficult, Im just getting quite frustrated with this.
Mar 21 '07 #52
NeoPa
32,173 Expert Mod 16PB
I can only guess, but try the Record source of the form itself.
Go back and reread post #46 and make sure you don't waste too much time by missing out any of the SQL that's needed.
Mar 21 '07 #53
I have been trying to figure this out for a few weeks now and I can't seem to get it to work. I am trying to get the asset field to auto fill when I enter the cusip number. My VB code is;
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 the SQLs for the two things I am looking up are;
Expand|Select|Wrap|Line Numbers
  1.  SELECT distinct [ASSET/CUSIP].CUSIP FROM [ASSET/CUSIP] ORDER BY [ASSET/CUSIP].CUSIP;
and
Expand|Select|Wrap|Line Numbers
  1.  SELECT distinct [ASSET/CUSIP].ASSET FROM [ASSET/CUSIP] ORDER BY [ASSET/CUSIP].ASSET;
Both fields are text fields and when I looked on other threads their problems seemed to stem from number fields. Please help me...Im starting to go bonkers over this...
Mar 27 '07 #54
I posted a new thread because I hadn't noticed you had responded last week. I named it "Microsoft Office Access can't find the field 'I' referred to in your expression." I listed the two SQLs I had and after I realized you had responded I went and looked at your suggestion but I was still unable to find the problem. I know you are not supposed to double post though so I do apologize for that and if there is a way for me to delete it I will.
Mar 27 '07 #55
NeoPa
32,173 Expert Mod 16PB
In that case I'll merge the threads into one.
This has happened now and the order the posts appear in here is the oder in which each post was added.
Mar 27 '07 #56
NeoPa
32,173 Expert Mod 16PB
What type of object is [ASSET/CUSIP]?
A Table or a QueryDef?
Mar 27 '07 #57
What type of object is [ASSET/CUSIP]?
A Table or a QueryDef?
It is a table.
Mar 29 '07 #58
NeoPa
32,173 Expert Mod 16PB
I have been trying to figure this out for a few weeks now and I can't seem to get it to work. I am trying to get the asset field to auto fill when I enter the cusip number. My VB code is;
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 the SQLs for the two things I am looking up are;
Expand|Select|Wrap|Line Numbers
  1.  SELECT distinct [ASSET/CUSIP].CUSIP FROM [ASSET/CUSIP] ORDER BY [ASSET/CUSIP].CUSIP;
and
Expand|Select|Wrap|Line Numbers
  1.  SELECT distinct [ASSET/CUSIP].ASSET FROM [ASSET/CUSIP] ORDER BY [ASSET/CUSIP].ASSET;
Both fields are text fields and when I looked on other threads their problems seemed to stem from number fields. Please help me...Im starting to go bonkers over this...
Can you tell us :
  1. Where these two SQL strings are to be found?
  2. Which objects are they bound to?
  3. At what stage of using your form does this error message appear?

I doubt it is coming from these SQL strings so I suspect there is something else you haven't told us about.
Mar 29 '07 #59
Can you tell us :
  1. Where these two SQL strings are to be found?
  2. Which objects are they bound to?
  3. At what stage of using your form does this error message appear?

I doubt it is coming from these SQL strings so I suspect there is something else you haven't told us about.
The first SQL is linked to the bound field Cusip Number.
The second is linked to the bound control asset/tr acct name.
The error appears when I tab out of the Cusip number field.
I hope that is what you were looking for.
Its entirely possible I haven't told you something, but only because I don't know what to tell.
Mar 29 '07 #60
NeoPa
32,173 Expert Mod 16PB
OK.
If I seem to be asking lots of questions without providing the answer, it's only because I don't know it yet.
When you refer to the "bound field Cusip Number", is this a TextBox control, on the form, bound to a field in your recordsource, called [Cusip Number]?
If so, what is the Control's name?
The main missing ingredient that I was asking for (on which pretty well everything else is built) is the RecordSource of the form itself. Can you post that for me please?
Please remember that it is critical for you to Copy/Paste the stuff in here. Retyping what you see is liable to suffer from the "Hands up all those who are absent" syndrome. Always Copy/Paste for something like this.
Mar 29 '07 #61
OK.
If I seem to be asking lots of questions without providing the answer, it's only because I don't know it yet.
When you refer to the "bound field Cusip Number", is this a TextBox control, on the form, bound to a field in your recordsource, called [Cusip Number]?
If so, what is the Control's name?
The main missing ingredient that I was asking for (on which pretty well everything else is built) is the RecordSource of the form itself. Can you post that for me please?
Please remember that it is critical for you to Copy/Paste the stuff in here. Retyping what you see is liable to suffer from the "Hands up all those who are absent" syndrome. Always Copy/Paste for something like this.
It is a combobox control that is bound to the ESCHEAT table and the Control's name is CUSIP NUMBER.
The record source for the form is ESCHEAT
Escheat is the table which all the information that is on my form goes.
I tried not to get too fancy with names and I didn't name many of the fields becasue I am working with a database that was created before I started working here. Im just trying to make it a little more error-proof.
Mar 30 '07 #62
NeoPa
32,173 Expert Mod 16PB
This normally refers to a query (or some SQL code) which is executed but something in the SQL has a value (in this case 'I') which it cannot recognise.
I just had a thought :
Check out all your controls and see if any one of them is attempting to bind to a field 'I'. It's a long shot, but all I can come up with I'm afraid.
Mar 30 '07 #63
This normally refers to a query (or some SQL code) which is executed but something in the SQL has a value (in this case 'I') which it cannot recognise.
I just had a thought :
Check out all your controls and see if any one of them is attempting to bind to a field 'I'. It's a long shot, but all I can come up with I'm afraid.
I thought about that before and I checked it again but I have found nothing. I took the code out and ran through the form and had no error. It has to be somewhere in the code. Can you please look at it one more time? If you dont find anything, I guess I'll have to just give up.
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
Apr 2 '07 #64
I thought about that before and I checked it again but I have found nothing. I took the code out and ran through the form and had no error. It has to be somewhere in the code. Can you please look at it one more time? If you dont find anything, I guess I'll have to just give up.
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
Nevermind...I guess I am just a space cadet. The problem was with the Asset/TR-Acct Number. It was supposed to be name not number. You have no idea how much of an idiot I feel like right now after all of this. Sorry for the confusion. It works just fine now. Thank you for all of you help everyone.
Apr 2 '07 #65
NeoPa
32,173 Expert Mod 16PB
No problem Crayola.
I'm glad you sorted your problem out mind you, as I looked again and still couldn't see anything :D
Apr 3 '07 #66

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
reply views Thread by zhoujie | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.