Record won't delete using db.execute

[debsamguru]

Hi,

I have a form NewOrderF which is called as follows:-

Private Sub NewOrderBtn_Click()
On Error GoTo Error_NewOrderBtn_Click
    Dim CustomerInputID As Variant
            
    CustomerInputID = Me.CustomerID
    DoCmd.OpenForm "NewOrderF", , , , , , CustomerInputID
    Exit Sub
    
Error_NewOrderBtn_Click:
    MsgBox "Error Number: " & Err.Number & " Description : " & Err.Description, , "Error - CustomersF - NewOrderBtn"
    Exit Sub

End Sub

A new order header record is created in the OrderHeaderT on opening the NewOrderF. I have a button, Close, which should delete anything that has been created if clicked. I have 'On Dirty' fields set up but if I simply click on the Close button, I would expect the order header record to get deleted but it doesn't. Here is my code - what am I doing wrong?

Private Sub Close_Click()
On Error GoTo Error_Close_Click

    Dim POIDInput As Variant
    Dim OrderLinesCount As Integer
    Dim Response As Integer
    Dim query As String
    Dim db As dao.Database
    Dim rs As dao.Recordset
    
    Set db = CurrentDb

    POIDInput = Me.ID
    MsgBox "POID = " & POIDInput
    
    If Me.SaveBtn.Enabled = True Then
        If msaved = True Then
            DoCmd.Close
            Exit Sub
        End If
    Else
        'Nothing has been entered at all - delete the inserted header record'

        query = "DELETE * FROM OrderHeaderT WHERE ID = " & POIDInput
        db.Execute (query), dbFailOnError
        Set db = Nothing
        DoCmd.Close
        msaved = False
        Exit Sub
    End If
    
    If POIDInput <> "" Or Me.CustomerID <> "" Then
        Response = MsgBox("This will delete the whole Order. Do you want to cancel this Order?", vbYesNo, "Cancel Order")
        If Response = vbYes Then
            query = "SELECT * FROM OrderItemsT WHERE POID = " & POIDInput
        
            Set rs = CurrentDb.OpenRecordset(query)
        
            If Not rs.BOF And Not rs.EOF Then
                rs.MoveFirst
                While (Not rs.EOF)
                    rs.Delete
                    rs.MoveNext
                Wend
            End If
        
            rs.Close
    
            Set rs = Nothing

            query = "DELETE * FROM OrderHeaderT WHERE ID = " & POIDInput
            db.Execute (query), dbFailOnError
            MsgBox "Order Cancelled", , "Cancel Order"
            Set db = Nothing
            DoCmd.Close
            msaved = False
        End If
    Else
        DoCmd.Close
    End If
    Exit Sub
    
Error_Close_Click:
    MsgBox "Error Number: " & Err.Number & " Description : " & Err.Description, , "Error - NewOrderF - Close"
    Exit Sub
            
End Sub

All I want to do is be able to clean up the records if the order is cancelled.

Thanks

 

[theDBguy]

Have you tried stepping through the code? Are you getting any error messages?

 

[GinaWhipp]

Hmm, instead of *query* try MyQuery and lose the parenthesis', so....

Dim MyQuery As String

db.Execute MyQuery, dbFailOnError

 

[Pat Hartman]

You should NOT allow empty or invalid records to be saved - EVER. Access makes it a mission to ensure that all data that is entered in a form gets saved. YOUR mission is to stop Access from saving empty or invalid records. The only way to do this reliably is to use the form's BeforeUpdate event. This is the last event that runs before a record is saved and it can never be bypassed. Think of this event as a flapper at the bottom of a funnel. If the flapper is open, the record goes through and gets saved. If the flapper is closed, the save is prevented.

You can define required fields at the table level but other validation is more easily done in the form. For example, if you are entering a new order the delivery date must be >= today's date. And the OrderDT should default to Date(). If you allow it to be entered, then it must be <= today's date. And, you might want outside ranges to prevent typos like 3/15/222. Access thinks this is a valid date but it sure isn't rational. You can't prevent totally wrong data from being entered if it is rational but we do what we can do.

