运行时错误3167和运行时错误3012 [英] Run-time error 3167 and Run-time error 3012

查看：746
本文介绍了运行时错误3167和运行时错误3012的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有关于code，低于此基础上组合框选择或访问窗体上的文本框的项新建一个查询的问题。

有时候，当我运行它工作正常，其他时间的查询，我得到两个错误之一。我认为它跟

  1. 无论是code，创造了sqlquery的查询本身
  2. 删除了NewQuery后，它的创建或
    3. 的
  3. 两者的组合。

任何可以提供的援助将大大AP preciated。我一直在尝试了各种不同的解决方案小时，但我来了短。

我收到第一个错误是运行时错误3167记录被删除就行了 DoCmd.OpenQueryNewQuery。

接下来的错误我收到在其他场合是运行时错误3012对象NewQuery已存在就行了设置QDF = CurrentDb。 CreateQueryDef（NewQuery，sqlquery的）。
谢谢你。

 上的错误恢复下一页
CurrentDb.QueryDefs.DeleteNewQuery


如果Text24＆LT;＆GT;  然后

sqlquery的=SELECT * FROM表，其中TABLE.WorkerID = [表格] [窗体] [Text24]！

其他

    ComboBox_condition =

    如果ComboBox0＆LT;＆GT;  然后
        ComboBox_condition = ComboBox_condition +TABLE.PayGrou pregion code ='＆放大器; ComboBox0和放大器; '
    结束如果


    如果ComboBox0＆LT;＆GT; 而ComboBox4＆LT;＆GT;  然后
        ComboBox_condition = ComboBox_condition +TABLE.PayGroupCountryDesc ='＆放大器; ComboBox4和放大器; '
    elseif的ComboBox4＆LT;＆GT;  然后
        ComboBox_condition = ComboBox_condition +和TABLE.PayGroupCountryDesc ='与＆amp; ComboBox4和放大器; '
    结束如果


    如果ComboBox0＆LT;＆GT; 而ComboBox4＆LT;＆GT; 而ComboBox6＆LT;＆GT;  然后
        ComboBox_condition = ComboBox_condition +TABLE.JobFamilyGroup ='＆放大器; ComboBox6和放大器; '
    elseif的ComboBox6＆LT;＆GT;  然后
        ComboBox_condition = ComboBox_condition +和TABLE.JobFamilyGroup ='与＆amp; ComboBox6和放大器; '
    结束如果


    如果ComboBox0＆LT;＆GT; 而ComboBox4＆LT;＆GT; 而ComboBox6＆LT;＆GT; 而ComboBox10＆LT;＆GT;  然后
        ComboBox_condition = ComboBox_condition +TABLE.JobTitle ='＆放大器; ComboBox10和放大器; '
    elseif的ComboBox10＆LT;＆GT;  然后
        ComboBox_condition = ComboBox_condition +和TABLE.JobTitle ='与＆amp; ComboBox10和放大器; '
    结束如果


    如果ComboBox0＆LT;＆GT; 而ComboBox4＆LT;＆GT; 而ComboBox6＆LT;＆GT; 而ComboBox10＆LT;＆GT; 而ComboBox12＆LT;＆GT;  然后
        ComboBox_condition = ComboBox_condition +TABLE.Job code ='＆放大器; ComboBox12和放大器; '
    elseif的ComboBox12＆LT;＆GT;  然后
        ComboBox_condition = ComboBox_condition +和TABLE.Job code ='＆放大器; ComboBox12和放大器; '
    结束如果


    如果ComboBox0＆LT;＆GT; 而ComboBox4＆LT;＆GT; 而ComboBox6＆LT;＆GT; 而ComboBox10＆LT;＆GT; 而ComboBox12＆LT;＆GT; 而ComboBox14＆LT;＆GT;  然后
        ComboBox_condition = ComboBox_condition +TABLE.CompensationGrade ='＆放大器; ComboBox14和放大器; '
    elseif的ComboBox14＆LT;＆GT;  然后
        ComboBox_condition = ComboBox_condition +和TABLE.CompensationGrade ='与＆amp; ComboBox14和放大器; '
    结束如果


    如果ComboBox0＆LT;＆GT; 而ComboBox4＆LT;＆GT; 而ComboBox6＆LT;＆GT; 而ComboBox10＆LT;＆GT; 而ComboBox12＆LT;＆GT; 而ComboBox14＆LT;＆GT; 而ComboBox16＆LT;＆GT;  然后
        ComboBox_condition = ComboBox_condition +TABLE.SalaryAdministrationPlan code ='＆放大器; ComboBox16和放大器; '
    elseif的ComboBox16＆LT;＆GT;  然后
        ComboBox_condition = ComboBox_condition +和TABLE.SalaryAdministrationPlan code ='＆放大器; ComboBox16和放大器; '
    结束如果


    如果ComboBox_condition＆LT;＆GT;  然后
        sqlquery的=SELECT * FROM表，其中+ ComboBox_condition
    其他
        sqlquery的=SELECT * FROM TABLE
    结束如果

结束如果


设置QDF = CurrentDb.CreateQueryDef（NewQuery，sqlquery的）

DoCmd.OpenQueryNewQuery

解决方案

运行时错误可能是固定的，加入 DB.QueryDefs.Refresh 添加或删除的QueryDef之后。

昏暗DB数据库 设置DB = CurrentDb DB.QueryDefs.DeleteNewQuery DB.QueryDefs.Refresh 设置QDF = DB.CreateQueryDef（NewQuery，sqlquery的） DB.QueryDefs.Refresh DoCmd.OpenQueryNewQuery

有一些其他的问题，您的code。

您应该给你的窗体控件有意义的名称，而不是如 ComboBox4 。这将有助于可读性很多。

您建设 ComboBox_condition 的过于复杂。最优雅的解决方案，以避免所有这些一倍条件：

总是与和，包括第一次将它们添加

如果ComboBox0＆LT;＆GT; 然后     ComboBox_condition = ComboBox_condition +和TABLE.PayGrou pregion code ='＆放大器; ComboBox0和放大器; ' 结束如果 '... 如果ComboBox12＆LT;＆GT; 然后     ComboBox_condition = ComboBox_condition +和TABLE.Job code ='＆放大器; ComboBox12和放大器; ' 结束如果 '...

，然后当你建立的最后陈述中，添加一个虚拟WHERE条件永远是真，所以WHERE子句始终是有效的：

sqlquery的=SELECT * FROM表，其中1 = 1和安培; ComboBox_condition

即使是检查如果ComboBox_condition＆LT;＆GT; 可以省略。

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"

解决方案

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.

这篇关于运行时错误3167和运行时错误3012的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
Access最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