Error traping for dynamic querydef

[josephbupe]

My dynamic query keeps deleting when an error occurs i.e if the user did not supply query criteria before pressong the "Search" button. Instead I want a message box to pop-up when an error occures.

I do not need to remove this line:

db.QueryDefs.Delete ("Q_ImageNormalSort_MySQL")

because when I do the dynamic query fails to work. But when I leave it there the query works just fine until an error occures.

How can I trap the error so that the user is only presented with a pop-up message?

This is my code:

Private Sub cmd1_Click()
Dim db As DAO.Database
Dim QD As QueryDef
Dim varWhere As Variant
Dim Answer As String
Dim varItem As Variant
Set db = CurrentDb()

    On Error GoTo Err_Msg
[COLOR=SeaGreen]'   Delete the existing dynamic query; trap the error if the query does
'   not exist.[/COLOR]
    db.QueryDefs.Delete ("Q_ImageNormalSort_MySQL")
    On Error Resume Next
    
   [COLOR=SeaGreen] '   Setup "Family Name" code block:[/COLOR]
    
    Answer = ""
    
        Answer = Answer & Me.txtFamilyName.Value & ","
    
    If Len(Answer & vbNullString) <> 0 Then
        Answer = left(Answer, Len(Answer) - 1)
    End If
    
    If Len(Answer & vbNullString) <> 0 Then
        varWhere = varWhere & " AND [Family Name] Like('*" & Answer & "*')"
    End If
    
 [COLOR=SeaGreen]'*********************************************************************[/COLOR]
     
    Set QD = db.CreateQueryDef("Q_ImageNormalSort_MySQL", _
    "Select * from Q_Persons_Crosstab2 " & (" where " + Mid(varWhere, 6) & ";"))

     Me.lstSearchResults.RowSource = "SELECT PersonID, [Full Name], Gender FROM Q_ImageNormalSort_MySQL"
     Me.F_Workspace_SideMenu.Form.RecordSource = "SELECT * FROM Q_ImageNormalSort_MySQL"

    Me.lstSearchResults.Visible = True

Exit_cmd1_Click:
Exit Sub

Err_Msg:
MsgBox "Sorry, you did not provide search criteria", vbExclamation, "Search error"
Resume Exit_cmd1_Click

End Sub

I will appreciate any help.

joseph

 

[JHB]

I would wait to delete it, until you have tested if search criteria is filled in. More or less like the code below.

Private Sub cmd1_Click()
  Dim db As DAO.Database
  Dim QD As QueryDef
  Dim varWhere As Variant
  Dim Answer As String
  Dim varItem As Variant
  
  Set db = CurrentDb()
  Answer = Me.txtFamilyName.Value & ","
  If Len(Answer & vbNullString) <> 0 Then
    Answer = Left(Answer, Len(Answer) - 1)
  End If
  If Len(Answer & vbNullString) <> 0 Then
    varWhere = " AND [Family Name] Like('*" & Answer & "*')"
    db.QueryDefs.Delete ("Q_ImageNormalSort_MySQL")
    Set QD = db.CreateQueryDef("Q_ImageNormalSort_MySQL", _
    "Select * from Q_Persons_Crosstab2 " & (" where " + Mid(varWhere, 6) & ";"))
    Me.lstSearchResults.RowSource = "SELECT PersonID, [Full Name], Gender FROM Q_ImageNormalSort_MySQL"
    Me.F_Workspace_SideMenu.Form.RecordSource = "SELECT * FROM Q_ImageNormalSort_MySQL"
    Me.lstSearchResults.Visible = True
  Else
    MsgBox "Sorry, you did not provide search criteria", vbExclamation, "Search error"
  End If
End Sub