cancel in form with subform

[Mike Brearley]

How do I go about doing an appropriate cancel on a form that has a subform.

What I'm running into with a typical undo and close is that if data was
entered in the main form, and the user is on the subform, the cancel only
undoes the data in the subform. Also, if the user enterd multiple records
on the subform, the cancel only undoes the current record.

There's two tables that I'm dealing with in these forms. Table one has
machine#, shift#, date and a bunch of downtime info. Table two has
machine#, shift#, date and info on parts run and production time.

A specific machine#, shift# and date occurance can only occur once in the
first table, but there can be a few in the second table (different parts
run, different operator running the machine, etc...) in the second table.

Any help would be greatly appreciated.

Thanks,
Mike

 

[John Vinson]

How do I go about doing an appropriate cancel on a form that has a subform.

It's difficult. See below.

What I'm running into with a typical undo and close is that if data was
entered in the main form, and the user is on the subform, the cancel only
undoes the data in the subform. Also, if the user enterd multiple records
on the subform, the cancel only undoes the current record.

Actually the mainform record is saved the instant you start entering
data into any subform record; each subform record is saved the moment
that you leave the record to a new one, or set focus back to the
mainform. So in any case it's too late to "undo" or "cancel" the
addition - it's already done and written to disk.

There's two tables that I'm dealing with in these forms. Table one has
machine#, shift#, date and a bunch of downtime info. Table two has
machine#, shift#, date and info on parts run and production time.

A specific machine#, shift# and date occurance can only occur once in the
first table, but there can be a few in the second table (different parts
run, different operator running the machine, etc...) in the second table.

What you will need to do is to define the relationship between the two
tables (on Machine#, Shift# and [Date], or if you have a surrogate key
on that field) to Cascade Deletes, and run a Delete query deleting the
record shown on the mainform. Naturally delete queries are a bit of a
sledgehammer approach and need to be done carefully, but if you just
have a Delete button which runs a query like this it should be OK:

Private Sub cmdCancel_Click()
Dim strSQL As String
Dim iAns As Integer
Dim db As DAO.Database
Dim qd As DAO.Querydef
On Error GoTo Proc_Error

strSQL = "DELETE * FROM TableOne WHERE [Machine#] = '" & _
Me!cboMachine & "' AND [Shift#] = " & Me!cboShift & _
" AND [Date] = #" & Me!txtDate & "#"
iAns = MsgBox("This will delete the header record for machine " & _
Me!cboMachine & " for " & Me!txtDate & ", Shift " & Me!cboShift & _
vbCrLf & " and " & DCount("*", "[TableTwo]", "[Machine#] = '" & _
Me!cboMachine & "' AND [Shift#] = " & Me!cboShift & _
" AND [Date] = #" & Me!txtDate & "#") & " TableTwo records.", _
vbYesNo)
If iAns = vbYes Then
Set db = CurrentDb
Set qd = db.CreateQuerydef("", strSQL)
qd.Execute dbFailOnError
Set qd = Nothing
End If
Proc_Exit: Exit Sub
Proc_Error:
<put your error handling code here>
Resume Proc_Exit
End Sub

John W. Vinson[MVP]

 

[Guest]

Mike:

Once the parent form's record has been saved, which happens when you move
focus to the subform, you need to Delete the parent form's record, which can
be done from the built in toolbar button or you could add a command button to
the form to do this. Ensure that a Cascade Delete referential operation is
enforced in the Relationship between the parent form's table and the
subform's underlying table. When you delete a record from the parent form
all records in the subform's table which reference it will also be deleted.

Ken Sheridan
Stafford, England

 

[Guest]

Hi Mike,

You can cancel a current edit of a record, but once the record has been
saved and is not "dirty", there is nothing left to cancle. There is a visual
clue if a record is dirty as long as you have the record selector visible. A
pencil symbol in a record selector indicates that the record has been
"dirtied". Once saved, the symbol turns back to a triangle on edge.

When you click into your subform, from the main form, Microsoft Access will
automatically save any changes made to the main form. Thus, the main form
record is no longer dirty. The same thing happens when you click from one
record in a subform to another, or if you move focus from a subform record
back to the main form--Access will automatically save changes. The Undo
action only works for a dirtied record that has not been committed.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

How do I go about doing an appropriate cancel on a form that has a subform.

What I'm running into with a typical undo and close is that if data was
entered in the main form, and the user is on the subform, the cancel only
undoes the data in the subform. Also, if the user enterd multiple records
on the subform, the cancel only undoes the current record.

There's two tables that I'm dealing with in these forms. Table one has
machine#, shift#, date and a bunch of downtime info. Table two has
machine#, shift#, date and info on parts run and production time.

A specific machine#, shift# and date occurance can only occur once in the
first table, but there can be a few in the second table (different parts
run, different operator running the machine, etc...) in the second table.

Any help would be greatly appreciated.

Thanks,
Mike

 

[Guest]

PS.

"Date" is a reserved word. You should avoid naming anything in Access with
reserved words. Also, I advise you to avoid using special characters, such as
the # sign, for anything that you assign a name to in Access.

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

Reserved Words in Microsoft Access
http://support.microsoft.com/?id=286335

List of reserved words in Jet 4.0
http://support.microsoft.com/?id=321266


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Hi Mike,

You can cancel a current edit of a record, but once the record has been
saved and is not "dirty", there is nothing left to cancle. There is a visual
clue if a record is dirty as long as you have the record selector visible. A
pencil symbol in a record selector indicates that the record has been
"dirtied". Once saved, the symbol turns back to a triangle on edge.

When you click into your subform, from the main form, Microsoft Access will
automatically save any changes made to the main form. Thus, the main form
record is no longer dirty. The same thing happens when you click from one
record in a subform to another, or if you move focus from a subform record
back to the main form--Access will automatically save changes. The Undo
action only works for a dirtied record that has not been committed.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

How do I go about doing an appropriate cancel on a form that has a subform.

What I'm running into with a typical undo and close is that if data was
entered in the main form, and the user is on the subform, the cancel only
undoes the data in the subform. Also, if the user enterd multiple records
on the subform, the cancel only undoes the current record.

There's two tables that I'm dealing with in these forms. Table one has
machine#, shift#, date and a bunch of downtime info. Table two has
machine#, shift#, date and info on parts run and production time.

A specific machine#, shift# and date occurance can only occur once in the
first table, but there can be a few in the second table (different parts
run, different operator running the machine, etc...) in the second table.

Any help would be greatly appreciated.

Thanks,
Mike