vba 访问子表单不刷新显示的记录

Access subform not refreshing displayed records

提问by Alin I

I have a mainform in access with a subform (continous form mode). The subform's source is a query that has a criteria parameter that gets the values from a combobox in mainform.

我有一个带有子表单的主表单（连续表单模式）。子窗体的源是一个查询，它有一个标准参数，它从主窗体的组合框中获取值。

When I open the mainform, the combobox is empty, and the subform displays all the records. If I select a value in the combobox and click a refresh button that has only the following line of code: me.subformname.form.requeryit works fine, and the subforms requeries and refreshes displaying the appropriate records. If I change the value in combobox and hit refresh button, again it requeries/refreshes the subform.

当我打开主窗体时，组合框为空，子窗体显示所有记录。如果我在组合框中选择一个值并单击只有以下代码行的刷新按钮：me.subformname.form.requery它工作正常，并且子窗体重新查询并刷新显示适当的记录。如果我更改组合框中的值并点击刷新按钮，它会再次重新查询/刷新子表单。

Problem is that if I filter the subfrom using the context menu (Right Click > "Equals to...")the subform is filtered accordingly then I remove the filter (Right Click > "Clear filter from...")the subform is unfiltered, but now if I change the combobox value and hit Refresh button, the subform doesn't refresh nor requery.

问题是，如果我使用上下文菜单(Right Click > "Equals to...")过滤子表单，相应地过滤子表单，然后我删除过滤器(Right Click > "Clear filter from...")，子表单未过滤，但现在如果我更改组合框值并点击刷新按钮，子表单不会刷新也不会重新查询。

The same is true if even if I open the query in the background. Once I filter, refresh doesn't work anymore.

即使我在后台打开查询也是如此。过滤后，刷新不再起作用。

This is the subform query:

这是子表单查询：

SELECT qry_listatoatesesizarile.idsesizare,
       branduri.numebrand,
       locatii.fsname,
       categoriiechipamente.categorieechipament,
       echipamente.codechipament,
       qry_listatoatesesizarile.nrsesizare,
       qry_listatoatesesizarile.datasesizare,
       qry_listatoatesesizarile.datasla,
       qry_listatoatesesizarile.detalii,
       qry_listatoatesesizarile.stare,
       Dlookup("[stareraport]", "listastarirapoarte",
       "[idstareraport]=" & [stare]) AS
       StareS,
       echipe.denumireechipa
       AS EchipaLocatie,
       qry_listarapoarte.nrraport,
       qry_listarapoarte.datainceput,
       qry_listarapoarte.datafinalizat,
       qry_listarapoarte.lucrariefectuate,
       qry_listarapoarte.cauzadefectiune,
       qry_listasesizariprogramate.denumireechipa
       AS EchipaProgramata,
       Iif([stare] = 1
            OR [stare] = 3
            OR [stare] = 0, Datediff("s", Cdate([datasla]), Now()),
       Datediff("s", Cdate([datasla]), Cdate([datafinalizat])))
       AS DiferentaSLASecs,
       Secs2dhms([diferentaslasecs])
       AS DiferentaSLA,
       Iif([diferentaslasecs] < 0, true, false)
       AS InSLA,
       Format([datasesizare], "mmmm yyyy")
       AS [Month],
       Iif([stare] = 2
            OR [stare] = 4, Iif([diferentaslasecs] < 0, "inchis in sla",
                            "inchis in afara sla"),
       Iif([diferentaslasecs] < 0, "deschis in sla", "deschis in afara sla"))
       AS
       Situatie,
       locatii.clientfrigorifice,
       clienti.client
FROM   clienti
       INNER JOIN (((((branduri
                       INNER JOIN ((qry_listatoatesesizarile
                                    INNER JOIN echipamente
                                            ON
qry_listatoatesesizarile.idechipament =
echipamente.idechipament)
                                   INNER JOIN locatii
                                           ON echipamente.idlocatie =
                                  locatii.idlocatie)
                               ON branduri.idbrand = locatii.idbrand)
                      INNER JOIN categoriiechipamente
                              ON echipamente.idcategorieechipament =
                                 categoriiechipamente.idcategorieechipament)
                     INNER JOIN echipe
                             ON qry_listatoatesesizarile.idechipa =
                                echipe.idechipa)
                    LEFT JOIN qry_listasesizariprogramate
                           ON qry_listatoatesesizarile.idsesizare =
                              qry_listasesizariprogramate.idsesizare)
                   LEFT JOIN qry_listarapoarte
                          ON qry_listatoatesesizarile.idraport =
                             qry_listarapoarte.idraport)
               ON clienti.idclient = locatii.clientfrigorifice
WHERE  ( ( ( Format([datasesizare], "mmmm yyyy") ) LIKE Iif(Nz(
                    [forms] ! [controlsla] ! [cb_lunaverificata], "") = "", "*",
                      [forms] ! [controlsla] ! [cb_lunaverificata]) )
         AND ( ( locatii.clientfrigorifice ) LIKE Iif(Nz(
               [forms] ! [controlsla] ! [cb_client]
                                                      ,
                                                          "") = "", "*",
               [forms] ! [controlsla] ! [cb_client]) ) ); 

回答by GeekInOhio

Access can be a bit funny about filtering, especially on subforms.

Access 在过滤方面可能有点有趣，尤其是在子表单上。

Try adding explicit code to your Combobox code to clear the filters every time. So it would look something like:

尝试在 Combobox 代码中添加显式代码以每次都清除过滤器。所以它看起来像：

Me.subformname.Form.Filter = Null
Me.subformname.Form.FilterOn = False
Me.subformname.Form.Requery

Alternatively, you could try dynamically resetting the RecordSource of the subform on every change in the Combobox. (Note that if you do this, you don't have to explicitly requery, as it is done automatically.) So in place of the code you have in the Combobox now, you'd have somthing along the lines of:

或者，您可以尝试在组合框中的每次更改时动态重置子窗体的 RecordSource。（请注意，如果您这样做，您不必显式重新查询，因为它是自动完成的。）因此，您现在可以在 Combobox 中使用以下代码代替：

Dim mySQLString as String

'Store subform query
mySQLString = "SELECT qry_listato... <<The rest of your query here>>>"

'Set Subform RecordSource
Me.subformname.Form.RecordSource = mySQLSTring

Hopefully one of those helps you out.

希望其中之一可以帮助您。