Cancel Button Code Not Working

[R Tanner]

I have the following code I am using to cancel a new record entered in
a database. Any ideas why it isn't working? I'm new to database
coding, but really familiar with coding in Excel.

Private Sub Command_Click()

If (Me.Dirty = True) Then
Me.Undo
End If

DoCmd.Close acForm, Me.Name

End Sub

 

[John W. Vinson]

I have the following code I am using to cancel a new record entered in
a database. Any ideas why it isn't working? I'm new to database
coding, but really familiar with coding in Excel.

Private Sub Command_Click()

If (Me.Dirty = True) Then
Me.Undo
End If

DoCmd.Close acForm, Me.Name

End Sub

It might not be working because the record has already been saved before you
get a chance to click the button. Does your form have one or more Subforms?
Setting focus to a subform saves the mainform record then and there. There are
other cases where the record will be (silently) saved. If so, the form will no
longer be Dirty and your code won't run (and there'll be nothing to undo
anyway).

 

[R Tanner]

It might not be working because the record has already been saved before you
get a chance to click the button. Does your form have one or more Subforms?
Setting focus to a subform saves the mainform record then and there. There are
other cases where the record will be (silently) saved. If so, the form will no
longer be Dirty and your code won't run (and there'll be nothing to undo
anyway).

Well...When you say subform what do you mean? I have several fields
that reference other tables, but all of the values in my form are
being put into one table...Either way, when I first open the form, I
tried just entering the date and then hitting cancel and it still
saves the record, with nothing in the record but the date...I need to
identify how I can keep that from happening...

 

[John W. Vinson]

Well...When you say subform what do you mean?

I mean a Subform Control on your form. I can't see your form, I don't know if
it has a Subform control on it or not, so I was asking you.

I have several fields
that reference other tables, but all of the values in my form are
being put into one table...Either way, when I first open the form, I
tried just entering the date and then hitting cancel and it still
saves the record, with nothing in the record but the date...I need to
identify how I can keep that from happening...

Do you have any OTHER code on the form? Were you aware that you can cancel a
record by hitting the <Esc> key twice? Does doing so successfully cancel the
record? If not, then *something* is saving the record right off the bat.

 

[R Tanner]

I mean a Subform Control on your form. I can't see your form, I don't know if
it has a Subform control on it or not, so I was asking you.


Do you have any OTHER code on the form? Were you aware that you can cancel a
record by hitting the <Esc> key twice? Does doing so successfully cancel the
record? If not, then *something* is saving the record right off the bat.

Okay I figured out what a subform is. I am having trouble figuring
out how to get them to work. On the Cancel button however. I did
know you can hit esc to get out of it. I tried that way and it still
saves it. It looks like it only saves the record when I enter the
date.

I enter the start and end date which are entered into my Tickets
table. Then I tab down and enter values in my next field, which looks
up the ID and Issue from my parent table Issues and then enters the
value in the corresponding foreign key column in Tickets. If I enter
values in my fields other than the Start and End dates, and then hit
cancel or esc., it exits the form without updating the database.

I have the following other code used on the form:

Dim LngErr As Long, strError As String

SaveIt = True

If (Me.Dirty = True) Then
Me.Dirty = False
End If

Sub Issue_Click()
Select Case IssueID.Value
Case Is = 274
Other.Enabled = True
Case Else
Other.Enabled = False
End Select
End Sub

 

[John W. Vinson]

It looks like it only saves the record when I enter the
date.

Sounds like the date control is the *last* control in the form's tab order. If
you want the record to only be saved when you specifically save it, set the
form's Cycle property to Yes - it will stay on the same record even if you
tab/enter to the end of the tab order.

 

[Linq Adams via AccessMonster.com]

I believe John meant to say set the form's Cycle Property to "Current Record"
rather than "Yes."

 

[R Tanner]

Sounds like the date control is the *last* control in the form's tab order. If
you want the record to only be saved when you specifically save it, set the
form's Cycle property to Yes - it will stay on the same record even if you
tab/enter to the end of the tab order.

Nope doesn't work. The cycle property has three settings. Current
page, Current Record, or All Records. I set it to Current Record and
it still saves the data when i enter the date. Any other suggestions?

 

[John W. Vinson]

Nope doesn't work. The cycle property has three settings. Current
page, Current Record, or All Records. I set it to Current Record and
it still saves the data when i enter the date. Any other suggestions?

What is the Tab Order property of the date control? What is the highest tab
order property of any control on the form (rightclick the little square at the
upper left corner and choose Tab Order to see all the controls in their
defined tab order). Again: *do you have ANY other VBA code on this form* which
might be saving the record?

 

[Linq Adams via AccessMonster.com]

Try adding this line

MsgBox "Form is dirty"

to your code, add a new record and see if the message box pops up:

Private Sub Command_Click()

If (Me.Dirty = True) Then
MsgBox "Form is dirty"
Me.Undo
End If

DoCmd.Close acForm, Me.Name

End Sub

Then you'll know whether or not the record is being saved before you tyr to
dump it.

 

