Problem with DAO FindFirst

dsoderstrom
dsoderstrom used Ask the Experts™
on
I'm getting an error message on the FindFirst line in the following code.
The error message is "Run-time error '3251'   Operation is not supported for this type of object."
Can someone tell me what I am doing wrong?

    Dim rs As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim i As Long
    Dim reccount As Long
   

    Set rs = CurrentDb.OpenRecordset("2015and2016ServicePartsOrders")
    Set rs2 = CurrentDb.OpenRecordset("ItemPriceHistory")
    reccount = DCount("*", "2015and2016ServicePartsOrders")
    rs.MoveFirst
    For i = 1 To reccount
        rs2.FindFirst ("[ItemNumber]= ' " & rs!ItemNumber & " ' ")
        If rs2.NoMatch Then
            rs.Edit
            rs![List Price] = 0
            rs.Update
        Endif
        If i = reccount Then
            Exit For
        Else
           rs.MoveNext
        End If
  next I
  rs.close
  rs2.close
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Test your restores, not your backups...
Top Expert 2016
Commented:
Try this small change:

    Set rs2 = CurrentDb.OpenRecordset("ItemPriceHistory", dbOpenDynaset)

Open in new window


»bp
Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
I also notice you have extra spaces, please adjust this line as well so the matches will be found:

        rs2.FindFirst ("[ItemNumber] = '" & rs!ItemNumber & "' ")

Open in new window


»bp
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
    Dim db as DAO.Database
    Dim rs As DAO.Recordset
    Dim rs2 As DAO.Recordset

   Set db = CurrentDB()
    Set rs = db.OpenRecordset("2015and2016ServicePartsOrders")
    Set rs2 = db.OpenRecordset("ItemPriceHistory")

   Do until rs.EOF
        rs2.FindFirst  "[ItemNumber]= ' " & rs!ItemNumber & " ' "
        If rs2.NoMatch Then
            rs.Edit
            rs![List Price] = 0
            rs.Update
        Endif

        rs.MoveNext
   loop

  rs.close
  Set rs = nothing

  rs2.close
  set rs2 = nothing

  set db = nothing

Open in new window

Author

Commented:
That fixed the problem.  Thank You.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial