update using recordsets

[sophia03]

im trying to update the table using the recordsets but keep geting error message "Cannot update. Database or object read-only" I didnt set anything to be read-only. here is my code so if somebody can review it and tell me what i'm doing wrong, i would realy appreciate

Public Function MoveRecordsets()

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim sql As String
Set db = CurrentDb

sql = " SELECT tblMaster.LoanNo, tblMaster.FirstName, tblMaster.LastName, tblMaster.Status, tblProccessData.LoanNo1, tblProccessData.FirstName1, tblProccessData.LastName1, tblProccessData.Status1"
sql = sql & " FROM tblMaster INNER JOIN tblProccessData ON tblMaster.LoanNo = tblProccessData.LoanNo1;"

Set rst = db.OpenRecordset(sql)



Do While Not rst.EOF

rst.Edit

If rst!Status = "not on our system" Then
rst!status1 = "not on our system"
End If

rst.Update
rst.MoveNext

Loop


End Function

 

[sophia03]

sorry, this is the code

ublic Function MoveRecordsets()

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim sql As String
Set db = CurrentDb

sql = " SELECT tblMaster.LoanNo, tblMaster.FirstName, tblMaster.LastName, tblMaster.Status, tblProccessData.LoanNo1, tblProccessData.FirstName1, tblProccessData.LastName1, tblProccessData.Status1"
sql = sql & " FROM tblMaster INNER JOIN tblProccessData ON tblMaster.LoanNo = tblProccessData.LoanNo1;"

Set rst = db.OpenRecordset(sql)



Do While Not rst.EOF

rst.Edit

If rst!Status = "not on our system" Then
rst!Status1 = rst!Status
End If

rst.Update
rst.MoveNext

Loop


End Function

 

[PHV]

Seems your recordset with joined tables is not updatable.
You can try something like this:
sql = "UPDATE tblProccessData" _
& " SET tblProccessData.Status1='not on our system'" _
& " WHERE tblProccessData.LoanNo1 IN" _
& " (SELECT tblMaster.LoanNo FROM tblMaster WHERE tblMaster.Status='not on our system')"
DoCmd.RunSQL sql


Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884