backing out of a new record on a user form. 1

[scroce]

Any help, specific or general would be appreciated.

I've been wrestling with this for a while now, I think it's because I don't think I understand what's happening fundamentally when a user creates a new record.

Problem explanation:
-I have a heavily coded form that contains customer data.
-Each customer has a primary key ID field.
-There are several other foreign keys on the field (like orders) that can't be null if there's a customer record
-User goes into a new record.
-User starts filling out form.
-User regrets it and wants to bail out.
-User somehow forgets to input primary key field, or one of the other fields that are required due to relationships.
-User can't back out of the record at all, because the form keeps throwing back integrity errors.
-User can hit "escape" 3 or 4 times to cancel the new record, but this is non-intuitive and impractical

I want to code the cmdPreviousRecord to ask the user if they wish to cancel the new record, and if so, to get rid of the incomplete record (which may be missing key fields) and go back to the last record in the recordset.


This code does not work (i may have commented out some of the if's incorrectly, but I think you get the idea):

Private Sub cmdPrevious_Click()
On Error GoTo Err_cmdPrevious_Click
On Error Resume Next
If Me.NewRecord = True Then
   If Me.txtMemberID.Value = "" Or IsNull(Me.txtMemberID.Value) Then
     If MsgBox("You want out?", vbYesNo, "Get outta here") = vbYes Then
        'this is not the way to do it,  but hey, I tried. 
       SendKeys "{ESC}"
       SendKeys "{ESC}"
       'This doesn't work at all, even when the new record gets cancelled successfully - Why not?:
       DoCmd.GoToRecord , , acPrevious
     End If
   Else
     DoCmd.GoToRecord , , acPrevious
   End If
 Else
  DoCmd.GoToRecord , , acPrevious
End If

 '   DoCmd.GoToRecord , , acPrevious

Exit_cmdPrevious_Click:
    Exit Sub

Err_cmdPrevious_Click:
    MsgBox Err.Description
    Resume Exit_cmdPrevious_Click
    
End Sub

Ah say, there's somethin' a little "eeeeeeee" 'bout a boy who don't like basbawl...

 

[RoyVidar]

Hi!

Try
Me.Undo in stead of your sending esc-key
(perhaps a couple of lines of it) If the gotoprevious doesn't work, try last or first or something

HTH Roy-Vidar

 

[scroce]

I tried me.undo unsuccessfully once or twice - I didn't throroughly check it out. Since you've suggested it, I'll work on that a little more

- but I did try RecordsGoToFirst/Last - it wouldn't let me do that either - It either does nothing at all or says "You can't go to the specified record" error.

I also tried RecordsetClone to no avail.

Also - what's the difference between using DoCmd.NextRecord and DoCmd.RunCommand.acCmdRecordsGotoNext? (i know, bad syntax, but again, you probably get the general drift of the question...)

Ah say, there's somethin' a little "eeeeeeee" 'bout a boy who don't like basbawl...

 

[RoyVidar]

Hi again!

Haven't the foggiest about the difference between the to mentioned methods.

Did a litle testing for myself:

if me.newrecord then
if msgbox("Save changes before exit?",vbyesno + vbquestion,"Save changes?&quot=vbyes then me.undo
endif
docmd.gotorecord,,acprevious

This worked like charm.

I'm guessing you might have other events/thingies firing off and making your Prev-routine not working (I've experienced some of that). For instance something in the on current event of the form...

If this doesen't work I'm afraid I can't help.

Roy-Vidar

 

[xtraterrestrial]

I just tried using me.undo and it works fine for me too. I wish I had found this thread earlier.

scroce, if you still can't get it to work, what I previously did was fill the record with dummy values and immediately delete the record afterwards. That seems to work.

 

[scroce]

yes - you have both really pinpointed my issue. It's good to know that I'm not the only one with this seemingly simple, yet deceptively difficult issue.

Roy - your guess is right, I have a lot of other code running on the form that's getting messed up - In fact I have a lot of things happening with Form_onCurrent

I've been basically fooling with varations of your example
for a while now - with limited success.

and Xtra - thank you for that bit of advice as well - I had thought of that and tried unsuccessfully as well, but I didn't know if it was a good way to do things - now that I know that others have done it this way, it gives me a little more fortitude to try again.

As long as I know that one of these two methods is most likely the way to go and I'm not missing something larger or more obvious, then I guess I'll be OK.

I am starting to get some results using the Undo command.


Ah say, there's somethin' a little "eeeeeeee" 'bout a boy who don't like basbawl...

 

[RoyVidar]

Hi again!

Should have been vbNo at the end of my IF statement.

You might watch for statements in your on current event that:
* assign values to some fields/controls
* "does" something to fields/controls that might invoke Default Value settings either on the control or the table

If you find any of those (or in fact for most of the content of on current) you could "eliminiate" them by putting them within a "IF not me.newrecord then..." or declare a form module level boolean variable you set to false only when invoking the cmdPrevious, and use that variable to skip unvanted code excecution.

Roy-Vidar

 

[scroce]

RoyVidar,

I think the crux of this problem was the "Undo" feature. Strategic use of that little command solved a good chunk of this.

Many thanks to all who replied. Sometimes the tinyest gap in coding knowledge really can crunch a person.

Ah say, there's somethin' a little "eeeeeeee" 'bout a boy who don't like basbawl...