vba 如何从另一个表单重新查询子表单？

How to requery a subform from another form?

提问by Tony L.

I've struggling with this problem on my own, then with some help, then search about it; but I haven't had any luck. So I decided to ask.

我自己一直在努力解决这个问题，然后在一些帮助下，然后搜索它；但我没有任何运气。所以我决定问问。

I have two forms in Access 2007lets call them MainFormand EntryForm.
MainFormhas a subform and a button. The button opens the EntryFormin Add Mode. What I want to do is when the EntryFormsaves the new record it would update (requery) the subform in MainForm.

我在Access 2007 中有两种形式可以调用它们MainForm和EntryForm.
MainForm有一个子窗体和一个按钮。该按钮EntryForm以添加模式打开。我想要做的是当EntryForm保存新记录时，它会更新（重新查询）MainForm.

I've try this setup code

我试过这个设置代码

Private Sub cmdSaveAndClose_Click()
    DoCmd.Save

    'requery list
    Forms![MainForm]![subformName].Requery

    '' I've also tried these
    'Forms![MainForm]![subformName].Form.Requery
    'Forms.("MainForm").[subformName].Requery
    'Forms.("MainForm").[subformName].Form.Requery


    DoCmd.Close
End Sub

None of these attempts seem to work. Is there a way to make this requery? Thanks for the help in advance.

这些尝试似乎都没有奏效。有没有办法让这个重新查询？我在这里先向您的帮助表示感谢。

采纳答案by David-W-Fenton

Just a comment on the method of accomplishing this:

只是对完成此操作的方法的评论：

You're making your EntryForm permanently tied to the form you're calling it from. I think it's better to not have forms tied to context like that. I'd remove the requery from the Save/Close routine and instead open the EntryForm modally, using the acDialog switch:

您正在使您的 EntryForm 永久绑定到您从中调用它的表单。我认为最好不要将表单与这样的上下文联系起来。我会从保存/关闭例程中删除重新查询，而是使用 acDialog 开关以模态方式打开 EntryForm：

  DoCmd.OpenForm "EntryForm", , ,"[ID]=" & Me!SubForm.Form!ID, , acDialog
  Me!SubForm.Form.Requery

That way, EntryForm is not tied down to use in one context. The alternative is to complicate EntryForm with something that is knowledgable of which form opened it and what needs to requeried. I think it's better to keep that kind of thing as close to the context in which it's used, and keep the called form's code as simple as possible.

这样，EntryForm 就不会被限制在一种上下文中使用。另一种方法是使 EntryForm 复杂化，因为它知道哪个表单打开了它以及需要重新查询什么。我认为最好让这种东西尽可能接近使用它的上下文，并保持被调用表单的代码尽可能简单。

Perhaps a principle here is that any time you are requerying a form using the Forms collection from another form, it's a good indication something's not right about your architecture -- that should happen seldom, in my opinion.

也许这里的一个原则是，每当您使用另一个表单中的 Forms 集合重新查询一个表单时，这是一个很好的迹象，表明您的架构存在问题——在我看来，这种情况应该很少发生。

回答by Fionnuala

You must use the name of the subform control, not the name of the subform, though these are often the same:

您必须使用子表单控件的名称，而不是子表单的名称，尽管它们通常是相同的：

 Forms![MainForm]![subform control name Name].Form.Requery

Or, if you are on the main form:

或者，如果您使用主表单：

 Me.[subform control name Name].Form.Requery

More Info: http://www.mvps.org/access/forms/frm0031.htm

更多信息：http: //www.mvps.org/access/forms/frm0031.htm

回答by user1603444

I tried several solutions above, but none solved my problem. Solution to refresh a subform in a form after saving data to database:

我尝试了上面的几种解决方案，但没有一个解决了我的问题。将数据保存到数据库后刷新表单中的子表单的解决方法：

Me.subformname.Requery

Me.subformname.Requery

It worked fine for me. Good luck.

它对我来说很好。祝你好运。

回答by Leo

I had a similar kind of issue, but with some differences...

我有类似的问题，但有一些不同......

In my case, my main form has a Control (vendor) which value I used to update a Query in my DB, using the following code:

在我的例子中，我的主表单有一个 Control（供应商），我用来更新我的数据库中的查询的值，使用以下代码：

Sub Set_Qry_PedidosRealizadosImportados_frm(Vd As Long)
Dim temp_qry As DAO.QueryDef

'Procedimento para ajustar o codigo do cliente na Qry_Pedidos realizados e importados
'Procedure to adjust the code of the client on Qry_Pedidos realizados e importados
Set temp_qry = CurrentDb.QueryDefs("Qry_Pedidos realizados e importados")
temp_qry.SQL = "SELECT DISTINCT " & _
            "[Qry_Pedidos distintos].[Codigo], " & _
            "[Qry_Pedidos distintos].[Razao social], " & _
            "COUNT([Qry_Pedidos distintos].[Pedido Avante]) As [Pedidos realizados], " & _
            "SUM(IIf(NZ([Qry_Pedidos distintos].[Pedido Flexx], 0) > 1, 1, 0)) As [Pedidos Importados] " & _
            "FROM [Qry_Pedidos distintos] " & _
            "WHERE [Qry_Pedidos distintos].Vd = " & Vd & _
            " Group BY " & _
            "[Qry_Pedidos distintos].[Razao social], " & _
            "[Qry_Pedidos distintos].[Codigo];"
