Updating using form bound to a query

[onechriswhite]

Hi


I have a form whose data comes from a query. This query draws data from four different tables, and I have noticed on this forum that there are issues as to whether you can update records using queries like this.

I am able to update data directly in my query so I thought I had got around the problem. I did this by changing some properties of the query and I think it is the "recordset type" property on the query (changed to "Dynaset (inconsistent updates)") (whatever that is), that is enabling me to do this. However, when I try to change data in my form, it doesn't let me make changes in the fields at all even though I can do so in the query.

I want to be able to make changes to the data in the form FRMPupilAchievementByPiece. Any idea on how I can do this? My query only has inner joins, if that's important. I know a bit, but obviously not enough! Any help appreciated.
Thanks
(PS I deleted most of the DB in the uploaded copy to reduce its size. Any better way?)

[dsmacs]

Hi Chris,

Can you provide details of the process? When uploading a Database, zip it first to reduce size. Can you reattach with more data and I will have a look. I think you will need a couple of subforms to accomplish what you are after.

Regards
Darren

[onechriswhite]

Zip it - thanks!

DB is attached.

I need to be able to go from FRMActivity and call up FRMPupilAchievementByPiece, such that the data in this form is filtered by AssessmentPieceID, and then edit the data in the "Security" Field. This field is from TBLPupilAchievement.

Previously I have achieved a similar result by opening an interim form and then usig a subform but I really don't want the process to be that slow!

Thanks
Chris

[dsmacs]

Hi Chris,

Inserted a subform for the FRMPupilAchievementByPiece, it allows updates;-



Cheers
Darren

[onechriswhite]

Thanks, that seems to work - but why it does, I don't know!

I'm now trying to change it so that Pupil Name appears in the master form and all the relevant focusIDs and fields from TBLPupilAchievement appear in the subform. Also, I only want those FocusIDs on the subform that appear in TBLAssessmentPieceFocus for the AssessmentID on the master form. I'll let you know how I get on but would appreciate your opinion n the result

Don't ask for a lot, do I?!

Still baffled as to why an update is allowed in a query but not in a form linked directly to that query.

Thanks for your work - really appreciated

Chris

[onechriswhite]

I've changed it around now so that it has a subform like you suggested, but behaves like a single form. It seems to work but I have had to set the locks to "Dynaset (inconsistent)" - if anyone knows what the implications of this are, I would be glad to understand it!

See attached. I'm still at a loss to understand why the single form didn't allow updates. Should I just get over it and move on?!

Thanks again
Chris