The only reason you might want to delete a record in this situation is if the user never entered child records and your business logic requires them. In any event, you can't use your button code to trap these actions. You really need to use the internal form events. You can have a save button that causes the record to be saved but the validation should happen in the form's BeforeUpdate event. Or you can have a Close button that causes the form to be closed but the close validation happens in the Close event. The reason for this is that you will drive yourself and the user crazy if you try to defeat Access at its own game. Better to use Access to gracefully do what you want it to do. You have control over what happens but it is only easy if you understand which events to use for what purposes. When working with a RAD tool like Access, "go along to get along" should be your motto.

You can define TempVars or global variables that are set to true when the user pushes one of your buttons. That allows you to distinguish the user requesting the action from Access requesting the action so you can customize the messages you raise. You should also avoid gratuitous warning messages. If you have a save button, think of it as comfort food rather than a rule that needs to be enforced. Users sometimes feel better if they push a button to save but Access doesn't require it. I normally don't use save buttons on forms unless I want the user to be able to save the record but not leave it for some reason. Maybe he needs to see a generated CustomID or dollar totals.

Bottom line, you can do what you want but just move the code to more appropriate events and be sure to add validation code to prevent empty/invalid data from being saved.

 

[The_Doc_Man]

            db.Execute (query), dbFailOnError

You have gotten some excellent advice. Gina's answer is the "WHY" and the other answers are the "what to do differently."

 

[debsamguru]

Pat, I agree completely with your comments and it frustrates me that the record is being created at all. I want to know why. I put a msgbox into 'BeforeInsert' and stepped through the code.

Private Sub Form_Load()
    Dim CustomerInputID As Variant
    
    Me.Currency.Value = " "
    If Len(Me.OpenArgs) > 0 Then
        CustomerInputID = Me.OpenArgs
        Me.CustomerID.Value = CustomerInputID
        Me.CurrencyType = Me.CustomerCombo.Column(3)
    End If
    
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
    MsgBox "beforeinsert"
End Sub

It went to Me.Currency.Value = " " and then straight into BeforeInsert. so something is making it create the record at that instant. I would prefer it to wait until I move into the subform to enter the items.

 

[Minty]

Not Before Insert.
In the Forms Before Update event.

This is the one that will allow you to block saving a record.

 

[debsamguru]

Thanks Minty. I appreciate this BUT the record is inserted immediately the form opens. So even if I do nothing on the form but close out, I need to delete the inserted record.

Also, when I add a new stock item using a similar form, this doesn't happen. The only difference I can see is that the NewOrderF has a subform.

 

[debsamguru]

Update: I have copied the NewOrderF form and taken out the subform but it still creates the record on the OrderHeaderT. When I add a new stock item, it goes through the same process of inserting into the StockItemT but the record doesn't show on the table until the record is committed.

The only difference I can see is the the NewStockItemF form's data source is the StockItemT whilst the NewOrderF form's data source is a SELECT

SELECT OrderHeaderT.*, CustomersT.CustomerCode
FROM CustomersT INNER JOIN OrderHeaderT ON CustomersT.CustomerID = OrderHeaderT.CustomerID;

 

[Mike Krailo]

Thanks Minty. I appreciate this BUT the record is inserted immediately the form opens. So even if I do nothing on the form but close out, I need to delete the inserted record.

Well don't do that then. That was the whole point Pat was trying to make.

 

[The_Doc_Man]

Trying to lighten the moment, ...

Patient: Doctor, when I bang my head against the wall it hurts a lot.
Doctor: Then stop banging your head against the wall.

Sorry, couldn't pass that up. BUT, back to being serious, let me ask a more constructive question.

Do you think you needed to create a new record immediately when the form opens? You commented thus:

It went to Me.Currency.Value = " " and then straight into BeforeInsert. so something is making it create the record at that instant.

Me.xxx is always a form property or form control. In your case, it is a data-bearing control. It is at that instant that you dirtied the form, and Access is designed to capture data. At that instant it HAS to have somewhere to put the data you started to insert by updating Me.Currency.Value - which, by the way, can be shortened to Me.Currency, because .Value is the default property for ALL controls that have a value. So the moment you updated a value on the form, you forced it to act towards saving whatever you were going to change by creating a place to PUT that new data - even though it was just a single space character. It still counts as something that wasn't there before. Putting a default value as a field property is the most common mistake for this specific problem of getting a new record prematurely. Pre-erasing fields in VBA is another good way to trigger the same effect.

 

[debsamguru]

