Avatar of etech0
etech0
Flag for United States of America asked on

Access recordset.findfirst error - 3070

I'm using recordset.findfirst to navigate to a certain record in a form.
I get the following error: RunTime error 3070 The Microsoft Access database engine does not recognize '[myfieldname]' as a valid field name or expression.
I double checked. That is exactly what the field is called, and it exists in the form.
What can be causing this error? Could it be because the form in question is a subform?
Microsoft Access

Avatar of undefined
Last Comment
etech0

8/22/2022 - Mon
Michael Vasilevsky

Could be how are you referencing it?

Me![MyFieldName] or Forms!MyForm!MySubForm.form![MyFieldName]?

Could you provide the entire line of code that's returning the error?
mbizup

Possibly - it depends on how you are defining your recordset and where your code is.

If your code is in the main form, and you are searching the subform's recordset, you should use the following definition (or something like this):


Set rs = Me.MySubformControlName.Form.Recordsetclone

Open in new window



If that doesn't help, please provide more details about your form, subform control name, actual code, and location of the code.
etech0

ASKER
Here is my code:

 
  Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "CatWebWork2F"
    DoCmd.OpenForm stDocName
    
    Dim rst As dao.Recordset, frm As Form
    
    Set frm = Forms("Catwebwork2f")!CatWebWork2SummaryF.Form
    Set rst = frm.RecordsetClone
    Dim crit As String
    crit = "BatchID = " & Me.NewProdSearchSubF.Form.NewProductBatchID
    rst.FindFirst crit
    If rst.NoMatch Then
        Forms!catwebwork2f.FilterCombo = 6
        Call Form_CatWebWork2F.BuildSQL
        rst.FindFirst "BatchID = " & Me.NewProdSearchSubF.Form.NewProductBatchID
        If rst.NoMatch Then MsgBox ("Error! CatWebWork Task not found for BatchID " & Me.NewProdSearchSubF.Form.NewProductBatchID): Exit Sub
    End If
    frm.Bookmark = rst.Bookmark
    
    
    Set rst = Nothing

Open in new window



The error occurs at     rst.FindFirst crit

The main form is Catwebwork2F, subform is Catwebwork2summaryF.
I'm running this from a different form, but that doesn't seem to be the problem (I think.)
Your help has saved me hundreds of hours of internet surfing.
fblack61
mbizup

I've added a Debug.print statement to list the fields in your recordset.  Does "BatchID" appear exactly like that in the list of fields?  Copy/paste that list from the immediate window into your next comment...


  Dim stDocName As String
    Dim stLinkCriteria As String
    dim fld as field

    stDocName = "CatWebWork2F"
    DoCmd.OpenForm stDocName
    
    Dim rst As dao.Recordset, frm As Form
    
    Set frm = Forms("Catwebwork2f")!CatWebWork2SummaryF.Form
    Set rst = frm.RecordsetClone
    '*************  This will list in the immediate window all the field names in your recordset.
    for each fld in rst.fields
         debug.print fld.name
    next
   '******************
    Dim crit As String
    crit = "BatchID = " & Me.NewProdSearchSubF.Form.NewProductBatchID
    rst.FindFirst crit
    If rst.NoMatch Then
        Forms!catwebwork2f.FilterCombo = 6
        Call Form_CatWebWork2F.BuildSQL
        rst.FindFirst "BatchID = " & Me.NewProdSearchSubF.Form.NewProductBatchID
        If rst.NoMatch Then MsgBox ("Error! CatWebWork Task not found for BatchID " & Me.NewProdSearchSubF.Form.NewProductBatchID): Exit Sub
    End If
    frm.Bookmark = rst.Bookmark
    
    
    Set rst = Nothing

Open in new window

etech0

ASKER
Interestingly enough, BatchID does not print. I don't know why this is, as it does appear in the field list for that form.
mbizup

What is the exact SQL for the subform's recordsource?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
mbizup

Also, are all the other fields listed as you would expect them to be for the subform in question?
etech0

ASKER
Here is the SQL:

SELECT [CatWeb Work].RequiresAction, [CatWeb Work].ID, [CatWeb Work].ACVENDT, [CatWeb Work].DescripChanges, [CatWeb Work].status, [CatWeb Work].Location, [CatWeb Work].ProdSpecialist, [CatWeb Work].copywriter, [CatWeb Work].[copy req], [CatWeb Work].[copy recd], [CatWeb Work].displaytype, [CatWeb Work].Priority, IIf([requiresaction]=True,'!','') AS ReqAct, [CatWeb Work].TicklerDate, Personnel.Username, [CatWeb Work].BatchID, Vendors.VenName
FROM Personnel RIGHT JOIN ([CatWeb Work] LEFT JOIN Vendors ON [CatWeb Work].ACVENDT = Vendors.ACVENDT) ON Personnel.PersonID = [CatWeb Work].Location
WHERE ((([CatWeb Work].status)='In Production' Or ([CatWeb Work].status)='Under Consideration') AND (([CatWeb Work].Location)<>15) AND (([CatWeb Work].ProdSpecialist)=15))
ORDER BY [CatWeb Work].TicklerDate, [CatWeb Work].Priority, Vendors.VenName;
etech0

