Error Handling VBA

[matt164]

Hi all,

I do not have much experience working with Error Handling code and therefore am running into quite a few problems in constructing this database. First off, I have a form (Form_GUI) where a user inputs information and then clicks "Ok" which executes a query (strQuery) and opens a continuous form (Form_GridDisplay1) to display the results. However, when the search doesn't match any records I get an error in vba. To resolve this I have tried to implement error handling, see below:

Form_GUI:

Option Compare Database
Option Explicit
Public strQuery As String
Private Sub ok_Click()
'Run query
Dim argCount As Integer
On Error GoTo Err_Handlerr
If IsNull(creatorcb.Value) And IsNull(assigncb.Value) And IsNull(Combo31) And IsNull(Text10) Then
    MsgBox "You Need To Select Some Values", vbCritical, "Lead Tracking"
    Exit Sub
End If
strQuery = "SELECT * From Lead Where "
If Not IsNull(creatorcb.Value) Then
    strQuery = strQuery & "Creator Like '" & creatorcb.Value & "'"
    argCount = argCount + 1
End If
If Not IsNull(assigncb.Value) Then
    If argCount > 0 Then strQuery = strQuery & " AND "
    strQuery = strQuery & "AssignedTo Like '" & assigncb.Value & "'"
    argCount = argCount + 1
End If
If Not IsNull(Text8.Value) Then
    If argCount > 0 Then strQuery = strQuery & " AND "
    strQuery = strQuery & "BorrowerFirstName Like '" & Text8.Value & "'"
    argCount = argCount + 1
End If
If Not IsNull(Text10.Value) Then
    If argCount > 0 Then strQuery = strQuery & " AND "
    strQuery = strQuery & "BorrowerLastName Like '" & Text10.Value & "'"
    argCount = argCount + 1
End If
If Not IsNull(Text12.Value) Then
    If argCount > 0 Then strQuery = strQuery & " AND "
    strQuery = strQuery & "Company Like '" & Text12.Value & "'"
    argCount = argCount + 1
End If
If Not IsNull(Text16.Value) Then
    If argCount > 0 Then strQuery = strQuery & " AND "
    strQuery = strQuery & "PropertyCity Like '" & Text16.Value & "'"
    argCount = argCount + 1
End If
If Not IsNull(Text14.Value) Then
    If argCount > 0 Then strQuery = strQuery & " AND "
    strQuery = strQuery & "PropertyState Like '" & Text14.Value & "'"
    argCount = argCount + 1
End If
If Not IsNull(Combo31.Value) Then
    If argCount > 0 Then strQuery = strQuery & " AND "
    strQuery = strQuery & "Status Like '" & Combo31.Value & "'"
    argCount = argCount + 1
End If
DoCmd.OpenForm "GridDisplay1", acNormal, , , acFormReadOnly, acDialog
Exit_Handlerr:
    Exit Sub
Err_Handlerr:
    MsgBox "Please Modify Your Search And Try Again", vbOKOnly, "Lead Tracking - Error"
    Resume Exit_Handlerr
    
End Sub

Form_GridDisplay1:

Private Sub Form_Open(Cancel As Integer)
'---- SQL comes from Form_GUI.strQuery ----
On Error GoTo Err_Handlerrr
Me.RecordSource = Form_GUI.strQuery
Me.cursorlock.SetFocus
Exit_Handlerrr:
    Cancel = True
    Exit Sub
Err_Handlerrr:
    MsgBox "Your Search Returned No Results Or An Unknown Error Occurred", vbOKOnly, "Lead Tracking"
    Resume Exit_Handlerrr
End Sub

When I run a search for a record that does not exist, I get two error messages (first due to GridDisplay1 opening, and second from the GUI error handling because the open_form failed to complete). Two is annoying, one is much preferred. But the real problem lies in that after I receive this first error message no matter what I do I cannot run a search for a record that I know exists because I will just keep getting error msg box. I have tried using the DCOUNT function and RecordSetClone to specifically handle the problem of 0 query results however both have refused to work. Obviously that is preferred instead of a general error msg box just assuming the error is due to lack of results. Any help would be much appreciated, thank you.

 

[jdraw]

review these debugging tips and techniques
http://www.cpearson.com/excel/DebuggingVBA.aspx

 

[ChrisO]

Matt164.

Your main problem is that you wrote too much code before testing it properly.

Reduce the code to the minimum and test again. In this case, comment out what you don’t need for testing.

Also:-
1. Try to get the code off the left margin. Procedure start, end and named or numerical labels go left margin, everything else indent by one Tab (4 spaces preferably).

2. Add some vertical white space, one line, between close coupled code blocks. A close coupled code block is one focus, one attention, on one thought.

3. Try not to use negatives in If conditions; example:-
If Not IsNull(creatorcb.Value) Then
only tests for Null.
If Len(creatorcb.Value) Then
tests for Null, Empty and a Zero Length String.
In other words, you are looking for something being there not if something is not there.

4. CamelCase variable names. creatorcb or CreatorCb? I don’t even know what it means but it is easier to read.

5. Drop properties like .Value. Value is the default in VBA but in VB .Text is the default. Needlessly specifying the default can cause code failure when converting VB code to VBA code if you only need the default. If you need Value in VB or Text in VBA then specify it as and when needed.

------

This thread is about Error Handling in VBA and a very large part of error handling involves writing the code in such a way as to isolate errors.

It’s really not about using this tool or that tool to find the problem. If we get to the situation of needing a tool to find a problem then we have almost certainly not written the code in such a way as to isolate a potential problem.

