We help IT Professionals succeed at work.

Access recordset.findfirst error - 3070

etech0
etech0 asked
on
1,160 Views
Last Modified: 2012-05-31
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?
Comment
Watch Question

Michael VasilevskySolutions Architect
CERTIFIED EXPERT

Commented:
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?
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
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.

Author

Commented:
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.)
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
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

Author

Commented:
Interestingly enough, BatchID does not print. I don't know why this is, as it does appear in the field list for that form.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
What is the exact SQL for the subform's recordsource?
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

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

Author

Commented:
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;

Author

Commented:
It looks like all the other fields are there.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
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?

Author

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
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?

Author

Commented:
Hi!

I get  The Microsoft Access database engine does not recognize 'MyDummyField ' as a valid field name or expression.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
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)

Author

Commented:
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!
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Can you post a sample copy of your database?
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
!!!!!!!!!! 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!
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Glad we tracked it down. :-)

You should be able to use your original code and query now instead of the 'test' versions I posted.

Author

Commented:
Did that. It works perfectly now!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.