查询保留旧标准&子窗体不会刷新

Question

我目前正在从头开始重新设计我公司的access数据库，没有任何经验，因为对其结构的最后一次编辑是在2006年，它基本上是一个包含20万条记录的列表，这是一个慢得要命的地狱。(当前使用工作特性的测试已经显示出显著的改进，耶！)

我试图创建一个显示查询结果的子窗体，但查询本身依赖于多选。该查询作用于一个客户Id，并根据选定的客户返回结果。(这是可行的)

我一直在寻找和寻找解决方案，但我就是不知道该怎么办。我正在处理的问题是双重的，我怀疑这是相关的，但首先要了解一点背景。

首先，该子窗体拒绝在视觉上进行更新。虽然查询保存了所需的结果，但子窗体根本不更新，我尝试过刷新、docmd.requery等，唯一有效的方法基本上就是“重新附加”源。任何试图刷新显示结果的方式都不能从“表面”访问。

其次，查询似乎“抓住”了之前选择的任何客户端。即使它可以从所有客户端(我将其写为“无选择意味着一切”)到特定客户端，但在两次选择之间，它保留了前一次选择的标准，并将其前移。

“‘Dashboard”是嵌套子窗体TabGeneralSubform的表单，qryTetraGeneral是用作子窗体的源的查询。MultiCustomer是多选表单。

Sub ListAll_Click() [access vba]


Set MyDB = CurrentDb
flgSelectAll = 0

Set qdef = MyDB.QueryDefs("qryTetraGeneral")

'strSQL is based on the SQL conversion of the SelectClientAll query
'the final AND clause will be appended by strWhere which is based on the MultiList Selection
'If there is no selection the query will default to All active Subs

 strSQL = "SELECT [EQ-TETRA_ISSI].ISSI,[EQ-TETRA_TEI].TEI, [EQ-TETRA_ISSI].Active," & _
    " [EQ-TETRA_TEI].Activation, Client_Table.[Customer Name], Client_Table.[Customer ID], [EQ-TETRA_REQFUL].ReqType, SERVICEREQUEST_TABLE.REQNUM," & _
    " SERVICEREQUEST_TABLE.REQDATE, SERVICEREQUEST_TABLE.RESPUSER" & _
    " FROM ((Client_Table INNER JOIN [EQ-TETRA_ISSI] ON Client_Table.[External Customer Index] = [EQ-TETRA_ISSI].[Customer Index])" & _
    " INNER JOIN SERVICEREQUEST_TABLE ON Client_Table.[External Customer Index] = SERVICEREQUEST_TABLE.Index) INNER JOIN ([EQ-TETRA_TEI]" & _
    " INNER JOIN [EQ-TETRA_REQFUL] ON [EQ-TETRA_TEI].TEI = [EQ-TETRA_REQFUL].TEI) ON (SERVICEREQUEST_TABLE.REQNUM = [EQ-TETRA_REQFUL].Reqnum)" & _
    " AND ([EQ-TETRA_ISSI].ISSI = [EQ-TETRA_REQFUL].ISSI)" & _
    " WHERE ((([EQ-TETRA_TEI].TEI)=[EQ-TETRA_REQFUL].[TEI]) AND (([EQ-TETRA_ISSI].Active)=True) AND (([EQ-TETRA_TEI].Activation)=True) AND ("

'extracts selection from list and creates SQL line to be added to strSQL

 For i = 0 To MultiCustomer.ListCount - 1
    If MultiCustomer.Selected(i) Then
        flgSelectAll = flgSelectAll + 1
        strIN = strIN & "([EQ-TETRA_ISSI].[Customer Index]=" & MultiCustomer.Column(0, i) & ")" & " OR "
    End If
 Next

 If flgSelectAll = 0 Then
    strSQL = "SELECT [EQ-TETRA_ISSI].ISSI,[EQ-TETRA_TEI].TEI, [EQ-TETRA_ISSI].Active," & _
    " [EQ-TETRA_TEI].Activation, Client_Table.[Customer Name], Client_Table.[Customer ID], [EQ-TETRA_REQFUL].ReqType, SERVICEREQUEST_TABLE.REQNUM," & _
    " SERVICEREQUEST_TABLE.REQDATE, SERVICEREQUEST_TABLE.RESPUSER" & _
    " FROM ((Client_Table INNER JOIN [EQ-TETRA_ISSI] ON Client_Table.[External Customer Index] = [EQ-TETRA_ISSI].[Customer Index])" & _
    " INNER JOIN SERVICEREQUEST_TABLE ON Client_Table.[External Customer Index] = SERVICEREQUEST_TABLE.Index) INNER JOIN ([EQ-TETRA_TEI]" & _
    " INNER JOIN [EQ-TETRA_REQFUL] ON [EQ-TETRA_TEI].TEI = [EQ-TETRA_REQFUL].TEI) ON (SERVICEREQUEST_TABLE.REQNUM = [EQ-TETRA_REQFUL].Reqnum)" & _
    " AND ([EQ-TETRA_ISSI].ISSI = [EQ-TETRA_REQFUL].ISSI)" & _
    " WHERE ((([EQ-TETRA_TEI].TEI)=[EQ-TETRA_REQFUL].[TEI]) AND (([EQ-TETRA_ISSI].Active)=True) AND (([EQ-TETRA_TEI].Activation)=True)) ORDER BY [EQ-TETRA_ISSI].ISSI;"
