A97 error #2109 ?

[MLH]

Anybody know why I might be getting error #2109 during the
procedure below? It does not occur on 2 desktops, but did
on a laptop. The error said:

"The following unexpected error occurred in Sub OwnerZip_AfterU pdate,
CBF on frmOwnerEntryFr m. 2109: "There is no field named 'OwnerCity' in
the current record."

Private Sub OwnerZip_AfterU pdate()
On Error GoTo OwnerZip_AfterU pdateErr
Dim ThisForm As String
ThisForm = Me.Name

If IsNull(DLookup( "[City]", "tblZipCode s",
"Zip=Forms!frmO wnerEntryFrm!Ow nerZip")) Then GoTo NotFound
Forms!frmOwnerE ntryFrm!OwnerCi ty = DLookup("City", "tblZipCode s",
"Zip=Forms!frmO wnerEntryFrm!Ow nerZip")
Forms!frmOwnerE ntryFrm!OwnerSt ate = DLookup("State" , "tblZipCode s",
"Zip=Forms!frmO wnerEntryFrm!Ow nerZip")

ExitOwnerZip_Af terUpdate:
Exit Sub

NotFound:
ZipNotFoundFlag = True ' (global to this form module) Zip
code entered was NOT found in the table
' You may wish to incorporate the AddZipBtn that you used on
cust-entry form in Credi-Clean
MsgBox "ZipCode not found - enter city and state manually.", 48,
"Not in zip list - " & MyApp$ & ", rev. " & MY_VERSION$
Dim MyControl As Control
Set MyControl = Forms!frmOwnerE ntryFrm!OwnerCi ty
DoCmd.GoToContr ol MyControl.Name
Exit Sub

OwnerZip_AfterU pdateErr:
If Err = 2448 Then GoTo NotFound '2448 = Can't Set Value error
Dim r As String, k As String, Message3 As String
r = "The following unexpected error occurred in Sub
OwnerZip_AfterU pdate, CBF on " & ThisForm & "."
k = CRLF & CRLF & str$(Err) & ": " & Quote & Error$ & Quote
Message3 = r & k
MsgBox Message3, 48, "Unexpected Error - " & MyApp$ & ", rev. " &
MY_VERSION$
Resume ExitOwnerZip_Af terUpdate

End Sub

I don't think I refer to any table fields in the procedure as
'OwnerCity'

[Salad]

MLH wrote:

Anybody know why I might be getting error #2109 during the
procedure below? It does not occur on 2 desktops, but did
on a laptop. The error said:

"The following unexpected error occurred in Sub OwnerZip_AfterU pdate,
CBF on frmOwnerEntryFr m. 2109: "There is no field named 'OwnerCity' in
the current record."

Private Sub OwnerZip_AfterU pdate()
On Error GoTo OwnerZip_AfterU pdateErr
Dim ThisForm As String
ThisForm = Me.Name

If IsNull(DLookup( "[City]", "tblZipCode s",
"Zip=Forms!frmO wnerEntryFrm!Ow nerZip")) Then GoTo NotFound
Forms!frmOwnerE ntryFrm!OwnerCi ty = DLookup("City", "tblZipCode s",
"Zip=Forms!frmO wnerEntryFrm!Ow nerZip")
Forms!frmOwnerE ntryFrm!OwnerSt ate = DLookup("State" , "tblZipCode s",
"Zip=Forms!frmO wnerEntryFrm!Ow nerZip")

ExitOwnerZip_Af terUpdate:
Exit Sub

NotFound:
ZipNotFoundFlag = True ' (global to this form module) Zip
code entered was NOT found in the table
' You may wish to incorporate the AddZipBtn that you used on
cust-entry form in Credi-Clean
MsgBox "ZipCode not found - enter city and state manually.", 48,
"Not in zip list - " & MyApp$ & ", rev. " & MY_VERSION$
Dim MyControl As Control
Set MyControl = Forms!frmOwnerE ntryFrm!OwnerCi ty
DoCmd.GoToContr ol MyControl.Name
Exit Sub

OwnerZip_AfterU pdateErr:
If Err = 2448 Then GoTo NotFound '2448 = Can't Set Value error
Dim r As String, k As String, Message3 As String
r = "The following unexpected error occurred in Sub
OwnerZip_AfterU pdate, CBF on " & ThisForm & "."
k = CRLF & CRLF & str$(Err) & ": " & Quote & Error$ & Quote
Message3 = r & k
MsgBox Message3, 48, "Unexpected Error - " & MyApp$ & ", rev. " &
MY_VERSION$
Resume ExitOwnerZip_Af terUpdate

End Sub

