Updating Recordset Not working

[primagic]

I am trying to update a recordset with the following code but get the error: Operation is not supported for this type of object. This happens on the find first bit

Dim rs As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb
Set rs = db.OpenRecordset("tblEngagements")
DoCmd.Save
With rs
.Edit
SrchCrit = CStr(Me!frmEngagementQuickAddSub!EngagementID)
rs.FindFirst "EngagementID = " & SrchCrit
rs!SchoolID = Me.SchoolID
rs!BookingYearID = Year(Date)
rs!EngagementNumber = "1"
rs!HowHeardID = Me.HowHeardID
.Update
End With
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

 

[TheAceMan1]

Try:

[blue]   Dim rs As DAO.Recordset, db As DAO.Database, [purple][b]SrchCrit[/b][/purple]

   Set db = CurrentDb
   Set rs = db.OpenRecordset("tblEngagements")
   [purple][b]DoCmd.RunCommand acCmdSaveRecord[/b][/purple]
   
   With rs
      .Edit
      SrchCrit = CStr([purple][b][frmEngagementQuickAddSub].Form[/b][/purple]!EngagementID)
      rs.FindFirst "EngagementID = [purple][b]'"[/b][/purple] & SrchCrit & [purple][b]"'"[/b][/purple]
      rs!SchoolID = Me.SchoolID
      rs!BookingYearID = Year(Date)
      rs!EngagementNumber = "1"
      rs!HowHeardID = Me.HowHeardID
      .Update
   End With
   
   Set rs = Nothing
   Set db = Nothing[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[PHV]

I'd FindFirst BEFORE Edit ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[primagic]

I still get the same error message with your modified code.

Any other ideas?

 

[PHV]

Did you Edit AFTER FindFirst ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[primagic]

Yeah I then changed my code to:

Dim rs As DAO.Recordset, db As DAO.Database, SrchCrit

   Set db = CurrentDb
   Set rs = db.OpenRecordset("tblEngagements")
   DoCmd.RunCommand acCmdSaveRecord
   
   With rs
      SrchCrit = CStr([frmEngagementQuickAddSub].Form!EngagementID)
      rs.FindFirst "EngagementID = '" & SrchCrit & "'"
      .Edit
rs!SchoolID = Me.SchoolID
      rs!BookingYearID = Year(Date)
      rs!EngagementNumber = "1"
      rs!HowHeardID = Me.HowHeardID
      .Update
   End With
   
   Set rs = Nothing
   Set db = Nothing

It stopped at the find first again

 

[HaworthBantam]

Not played with Access in a while, plus I have a memory like a sieve, but is the reference to rs within the "With" block necessary ?

Dim rs As DAO.Recordset, db As DAO.Database, SrchCrit

   Set db = CurrentDb
   Set rs = db.OpenRecordset("tblEngagements")
   DoCmd.RunCommand acCmdSaveRecord
   
   With rs
      SrchCrit = CStr([frmEngagementQuickAddSub].Form!EngagementID)
      .FindFirst "EngagementID = '" & SrchCrit & "'"
      .Edit
      !SchoolID = Me.SchoolID
      !BookingYearID = Year(Date)
      !EngagementNumber = "1"
      !HowHeardID = Me.HowHeardID
      .Update
   End With
   
   Set rs = Nothing
   Set db = Nothing

 

[PHV]

What happens if you replace this:
Set rs = db.OpenRecordset("tblEngagements")
with this:
Set rs = db.OpenRecordset("SELECT * FROM tblEngagements")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[primagic]

Ok now I get a data type mismatch error

 

[PHV]

Replace this:
.FindFirst "EngagementID = '" & SrchCrit & "'"
with this:
.FindFirst "EngagementID = " & SrchCrit

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[primagic]

ok no errors but not updating

 

[dhookom]

Learn how to debug/troubleshoot faq705-7148. I'm not sure why you are opening a recordset of the entire table when you want to work with only a single or subset of records. I would include the search criteria as a where clause in the OpenRecordset

Dim strSQL As String
strSQL = "SELECT field1, field2, field3,... " & _
   "FROM tblEngagements " & _
   "WHERE EngagementID = ..."
'you can now debug your strSQL
Set rs = db.OpenRecordset(strSQL)
With rs
    If Not .eof Then
    '....etc

Duane
Hook'D on Access
MS Access MVP