Else
    strWhere = Left(strIN, Len(strIN) - 3) & ")) ORDER BY [EQ-TETRA_ISSI].ISSI;"
    strSQL = strSQL + strWhere
End If

qdef.SQL = strSQL
Call CallRefreshForm([Forms]![Dashboard])

For Each ListObject In MultiCustomer.ItemsSelected
    MultiCustomer.Selected(ListObject) = False
Next ListObject

现在，不可否认的是，代码是从我的前辈留下的东西拼凑起来的，SQL语句看起来很混乱，但是，它可以工作，并根据标准显示所需的结果，目前对我来说已经足够好了。当我知道所有想要的特性都在工作时，我可能会稍后尝试对其进行裁剪。

Answer 1

有几种方法可以简化这一过程。首先，当您在列表框的基础上构建SQL时，不要使用很多IN/OR，只需使用一次IN (我在这里进行了一点空气编码，所以请原谅任何细微的错误)。

For i = 0 To MultiCustomer.ListCount - 1
    If MultiCustomer.Selected(i) Then
        strSQL = strSQL & MultiCustomer.Column(0, i) & "," 
    End If
Next i
If Right(strSQL,1)="," Then strSQL=Left(strSQL,Len(strSQL)-1)
If Len(strSQL)>0 Then
    strSQL="SELECT [EQ-TETRA_ISSI].ISSI,[EQ-TETRA_TEI].TEI, [EQ-TETRA_ISSI].Active," & _
    " [EQ-TETRA_TEI].Activation, Client_Table.[Customer Name], Client_Table.[Customer ID], [EQ-TETRA_REQFUL].ReqType, SERVICEREQUEST_TABLE.REQNUM," & _
    " SERVICEREQUEST_TABLE.REQDATE, SERVICEREQUEST_TABLE.RESPUSER" & _
    " FROM ((Client_Table INNER JOIN [EQ-TETRA_ISSI] ON Client_Table.[External Customer Index] = [EQ-TETRA_ISSI].[Customer Index])" & _
    " INNER JOIN SERVICEREQUEST_TABLE ON Client_Table.[External Customer Index] = SERVICEREQUEST_TABLE.Index) INNER JOIN ([EQ-TETRA_TEI]" & _
    " INNER JOIN [EQ-TETRA_REQFUL] ON [EQ-TETRA_TEI].TEI = [EQ-TETRA_REQFUL].TEI) ON (SERVICEREQUEST_TABLE.REQNUM = [EQ-TETRA_REQFUL].Reqnum)" & _
    " AND ([EQ-TETRA_ISSI].ISSI = [EQ-TETRA_REQFUL].ISSI)" & _
    " WHERE [EQ-TETRA_TEI].TEI=[EQ-TETRA_REQFUL].[TEI] " _
    & " AND [EQ-TETRA_ISSI].Active=True " _
    & " AND [EQ-TETRA_TEI].Activation=True " _
    & " AND [EQ-TETRA_ISSI].[Customer Index] IN (" & strSQL & ")
Else
    strSQL="SELECT [EQ-TETRA_ISSI].ISSI,[EQ-TETRA_TEI].TEI, [EQ-TETRA_ISSI].Active," & _
    " [EQ-TETRA_TEI].Activation, Client_Table.[Customer Name], Client_Table.[Customer ID], [EQ-TETRA_REQFUL].ReqType, SERVICEREQUEST_TABLE.REQNUM," & _
    " SERVICEREQUEST_TABLE.REQDATE, SERVICEREQUEST_TABLE.RESPUSER" & _
    " FROM ((Client_Table INNER JOIN [EQ-TETRA_ISSI] ON Client_Table.[External Customer Index] = [EQ-TETRA_ISSI].[Customer Index])" & _
    " INNER JOIN SERVICEREQUEST_TABLE ON Client_Table.[External Customer Index] = SERVICEREQUEST_TABLE.Index) INNER JOIN ([EQ-TETRA_TEI]" & _
    " INNER JOIN [EQ-TETRA_REQFUL] ON [EQ-TETRA_TEI].TEI = [EQ-TETRA_REQFUL].TEI) ON (SERVICEREQUEST_TABLE.REQNUM = [EQ-TETRA_REQFUL].Reqnum)" & _
    " AND ([EQ-TETRA_ISSI].ISSI = [EQ-TETRA_REQFUL].ISSI)" & _
    " WHERE [EQ-TETRA_TEI].TEI=[EQ-TETRA_REQFUL].[TEI] " _
    & " AND [EQ-TETRA_ISSI].Active=True " _
    & " AND [EQ-TETRA_TEI].Activation=True "
End If
strSQL=strSQL & " ORDER BY [EQ-TETRA_ISSI].ISSI;"

我也不相信您需要在WHERE子句中使用[EQ-TETRA_TEI].TEI=[EQ-TETRA_REQFUL].[TEI]，因为您已经在使用它将两个表连接在一起。

致以敬意，