I don't think I refer to any table fields in the procedure as
'OwnerCity'

First, check the ControlSource and Name fields in the property sheet for
the City field.

Next, break the zip from the forms zip and put a single quote around the
zip field. Don't use a quote if zip in the table is numerica which
would be odd. Ex:

'alpha
Forms!frmOwnerE ntryFrm!OwnerCi ty = DLookup("City", "tblZipCode s",
"Zip = '" & Forms!frmOwnerE ntryFrm!OwnerZi p & "'")

'numeric
Forms!frmOwnerE ntryFrm!OwnerCi ty = DLookup("City", "tblZipCode s",
"Zip = " & Forms!frmOwnerE ntryFrm!OwnerZi p)

[MLH]

>

First, check the ControlSource and Name fields in the property sheet for
the City field.
The textbox control on frmOwnerEntryFo rm that I use to handle the
cityname data is named OwnerCity #AND# the relevant table name
is also OwnerCity, this is what is shown in the ControlSource property
for that textbox control. The related fieldname in tblZipCodes is
named CITY.

Next, break the zip from the forms zip and put a single quote around the
zip field. Don't use a quote if zip in the table is numerica which
would be odd. Ex:

'alpha
Forms!frmOwner EntryFrm!OwnerC ity = DLookup("City", "tblZipCode s",
"Zip = '" & Forms!frmOwnerE ntryFrm!OwnerZi p & "'")

I'll try that tomorrow. The laptop is 225 miles from here and the
business owner will be in tomorrow. Meanwhile, I've tested your
syntax in the debug window. I see that it works. But I don't see
what it is that the different syntax was intended to address. Looks
interesting and I'm curious.

[Salad]

MLH wrote:

First, check the ControlSource and Name fields in the property sheet for
the City field.

The textbox control on frmOwnerEntryFo rm that I use to handle the
cityname data is named OwnerCity #AND# the relevant table name
is also OwnerCity, this is what is shown in the ControlSource property
for that textbox control. The related fieldname in tblZipCodes is
named CITY.

Next, break the zip from the forms zip and put a single quote around the
zip field. Don't use a quote if zip in the table is numerica which
would be odd. Ex:

'alpha
Forms!frmOwne rEntryFrm!Owner City = DLookup("City", "tblZipCode s",
"Zip = '" & Forms!frmOwnerE ntryFrm!OwnerZi p & "'")

I'll try that tomorrow. The laptop is 225 miles from here and the
business owner will be in tomorrow. Meanwhile, I've tested your
syntax in the debug window. I see that it works. But I don't see
what it is that the different syntax was intended to address. Looks
interesting and I'm curious.

I know there are some rules about this type of stuff...tho I've never
read them, just know they exist. So instead of wondering if it will
work or not work, I explicitly state the value...by breaking it up so
that I KNOW that the where clause is getting a value. By doing "Zip =
'" & Forms!frmOwnerE ntryFrm!OwnerZi p & "'") I know that the value
OwnerZip is being passed. Not breaking it out in a query is fine but I
break it out when not in a query.

[David W. Fenton]

MLH <CR**@NorthStat e.net> wrote in
news:gh******** *************** *********@4ax.c om:

If IsNull(DLookup( "[City]", "tblZipCode s",
"Zip=Forms!frmO wnerEntryFrm!Ow nerZip")) Then GoTo NotFound
Forms!frmOwnerE ntryFrm!OwnerCi ty = DLookup("City", "tblZipCode s",
"Zip=Forms!frmO wnerEntryFrm!Ow nerZip")
Forms!frmOwnerE ntryFrm!OwnerSt ate = DLookup("State" ,
"tblZipCode s", "Zip=Forms!frmO wnerEntryFrm!Ow nerZip")

This is simply really dreadful code.

GoTo aside, you should *never* be passing the control to the
DLookup. This:

DLookup("State" , "tblZipCode s",
"Zip=Forms!frmO wnerEntryFrm!Ow nerZip")

should always be this:

DLookup("State" , "tblZipCode s", "[Zip]=" & _
Forms!frmOwnerE ntryFrm!OwnerZi p)

if OwnerZip is numeric and this if it's text:

DLookup("State" , "tblZipCode s", "[Zip]='" & _
Forms!frmOwnerE ntryFrm!OwnerZi p & "'")

Now, you also are have one more DLookup in your code than you need.

Dim varCity As Variant

varCity = DLookup("[City]", "tblZipCode s", "[Zip]='" & _
Forms!frmOwnerE ntryFrm!OwnerZi p & "'")
If Not IsNull(varCity) Then
Forms!frmOwnerE ntryFrm!OwnerCi ty = varCity
Forms!frmOwnerE ntryFrm!OwnerSt ate = _
DLookup("State" , "tblZipCode s", "[Zip]='" & _
Forms!frmOwnerE ntryFrm!OwnerZi p & "'")
End If