Thank you The_Doc_Man, that makes perfect sense and explains many things.

So, I call the form using an argument, which by your logic, effectively fills in the CustomerCode and creates the record. I then bring the Currency Code in for that Customer. I set the default value to "" because the form can be called from 2 places with 2 different arguments (via the customer or via the supplier - if coming from the supplier, I need the field to be blank).

What you are saying is that whatever I do, because I fill in these fields, the record will always be created and therefore will always need to be deleted.

Leading on from that ... I have 2 questions:
1. What is a better way to do this?
2. If I carry on with this and accept that I need to delete this record, why is my DELETE not working (which was my initial question)?

 

[Pat Hartman]

The Load event is the wrong event to use because YOU are forcing the record to be created and that is not what you want to do. So, what Is the correct event? To add data to a record being created, I use the BeforeInsert event. That event doesn't run until the user has typed the first character which has dirtied the record. So --

    If Len(Me.OpenArgs) > 0 Then
        Me.CustomerID = Me.OpenArgs
    End If

Is all you need.

You should NOT be saving the Currency type. That duplicates data available from the combo. If this might change over time, then it is OK to save it here but otherwise, you can show it using an unbound control with this as its ControlSource. You should not duplicate data unnecessarily
= CustomerCombo.Column(3)

 

[The_Doc_Man]

2. If I carry on with this and accept that I need to delete this record, why is my DELETE not working (which was my initial question)?

    POIDInput = Me.ID
    MsgBox "POID = " & POIDInput

...

        query = "DELETE * FROM OrderHeaderT WHERE ID = " & POIDInput
        db.Execute query, dbFailOnError

At this point in your code, if you put in a breakpoint ON or BEFORE the db.Execute and do a Debug.Print of query, I would surmise that the value of POIDInput somehow isn't right? BUT that dbFailOnError syntax should trigger an error message if something is wrong - OTHER THAN that the record to be deleted doesn't exist, because if Access goes to delete a record and it doesn't exist, it doesn't complain. After all, you wanted the record gone and it IS gone, isn't it?

Since I didn't see you mention a specific error, my question becomes, DO you get an error? You have some MSGBOX waypoints to let you know what is happening. I noticed that you have TWO code snippets that would try to delete the record using that same query text but in different places. Can you tell us which one fails?

 

[Galaxiom]

While not actually answering your question, you might like to take a look at using a Transacted Bound Form for the items. Multiple records can be created in the form but none are written to the table until a Save is triggered.
I have posted an example in post #9 of this thread. There are other examples.

BTW A few us us have tried to come up with a way to use the technique for both the main form and subform but I don't know of anyone who has succeeded.

 

[Pat Hartman]

Using an unbound form is not necessary. What is necessary is to understand how bound forms work so you understand what event your code needs to be in to work effectively. There is nothing in the OP's request that even hints at the need for an unbound form. The OP's problem is not understanding how bound forms work. Remember, we are working with Access not C or some other non-RAD tool.

 

[Galaxiom]

Using an unbound form is not necessary. There is nothing in the OP's request that even hints at the need for an unbound form.

Agreed but your mention of them is the only reference to unbound forms in this whole thread.

 

[Pat Hartman]

No, I suggested showing the second lookup field in an unbound CONTROL rather than copying data and saving it twice.

The current code includes ---
Me.CurrencyType = Me.CustomerCombo.Column(3)
which is duplicating CurrencyType possibly unnecessarily.

 

[Galaxiom]

No, I suggested showing the second lookup field in an unbound CONTROL

Indeed. I just don't understand why you introduced any reference unbound forms in post #16 at all, even to disparage them when nobody else at all had even implied they might be part of a solution.

As you said yourself:

There is nothing in the OP's request that even hints at the need for an unbound form.

Why did you even mention them?

 

[debsamguru]

Thank you all for your replies. To answer some questions:
1. The Currency Type is shown and available for update is because the currency CAN change for a specific order. The Customer's default currency is shown as the default, but that can be changed, and therefore needs to be tied to the order.
2. I have put msgbox waypoints in to show that my DELETE statement is correct but it still doesn't work.

The stupid thing is that this DID work! As far as I know, I was just tidying up after myself by adding the error catching code. Now none of the DELETES are working! I will go back to a previous version and see if I've done something different!