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

[Crayola465]

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!

[Rabbit]

Not a problem, good luck.

[Crayola465]

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.

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

[Rabbit]

General syntax of DLookup is:

DLookup("[Field Name]", "[Table Name]", "Where Statement")

Is it returning anything at all? What is it doing wrong? Is CusID a number or text?

[Crayola465]

General syntax of DLookup is:

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.

[Rabbit]

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

You have to surround text with quotes. No quotes means it's a number or a field.

[Crayola465]

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

[Crayola465]

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.

[Rabbit]

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.

[Crayola465]

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.

[Crayola465]

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

[nico5038]

Does anyone know why this code is not working?

Private Sub CUSIP_NUMBER_Af terUpdate()
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)

[Rabbit]

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?

[Crayola465]

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.

[MMcCarthy]

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.