How to add/save items to a continuous subform using command button on main form?

[beacon]

Hi everybody,

[Access 2003]

I have a main form, frmDeficiency, that has a tab control, deficiencyTabControl, that has a subform, fsubEpisodeDetail, on page 2 of the tab control. I also have a command button on the main form that I'm going to be using to submit the data.

The main form is bound to a query, qryPatientDetail, and is filtered using a search form. The subform is currently linked to the main form with PatientDetailID as the Master and PatientDetailIDFK as the Child. Currently, the subform is bound to a query called qryEpisodeDetail, but I'm wondering if I shouldn't make it unbound.

Here's my dilemma. When a user searches for a patient using the search form and the main form is filtered, I want the subform to show all records that have been entered. However, if new records are entered, I don't want those to save until the user clicks the submit button on the main form.

Currently, the users access the subform and anything they enter is automatically saved to the underlying table and the submit form is just a way for the user to get back to the switchboard.

I can make the subform unbound and load the record source in the Form_Load event for the subform when the subform loads inside the main form, but that doesn't resolve the issue with being able to prevent records from saving until the submit button is pressed.

Just in case it matters, the main form is set to allow edits, but not additions, deletions, or data entry. The subform is set to allow edits, deletions, and additions, but not data entry.

Is it possible to have a subform load with previously entered records, but not save new records until the user clicks the submit button on the main form? Will I need to make each of the controls unbound too?

As always, I appreciate the help,
beacon

[TheSmileyCoder]

In short: Yes.

I think the approach to use here, would be to have a field in your table, lets call it b_Submitted. Set the default value of the field to False.

Now when a user starts entering data, the default will mark the record as not submitted.

When the user clicks submit (And probably also if the user closes the form through other means) you ask the user if he wants to submit the X records he made (you could count them for him)

If he says yes, simply run a update query, setting b_Submitted=True for that patient ID. If he says no, run a delete query for that patientID deleting all fields with b_Submitted=false

You should probably add a check of some sort, where you scan the table for "old" unsubmitted records, and decide what to do with them, since its possible if a user closes access (Or gets a Blue Screen of Death) that he will not have had the chance to select whether or not to submit the data.

[beacon]

What about if the user is updating a row that has already been submitted and they decide to close without saving their changes?

If I set the b_Submitted = False for the rows that have been updated, then when the form is closed I wouldn't be able to revert back to it without something else to track which fields were updated and the original values, right?

[TheSmileyCoder]

There may be smarter ways of doing this, but in this case what I would probably do, would be to make a temporary table, and when the user moves from 1 record to the next in your main table, you delete/populate the temporary table, while keeping track of which records have changed.

[beacon]

I was afraid of that. I didn't really want to get into having to create temporary tables for this database, but there be no other way around it.

Thanks for your help TSO!