ASKER
It looks like all the other fields are there.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
mbizup

Try changing your SQL to this:

SELECT [CatWeb Work].RequiresAction, [CatWeb Work].ID, [CatWeb Work].ACVENDT, [CatWeb Work].DescripChanges, [CatWeb Work].status, [CatWeb Work].Location, [CatWeb Work].ProdSpecialist, [CatWeb Work].copywriter, [CatWeb Work].[copy req], [CatWeb Work].[copy recd], [CatWeb Work].displaytype, [CatWeb Work].Priority, IIf([requiresaction]=True,'!','') AS ReqAct, [CatWeb Work].TicklerDate, Personnel.Username,[CatWeb Work].BatchID,  [CatWeb Work].BatchID AS BID, Vendors.VenName
FROM Personnel RIGHT JOIN ([CatWeb Work] LEFT JOIN Vendors ON [CatWeb Work].ACVENDT = Vendors.ACVENDT) ON Personnel.PersonID = [CatWeb Work].Location
WHERE ((([CatWeb Work].status)='In Production' Or ([CatWeb Work].status)='Under Consideration') AND (([CatWeb Work].Location)<>15) AND (([CatWeb Work].ProdSpecialist)=15))
ORDER BY [CatWeb Work].TicklerDate, [CatWeb Work].Priority, Vendors.VenName; 

Open in new window


And the problematic  line of code to this:

    crit = "BID = " & Me.NewProdSearchSubF.Form.NewProductBatchID

Open in new window



Btw, is BatchID  Text or numeric?
etech0

ASKER
It's numeric.

I made your corrections to the SQL and VBA, and now I get:
 The Microsoft Access database engine does not recognize 'BID' as a valid field name or expression.
mbizup

Try one more test:

SELECT 123 AS MyDummyField, [CatWeb Work].RequiresAction, [CatWeb Work].ID, [CatWeb Work].ACVENDT, [CatWeb Work].DescripChanges, [CatWeb Work].status, [CatWeb Work].Location, [CatWeb Work].ProdSpecialist, [CatWeb Work].copywriter, [CatWeb Work].[copy req], [CatWeb Work].[copy recd], [CatWeb Work].displaytype, [CatWeb Work].Priority, IIf([requiresaction]=True,'!','') AS ReqAct, [CatWeb Work].TicklerDate, Personnel.Username,[CatWeb Work].BatchID,  [CatWeb Work].BatchID AS BID, Vendors.VenName
FROM Personnel RIGHT JOIN ([CatWeb Work] LEFT JOIN Vendors ON [CatWeb Work].ACVENDT = Vendors.ACVENDT) ON Personnel.PersonID = [CatWeb Work].Location
WHERE ((([CatWeb Work].status)='In Production' Or ([CatWeb Work].status)='Under Consideration') AND (([CatWeb Work].Location)<>15) AND (([CatWeb Work].ProdSpecialist)=15))
ORDER BY [CatWeb Work].TicklerDate, [CatWeb Work].Priority, Vendors.VenName; 

Open in new window



   crit = "MyDummyField = 123" 

Open in new window



This is 'hard-coding' 123 for an entire column called MyDummyField in your form's recordsouce.

Does this still cause an error?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
etech0

ASKER
Hi!

I get  The Microsoft Access database engine does not recognize 'MyDummyField ' as a valid field name or expression.
mbizup

That's what I thought would happen.

My guess from the last few posts is that Catwebwork2f (or some other open form) may have mutiple very similar subforms - with nearly identical recordset queries, and that you are using the wrong subform name in the following line of code:

   Set frm = Forms("Catwebwork2f")!CatWebWork2SummaryF.Form

Open in new window



(Double and triple check your form and subform names)
etech0

ASKER
What you are saying makes sense. However, double and triple checking did not produce any inconsistencies between form names.

I even went as far as to do the following:
-select the subform name from that line of code
-copy
-paste it into the search box
-open the subform in design view
-go straight to the recordsource
-it's the same one, and shows MyDummyValue in design view

I don't get it!
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
mbizup

Can you post a sample copy of your database?
ASKER CERTIFIED SOLUTION
mbizup

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
etech0

ASKER
!!!!!!!!!! You got it !!!!!!!!!!

I can't believe I forgot about that code! I put it there, after all. (And for a good reason.)

Thank you, thank you, thank you!
mbizup

Glad we tracked it down. :-)

You should be able to use your original code and query now instead of the 'test' versions I posted.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
etech0

ASKER
Did that. It works perfectly now!