Record won't delete using db.execute (1 Viewer)

debsamguru

Member
Local time
Today, 23:52
Joined
Oct 24, 2010
Messages
82
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:
Code:
        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

Super Moderator
Staff member
Local time
Today, 19:52
Joined
Feb 19, 2002
Messages
42,981
@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

Immoderate Moderator
Staff member
Local time
Today, 18:52
Joined
Feb 28, 2001
Messages
27,001
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:
Code:
        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

Member
Local time
Today, 23:52
Joined
Oct 24, 2010
Messages
82
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

MVP / VIP
Local time
Today, 23:52
Joined
Jan 14, 2017
Messages
18,186
@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.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 10:52
Joined
Jan 20, 2009
Messages
12,849
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

MVP / VIP
Local time
Today, 23:52
Joined
Jan 14, 2017
Messages
18,186
Thanks Greg & GK.
Potentially a very powerful tool but the various warnings are very clear.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:52
Joined
Feb 19, 2002
Messages
42,981
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
Code:
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

Member
Local time
Today, 23:52
Joined
Oct 24, 2010
Messages
82
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

Well-known member
Local time
Today, 19:52
Joined
Mar 28, 2020
Messages
1,030
There has to be more than a msgbox in there or it won't fire. Pat showed you some examples how to do it.
 

isladogs

MVP / VIP
Local time
Today, 23:52
Joined
Jan 14, 2017
Messages
18,186
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
 

Users who are viewing this thread

Top Bottom