read only, can't change records in query

[schalkse]

Ok, been puzzled by this one.
I have a form with a subform in it. The subform is based on a query. It shows all my active staff. On the form i have a button that (atleast i would like that it does that) when clicked it sets the field "selected" on true on the subform.

The code behind the button works just fine, but i always get an error stating, can't change data, database or object is read only. Funny thing is, when i click on the checkbox "selected" in the subform behind each staff member, i can set it to true. Why does it not work? I tried it with selecting the query itself (qerWerknemersActief instead of tblWerknemers) and also not working. Here's the code, hope someone has a clue. Oh, in each form everything is set on yes except adding records, so it's not that change records is set to no.

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

Dim strSQL As Variant


strSQL = "SELECT tblWerknemers.Id, tblWerknemers.Werknemer, tblWerknemers.Selectie, tblWerknemers.Actief " & _
"FROM tblWerknemers " & _
"WHERE (((tblWerknemers.Actief)= True));"


Set db = CurrentDb

Set rs = db.OpenRecordset(strSQL, dbOpenForwardOnly)
Do Until rs.EOF
rs!Selectie = True
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set db = Nothing
Call Form_frmBeheerDeelnemers.Requery

 

[ejstefl]

Try changing dbOpenForwardOnly to dbOpenDynaset and see if that works...

 

[schalkse]

Try changing dbOpenForwardOnly to dbOpenDynaset and see if that works...

cool, one step further, but i get this error now "Update or CancelUpdate without AddNew or Edit"

Any idea what step i am missing?

Thanks

 

[ejstefl]

Try this:

Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
Do Until rs.EOF
rs.edit
rs!Selectie = True
rs.update
rs.MoveNext
Loop

 

[schalkse]

Thanks alot, works like a charm now

 

[m17347047711116]

I have experienced this simple fix for me was to go into the properties for the sub form and change from Dyna to Dyna (inconsistent updates).
This worked for me.