Record won't delete using db.execute

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

 

[Pat Hartman]

@debsamguru So, you are not going to fix the code so you don't need to do the delete? You are going to continue to use the wrong event which is what is causing the problem?

@Galaxiom It looks like I misunderstood your reference. I don't see a link to any example so I can't look at it.

 

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

 

[GK in the UK]

Here's an article about transactions in bound forms:

How to use transactions in Microsoft Access VBA - Codekabinett

I haven't tried it largely because of the warning against user input within a transaction.

 

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

It isn't my idea. I don't know who did it originally but I have since seen it mentioned with Leigh Purvis in a thread from 2009.
I came across it in this thread. ChrisO (RIP) had some criticisms of it there. Best pay attention when ChrisO has an opinion.

Spikel was looking for a solution here but nobody came up with anything.

I mentioned on one of the threads that I thought it should be possible but couldn't get it to work though I didn't really persist. I still hope it would be possible as it would a fantastic structure. Good luck if you want to give it a shot.

I don't really remember the problem I ran into but it was to do with moving to the subform. Maybe it was with the master record not being saved so the subform records couldn't be written. There would be ways around this using VBA to write the link fields. Or maybe it wouldn't let the focus shift to the subform while the mainform was Dirty.

 

[isladogs]

Thanks Greg & GK.
Potentially a very powerful tool but the various warnings are very clear.

 

[Pat Hartman]

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

Please post the code you have in the BeforeUpdate event. You have to cancel the event when you find an error. Here's an example

If Me.SomeDate & "" = "" Then
    Msgbox "Some date is required.", vbOKOnly
    Cancel = True
    Me.SomeDate.SetFocus
    Exit Sub
End If

If Me.SSN & "" = "" Then
    Msgbox "SSN is required",vbOKOnly
    Cancel = True
    Me.SSN.SetFocus
    Exit Sub
End If

'always check for new record but only check for existing record if current SSN is
'different from previous SSN
If Me.NewRecord OR Me.SSN <> Me.SSN.OldValue Then 
    If DCount("*", "tblEmployee","SSN = '" & Me.SSN & "'") > 0 Then
        Msgbox "SSN already exists for a different employee",vbOKOnly
        Cancel = True
        Me.SSN.SetFocus
        Exit Sub
    End If
End If

If the form is bound, the FORM's BeforeUpdate event CANNOT be bypassed. PERIOD. That is the way form events work.

 

[debsamguru]

Pat, all I have in my BeforeUpdate is a msgbox to show me when it is called. And sometimes it is called and sometimes it isn't and I can see no rhyme or reason as to when it is called. I can do the same action (open the form, then use the Close button to close it straight away) 3 times - once it will display the BeforeUpdate msg, the other 2 times, it won't. So all I can do is assume that it won't happen and make contingencies to delete the created records.

 

[Mike Krailo]

There has to be more than a msgbox in there or it won't fire. Pat showed you some examples how to do it.

 

[NauticalGent]

Best pay attention when ChrisO has an opinion.

 

[isladogs]

I haven't followed this thread in detail as you've already had a lot of good advice.
However, a couple of things that haven't been mentioned and may just be relevant?
1. In post #6, you showed Currency as " " - a single space. Try an empty string "" if you really need that value. Or omit it?
2. Why are you using variant for CustomerInputID. Use Number or Text as appropriate