Solved

How to use "DoCmd.GoToRecord , , acNewRec", when opening a form without automatically saving the record when closing it?

Posted on 2003-11-28
13
2,307 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:Lukasx
  • 6
  • 3
  • 2
  • +2
13 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 9837135
You have to use the Form_Beforeupdate and AfterUpdate procedures.

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
0
 
LVL 23

Expert Comment

by:heer2351
ID: 9837157
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
0
 
LVL 23

Expert Comment

by:heer2351
ID: 9837178
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.
0
 
LVL 2

Expert Comment

by:cocobingo
ID: 9837190
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
0
 
LVL 23

Expert Comment

by:heer2351
ID: 9837203
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
0
 

Author Comment

by:Lukasx
ID: 9837209
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
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 77

Expert Comment

by:peter57r
ID: 9837257
Yes.
It's easily fixable but the code from heer2351 will work just as well.
So switch to that.

Pete



0
 

Author Comment

by:Lukasx
ID: 9837396
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
0
 
LVL 23

Accepted Solution

by:
heer2351 earned 250 total points
ID: 9839572
That sounds easier than it is, I can add a Cancel button and even make it function like you want (code below). But there is one drawback, if you close the form and hit Cancel and the record is in reality saved. So closing the form again will not make the messagebox appear again. If you make changes in the meantime you will get the message box but if you hit no you will be stuck with the record that was saved when you hit cancel.

To really add a cancel button to the messagebox you have to remove the standard close button from the form and build your own close button. I would consider that to be a new question however since you did not mention anything about the cancel in your original question.

'---- Start Code

Private createNewRecord As Boolean
Private cancelRecord As Boolean

Private Sub Form_beforeupdate(Cancel As Integer)
  Dim result As Integer

  createNewRecord = False
  'Prompt the user to save
  result = MsgBox("Save this record.", vbYesNoCancel + vbQuestion, "Save record?")
  If result = vbCancel Then
    cancelRecord = True
  ElseIf result = vbNo Then
    Me.Undo
  ElseIf result = vbYes Then
    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 Or cancelRecord
  If createNewRecord = True Then
    DoCmd.GoToRecord , , acNewRec
  End If
  createNewRecord = False
  cancelRecord = False
End Sub
'--- End of Code
0
 
LVL 23

Expert Comment

by:heer2351
ID: 9845998
Lukasx would you mind explaining why you grade my answer with a B?
0
 

Author Comment

by:Lukasx
ID: 9849833
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:

http://www.experts-exchange.com/Community_Support/Q_20812593.html  

Thanks very much for your answer, it was very useful!

 I'm sorry I appeared so ungrateful!!
0
 
LVL 23

Expert Comment

by:heer2351
ID: 9851393
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 :)
0
 

Expert Comment

by:YensidMod
ID: 9852047
Asker asked that grade be changed to an A.

Yensidmod
EE Moderator
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now