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

[NeoPa]

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.

[Crayola465]

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);

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

That is what is in the Row source field for the Cusip Number.

[NeoPa]

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.

[Crayola465]

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;

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?

[nico5038]

Hmm, your field naming is risky for A2002/3.
Did you try to use names without the special characters "/" and "-" ?

Nic;o)

[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]

Microsoft Office Access can't find the field 'I' referred to in your expression

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.