Jim Lawrence
accessd at shaw.ca
Tue Oct 12 23:49:43 CDT 2010
Hi Rocky:
That would have been nearly my last guess... it must be that when all
possible logical solutions have been exhausted the only option left, no
matter how implausible must be the answer. (Doyle could not have said it
better)
All I can say is welcome to the strange world of MS Access where the laws of
the universe and logic do not necessarily apply.
Jim
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
Sent: Tuesday, October 12, 2010 9:15 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Error 3070 - field not recognized
No - I always prefix field names with fld and never use them for anything
else. Nope - deleting the field form the table and re-entering it solved
the problem. God knows why. I'm thinking rift in the temporal matrix. Some
kind of quantum effect. Or a poltergeist.
R
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
Sent: Tuesday, October 12, 2010 7:53 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Error 3070 - field not recognized
Hi Rocky:
One quick thought. You would not have added a 'caption name' so the field
name displays differently or made a calculation, lookup or query field, that
you are trying to access in the subform? If so, that could be problems.
Jim
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
Sent: Tuesday, October 12, 2010 6:00 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Error 3070 - field not recognized
Jim:
cboLotSerialReferences is displaying a value.
After the Set rs MsgBox rs.RecorCount displays 7
However...
MsgBox "First record field value = " &
rs.Fields("fldLotSerialReference").Value & ""
Errors 3265 - Item not found in this collection
So I added:
Dim fld As Field
For Each fld In rs.Fields
MsgBox fld.Name
Next fld
And watched as each field name in the bound table tblLotSerial came up in
sequence, among them was fldLotSerialReference.
Strange, huh?
It's fldLotSerialReference that's the problem - something strange there.
I wonder what would happen if I deleted the field from the table and
recreated it?
Well what do you know - that fixed it! I wonder if there was some kind of
hidden character in there? Anyway thanks for hanging in there with me. That
was an odd problem.
R
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
Sent: Tuesday, October 12, 2010 3:55 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Error 3070 - field not recognized
Hi Rocky:
More obvious stuff: So the following code;
MsgBox "Lot Combo selection = " & Me.cboLotSerialReferences.Column(0)
...is displaying a value.
Assuming then that the value returned from the Lot combo box is correct and
the search field is correct then how many rows/fields are being placed in
the recordset 'Me.subfrmLotSerial.Form.RecordsetClone'? (...rs.RecordCount)
with something like:
With rs
.movelast
.movefirst
MsgBox "records = " & .RecordCount
End With
...and one final check, assuming that the record count has a value greater
than zero check to see whether the field actually exists.
MsgBox "First record field value = " &
rs.fields("fldLotSerialReference").value & ""
I am sure you have already checked this but decided to ask the obvious
questions anyway. ;-)
Jim
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
Sent: Tuesday, October 12, 2010 1:10 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Error 3070 - field not recognized
LotSerial combo has only one column. I put a MsgBox in to display strSQL
and it looks right. It's the field fldLotSerialReference that it's having a
problem with. strSQL reads fldLotSerialReference = 'xxx' after a selection
is made from the combo box.
The nearly identical code for the part number works just fine. That's
what's so baffling.
R
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
Sent: Tuesday, October 12, 2010 12:40 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Error 3070 - field not recognized
Hi Rocky:
The obvious question would be whether the 'lot' combo is returning a
value...right column?...or if it is a number field for lot number; have you
set it to a string and then TRIMMED of extra spaces?
Jim
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
Sent: Tuesday, October 12, 2010 11:38 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Error 3070 - field not recognized
OK per a snip I saw on the web I changed the routine to use rs dimmed as a
DAO.Recordset. There are two combo boxes the user can use to find a record
- one for a part number, one for a lot number. The part number one works,
lot number one fails:
Private Sub cboLotSerialReferences_AfterUpdate()
strSQL = "fldLotSerialReference = '" _
& Me.cboLotSerialReferences.Column(0) & "'"
Set rs = Me.subfrmLotSerial.Form.RecordsetClone
rs.FindFirst strSQL
Me.subfrmLotSerial.Form.Bookmark = rs.Bookmark
End Sub
Private Sub cboPart_AfterUpdate()
strSQL = "fldLotSerialPartNumber= '" _
& Me.cboPart.Column(0) & "'"
Set rs = Me.subfrmLotSerial.Form.RecordsetClone
rs.FindFirst strSQL
Me.subfrmLotSerial.Form.Bookmark = rs.Bookmark
End Sub
Rocky
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
Sent: Tuesday, October 12, 2010 10:47 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Error 3070 - field not recognized
Dear List:
This has got to be something simple that I'm just not seeing because I've
used this technique for years with no problem. But I'm stumped.
User selects a value from a combo box (in this case a lot number) and I use
.FindFirst and .Bookmark to set the record selector to the selected record
on a continuous form. The combo box is on the main form, continuous form is
a sub form. (BTW I tried putting the combo right on the subform but had the
same problem.)
Here's the code:
Private Sub cboLotSerialReferences_AfterUpdate()
Dim strSQL As String
strSQL = "fldLotSerialReference = '" _
& Me.cboLotSerialReferences.Column(0) & "'"
MsgBox strSQL
Me.subfrmLotSerial.Form.RecordsetClone.FindFirst strSQL Me.Bookmark =
Me.RecordsetClone.Bookmark
End Sub
where fldLotSerial is a bound field on the subform. The MsgBox shows
fldLotSerialReference = 'aaa'. On the .FindFirst statement I get error 3070
- Microsoft Jet database engine does not recognize 'fldLotSerialReference'
as a valid field name or expression.
Any clues?
MTIA
Rocky Smolin
Beach Access Software
858-259-4334
Skype: rocky.smolin
www.e-z-mrp.com <http://www.e-z-mrp.com/>
www.bchacc.com <http://www.bchacc.com/>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com