dLookup error

[Ken Ivins]

I have a form in which I want to automatically fill in the zip code when the
town is selected from the combo box. I also need it flexible so towns and
zips can be added and modified (I can handle that part, I believe). Also
some cities have multiple zips and sometimes multiple towns share the same
zip.

So I created a table for this form called tblSubscriber and in a query
linked it with a table of tblPlans for this form. In addition I created a
table called tblCityPostal with the town names and their corresponding zip
codes.

On my form (frmSubscriber) I have a combo box for the cities that look to
tblCityPostal. The I created an After Update event to look to the table
tblCityPostal to paste the appropriate zip code to the zip code field (see
below).

Dim strZCode As String
strZCode = DLookup("[ctPostalCode]", "tblCityPostal", "[ctCity_Towns] ="
& Me!cmbCity)
Me!txbZip = strZCode

When I do this I get one of two errors (see below). I have tried many
combinations from saving the record, to moving the " and taking out the &,
to adding Forms!form name, to creating a string for the City name, all with
the same results. Any ideas what is causing this?


Run time error '2001'
You canceled the previous operation.


Run time error '3075'
Syntax error (missing operator) in query expression '[ctCity_Town]=Clifton
Park'.


Thanks,
Ken Ivins

 

[Bruce M. Thompson]

strZCode = DLookup("[ctPostalCode]", "tblCityPostal", "[ctCity_Towns] ="

& Me!cmbCity) [...]
Run time error '2001'
You canceled the previous operation.

Not sure about the cause for this one, but the change below may eliminate it.

Run time error '3075'
Syntax error (missing operator) in query expression '[ctCity_Town]=Clifton
Park'.

You need to embed quotes around the value retrieved from the combo box. Try
changing your code to this one line (note the embedded quotes - and watch line
wrap):

Me!txbZip = DLookup("[ctPostalCode]", "tblCityPostal", _
"[ctCity_Towns] =""" & Me!cmbCity & """")

Let me know how this works out. If this doesn't eliminate the first error
message, you may need to provide more information.

 

[Ken Ivins]

Bruce,

I tried that possible solution and got a VBA error when I left the line:

Compile error:
Expected: list separator or)

Maybe I have another setting wrong? I'm Not sure.

Ken

strZCode = DLookup("[ctPostalCode]", "tblCityPostal", "[ctCity_Towns] ="
& Me!cmbCity) [...]
Run time error '2001'
You canceled the previous operation.

Not sure about the cause for this one, but the change below may eliminate it.

Run time error '3075'
Syntax error (missing operator) in query expression '[ctCity_Town]=Clifton
Park'.

You need to embed quotes around the value retrieved from the combo box. Try
changing your code to this one line (note the embedded quotes - and watch line
wrap):

Me!txbZip = DLookup("[ctPostalCode]", "tblCityPostal", _
"[ctCity_Towns] =""" & Me!cmbCity & """")

Let me know how this works out. If this doesn't eliminate the first error
message, you may need to provide more information.

 

[Ken Ivins]

Bruce,

You were right. Please ignore my other string. My error was that I put 2
quote marks at the end instead of three. Three was the trick. Thanks for
your help.

Ken

PS a single quote after the equal and in between the final quotes also
worked. Is there a difference?

strZCode = DLookup("[ctPostalCode]", "tblCityPostal", "[ctCity_Towns] ="
& Me!cmbCity) [...]
Run time error '2001'
You canceled the previous operation.

Not sure about the cause for this one, but the change below may eliminate it.

Run time error '3075'
Syntax error (missing operator) in query expression '[ctCity_Town]=Clifton
Park'.

You need to embed quotes around the value retrieved from the combo box. Try
changing your code to this one line (note the embedded quotes - and watch line
wrap):

Me!txbZip = DLookup("[ctPostalCode]", "tblCityPostal", _
"[ctCity_Towns] =""" & Me!cmbCity & """")

Let me know how this works out. If this doesn't eliminate the first error
message, you may need to provide more information.

 

[Bruce M. Thompson]

PS a single quote after the equal and in between the final quotes also

worked. Is there a difference?

Yes. If the "cmbCity" control's value contains a single quote, the code will
fail:

"'" & "O'Neil" & "'" will result in 'O'Neil'

.... whereas ...

"""" & "O'Neil" & """" will result in "O'Neil"

 

[Ron Weiner]

As for me I have always used the Single Quote to wrap up strings in
expressions like this.

If you use use double quotes it will fail if the cmbCity was 21" TV.
Hmmm... Rock and Hard Palce. So what to Do?

What I do is to pass the string through a function that doubles up any
single quotes in the string, which makes it all OK. Here is the Function

Public Function FixQuotes(strQuoted As String) As String
' Purpose Double up single quotes for use in a Sql Update query
Dim I As Integer, strOut As String
strOut = ""
For I = 1 To Len(strQuoted)
If Mid(strQuoted, I, 1) = Chr(39) Then
strOut = strOut & Chr(39)
End If
strOut = strOut & Mid(strQuoted, I, 1)
Next
FixQuotes = strOut
End Function

"'" & FixQuotes("O'Neil") & "'" will result in 'O''Neil' which Access will
be happy to treat as O'Neil when it goes and does its thing in a Dlookup, or
an Insert or Update.

lngPeopleID = dlookup("PeopleID","tblPeople","LastName='" &
FixQuotes(cmbCity.value) & "'") Will work just just fine even if
cmbCity.value is O'Neil.

Using this method I dont worry about embedded single or double quotes.
Stuff just seems to Work OK.

Ron W

 

[Bruce M. Thompson]

If you use use double quotes it will fail if the cmbCity was 21" TV.

Hmmm... Rock and Hard Palce. So what to Do?

Don't allow the double quote to be entered so they must use "in" or "inch".

<g>

 

[Ken Ivins]

Ron, Bruce and John,

Thanks for all your help. All of this is really helpful. It also gives me a
lot to think about for future databases. I never thought a simple question
would generate such a response. Hopefully this helps others as well. Keep up
the good work.

Ken

Bruce,

You were right. Please ignore my other string. My error was that I put 2
quote marks at the end instead of three. Three was the trick. Thanks for
your help.

Ken

PS a single quote after the equal and in between the final quotes also
worked. Is there a difference?

strZCode = DLookup("[ctPostalCode]", "tblCityPostal",

"[ctCity_Towns]

="

& Me!cmbCity) [...]
Run time error '2001'
You canceled the previous operation.

Not sure about the cause for this one, but the change below may

eliminate

it.

Run time error '3075'
Syntax error (missing operator) in query expression '[ctCity_Town]=Clifton
Park'.

You need to embed quotes around the value retrieved from the combo box. Try
changing your code to this one line (note the embedded quotes - and

watch

line
wrap):

Me!txbZip = DLookup("[ctPostalCode]", "tblCityPostal", _
"[ctCity_Towns] =""" & Me!cmbCity & """")

Let me know how this works out. If this doesn't eliminate the first error
message, you may need to provide more information.

--
Bruce M. Thompson, Microsoft Access MVP
(e-mail address removed) (See the Access FAQ at http://www.mvps.org/access)

NO Email Please. Keep all communications

within the newsgroups so that all might benefit.<<