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

[Crayola465]

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.

[NeoPa]

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;

Private Sub CUSIP_NUMBER_AfterUpdate()
    Me.[ASSET/TR-ACCT_NUMBER] = DLookup("[ASSET]", "[ASSET/CUSIP]", "[CUSIP] = '" & Me.[CUSIP_NUMBER] & "'")
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.

[Crayola465]

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.

[NeoPa]

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.

[Crayola465]

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;

 Private Sub CUSIP_NUMBER_AfterUpdate()
    Me.[ASSET/TR-ACCT_NUMBER] = DLookup("[ASSET]", "[ASSET/CUSIP]", "[CUSIP] = '" & Me.[CUSIP_NUMBER] & "'")
End Sub

and the SQLs for the two things I am looking up are;

 SELECT distinct [ASSET/CUSIP].CUSIP FROM [ASSET/CUSIP] ORDER BY [ASSET/CUSIP].CUSIP;

and

 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...

[Crayola465]

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.

[NeoPa]

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.

[NeoPa]

What type of object is [ASSET/CUSIP]?
A Table or a QueryDef?

[Crayola465]

What type of object is [ASSET/CUSIP]?
A Table or a QueryDef?

It is a table.

[NeoPa]

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;

 Private Sub CUSIP_NUMBER_AfterUpdate()
    Me.[ASSET/TR-ACCT_NUMBER] = DLookup("[ASSET]", "[ASSET/CUSIP]", "[CUSIP] = '" & Me.[CUSIP_NUMBER] & "'")
End Sub

and the SQLs for the two things I am looking up are;

 SELECT distinct [ASSET/CUSIP].CUSIP FROM [ASSET/CUSIP] ORDER BY [ASSET/CUSIP].CUSIP;

and

 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.

[Crayola465]

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.

[NeoPa]

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.

[Crayola465]

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.

[NeoPa]

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.

[Crayola465]

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.

Private Sub CUSIP_NUMBER_AfterUpdate()
    Me.[ASSET/TR-ACCT_NUMBER] = DLookup("[ASSET]", "[ASSET/CUSIP]", "[CUSIP] = '" & Me.[CUSIP_NUMBER] & "'")
End Sub

[Crayola465]

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.

Private Sub CUSIP_NUMBER_AfterUpdate()
    Me.[ASSET/TR-ACCT_NUMBER] = DLookup("[ASSET]", "[ASSET/CUSIP]", "[CUSIP] = '" & Me.[CUSIP_NUMBER] & "'")
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.

[NeoPa]

No problem Crayola.
I'm glad you sorted your problem out mind you, as I looked again and still couldn't see anything :D