Problem with DAO FindFirst

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
dsoderstromAsked:
Who is Participating?
 
Bill PrewCommented:
Try this small change:

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

Open in new window


»bp
0
 
Bill PrewCommented:
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
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
    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

0
 
dsoderstromAuthor Commented:
That fixed the problem.  Thank You.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.