don't save subform data on form dirty

[dsc2bjn]

I poste this in another area, but didn't get what I needed.

I have form which contains a subform. I placed code in the main form to not
save the information, if the user clicks on a "Close without Saving" button.
If the subform contains data, the record is saved anyway. How can I drop
the subform data if the user click on the "Close without Saving" button?

Code in my main form:

If Me.Dirty Then

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
DoCmd.Close

 

[Ken Sheridan]

If the subform contains data then the parent form's current record has been
saved already, so you'll need to delete the parent form's current record and
those in the subform. Assuming that the underlying tables are related in a
on-to-many relationship then the easiest way to do this is to enforce cascade
deletes in the relationship. All you need to do then is delete the parent
form's record and the related records in the referencing table will be
automatically deleted.

If you first ensure that the parent form's record is saved then you can
simply execute an SQL statement to delete it by referencing the primary key
value of the current record. If we assume the primary key is called MyID and
is a number data type, and the parent form's table is called MyTable the code
would be:

Dim dbs As DAO.Database
Dim strSQL As String

Set dbs = CurrentDb

strSQL = "DELETE * FROM MyTable " & _
"WHERE MyID = " & Me.MyID

' ensure record is saved
On Error Resume Next
Me.Dirty = False
If Err <> 0 Then
' undo record if can't be saved
Me.Undo
End If
' delete record
dbs.Execute strSQL
' close form
DoCmd.Close acForm, Me.Name

The error handling I've included is rather crude as it takes no account of
why the record can't be saved, but without knowing more about the table
definition I can't really be more precise. It should suffice.

Ken Sheridan
Stafford, England

 

[tina]

well, the problem is this: if you enter data in a mainform record, and then
move to the subform, the mainform record is automatically saved. and if you
enter data in a subform record, and then move to the mainform, the subform
record is automatically saved. if you're in the subform, and click on a
command button in the mainform, any changes to the subform record are saved
*before* the code behind the command button runs.

if it's an issue of deleting an entire record that you didn't want added,
it's simple enough to write code to delete the current mainform record -
which will automatically delete the related subform record(s). but if you
don't want to *delete* an entire record, but simply stop changes from being
saved in an existing record, then i think you're going to have to add code
to the BeforeUpdate events of both the mainform and the subform, to ask the
user whether or not to save changes.

hth

 

[dsc2bjn]

Deleting the parent and children records works for new entries to the database.

I now need to be able to dump the form and subform changes, if the user
changes their mind. I want to keep the data that was in the tables before
they started editing the record.

 

[Dale Fye]

One method that I have used in the past is to create a local temporary table,
where I store the data for the subform.

When the user selects a new record on main form, I load the temp table with
the data from the main child table. This way, all changes and additions to
the child data are in a table of their own.

If I cancel the updates to the main form, or close without save, or
whatever, I just don't copy the info from the temp table back to the actual
child table. If I do save it, then I write an update query that updates the
records in the main child table for those records that already exist, and an
insert query to insert new records into the main child table for those that
are new.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Ken Sheridan]

I'd favour a slightly modified version of Dale's approach which would cater
for multiple saves of the parent form's current row, i.e. if the user hops
back and forth between the parent and subforms, making successive amendments
to the parent form's current row, each of which would be saved when focus is
moved to the subform. An undo of the parent form in this situation would
only revert to the previous save not the status quo before the user started
editing the row.

However you do it the relevant data from both the referenced and referencing
tables needs to be temporarily stored in data structures before any edits.
This could be done most efficiently by storing the parent form's current row
in a one-dimensional array, and the subform's rows in a two-dimensional
array. Tables which mirror the structures of the referenced and referencing
tables would be simpler, however.

The process would be:

1. In the parent form's Current event procedure execute SQL statements which:

1.1 Delete all rows from both 'mirror' tables.

1.2 Append the parent form's current row to the mirror referenced table.

1.3 Append the subform's current rows to the mirror referencing table.

2. In the 'exit without saving' button's Click event procedure:

2.1 Save and then delete the parent form's current row and, via cascade
deletion, the subform's rows using code like that I posted in my first reply.
Then execute SQL statements which:

2.2 Append the row from the mirror referenced table to the actual
referenced table.

2.3 Append the rows from the mirror referencing table to the actual
referencing table.

Ken Sheridan
Stafford, England

 

[Rick Brandt]

Deleting the parent and children records works for new entries to the
database.

I now need to be able to dump the form and subform changes, if the
user changes their mind. I want to keep the data that was in the
tables before they started editing the record.

In my experience users changing their mind about a data change is a total
non-issue. Certainly not a frequent enough occurrence for me to waste time
developing complicated soultions for it.

 

[John W. Vinson]

In my experience users changing their mind about a data change is a total
non-issue. Certainly not a frequent enough occurrence for me to waste time
developing complicated soultions for it.

I agree. Train the users to "do it right or do it over".