Suppress Error Messages in Access

1
votes

I have an access form that runs a query. However it runs a query using vba code and sometimes it searches the table for text fields and sometimes for number fields depending on the field they choose on a combo box in the form.

I have left a note that if they wish to search for a text field they must enter double quotes or the code will not work. However, if the user does not follow these directions they will get a popup that exlains the coding issue with the options debug and end. I do not want them to see this message.

Is there a way to suppress this error message and write my own?

Edit: Dim dbsCurrent As Database Dim qryTest As QueryDef

    varWhere = "WHERE InclusiveParent." & Combo0.Value & "=" & Text2

    varWhere = "select Location, IcmService, IcmScript, ThresholdVariable, PbxVdn, Domestic, FirstSecondLook, DNIS, Tollfree, Outdial, Description, Carrier, DefaultTollfree, BlockedRoute, Variable3, Variable4, Variable5, Variable9, ValueScrVdn, Cvp from InclusiveParent " & varWhere

    'Filter frmCustomers based on search criteria
    'Dim dbsCurrent As Database
    'Dim qryTest As QueryDef

    Set dbsCurrent = CurrentDb
    Set qryTest = dbsCurrent.QueryDefs("Broaden")
    qryTest.SQL = varWhere


    'Close frmSearch
    DoCmd.Close acForm, "SearchDependents"

InclusiveParent is a query that I'm requery-ing and Broaden is the requery. SearchDependents is the name of the Form. Combo0 is a combo box that lets them select which field to choose to filter. And Text2 is the text field that they enter the filter criteria in. However, not all fields are numbers, so when they choose to filter by a text field they must enter double quotes or the code fails.

2
ms-access
Use the VBA Docs to learn about "On Error" or post an illustrative portion of your code to increase your chances of getting detailed advice. - Ekkehard.Horner
I have updated it! Thank you for your suggestion - Zack
Change the UI so the user can't possibly put in the wrong data type. Then you won't have to code around errors. That is, don't let a user make a mistake that you can foresee. - David-W-Fenton

2 Answers

1
votes

Try this and remove the note to require quotation marks:

varWhere = "WHERE InclusiveParent." & Combo0.Value & "="
If IsNumeric(Text2.Value) Then
    varWhere = varWhere & Text2.Value
Else
    varWhere = varWhere & """" & Text2.Value & """"
End If
1
votes

Since Combo0.Value is the name of a field in InclusiveParent query, check the data type of that field. You can use that information to determine whether or not you need to wrap Text2.Value in quotes. By knowing the field's data type, you can also validate Text2.Value ... make sure it is a valid number when Combo0.Value is a number field. This will also allow you to ensure sure the value is quoted when the user enters only digits for Text2.Value but Combo0.Value is a text field.

Select Case dbsCurrent.QueryDefs("InclusiveParent").Fields(Me.Combo0.Value).Type
Case dbBigInt, dbByte, dbCurrency, dbDecimal, dbDouble, _
        dbFloat, dbInteger, dbLong, dbSingle
    If Not IsNumeric(Me.Text2.Value) Then
        '* warn user and request a valid number *'
    Else
        '* build WHERE clause without quotes around Text2.Value *'
    End If
Case dbChar, dbMemo, dbText
    '* build WHERE clause with quotes around Text2.Value *'
Case Else
    '* decide what you want for field which is neither text nor numeric *'
End Select