simple cancel record I cant figure out

[buratti]

I need to cancel or delete the current NEW record from saving when closed, but only when certain criteria is being met. It seems simple enough where I should be able to figure out by myself, but I can't get it right to cancel under my certain circumstances. What I need is for... If my orders form is opened from the customers form, and no new data has been entered, then cancel/delete that order when the form is closed.
Background info...
The customers form passes an openargs value when opening the orders form to auto-fill customer data in that form. So a way to tell that the orders form has been opened form the customers form is If not isnull(me.openargs)... and I know the way to tell if the form has been modified is something like If me.dirty ...., but when I put them together in the onclose event, noting happens.

Private Sub Form_Close()

If Not IsNull(Me.OpenArgs) And Me.NewRecord Then
If msgbox("do you want to cancel",vbYesNo) Then
DoCmd.RunCommand acCmdUndo
End If
End If

I did some searching around these posts and read to put the code in the beforeupdate event, which works better, but if the user DOES want to continue entering the order, whenever they click in the subform of the orders form (btw, which is where the first field they would be entering data in after the form is opened from the customers form), the cancel/delete prompt displays. I cannot figure out how to cancel the entry only under those circumstances listed above. Can anyone help me?

 

[vbaInet]

maybe change the logic of the way you handle new records. i am presuming that when the orders form is opened from the customers it automatically goes to a new record? if that's the case you could have a "New Record" button instead and use that for going to a New Record. does this help?

 

[boblarson]

By the time the form hits the Close event it is way past the time. You need to hit the form's BEFORE UPDATE event to do your testing and then discard at that time if you so desire.

 

[buratti]

Technically speaking, yes, when opened from the customers form, the orders form goes to a new record, HOWEVER, as stated above, it also passes an openargs value of the customer account number and enters it in the appropiate field. That account number field in the orders form lookups that customer information to start a new order for that customer, hence entering/displaying customer data for that order. Having just a "New Record" button would defeat the purpose of auto-filling customer data directly from the customers form (which I spent a lot of time trying to figure out how to do that) Thanks for that suggestion though, anything else???

 

[vbaInet]

Just fyi OpenArgs is not a control so evaluating for a null string is unlikely because I believe it is automatically initialised with the Empty string when the form is loaded. And like boblarson rightly pointed out you that event has already fired so you will need to find and delete than newly created record

So what you would do is this:

Private Sub Form_Close()
 
If Me.OpenArgs <> "" Then
         ' Sort the controls record source in descending order by ID
    Me.OrderBy = "OrderID DESC"
    ' Move to the control that has the order id
    OrderID.SetFocus
    ' Call the delete function
    Docmd.RunCommand DeleteRecord
End If

Try this

 

[vbaInet]

Just fyi OpenArgs is not a control so evaluating for a null string is unlikely because I believe it is automatically initialised with the Empty string when the form is loaded. And like boblarson rightly pointed out you that event has already fired so you will need to find and delete than newly created record

So what you would do is this:

Private Sub Form_Close()
 
If Me.OpenArgs <> "" Then
         ' Sort the controls record source in descending order by ID
    Me.OrderBy = "OrderID DESC"
    ' Move to the control that has the order id
    OrderID.SetFocus
    ' Call the delete function
    Docmd.RunCommand DeleteRecord
End If

Try this

Forgot to mention, before you run the delete command, refresh and requery your form (i.e. Me.Requery, Me.Refresh) and after you run the delete command remember to reorder your form (i.e. Me.OrderBy = "OrderID ASC")

 

[dcb]

DoCmd.RunCommand acCmdUndo

 

[buratti]

Thanks for that suggestion. I'm going to retire on working on this DB for the evening, but will try it out tomorrow and let you know how it works. in the mean time I did figure something out. It's not the prettiest, but it works for what I need. I disabled the close button (x) and made my own close button on the form and added my specific criteria to that and it seems to be working. My code is similar to what I had before, so dont really understand why it wasn't working in the beforeupdate event, but I'm not gonna question it. Will try your simpler code tomorrow. Thanks!!!

 

[vbaInet]