It might just look like programming style but that style actually helps to reduce errors by forcing a focus on the single thought process which produced the code in the first place.

Chris.

 

[spikepl]

Unless you know what you are doing, referring to objects on a form using this syntax:

Form_GUI.strQuery

is bound to get you into trouble, because it might open invisible forms which then linger in the background and mess things up.

Use the syntax:

Forms!GUI.strQuery

Using mixed case (as opposed to all lower case) for defining names has the further advantage that misspelled names become immediately obvious if you subsequently type them in all lower case, because the interpreter does not find their definitions, and therefore does not convert them into mixed case.

 

[matt164]

Thank you for the input, I will work on correcting syntax and spacing to make the code easier to read - but the issue here isn't the query because that works just fine, I just would like to have a message box pop up when there are no results as opposed to a "Could not find specified record" in VB.

 

[ChrisO]

Matt164

I was rather hoping that when you reformatted the code you would see that the Form_Open event is always cancelled. It’s that sort of error which needs to be fixed before chasing other errors.

Chris.

 

[ChrisO]

Just as an example, and I can still keep it on one page (screen) at the moment:-

Option Compare Database
Option Explicit


Private Sub cmdOK_Click()
    Dim strSQL As String
    
    On Error GoTo ErrorHandler
    
    If Len(Me.txtCreatorCb) Or Len(Me.txtAssignCb) Or Len(Me.cboStatus) Or Len(Me.txtBorrowerLastName) Then
        strSQL = "SELECT * FROM tblLead WHERE 1 = 1 "
        
        If Len(Me.txtCreatorCb) Then
            strSQL = strSQL & " AND Creator = " & Chr(34) & Me.txtCreatorCb & Chr(34)
        End If

        If Len(Me.txtAssignCb) Then
            strSQL = strSQL & " AND AssignedTo = " & Chr(34) & Me.txtAssignCb & Chr(34)
        End If

        If Len(Me.cboStatus) Then
            strSQL = strSQL & " AND Status = " & Chr(34) & Me.cboStatus & Chr(34)
        End If

        If Len(Me.txtBorrowerLastName) Then
            strSQL = strSQL & " AND BorrowerLastName = " & Chr(34) & Me.txtBorrowerLastName & Chr(34)
        End If

        If Len(Me.txtBorrowerFirstName) Then
            strSQL = strSQL & " AND BorrowerFirstName = " & Chr(34) & Me.txtBorrowerFirstName & Chr(34)
        End If

        If Len(Me.txtCompany) Then
            strSQL = strSQL & " AND Company = " & Chr(34) & Me.txtCompany & Chr(34)
        End If

        If Len(Me.txtPropertyCity) Then
            strSQL = strSQL & " AND PropertyCity = " & Chr(34) & Me.txtPropertyCity & Chr(34)
        End If

        If Len(Me.txtPropertyState) Then
            strSQL = strSQL & " AND PropertyState = " & Chr(34) & Me.txtPropertyState & Chr(34)
        End If
        
        DoCmd.OpenForm FormName:="GridDisplay1", DataMode:=acFormReadOnly, WindowMode:=acDialog, OpenArgs:=strSQL
    Else
        MsgBox "You Need To Select Some Values", vbCritical, "Lead Tracking"
    End If

ExitProcedure:
    Exit Sub
    
ErrorHandler:
    MsgBox "An Unexpected Error Occurred", vbOKOnly, "Lead Tracking"
    
    Resume ExitProcedure
    
End Sub

And:-

Option Compare Database
Option Explicit


Private Sub Form_Open(Cancel As Integer)
    
    On Error GoTo ErrorHandler
    
    If Len(Me.OpenArgs) Then
        Me.RecordSource = Me.OpenArgs
    Else
        DoCmd.Close acForm, Me.Name
    End If
    
ExitProcedure:
    Exit Sub
    
ErrorHandler:
    MsgBox "An Unexpected Error Occurred", vbOKOnly, "Lead Tracking"
    
    DoCmd.Close acForm, Me.Name

    Resume ExitProcedure
        
End Sub

There may still be logical things wrong with it, in particular:-

If Len(Me.txtCreatorCb) Or Len(Me.txtAssignCb) Or Len(Me.cboStatus) Or Len(Me.txtBorrowerLastName) Then

but it makes a start.

Chris.

 

[ChrisO]

Something was niggling at me about the line:-

If Len(Me.txtCreatorCb) Or Len(Me.txtAssignCb) Or Len(Me.cboStatus) Or Len(Me.txtBorrowerLastName) Then

The thing is that it is not required for a search. Criteria for a search only limits (reduces) the dataset returned so if a search has no criteria then the search should return everything in the table. If we require a criterion then it becomes impossible for the user to see everything in the table.

Another point is that it should not be required for the user to type a criterion to completion. If what they type is not in the table then there would be no return. The user should be limited to criteria which are already in the table simply because there is no point searching for something which is not already there.

So the table itself defines what can be searched on. That then removes the need for freestyle typing in text boxes and causes us to use combo boxes limited to list.

For example:-
There should be no text box for the BorrowerLastName criterion. What it should be is a Combo Box with a Row Source of:-
SELECT DISTINCT tblLead.BorrowerLastName FROM tblLead ORDER BY tblLead.BorrowerLastName;

The overall criteria may still produce no records but at least we know that each criterion is correct even if a criterion results in False i.e. Null, Empty or ZLS.

Chris.