[John W. Vinson]

Nope doesn't work. The cycle property has three settings. Current
page, Current Record, or All Records. I set it to Current Record and
it still saves the data when i enter the date. Any other suggestions?

Try putting a line of code in the Form's BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel as Integer)
MsgBox "Access is saving the record!", vbOKOnly
End Sub

Use your form and see what form action causes the message to appear. I'm
totally baffled!

 

[R Tanner]

I believe John meant to say set the form's Cycle Property to "Current Record"
rather than "Yes."

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200810/1

Yes thank you for the info. I figured. I tried that and it still
saves the record when I tab away from the date field. The only other
thing I could think of to do is to put a loop in my cancel event code
to loop through every record in my table and delete the corresponding
record with the date and time. Is seems like there has to be a better
way to do this though...

 

[nouveauricheinvestments]

I believe John meant to say set the form's Cycle Property to "Current Record"
rather than "Yes."

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200810/1

Yes I tried this and it still didn't work. The only other thing I
could think of doing was writing a loop in the cancel button_Click
event code that loops through every record in the table and deletes
the related record...That is so ridiculously inefficient
though...there has to be a simpler way..

 

[R Tanner]

What is the Tab Order property of the date control? What is the highest tab
order property of any control on the form (rightclick the little square at the
upper left corner and choose Tab Order to see all the controls in their
defined tab order). Again: *do you have ANY other VBA code on this form* which
might be saving the record?

--

             John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

My Tab order starts out with the StartTime and then EndTime is right
after that.

The following code is also on the form. Which should not be executed
until I hit the button.

Maybe I should just delete the form and start again..

Private Sub Save_Click()

DoCmd.Save acForm, Me.Name


End Sub

 

[John W. Vinson]

My Tab order starts out with the StartTime and then EndTime is right
after that.

The following code is also on the form. Which should not be executed
until I hit the button.

Maybe I should just delete the form and start again..

Private Sub Save_Click()

DoCmd.Save acForm, Me.Name

In any case, this code does NOT save the record - it saves design changes to
the form. Microsoft made a mistake with the user interface here in my opinion,
lots of people make this very reasonable mistake!

Robin, if the data is not confidential, could you compact the database,
compress it with WinZip or equivalent, and email it to me at jvinson <at>
wysard of info <dot> com? You're clearly doing SOMETHING simple but incorrect,
but blamed if I can figure out what!

 

[R Tanner]

My Tab order starts out with the StartTime and then EndTime is right
after that.

The following code is also on the form.  Which should not be executed
until I hit the button.

Maybe I should just delete the form and start again..

Private Sub Save_Click()

DoCmd.Save acForm, Me.Name

End Sub- Hide quoted text -

- Show quoted text -

I hit the Cancel button and I got a message box saying access is
saving the record. This is where I have the code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
MsgBox "Access is saving the record!", vbOKOnly

End Sub

I also added the code to the Cancel_Click event to trigger a message
box saying 'Form is dirty', which never appeared.

 

[R Tanner]

My Tab order starts out with the StartTime and then EndTime is right
after that.

The following code is also on the form.  Which should not be executed
until I hit the button.

Maybe I should just delete the form and start again..

Private Sub Save_Click()

DoCmd.Save acForm, Me.Name

End Sub- Hide quoted text -

- Show quoted text -

and it appears as if it isn't just for the date that it is saving
it...cause I got the message box regardless of what I filled in - when
I hit the cancel button...

 

[R Tanner]

My Tab order starts out with the StartTime and then EndTime is right
after that.

The following code is also on the form.  Which should not be executed
until I hit the button.

Maybe I should just delete the form and start again..

Private Sub Save_Click()

DoCmd.Save acForm, Me.Name

End Sub- Hide quoted text -

- Show quoted text -

Seems like google groups are behaving weird...I posted alot of
messages yesterday but I didn't see any of them show up until I came
into work today...hmm

 

[R Tanner]

In any case, this code does NOT save the record - it saves design changesto
the form. Microsoft made a mistake with the user interface here in my opinion,
lots of people make this very reasonable mistake!

Robin, if the data is not confidential, could you compact the database,
compress it with WinZip or equivalent, and email it to me at jvinson <at>
wysard of info <dot> com? You're clearly doing SOMETHING simple but incorrect,
but blamed if I can figure out what!

I tried to send it to the following emails. It was a no go.

jvinson@stop_spam.wysardofinfo.com
(e-mail address removed)
(e-mail address removed)

Essentially what seems to be happening is whenever I exit form view
and try to go to design view, hit cancel, etc. the form_BeforeUpdate
procedure is executed...I have nothing calling that procedure. The
only thing I have in it is there is:

MsgBox "Access is saving the record!", vbOKOnly

How is that procedure possibly saving the record anyways? There is
nothing there indicating that it is...

 

[John W. Vinson]

I tried to send it to the following emails. It was a no go.

jvinson@stop_spam.wysardofinfo.com

Remove stop_spam. from the above.