no worries. here's a tested code that works. I've included all the things mentioned and also added two more lines so you don't get the built-in prompt

    If Me.OpenArgs <> "" Then
        ' Sort the controls record source in descending order by ID
        Me.OrderBy = "OrderID DESC"
        DoCmd.GoToRecord , , acLast
        Me.Requery
        Me.Refresh
        ' Move to the control that has the order id
        OrderID.SetFocus
        ' Call the delete function
        DoCmd.SetWarnings False
        DoCmd.RunCommand acCmdDeleteRecord
        DoCmd.SetWarnings True
        Me.OrderBy = "OrderID ASC"
        Me.Requery
        Me.Refresh
        ' Go to first record (You can also make it go to the record the user was on too, work it out!)
        DoCmd.GoToRecord , , acFirst
    End If

good night

 

[dcb]

no worries. here's a tested code that works. I've included all the things mentioned and also added two more lines so you don't get the built-in prompt

    If Me.OpenArgs <> "" Then
        ' Sort the controls record source in descending order by ID
        Me.OrderBy = "OrderID DESC"
        ' Move to the control that has the order id
        Me.Requery
        Me.Refresh
        DoCmd.GoToRecord , , acLast
        OrderID.SetFocus
        ' Call the delete function
        DoCmd.SetWarnings False
        DoCmd.RunCommand acCmdDeleteRecord
        DoCmd.SetWarnings True
        Me.OrderBy = "OrderID ASC"
        ' Go to first record (You can also make it go to the record the user was on too, work it out!)
        DoCmd.GoToRecord , , acFirst
    End If

good night

If you are on a record and run
DoCmd.RunCommand acCmdUndo
It will delete if it has saved it and disgard it if it hasn't
The openargs has nothing to do with it!

 

[vbaInet]

If you are on a record and run
DoCmd.RunCommand acCmdUndo
It will delete if it has saved it and disgard it if it hasn't
The openargs has nothing to do with it!

Absolutely, then you could call the Requery and Refresh functions afterwards. I'm used to unbound controls so I tend to code everything so I have greater flexibility.

By the way, in author's previous first few messages you will find that he mentioned he's using openargs on the On Load event to create a new record. so if he wasn't opening the Orders form via customers form then he wouldn't need to run th code.

 

[dcb]

Absolutely, then you could call the Requery and Refresh functions afterwards. I'm used to unbound controls so I tend to code everything so I have greater flexibility.

By the way, in author's previous first few messages you will find that he mentioned he's using openargs on the On Load event to create a new record. so if he wasn't opening the Orders form via customers form then he wouldn't need to run th code.

My understanding of the OP was that he was using open args to add data to the formn on open - This would be why he is automatically saving the record on close
Shouldnt need requery

 

[vbaInet]

My understanding of the OP was that he was using open args to add data to the formn on open - This would be why he is automatically saving the record on close
Shouldnt need requery

You're right (again) dcb. I've just re-read his opening remarks. How do you suggest he handles this? I would say declare a boolean variable and initialise it to false on load of Orders form. Change the value to True on the AfterUpdate event and set it to False for anytime the Undo function is called or when a Delete operation is called. Then check against that variable. Good idea?

 

[dcb]

By the time the form hits the Close event it is way past the time. You need to hit the form's BEFORE UPDATE event to do your testing and then discard at that time if you so desire.

Theres your answer

Before update event
Test each box for nulls, zero length strings etc
If test fails (no all data filled in) Then
ask user if they want to discard
based on msgbox rslt then follow action

 

[vbaInet]

Theres your answer

Before update event
Test each box for nulls, zero length strings etc
If test fails (no all data filled in) Then
ask user if they want to discard
based on msgbox rslt then follow action

Wouldn't that cause the event to be fired and the message box displayed for everytime you open the form via the customer's table?

 

[boblarson]

Theres your answer

Before update event
Test each box for nulls, zero length strings etc
If test fails (no all data filled in) Then
ask user if they want to discard
based on msgbox rslt then follow action

Yep, you would see if all is filled out, and issue a

Cancel = True

if not and then a message box with a question of whether they want to continue with this record or not and if not,

Me.Undo

 

[vbaInet]

But that's not the poster's requirements. From my understanding he wants to handle this when the form is closed not when it's opened.

 

[boblarson]

But that's not the poster's requirements. From my understanding he wants to handle this when the form is closed not when it's opened.

When the form closes, if there is a dirty record, then you would still use the form's Before Update to validate and either save or discard. The Before Update event will come before the close event.

 

[vbaInet]

Which is why in this case you ignore the BeforeUpdate event and look for a workaround that will identify if a record was added or not after it was saved. I think at this point the poster needs to comment on his needs and clarify further.