VBA Access 2007中打开查询功能

debugcn 发表于 Dev

用户名

为了避免重复代码，我尝试在模块中使用函数，而不是与表单相关的子函数。该子程序已经由其他人创建，我只需要进行一些较小的更改就可以使其正常工作。这样做使我陷入了非常简单的问题，而不幸的是，我无法在其他地方找到解决方案。

在此函数中，我想以字符串形式打开数据库中的查询，因为在执行查询之前会将其他过滤器添加到查询中。我应该如何打开该查询？

下面显示了作为子代码的代码。

Private Sub Unhide_Click()
    Dim query_pre As String
    Dim whereStr As String

    whereStr = " (AND ((IIF(IsNull([Actions complete].OTL_man), ([Actions complete].ATL_man) < DateAdd('m', 6, Date()),
                                                               ([Actions complete].OTL_man) < DateAdd('m', 6, Date())))" & _
               " AND (([Actions complete].Finished) Is Null)))"

    owner_id = Nz(DLookup("ID", "Owners", "[Full Name] like '*" & prefilter.Value & "*' "), -1)
    sender_id = Nz(DLookup("ID", "Senders", "[Sender] like '*" & prefilter.Value & "*' "), -1)

    query_pre = " [Actions complete].[Ref_man] Like '*" & prefilter.Value & "*'" & _
                " OR [Actions complete].[owner_man] = " & owner_id & _
                " OR [Actions complete].[action_man] Like '*" & prefilter.Value & "*'" & _
                " OR [Actions complete].[Sender_man] = " & sender_id & _
                " OR [Actions complete].[ATL_man] Like '*" & prefilter.Value & "*'" & _
                " OR [Actions complete].[OTL_man] Like '*" & prefilter.Value & "*'" & _
                " OR [Actions complete].[finished] Like '*" & prefilter.Value & "*' "

    final_query2 = "SELECT [Actions complete].* FROM [Actions complete] WHERE (( " & query_pre & " ) " & whereStr & ") ORDER BY [OTL_man] ASC "

    final_query3 = "SELECT [Actions complete].* FROM [Actions complete] WHERE ( " & query_pre & " ) ORDER BY [OTL_man] ASC"

    If Unhide.Value = True And CurrentDb.OpenRecordset(final_query3).RecordCount <> 0 Then
        Me.RecordSource = final_query3
    ElseIf Unhide.Value = False And CurrentDb.OpenRecordset(final_query2).RecordCount <> 0 Then
        Me.RecordSource = final_query2
    Else
        MsgBox "There are no records matching the filter criteria. The filter was ignored"
    End If
End Sub

现在，我想更改此代码，以便可以根据情况更改“完成动作”的内容。因此我将其转移到一个函数，如前所述，以避免重复代码。

Function mail_filter(Past_unhide As Boolean, Inactive_unhide As Boolean)
    Dim query_pre As String
    Dim where As String
    Dim Relevant_Query As String

    If Inactive_unhide = False Then
        Relevant_Query = DoCmd.OpenQuery "[Actions complete]"               'This is where i am stuck right now
    Else
        Relevant_Query = DoCmd.OpenQuery([Actions complete with inactive])  'This is where i am stuck right now
    End If

where = "AND ((  IIF(isnull([Relevant_Query].OTL_man), ([Relevant_Query].ATL_man)<DateAdd('m',6,Date()) ,([Relevant_Query].OTL_man)<DateAdd('m',6,Date()) ) ) AND (([Relevant_Query].Finished) Is Null)) "
query_pre = " "

owner_id = Nz(DLookup("ID", "Owners", "[Full Name] like '*" & prefilter.Value & "*' "), -1)
sender_id = Nz(DLookup("ID", "Senders", "[Sender] like '*" & prefilter.Value & "*' "), -1)

query_pre = " [Relevant_Query].[Ref_man] Like '*" & prefilter.Value & "*' OR [Relevant_Query].[owner_man] = " & owner_id & " OR [Relevant_Query].[action_man] Like '*" & prefilter.Value & "*' OR [Relevant_Query].[Sender_man] = " & sender_id & " OR [Relevant_Query].[ATL_man] Like '*" & prefilter.Value & "*' OR [Relevant_Query].[OTL_man] Like '*" & prefilter.Value & "*' OR [Relevant_Query].[finished] Like '*" & prefilter.Value & "*' "

final_query2 = "SELECT [Relevant_Query].* FROM [Relevant_Query] WHERE ( " & query_pre & " ) " & where & " ORDER BY [OTL_man] ASC"
final_query3 = "SELECT [Relevant_Query].* FROM [Relevant_Query] WHERE ( " & query_pre & " ) ORDER BY [OTL_man] ASC"
If Forms!mail_v3!Past_unhide.Value = True And CurrentDb.OpenRecordset(final_query3).RecordCount <> 0 Then
    Forms!mail_v3.RecordSource = final_query3
ElseIf Forms!mail_v3!Past_unhide.Value = False And CurrentDb.OpenRecordset(final_query2).RecordCount <> 0 Then
    Forms!mail_v3.RecordSource = final_query2
Else
    MsgBox "There are no records matching the filter criteria. The filter was ignored"
End If

End Function

某种程度上，Acces不清楚我要打开哪个查询。我应该怎么做？

帕特里克·奥诺涅兹

问题有点不清楚。我认为您期望的是：

If Inactive_unhide = False Then
    Relevant_Query = "Actions complete" 
Else
    Relevant_Query = "Actions complete with inactive"
End If
'more stuff here
'....
final_query2 = "SELECT * FROM [" & Relevant_Query & "] WHERE..."

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。