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

 

[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)?

 

[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.

 

[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.

 

[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!

 

[debsamguru]

OK. I've solved it.

The fact that it was sometimes doing the delete and other times not was really doing my head in! I finally put a msgbox in with the errorcode from the dbFailOnError. This came back with a code of 128 (sometimes) so I put the following code in:

        sqlquery = "DELETE * FROM OrderHeaderT WHERE ID = " & POIDInput
        db.Execute sqlquery, dbFailOnError
        If dbFailOnError = 128 Then
            Me.Undo
        End If

This meant that if the record had not been committed yet, I could do an undo and it would work.

 

[The_Doc_Man]

OK. I've solved it.

The fact that it was sometimes doing the delete and other times not was really doing my head in! I finally put a msgbox in with the errorcode from the dbFailOnError. This came back with a code of 128 (sometimes) so I put the following code in:

        sqlquery = "DELETE * FROM OrderHeaderT WHERE ID = " & POIDInput
        db.Execute sqlquery, dbFailOnError
        If dbFailOnError = 128 Then
            Me.Undo
        End If

This meant that if the record had not been committed yet, I could do an undo and it would work.

Makes sense. Cannot delete what hasn't been saved yet.

 

[debsamguru]

I understand what you are saying Pat, but how can I process it in the BeforeUpdate event if it never goes into this event?

 

[isladogs]

@Galaxiom
Thanks for the transacted bound form example which I'd not seen before. Very clever!
Do you have any links to other attempts to get this working for a form & subform, even if they are unsuccessful.