Access recordset.findfirst error - 3070

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?

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

Select allOpen 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.

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

Select allOpen 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.)

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

Select allOpen in new window

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

What is the exact SQL for the subform's recordsource?

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

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;

It looks like all the other fields are there.

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; 

Select allOpen in new window

And the problematic  line of code to this:

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

Select allOpen in new window

Btw, is BatchID  Text or numeric?

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.

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; 

Select allOpen in new window

   crit = "MyDummyField = 123" 

Select allOpen 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?

Hi!

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

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

Select allOpen in new window

(Double and triple check your form and subform names)

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!

Can you post a sample copy of your database?

!!!!!!!!!! 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!

Glad we tracked it down. :-)

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

Did that. It works perfectly now!