Now, isn't that much simpler?

But my preferred method for this is to have a subroutine that you
pass the two controls, and that function uses DAO to open a
recordset and look up the values and put them in the passed
controls. Such a subroutine would look like this:

Public Sub LookupCityState FromZip(ByRef ctlCity As Control, _
ctlState As Control, ByRef strZip As String)
Dim db As DAO.Database
Dim strSQL As String
Dim rs As DAO.Recordset

strSQL = "SELECT tblZipCodes.Cit y, tblZipCodes.Sta te _
FROM tblZipCodes WHERE tblZipCodes.Zip ='" & strZip & "';"

Set db = CurrentDB()
Set rs = db.OpenRecordse t(strSQL)
If rs.RecordCount > 0 Then
rs.MoveFirst
ctlCity = rs!City
ctlState = rs!State
End If
rs.Close
Set rs = Nothing
set db = Nothing
End Sub

Now, to make that more efficient, you should reallly use some form
of caching the database reference, either using something like my
dbLocal() function (you should easily be able to to find the code
by
searching the Google Groups archive for CDMA), or by cahing your
own
db reference and passing it in as an argument to this subroutine.
Obviously, if you're using a cahced database reference, you'd
remove
all the database-related declaration and cleanup in the subroutine.

The key advantage here is that it replaces two DLookups with one
call to a subroutine. Also, if you're dealing with the version of
Access97 in which DLookups on linked tables were very slow, this
will be faster than the DLookups.

Basically, any time you're looking up more than one field from a
single table, DLookup is going to be terribly inefficient.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[davidgatheral]

Good work Mr Fenton.

DG.

[MLH]

On Mon, 07 Nov 2005 20:23:41 -0600, "David W. Fenton"
<dX********@bwa y.net.invalid> wrote:

DLookup("State ", "tblZipCode s",
"Zip=Forms!frm OwnerEntryFrm!O wnerZip")

should always be this:

DLookup("State ", "tblZipCode s", "[Zip]=" & _
Forms!frmOwnerE ntryFrm!OwnerZi p)

I appreciate the comments, Mr Fenton. There's a lot
there and I'm digesting it as time permits. For now, in
regard to the above recommendation, I'm trying to
understand what modifying the where clause from

This
"Zip=Forms!frmO wnerEntryFrm!Ow nerZip"

To This
[Zip]=" & Forms!frmOwnerE ntryFrm!OwnerZi p

is mean to accomplish. I see they are different. And
it appears both are meant to imply (in plain english)
"where the table's [Zip] field matches the value in
the OwnerZip control on frmOwnerEntryFr m"

Doubtlessly, there's a good reason. But the difference
is just subtle enough that I'm not seeing it. I want to
understand this so I can write better DLookUp stmts.

[MLH]

Thanks, David. Making the mods to the where clause in the DLookup
statement you suggested worked for me. At least, it got rid of error
2109. Line #40 was the culprit. You can see I've modified lines 50 and
60 similarly. But now I get an error when line #40 runs. Its reported
like this: The following enexpected error occurred in Sub
OwnerZip_AfterU pdate, line #40, CBF on frmEditOwners. 2186: "This
property isn't available in Design View. Switch to Form view to access
this property, or remove the reference to the property."

That one's got me stumped. I'm able to run the code over and over,
testing different methods and procedures. The 2186 error keeps
repeating.

Private Sub OwnerZip_AfterU pdate()
10 On Error GoTo OwnerZip_AfterU pdateErr
20 Dim ThisForm As String
30 ThisForm = Me.Name
40 If IsNull(DLookup( "[City]", "tblZipCode s", "[Zip]=" &
Forms!frmOwnerE ntryFrm!OwnerZi p)) Then GoTo NotFound
50 Forms!frmOwnerE ntryFrm!OwnerCi ty = DLookup("City", "tblZipCode s",
"[Zip]=" & Forms!frmOwnerE ntryFrm!OwnerZi p)
60 Forms!frmOwnerE ntryFrm!OwnerSt ate = DLookup("State" , "tblZipCode s",
"[Zip]=" & Forms!frmOwnerE ntryFrm!OwnerZi p)