End Sub

Since the beginning my subform record source was the query named "Qry_Pedidos realizados e importados".

从一开始我的子表单记录源就是名为“Qry_Pedidos realizados e importados”的查询。

But the only way I could update the subform data inside the main form context was to refresh the data source of the subform to it self, like posted bellow:

但是我可以更新主表单上下文中的子表单数据的唯一方法是将子表单的数据源刷新为它自己，如下所示：

Private Sub cmb_vendedor_v1_Exit(Cancel As Integer)
'Codigo para atualizar o comando SQL da query
'Code to update the SQL statement of the query 
    Call Set_Qry_Pedidosrealizadosimportados_frm(Me.cmb_vendedor_v1.Value)

'Codigo para for?ar o Access a aceitar o novo comando SQL
'Code to force de Access to accept the new sql statement
    Me!Frm_Pedidos_realizados_importados.Form.RecordSource = "Qry_Pedidos realizados e importados"
End Sub

No refresh, recalc, requery, etc, was necessary after all...

毕竟不需要刷新、重新计算、重新查询等......

回答by Hannu T

Just discovered that if the source table for a subform is updated using adodb, it takes a while until the requery can find the updated information.

刚刚发现如果使用adodb更新子表单的源表，需要一段时间才能重新查询找到更新的信息。

In my case, I was adding some records with 'dbconn.execute "sql" ' and wondered why the requery command in vba doesn't seem to work. When I was debugging, the requery worked. Added a 2-3 second wait in the code before requery just to test made a difference.

就我而言，我添加了一些带有 'dbconn.execute "sql" ' 的记录，并想知道为什么 vba 中的 requery 命令似乎不起作用。当我调试时，重新查询工作。在重新查询之前在代码中添加了 2-3 秒的等待，只是为了测试有所不同。

But changing to 'currentdb.execute "sql" ' fixed the problem immediately.

但是更改为 'currentdb.execute "sql" ' 立即解决了问题。

回答by user3758018

All your controls are belong to us!

您所有的控件都属于我们！

Fionnuala answered this correctly but skimmers like me would find it easy to miss the point.

Fionnuala 正确回答了这个问题，但像我这样的撇油器会发现很容易错过这一点。

You don't refresh the subFORM you refresh the subform CONTROL. In fact, if you check with allforms() the subForm isn't even loaded as far as access is concerned.

您不刷新子表单，而是刷新子表单 CONTROL。事实上，如果您使用 allforms() 检查，就访问而言，甚至不会加载 subForm。

On the main form look at the label the subform wizard provided or select the subform by clicking once or on the border around it and look at the "caption" in the "Other" tab in properties. That's the name you use for requerying, not the name of the form that appears in the navigation panel.

在主窗体上查看子窗体向导提供的标签，或者通过单击一次或单击子窗体周围的边框来选择子窗体，然后查看属性中“其他”选项卡中的“标题”。这是您用于重新查询的名称，而不是出现在导航面板中的表单名称。

In my case I had a subform called frmInvProdSub and I tried for many hours to figure out why Access didn't think it existed. I gave up, deleted the form and re-created it. The very last step is telling it what you want to call the control so I called it frmInvProdSub and finished the wizard. Then I tried and voila, it worked!

就我而言，我有一个名为 frmInvProdSub 的子表单，我尝试了好几个小时来弄清楚为什么 Access 认为它​​不存在。我放弃了，删除了表格并重新创建了它。最后一步是告诉它您想调用什么控件，因此我将其命名为 frmInvProdSub 并完成了向导。然后我试了一下，瞧，它奏效了！

When I looked at the form name in the navigation window I realized I'd forgotten to put "Sub" in the name! That's when it clicked. The CONTROL is called frmInvProdSub, not the form and using the control name works.

当我在导航窗口中查看表单名称时，我意识到我忘记在名称中添加“Sub”！那是它点击的时候。CONTROL 被称为 frmInvProdSub，而不是表单和使用控件名称的作品。

Of course if both names are identical then you didn't have this problem lol.

当然，如果两个名字相同，那么你就没有这个问题，哈哈。

回答by Vitanov

By closing and opening, the main form usually runs all related queries (including the subform related ones). I had a similar problem and resolved it by adding the following to Save Command button on click event.

通过关闭和打开，主窗体通常会运行所有相关的查询（包括子窗体相关的查询）。我遇到了类似的问题，并通过将以下内容添加到单击事件的保存命令按钮来解决它。

DoCmd.Close acForm, "formname", acSaveYes
DoCmd.OpenForm "formname"