Can't edit values in continuous subform (1 Viewer)

[AOB]

I have a form with a continuous subform. The subform uses a local staging table as its record source; that staging table is cleared and refilled when the main form is loaded such that the staging table only contains the records specific to the record for the main form. The idea being that I can allow users to make changes directly in the subform (specifically, dates in textboxes) and those changes are only reflected in the local staging table, and then from the main form they can save changes and I have another process that effectively merges the staging table back to the "live" table (i.e. delete anything that's been deleted, update anything that's been updated, insert anything that's new)

For some weird reason, though, I can't edit the dates in the subform? I can click into the textbox and see the date picker, select a different date, but it won't update the value in the staging table? I can't trap any of the events for the text box (Change / BeforeUpdate / AfterUpdate) because they never fire?

The Record Source for the subform is the staging table itself (not a complex query with joins, making it non-updateable - which was the motivation for binding the subform directly to the staging table in the first place). Recordset Type is Snapshot. Allow Edits set to Yes. The textbox holding the date is Enabled = Yes / Locked = No.

Can't figure out why it won't allow the change? Any suggestions?

 

[ebs17]

Recordset Type is Snapshot

Snapshot is per definition only for reading.

 

[arnelgp]

ot a complex query with joins, making it non-updateable -

 

[AOB]

Snapshot is per definition only for reading.

Yes, that did cross my mind; what's strange is, if I change it to Dynaset, the subform doesn't seem to load properly? It does show that records exist but all the fields are blank?

 

[ebs17]

Snapshot reads all fields immediately, Dynaset reads only the keys first, the fields are loaded if necessary.
Now the questions arise: What does your table look like, and from where is it loaded?

 

[arnelgp]

The Record Source for the subform is the staging table itself (not a complex query with joins, making it non-updateable - which was the motivation for binding the subform directly to the staging table in the first place). Recordset Type is Snapshot. Allow Edits set to Yes. The textbox holding the date is Enabled = Yes / Locked = No.

 

[AOB]

Snapshot reads all fields immediately, Dynaset reads only the keys first, the fields are loaded if necessary.
Now the questions arise: What does your table look like, and from where is it loaded?

Aaargh - yup, have it now. It was actually displaying perfectly fine, forgot to remember the join that I lost when I switched from a non-updateable query to a staging table and omitted the name corresponding to the ID / key. Have corrected and now it's working perfectly.

Thanks a million, apologies, I knew it was something simple but couldn't see the wood for the trees, needed a fresh pair of eyes to point out the obvious!

 

[Pat Hartman]

I hate to get involved especially since you think your problem is solved but editable forms should never be bound to complex queries. There is always ONE and only ONE table being updated. Occasionally, we join to lookup tables in order to show ancillary data like a customer address even when we specifically lock the controls to prevent updating. An example might be a subform with a junction table. the RecordSource might join to the "other" side of the relationship to show data from that side. So a student form might have a subform with classes. The subform is bound to the junction table that connects students to classes since this is a m-m relationship. In addition to the class name chosen by the combo, maybe the subform will show the classroom or the teacher, but you would never allow updating of the classroom or the teacher from the subform of a student since that would affect EVERY member of the class.

 

[AOB]

Understood Pat - and, again, agreed. That's specifically why I created the local staging table to bind to the subform. The query loads the table with the data relevant to the subform (i.e. relevant to the display) and edits are made to the staging table, not to the actual parent tables. I have a separate function/process that properly propagates appropriate changes back to the "live" tables in a very controlled manner.

 

[Pat Hartman]

That is NOT a reason for creating a staging subform. You have made a lot of extra work for yourself.

 

[AOB]

It's not the only reason I did it...