By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,489 Members | 1,777 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,489 IT Pros & Developers. It's quick & easy.

A97 error #2109 ?

P: n/a
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_AfterUpdate,
CBF on frmOwnerEntryFrm. 2109: "There is no field named 'OwnerCity' in
the current record."

Private Sub OwnerZip_AfterUpdate()
On Error GoTo OwnerZip_AfterUpdateErr
Dim ThisForm As String
ThisForm = Me.Name

If IsNull(DLookup("[City]", "tblZipCodes",
"Zip=Forms!frmOwnerEntryFrm!OwnerZip")) Then GoTo NotFound
Forms!frmOwnerEntryFrm!OwnerCity = DLookup("City", "tblZipCodes",
"Zip=Forms!frmOwnerEntryFrm!OwnerZip")
Forms!frmOwnerEntryFrm!OwnerState = DLookup("State", "tblZipCodes",
"Zip=Forms!frmOwnerEntryFrm!OwnerZip")

ExitOwnerZip_AfterUpdate:
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!frmOwnerEntryFrm!OwnerCity
DoCmd.GoToControl MyControl.Name
Exit Sub

OwnerZip_AfterUpdateErr:
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_AfterUpdate, 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_AfterUpdate

End Sub

I don't think I refer to any table fields in the procedure as
'OwnerCity'
Nov 13 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a
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_AfterUpdate,
CBF on frmOwnerEntryFrm. 2109: "There is no field named 'OwnerCity' in
the current record."

Private Sub OwnerZip_AfterUpdate()
On Error GoTo OwnerZip_AfterUpdateErr
Dim ThisForm As String
ThisForm = Me.Name

If IsNull(DLookup("[City]", "tblZipCodes",
"Zip=Forms!frmOwnerEntryFrm!OwnerZip")) Then GoTo NotFound
Forms!frmOwnerEntryFrm!OwnerCity = DLookup("City", "tblZipCodes",
"Zip=Forms!frmOwnerEntryFrm!OwnerZip")
Forms!frmOwnerEntryFrm!OwnerState = DLookup("State", "tblZipCodes",
"Zip=Forms!frmOwnerEntryFrm!OwnerZip")

ExitOwnerZip_AfterUpdate:
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!frmOwnerEntryFrm!OwnerCity
DoCmd.GoToControl MyControl.Name
Exit Sub

OwnerZip_AfterUpdateErr:
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_AfterUpdate, 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_AfterUpdate

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!frmOwnerEntryFrm!OwnerCity = DLookup("City", "tblZipCodes",
"Zip = '" & Forms!frmOwnerEntryFrm!OwnerZip & "'")

'numeric
Forms!frmOwnerEntryFrm!OwnerCity = DLookup("City", "tblZipCodes",
"Zip = " & Forms!frmOwnerEntryFrm!OwnerZip)
Nov 13 '05 #2

P: n/a
MLH
>
First, check the ControlSource and Name fields in the property sheet for
the City field.
The textbox control on frmOwnerEntryForm 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!frmOwnerEntryFrm!OwnerCity = DLookup("City", "tblZipCodes",
"Zip = '" & Forms!frmOwnerEntryFrm!OwnerZip & "'")

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.
Nov 13 '05 #3

P: n/a
MLH wrote:
First, check the ControlSource and Name fields in the property sheet for
the City field.


The textbox control on frmOwnerEntryForm 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!frmOwnerEntryFrm!OwnerCity = DLookup("City", "tblZipCodes",
"Zip = '" & Forms!frmOwnerEntryFrm!OwnerZip & "'")


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!frmOwnerEntryFrm!OwnerZip & "'") 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.
Nov 13 '05 #4

P: n/a
MLH <CR**@NorthState.net> wrote in
news:gh********************************@4ax.com:
If IsNull(DLookup("[City]", "tblZipCodes",
"Zip=Forms!frmOwnerEntryFrm!OwnerZip")) Then GoTo NotFound
Forms!frmOwnerEntryFrm!OwnerCity = DLookup("City", "tblZipCodes",
"Zip=Forms!frmOwnerEntryFrm!OwnerZip")
Forms!frmOwnerEntryFrm!OwnerState = DLookup("State",
"tblZipCodes", "Zip=Forms!frmOwnerEntryFrm!OwnerZip")


This is simply really dreadful code.

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

DLookup("State", "tblZipCodes",
"Zip=Forms!frmOwnerEntryFrm!OwnerZip")

should always be this:

DLookup("State", "tblZipCodes", "[Zip]=" & _
Forms!frmOwnerEntryFrm!OwnerZip)

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

DLookup("State", "tblZipCodes", "[Zip]='" & _
Forms!frmOwnerEntryFrm!OwnerZip & "'")

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

Dim varCity As Variant

