Lukasx
asked on
How to use "DoCmd.GoToRecord , , acNewRec", when opening a form without automatically saving the record when closing it?
Hi folks,
This seems like a really easy one, but it's got me stuck!
I am using the "DoCmd.GoToRecord , , acNewRec" command on a command button to open a form with blank fields ready to create a new record.
However, I'm finding that when I close the form, the record is saved automatically!!
Can someone give me a way of forcing the user to decide whether to save the record or not when leaving the form.
If they select no, the form closes without saving the record and opens the main form.
If they do select save, ideally another msg box should ask if they want to create another record.
If yes, the form fileds are reset in preparation for a new record to be entered.
If no, then the form closes without saving the record and opens the main form.
Cheers for your help!
Luke
This seems like a really easy one, but it's got me stuck!
I am using the "DoCmd.GoToRecord , , acNewRec" command on a command button to open a form with blank fields ready to create a new record.
However, I'm finding that when I close the form, the record is saved automatically!!
Can someone give me a way of forcing the user to decide whether to save the record or not when leaving the form.
If they select no, the form closes without saving the record and opens the main form.
If they do select save, ideally another msg box should ask if they want to create another record.
If yes, the form fileds are reset in preparation for a new record to be entered.
If no, then the form closes without saving the record and opens the main form.
Cheers for your help!
Luke
This is how I would do it:
You need an beforeUpdate event and an unLoad event. Do not forget the stopClose variable defined in the declaration section of the form module:
'---- Start Code
Private stopClose As Boolean
Private Sub Form_beforeupdate(Cancel As Integer)
stopClose = False
'Prompt the user to save
If MsgBox("Save this record.", vbYesNo + vbQuestion, "Save record?") = vbNo Then
Me.Undo
If MsgBox("Do you want to create a new record?", vbYesNo + vbQuestion, "New record?") = vbYes Then
stopClose = True
End If
End If
End Sub
Private Sub Form_Unload(Cancel As Integer)
Cancel = stopClose
stopClose = False
End Sub
'--- End of Code
You need an beforeUpdate event and an unLoad event. Do not forget the stopClose variable defined in the declaration section of the form module:
'---- Start Code
Private stopClose As Boolean
Private Sub Form_beforeupdate(Cancel As Integer)
stopClose = False
'Prompt the user to save
If MsgBox("Save this record.", vbYesNo + vbQuestion, "Save record?") = vbNo Then
Me.Undo
If MsgBox("Do you want to create a new record?", vbYesNo + vbQuestion, "New record?") = vbYes Then
stopClose = True
End If
End If
End Sub
Private Sub Form_Unload(Cancel As Integer)
Cancel = stopClose
stopClose = False
End Sub
'--- End of Code
Sorry Pete did not see your comment before I posted mine, but I do get some error with your solution.
Lukasx misread your question about the prompt for another record, I thought you wanted this prompt when the user selected no. I will update the code.
Lukasx misread your question about the prompt for another record, I thought you wanted this prompt when the user selected no. I will update the code.
Hi!
To delete the current record, you can use :
Me.RecordSet.Delete
I guess you would put this in the form's OnClose event. That would delete the current record by default.
You could then create two buttons on your form :
- Close without saving :
Me.RecordSet.Delete
DoCmd.Close acForm, Me.Name
- Save and Close :
DoCmd.Close acForm, Me.Name
I am not sure that the form's OnClose event would work though. Maybe the form would be closed before the call the Delete.
Arnaud
To delete the current record, you can use :
Me.RecordSet.Delete
I guess you would put this in the form's OnClose event. That would delete the current record by default.
You could then create two buttons on your form :
- Close without saving :
Me.RecordSet.Delete
DoCmd.Close acForm, Me.Name
- Save and Close :
DoCmd.Close acForm, Me.Name
I am not sure that the form's OnClose event would work though. Maybe the form would be closed before the call the Delete.
Arnaud
Updated code:
'---- Start Code
Private createNewRecord As Boolean
Private Sub Form_beforeupdate(Cancel As Integer)
createNewRecord = False
'Prompt the user to save
If MsgBox("Save this record.", vbYesNo + vbQuestion, "Save record?") = vbNo Then
Me.Undo
Else
If MsgBox("Do you want to create a new record?", vbYesNo + vbQuestion, "New record?") = vbYes Then
createNewRecord = True
End If
End If
End Sub
Private Sub Form_Unload(Cancel As Integer)
Cancel = createNewRecord
If createNewRecord = True Then
DoCmd.GoToRecord , , acNewRec
End If
createNewRecord = False
End Sub
'--- End of Code
'---- Start Code
Private createNewRecord As Boolean
Private Sub Form_beforeupdate(Cancel As Integer)
createNewRecord = False
'Prompt the user to save
If MsgBox("Save this record.", vbYesNo + vbQuestion, "Save record?") = vbNo Then
Me.Undo
Else
If MsgBox("Do you want to create a new record?", vbYesNo + vbQuestion, "New record?") = vbYes Then
createNewRecord = True
End If
End If
End Sub
Private Sub Form_Unload(Cancel As Integer)
Cancel = createNewRecord
If createNewRecord = True Then
DoCmd.GoToRecord , , acNewRec
End If
createNewRecord = False
End Sub
'--- End of Code
ASKER
Hi Peter!
Thank for your reply!
The code mostly works, but if I close the form without saving, I get the following error message:
Run-Time error '2501':
The Close action was canceled.
The Debugger points me to this line:- DoCmd.Close acForm, Me.Name
Thank for your reply!
The code mostly works, but if I close the form without saving, I get the following error message:
Run-Time error '2501':
The Close action was canceled.
The Debugger points me to this line:- DoCmd.Close acForm, Me.Name
Yes.
It's easily fixable but the code from heer2351 will work just as well.
So switch to that.
Pete
It's easily fixable but the code from heer2351 will work just as well.
So switch to that.
Pete
ASKER
Hi heer!
Thanks for your response, I think I'll go with your solution then!
Can you just tell me how I might add a Cancel button to the first "Do you want to save" prompt, which will undo the closing of the form and take the user back to the record they were working on if they change their mind about saving or closing the form.
Then the points are yours!!!
Cheers,
Luke
Thanks for your response, I think I'll go with your solution then!
Can you just tell me how I might add a Cancel button to the first "Do you want to save" prompt, which will undo the closing of the form and take the user back to the record they were working on if they change their mind about saving or closing the form.
Then the points are yours!!!
Cheers,
Luke
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Lukasx would you mind explaining why you grade my answer with a B?
ASKER
My Apologies!
I have just now read up on the grading system and it's significance. I haven't been using this forum for very long and was unaware of the criteria for grading until this point in time.
I've also read up on the 'How to change an awarded grade' section, so I'm going to up your grade to an 'A'!
Oops! Sorry about that! At least from now on I'll be giving appropriate grades :-)
Here is the Community Support question that I have posted to effect the change:
https://www.experts-exchange.com/questions/20812593/Please-upgrade-the-grade-awarded-to-an-'A'-for-the-following-question.html
Thanks very much for your answer, it was very useful!
I'm sorry I appeared so ungrateful!!
I have just now read up on the grading system and it's significance. I haven't been using this forum for very long and was unaware of the criteria for grading until this point in time.
I've also read up on the 'How to change an awarded grade' section, so I'm going to up your grade to an 'A'!
Oops! Sorry about that! At least from now on I'll be giving appropriate grades :-)
Here is the Community Support question that I have posted to effect the change:
https://www.experts-exchange.com/questions/20812593/Please-upgrade-the-grade-awarded-to-an-'A'-for-the-following-question.html
Thanks very much for your answer, it was very useful!
I'm sorry I appeared so ungrateful!!
Thanks and no offense taken. I am just always curious if there is a problem with my answer when I get less than an A, while IMHO the answer is A-worthy :)
Asker asked that grade be changed to an A.
Yensidmod
EE Moderator
Yensidmod
EE Moderator
Code is below. Put the name of your 'main form' where necessary.
Pete
Sub Form_beforeupdate(Cancel As Integer)
Dim resp
resp = MsgBox("Do you wish to save the record?", vbYesNo + vbQuestion, "Form Close")
If resp = vbNo Then
Cancel = True
DoCmd.OpenForm "the mainform"
DoCmd.Close acForm, Me.Name
Else
End If
End Sub
Sub Form_Afterupdate()
Dim resp
resp = MsgBox("Do you wish to create another record?", vbYesNo + vbQuestion, "Record Saved")
If resp = vbNo Then
DoCmd.OpenForm "the mainform"
DoCmd.Close acForm, Me.Name
Else
DoCmd.GoToRecord , , acNewRec
End If
End Sub