dLookup error

  • Thread starter Thread starter Ken Ivins
  • Start date Start date
K

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
 
strZCode = DLookup("[ctPostalCode]", "tblCityPostal", "[ctCity_Towns] ="
& Me!cmbCity) [...]
Run time error '2001'
You canceled the previous operation.
Click to expand...

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'.
Click to expand...

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,

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


Bruce M. Thompson said:
strZCode = DLookup("[ctPostalCode]", "tblCityPostal", "[ctCity_Towns] ="
& Me!cmbCity) [...]
Run time error '2001'
You canceled the previous operation.
Click to expand...

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'.
Click to expand...

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.
:-)
Click to expand...
 
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?






Bruce M. Thompson said:
strZCode = DLookup("[ctPostalCode]", "tblCityPostal", "[ctCity_Towns] ="
& Me!cmbCity) [...]
Run time error '2001'
You canceled the previous operation.
Click to expand...

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'.
Click to expand...

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.
:-)
Click to expand...
 
PS a single quote after the equal and in between the final quotes also
worked. Is there a difference?
Click to expand...

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"
 
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
 
If you use use double quotes it will fail if the cmbCity was 21" TV.
Hmmm... Rock and Hard Palce. So what to Do?
Click to expand...

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

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






Ken Ivins said:
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?






Bruce M. Thompson said:
strZCode = DLookup("[ctPostalCode]", "tblCityPostal",
Click to expand...
Click to expand...
"[ctCity_Towns]
="
& Me!cmbCity) [...]
Run time error '2001'
You canceled the previous operation.
Click to expand...

Not sure about the cause for this one, but the change below may
Click to expand...
eliminate
it.
Run time error '3075'
Syntax error (missing operator) in query expression '[ctCity_Town]=Clifton
Park'.
Click to expand...

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
Click to expand...
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
Click to expand...
within the newsgroups so that all might benefit.<<
Click to expand...
Click to expand...
 
Back
Top