Run-time error 3167 and Run-time error 3012

问题:

I have a question regarding the code below which creates a new query based on combobox selections or a textbox entry on an Access form.

Sometimes when I run the query it works fine and other times I get one of two errors. I think it has to do with

1. either the code that creates the "sqlquery" query itself
2. the deletion of the "NewQuery" after its created or
3. a combination of both.

Any assistance you can provide would be greatly appreciated. I have been trying a variety of different solutions for hours, but I am coming up short.

The first error I receive is Run-time error 3167 Record is deleted on the line DoCmd.OpenQuery "NewQuery".

The next error I receive on other occasions is Run-time error 3012 Object "NewQuery" already exists on the line Set qdf = CurrentDb.CreateQueryDef("NewQuery",sqlquery).
Thank you.

On Error Resume Next
CurrentDb.QueryDefs.Delete "NewQuery"


If Text24 <> "" Then

sqlquery = "SELECT * FROM TABLE WHERE TABLE.WorkerID = [Forms]![UserForm]![Text24];"

Else

    ComboBox_condition = ""

    If ComboBox0 <> "" Then
        ComboBox_condition = ComboBox_condition + " TABLE.PayGroupRegionCode = '" & ComboBox0 & "'"
    End If


    If ComboBox0 <> "" And ComboBox4 <> "" Then
        ComboBox_condition = ComboBox_condition + " TABLE.PayGroupCountryDesc = '" & ComboBox4 & "'"
    ElseIf ComboBox4 <> "" Then
        ComboBox_condition = ComboBox_condition + " AND TABLE.PayGroupCountryDesc = '" & ComboBox4 & "'"
    End If


    If ComboBox0 <> "" And ComboBox4 <> "" And ComboBox6 <> "" Then
        ComboBox_condition = ComboBox_condition + " TABLE.JobFamilyGroup = '" & ComboBox6 & "'"
    ElseIf ComboBox6 <> "" Then
        ComboBox_condition = ComboBox_condition + " AND TABLE.JobFamilyGroup = '" & ComboBox6 & "'"
    End If


    If ComboBox0 <> "" And ComboBox4 <> "" And ComboBox6 <> "" And ComboBox10 <> "" Then
        ComboBox_condition = ComboBox_condition + " TABLE.JobTitle = '" & ComboBox10 & "'"
    ElseIf ComboBox10 <> "" Then
        ComboBox_condition = ComboBox_condition + " AND TABLE.JobTitle = '" & ComboBox10 & "'"
    End If


    If ComboBox0 <> "" And ComboBox4 <> "" And ComboBox6 <> "" And ComboBox10 <> "" And ComboBox12 <> "" Then
        ComboBox_condition = ComboBox_condition + " TABLE.JobCode = '" & ComboBox12 & "'"
    ElseIf ComboBox12 <> "" Then
        ComboBox_condition = ComboBox_condition + " AND TABLE.JobCode = '" & ComboBox12 & "'"
    End If


    If ComboBox0 <> "" And ComboBox4 <> "" And ComboBox6 <> "" And ComboBox10 <> "" And ComboBox12 <> "" And ComboBox14 <> "" Then
        ComboBox_condition = ComboBox_condition + " TABLE.CompensationGrade = '" & ComboBox14 & "'"
    ElseIf ComboBox14 <> "" Then
        ComboBox_condition = ComboBox_condition + " AND TABLE.CompensationGrade = '" & ComboBox14 & "'"
    End If


    If ComboBox0 <> "" And ComboBox4 <> "" And ComboBox6 <> "" And ComboBox10 <> "" And ComboBox12 <> "" And ComboBox14 <> "" And ComboBox16 <> "" Then
        ComboBox_condition = ComboBox_condition + " TABLE.SalaryAdministrationPlanCode = '" & ComboBox16 & "'"
    ElseIf ComboBox16 <> "" Then
        ComboBox_condition = ComboBox_condition + " AND TABLE.SalaryAdministrationPlanCode = '" & ComboBox16 & "'"
    End If


    If ComboBox_condition <> "" Then
        sqlquery = "SELECT * FROM TABLE WHERE " + ComboBox_condition
    Else
        sqlquery = "SELECT * FROM TABLE"
    End If

End If


Set qdf = CurrentDb.CreateQueryDef("NewQuery", sqlquery)

DoCmd.OpenQuery "NewQuery"

回答1:

The runtime errors might be fixed by adding DB.QueryDefs.Refresh after adding or deleting a querydef.

Dim DB As Database

Set DB = CurrentDb
DB.QueryDefs.Delete "NewQuery"
DB.QueryDefs.Refresh

Set qdf = DB.CreateQueryDef("NewQuery", sqlquery)
DB.QueryDefs.Refresh
DoCmd.OpenQuery "NewQuery"

---

There are some other problems with your code.

You should give your form controls meaningful names instead of e.g. ComboBox4. This will help readability a lot.

Your construction of ComboBox_condition is overly complex. The most elegant solution to avoid all these doubled conditions is:

Always add them with AND, including the first one:

If ComboBox0 <> "" Then
    ComboBox_condition = ComboBox_condition + " AND TABLE.PayGroupRegionCode = '" & ComboBox0 & "'"
End If
' ...
If ComboBox12 <> "" Then
    ComboBox_condition = ComboBox_condition + " AND TABLE.JobCode = '" & ComboBox12 & "'"
End If
' ...

and then when you build the final statement, add a dummy WHERE condition that is always True, so the WHERE clause is always valid:

sqlquery = "SELECT * FROM TABLE WHERE 1=1 " & ComboBox_condition

Even the check If ComboBox_condition <> "" can be omitted.