Error object is open read only

[DC CS]

Hi,
Please see below code:



Set Dbs4 = CurrentDb
SQL4 = "SELECT StartDate, EndDate, CertNumber FROM Tbl_Certification_BrokenCourses " _
& "GROUP BY StartDate, EndDate, CertNumber " _
& "HAVING CertNumber = " & Val(Forms!Frm_CertificationEditDatesBrokenCourses. CertNumber) & " ORDER BY EndDate;"
Set Rst4 = Dbs4.OpenRecordset(SQL4)


Rst4.MoveLast: Rst4.MoveFirst
i = 1
With Rst4
.Edit
!StartDate = Me("StartDate" & i)
!EndDate = Me("EndDate" & i)
.Update
i = i + 1
Rst4.MoveNext
End With

Once the ".Edit" is executed, I get the error 3027 - Object is read only.

Does someone know why? I have other code with the ".Edit" and runs without any issues.

Thanks

[Welshgasman]

Try editing that as a query.?
If you cannot, then it is read only.

You can also pass a parameter to OpenRecordset https://learn.microsoft.com/en-us/of...set-method-dao

[DC CS]

I found the culprit - it is due to the GROUP BY clause.

How do one substitute the GROUP BY clause?

[Minty]

With great difficulty if you want to group the records. Once grouped you have a set of non uniquely-identifiable records.

You could simply use an update query (rather than looping through them) joined to the courses you select via your grouping query, but it will by definition update all of the records returned.

[Welshgasman]

Perhaps DISTINCT ?

[DC CS]

Hi all,
Thanks for the assistance. The UPDATE query is doing the job.
A bit more coding, but it works

DISTINCT only retrieves 1 of the duplicate records, and not all 7 of them.

Thanks