Error trap for update query (1 Viewer)

tmyers

Well-known member
Local time
Today, 08:30
Joined
Sep 8, 2020
Messages
1,090
I am trying to do the following code:
Code:
Private Sub GPTxtbx_AfterUpdate()

Dim strsql

On Err GoTo errhandler

    strsql = " UPDATE tblProduct SET tblProduct.GP = " & Me.GPTxtbx & ""
    strsql = strsql & " WHERE (((tblProduct.JobID)=[Forms]![JobQuote]![JobID]));"
    
    DoCmd.SetWarnings False
    DoCmd.RunSQL strsql
    DoCmd.SetWarnings True
    DoCmd.RefreshRecord
    
errhandler:
    If IsNull(strsql) Then
    Exit Sub
    End If

End Sub

I am just using the textbox to mass update fields. When you delete the value out of the textbox causing a null, the sql throws a syntax error. That is what I was attempting to trap, but it isn't quite working. I believe its because strsql isn't technically null, the textbox is which is what is causing the issue (although I thought initially it would just update all fields to null).

How can I properly trap this error?
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:30
Joined
Sep 21, 2011
Messages
14,051
Use the NZ() function?, or better still, test the control before trying to use it?
 

tmyers

Well-known member
Local time
Today, 08:30
Joined
Sep 8, 2020
Messages
1,090
Use the NZ() function?, or better still, test the control before trying to use it?
It actually dawned on me moments after posting (sometimes typing things out helps me solve issues).
I just encased the code in an IF statement and it solved it.

Code:
Private Sub GPTxtbx_AfterUpdate()

Dim strsql

If IsNull(Me.GPTxtbx) Then
    Exit Sub
    Else

        strsql = " UPDATE tblProduct SET tblProduct.GP = " & Me.GPTxtbx.Value & ""
        strsql = strsql & " WHERE (((tblProduct.JobID)=[Forms]![JobQuote]![JobID]));"
    
        DoCmd.SetWarnings False
        DoCmd.RunSQL strsql
        DoCmd.SetWarnings True
        DoCmd.RefreshRecord
End If


End Sub
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:30
Joined
Sep 21, 2011
Messages
14,051
No real need for an Else if you do it that way?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:30
Joined
Oct 29, 2018
Messages
21,358
Sometimes, it's much easier to avoid/prevent the error than try to catch it. Cheers!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:30
Joined
Feb 28, 2001
Messages
27,001
You don't write error handlers that way. I'll try to show you something more technically correct, but then I will have to explain why you don't want to do what you did.

Code:
Private Sub GPTxtbx_AfterUpdate()

Dim strsql

On Error GoTo errhandler

    strsql = " UPDATE tblProduct SET tblProduct.GP = " & Me.GPTxtbx & " "
    strsql = strsql & " WHERE (((tblProduct.JobID)=[Forms]![JobQuote]![JobID]));"
    
    '    DoCmd.SetWarnings False
    DoCmd.RunSQL strsql
    '    DoCmd.SetWarnings True
    DoCmd.RefreshRecord
    GoTo GPTAU_Done

errhandler:
    MsgBox "Error " & Err.Number & " - " & Err.Description & " in GPTxtbx_AfterUpdate", vbOKOnly, "Error trap"
    Resume GPTAU_Done

GPTAU_Done:

End Sub

OK, now the issues:

1. You wrote On Err but the correct syntax is On Error - so your syntax wouldn't work as expected.

2. strsql will never be empty but it might have a syntax error. Therefore, your IsNull test would never find "TRUE" and never execute the indicated action of EXIT SUB.

3. If you are going to trap the error, don't turn off warnings. That blocks traps.

4. IF you ever take the error trap, you are NOT in subroutine context any more - you are in trap context. This has special requirements having to do with cleaning the hardware stack when you are done. You RETURN from functions and EXIT SUB from subroutines but you RESUME from traps, and the effects of those keywords are NOT identical. As you had originally written it, the ON ERR syntax would not have declared the trap, so you would not have entered trap context - but if you had ever fixed that one error, then triggered the trap, that incorrect dismissal of trap context could have caused your code to crash Access. It is not common, but we HAVE had a few cases of this problem.

5. You COULD have simply used the IsNull BEFOREHAND to see if your textbox was empty, but I would have used a different method - something like IF NZ( txtbox, "") = "" THEN... to decide whether to just do an early exit from the subroutine and not bother to run the SQL in that case. No error trapping involved because here, you are preventing the action that would have triggered the trap.

NOTE: My #5 is consistent with the advice offered by Gasman and theDBguy.
 

tmyers

Well-known member
Local time
Today, 08:30
Joined
Sep 8, 2020
Messages
1,090
Thanks Doc. I always appreciate your answers as you go out of your way to very thoroughly explain something.
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:30
Joined
Sep 21, 2011
Messages
14,051
I never noticed until Doc posted, but you have not Dimmed strSQL fully as well.?
 

tmyers

Well-known member
Local time
Today, 08:30
Joined
Sep 8, 2020
Messages
1,090
I never noticed until Doc posted, but you have not Dimmed strSQL fully as well.?
I caught it shortly after posting. Nickle for every time I have done that though.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:30
Joined
Feb 28, 2001
Messages
27,001
True, Gasman, there was no type. But by default, that means that strsql was a variant. Option Explicit would have accepted that because at least the name was allocated ahead of use. Option Explicit oddly enough does not "care" about data type - only declaration of the names.
 

Users who are viewing this thread

Top Bottom