Closing a Form and adding a Record

[PRD]

There has got to be a simple solution to this problem but I can’t seem to be able to find it…


I created a Form to add records to a Table. There are Command Buttons to Save the record and Close the Form. I also load half a dozen fields with default values using the OnCurrent Event Procedure. When the users save the record and then close the Form everything works fine. But if they go to add a record but then change their mind and close the Form the Form adds the partially created record.


I tried using the Form’s BeforeUpdate Event Procedure to check to see if the record is ‘dirty’ and then ‘undo’ it (which works) but my problem is I can’t then close the Form after its undone. I can‘t seem to be able to find a ‘Close Form’ command which will work inside the Event Procedure of the Form being closed.


So my question is, how do you prevent a Form from adding a record upon the closing of that Form?


Currently, I run a program after the Form is closed which reads through the entire Table deleting any ‘blank’ records but this is a pretty ugly solution. Any suggestions you have would be appreciated. Thx.

 

[John Big Booty]

Why not set the Form's Control Box property to False (No), this will mean that the only way to close the form is via your Close button which can then use the following code;

    If Me.Dirty = True Then
        If IsNull(Me.Filed1) Or IsNull(Me.Field2) Then   [COLOR="Green"]'You will need to adjust this to test for the default state of your fileds.[/COLOR]
            Me.Undo
        End If
    End If

 

[missinglinq]

You're trying to reinvent the way Access is intended to work, and this becomes a problem with experienced Access data input users. They know that Access saves records when moving to another record or when the form is closed, and expect this behavior. In my opinion, it's a better policy to allow Access to work in its native way and merely check with the user before the record is saved, allowing them to save it or dump the new record or changes. This piece of code will do just that

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not (Me.NewRecord) Then
 If MsgBox("Would You Like To Save The Changes To This Record?", vbQuestion + vbYesNo + vbDefaultButton1, "Save Changes to Record ???") = vbNo Then
  Me.Undo
 End If
Else
 If MsgBox("Would You Like To Save This New Record?", vbQuestion + vbYesNo + vbDefaultButton1, "Save This Record ???") = vbNo Then
  Me.Undo
 End If
End If
End Sub

You can still have your "Save" button, if some users expect it, but simply place your Close command in it, When pressed Access will go to the Form_BeforeUpdate event and the user can decode to save or dump the Record.

Linq ;0)>

 

[PRD]

OK, I don’t think I am making myself clear as I am doing almost exactly what you suggest with no luck. So let‘s start with the ‘Close‘ command button. In the OnClick property I run a Macro which consists only of the ‘Close‘ statement so naturally when I click on the command button the Form closes.

Next, I have added the following code to the Form’s BeforeUpdate Event Procedure…

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim ctl As Control

If Me.Dirty Then
If MsgBox("Do you want to save?", vbYesNo + vbQuestion, _
"Save Record") = vbNo Then
Me.Undo
End If
End If

So now I open the Form (which immediately becomes dirty because of the half dozen default fields). If I click on the ‘Close’ button a MsgBox correctly appears asking if I want to save the record. If I click ‘No’ the fields on the screen become blank (they are undone) but the Form remains open. If I click on the ‘Close’ button again nothing happens and the Form remains open. No matter how many times I click on the ‘Close’ button the Form remains open and in fact I have to actually end the Access program to close the Form. Its as if the program is stuck in a loop within the BeforeUpdate Event Procedure and never passes control back to the ‘Close’ button.

So I would like to code something like the following…

If Me.Dirty Then
If MsgBox("Do you want to save?", vbYesNo + vbQuestion, _
"Save Record") = vbNo Then
Me.Undo
Close Form
End If
Close Form
End If

But of course there is no such command as Close Form

So how do I pass control back to the ‘Close’ button after the BeforeUpdate Event Procedure is executed?

Will setting the Control Box to False accomplish this? Thx.

 

[John Big Booty]

What you are looking for (and was missing from my code ) is DoCmd.Close.

My code should have looked like;

    If Me.Dirty = True Then
        If IsNull(Me.Filed1) Or IsNull(Me.Field2) Then   'You will need to adjust this to test for the default state of your fileds.
            Me.Undo
        End If
    End If

DoCmd.Close

 

[John Big Booty]

Your Code would look like;

If Me.Dirty Then
     If MsgBox("Do you want to save?", vbYesNo + vbQuestion, "Save Record") = vbNo Then
          Me.Undo
     End If
End If

DoCmd.Close

 

[PRD]

Cooool! I knew there must be a 'simple' solution to this problem. Can't wait to try it out tomorrow when I go back to work. Thanks again!

 

[PRD]

I guess I should have been able to figure out the 'DoCmd.Close' statement myself but I am still a little new working with Access.

The only change I made was that I put the code into the 'OnClick' property of the Command Button instead of the 'BeforeUpdate' property of the Form - works like a charm. Thanks again for all of your help!