varCity = DLookup("[City]", "tblZipCodes", "[Zip]='" & _
Forms!frmOwnerEntryFrm!OwnerZip & "'")
If Not IsNull(varCity) Then
Forms!frmOwnerEntryFrm!OwnerCity = varCity
Forms!frmOwnerEntryFrm!OwnerState = _
DLookup("State", "tblZipCodes", "[Zip]='" & _
Forms!frmOwnerEntryFrm!OwnerZip & "'")
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 LookupCityStateFromZip(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.City, tblZipCodes.State _
FROM tblZipCodes WHERE tblZipCodes.Zip='" & strZip & "';"

Set db = CurrentDB()
Set rs = db.OpenRecordset(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
Nov 13 '05 #5

P: n/a
Good work Mr Fenton.

DG.

Nov 13 '05 #6

P: n/a
MLH
On Mon, 07 Nov 2005 20:23:41 -0600, "David W. Fenton"
<dX********@bway.net.invalid> wrote:
DLookup("State", "tblZipCodes",
"Zip=Forms!frmOwnerEntryFrm!OwnerZip")

should always be this:

DLookup("State", "tblZipCodes", "[Zip]=" & _
Forms!frmOwnerEntryFrm!OwnerZip)

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!frmOwnerEntryFrm!OwnerZip"

To This
[Zip]=" & Forms!frmOwnerEntryFrm!OwnerZip

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 frmOwnerEntryFrm"

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.
Nov 13 '05 #7

P: n/a
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_AfterUpdate, 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_AfterUpdate()
10 On Error GoTo OwnerZip_AfterUpdateErr
20 Dim ThisForm As String
30 ThisForm = Me.Name
40 If IsNull(DLookup("[City]", "tblZipCodes", "[Zip]=" &
Forms!frmOwnerEntryFrm!OwnerZip)) Then GoTo NotFound
50 Forms!frmOwnerEntryFrm!OwnerCity = DLookup("City", "tblZipCodes",
"[Zip]=" & Forms!frmOwnerEntryFrm!OwnerZip)
60 Forms!frmOwnerEntryFrm!OwnerState = DLookup("State", "tblZipCodes",
"[Zip]=" & Forms!frmOwnerEntryFrm!OwnerZip)

100 If Not IsNull(Me!OwnerCity) And Me!OwnerCity <> "" Then
110 MyString = Me!OwnerCity
120 MyVariant = DLookup("[CityID]", "tblNCcities",
"[tblNCcities].[CityName]=GetMyString()")
130 If Not IsNull(MyVariant) Then
140 MyOtherVariant = DLookup("[County]",
"qryNCCitiesCounties",
"[qryNCCitiesCounties].[CityName]=GetMyString()")
150 If Not IsNull(MyOtherVariant) 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.County,
tblNCcities.CityName FROM (tblNCcities LEFT JOIN
tblNCcityCountyJunctionTable "
180 MySQL = MySQL & "ON tblNCcities.CityID =
tblNCcityCountyJunctionTable.CityID) LEFT JOIN tblNCcounties ON "
190 MySQL = MySQL & "tblNCcityCountyJunctionTable.CountyID
= tblNCcounties.CountyID WHERE (((tblNCcities.CityName) "
200 MySQL = MySQL & "= GetMyString())) ORDER BY
tblNCcities.CityName;"
210 Me!CountyChooserBox.RowSource = MySQL
220 Me!CountyChooserBox.Requery
230 Else
240 Me!CountyChooserBox.RowSource = "SELECT DISTINCTROW
tblNCcounties.County FROM tblNCcounties;"
250 Me!CountyChooserBox.Requery
260 End If
270 Else
280 Me!CountyChooserBox.RowSource = "SELECT DISTINCTROW
tblNCcounties.County FROM tblNCcounties;"
290 Me!CountyChooserBox.Requery
300 End If
310 End If

ExitOwnerZip_AfterUpdate:
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!frmOwnerEntryFrm!OwnerCity
360 DoCmd.GoToControl MyControl.Name
370 Exit Sub

OwnerZip_AfterUpdateErr:
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_AfterUpdate, 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_AfterUpdate

End Sub

Nov 13 '05 #8

P: n/a
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)

Nov 13 '05 #9

P: n/a
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!frmOwnerEntryFrm!OwnerZip & "'"

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

and that I was hoping
"Zip=Forms!frmOwnerEntryFrm!OwnerZip"

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!frmOwnerEntryFrm!OwnerZip & "'") 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.


Nov 13 '05 #10

P: n/a
MLH <CR**@NorthState.net> wrote in
news:jp********************************@4ax.com:
On Mon, 07 Nov 2005 20:23:41 -0600, "David W. Fenton"
<dX********@bway.net.invalid> wrote:
DLookup("State", "tblZipCodes",
"Zip=Forms!frmOwnerEntryFrm!OwnerZip")

should always be this:

DLookup("State", "tblZipCodes", "[Zip]=" & _
Forms!frmOwnerEntryFrm!OwnerZip) 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!frmOwnerEntryFrm!OwnerZip"

To This
[Zip]=" & Forms!frmOwnerEntryFrm!OwnerZip

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 frmOwnerEntryFrm"


The second resolves the value in your VBA code.

The first (your orginal one) leaves the resolving of the control
value to DLookup() itself. It's always more efficient to do it as
close to the control as possible, rather than passing it off to
functions that may or may not be efficient in using the Access
expression service to resolve the value.
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.


There's nothing subtle about the difference at all. In your
original
case, you're depending on DLookup's ability to resolve the control
reference to an actual value, and that's dangerous and probably
very
iniefficient (since it has to pass it off to the Access expression
service). If you resolve the value and pass a literal to the
DLookup, the DLookup doesn't have to do anything special at all.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.