100 If Not IsNull(Me!Owner City) And Me!OwnerCity <> "" Then
110 MyString = Me!OwnerCity
120 MyVariant = DLookup("[CityID]", "tblNCcitie s",
"[tblNCcities].[CityName]=GetMyString()" )
130 If Not IsNull(MyVarian t) Then
140 MyOtherVariant = DLookup("[County]",
"qryNCCitiesCou nties",
"[qryNCCitiesCoun ties].[CityName]=GetMyString()" )
150 If Not IsNull(MyOtherV ariant) Then
160 'There's at least 1 county matched up with the named
city, so set the county-chooser combox rowsource
170 MySQL = "SELECT tblNCcounties.C ounty,
tblNCcities.Cit yName FROM (tblNCcities LEFT JOIN
tblNCcityCounty JunctionTable "
180 MySQL = MySQL & "ON tblNCcities.Cit yID =
tblNCcityCounty JunctionTable.C ityID) LEFT JOIN tblNCcounties ON "
190 MySQL = MySQL & "tblNCcityCount yJunctionTable. CountyID
= tblNCcounties.C ountyID WHERE (((tblNCcities. CityName) "
200 MySQL = MySQL & "= GetMyString())) ORDER BY
tblNCcities.Cit yName;"
210 Me!CountyChoose rBox.RowSource = MySQL
220 Me!CountyChoose rBox.Requery
230 Else
240 Me!CountyChoose rBox.RowSource = "SELECT DISTINCTROW
tblNCcounties.C ounty FROM tblNCcounties;"
250 Me!CountyChoose rBox.Requery
260 End If
270 Else
280 Me!CountyChoose rBox.RowSource = "SELECT DISTINCTROW
tblNCcounties.C ounty FROM tblNCcounties;"
290 Me!CountyChoose rBox.Requery
300 End If
310 End If

ExitOwnerZip_Af terUpdate:
Exit Sub

NotFound:
320 ZipNotFoundFlag = True ' (global to this form module) Zip
code entered was NOT found in the table
330 MsgBox "ZipCode not found - enter city and state manually.", 48,
"Not in zip list - " & MyApp$ & ", rev. " & MY_VERSION$
340 Dim MyControl As Control
350 Set MyControl = Forms!frmOwnerE ntryFrm!OwnerCi ty
360 DoCmd.GoToContr ol MyControl.Name
370 Exit Sub

OwnerZip_AfterU pdateErr:
If Err = 2448 Then GoTo NotFound '#2448 is Can't Set Value
error. That occurs if the DLookUp returns Null.
Dim r As String, k As String, Message3 As String
r = "The following unexpected error occurred in Sub
OwnerZip_AfterU pdate, line #" & Trim$(CStr(Erl) ) & ", CBF on " &
ThisForm & "."
k = CRLF & CRLF & str$(Err) & ": " & Quote & Error$ & Quote
Message3 = r & k
MsgBox Message3, 48, "Unexpected Error - " & MyApp$ & ", rev. " &
MY_VERSION$
Resume ExitOwnerZip_Af terUpdate

End Sub

[MLH]

Big Time Oops - of course. I had cut 'n pasted the code in here from
another form. Forgot to change the form name and the other form still
open in design view. (error #9999: "Access for Alzheimers patients")

(My apologies to those seriously trying to lend me a hand here)

[MLH]

Yep. You and David have both given me textbook examples of exactly
how its supposed to be done. Here's one straight from HELP that looks
identical in its recommended syntax...

DLookup("[ProductName]", "Products", "[ProductID] =" & Forms![Order
Details]!ProductID)

I've been writing these Where criteria clauses in DLookup statements
wrong for so long it almost seems right. My apps have been curiously
forgiving, it seems. I don't understand how it popped up, really. On 2
systems - the syntax I was using never exhibited the symptoms we've
been discussing in this thread. But sure as heck, as soon as I put it
on a laptop 225 miles away - up it popped!

I know there's a difference. Your suggestions fixed my problem. Of
that, there's no doubt. But the difference is so subtle that I'm just
missing the ALL TOO IMPORTANT point. I just don't get it.

I can see that
"Zip = '" & Forms!frmOwnerE ntryFrm!OwnerZi p & "'"

resolves to
Zip = '12345' (if 12345 is what's in the form's textbox)

and that I was hoping
"Zip=Forms!frmO wnerEntryFrm!Ow nerZip"

would resolve to about the same thing. I guess it didn't. But I am
stumped that we have somehow had no problem with the syntax
for more than 5 months on 2 other machines.

I know there are some rules about this type of stuff...tho I've never
read them, just know they exist. So instead of wondering if it will
work or not work, I explicitly state the value...by breaking it up so
that I KNOW that the where clause is getting a value. By doing "Zip =
'" & Forms!frmOwnerE ntryFrm!OwnerZi p & "'") I know that the value
OwnerZip is being passed. Not breaking it out in a query is fine but I
break it out when not in a query.