在VBA(访问)中独立地重新查询表单的多个实例 [英] Requery multiple instances of form independently in VBA (Access)

问题描述

我正在使用艾伦·布朗(Allen Browne)出色的代码( http://allenbrowne.com/ser-35.html)，以在点击事件中打开表单的多个实例.我知道新实例是只读的，由于这篇文章，我设法记录了用户输入的数据，以保持日志记录(

I am using Allen Browne's brilliant code (http://allenbrowne.com/ser-35.html) to open several instances of a form on click event. I understand that new instances are read-only and I managed to record the data the user inputs, to keep a log, thanks to this post (Access 2007 / VBA - Multiple Instances of Form, Update controls on specific instance from Module).

我在每种表单上都有几个组合框，并且希望根据其他用户的选择(对于每种表单，不在表单之间)来更新/重新查询其中显示的内容.例如，用户选择cmb1上的组件A，cmb2仅显示组件A的可能故障.

I have several combo boxes on each form and would like to update/requery what is shown in one depending on user selection on other (for each form, not among forms). For ex, user choose component A on cmb1, cmb2 only shows possible failures of component A.

{me.Requery}将仅且始终在Forms类中重新查询该表单(从数据库窗口/导航窗格中打开的表单(如果有的话)，而不是下面的clnClient集合上的实例.但是，我可以使用"Me"传递信息并检索日志记录的值.

{me.Requery} will only and always requery the form in the Forms class (the one opened from the database window/nav pane, if any, not the instances on clnClient collection below). However, I can pass information with "Me" and retrieve values for the logging.

使用来自Allen Browne的经过稍加修改的OpenAClient版本以及MajP的输入(在此很棒的线程中

Using a slightly modified OpenAClient version from Allen Browne's with input from MajP (in this great thread https://www.tek-tips.com/viewthread.cfm?qid=1753790)

Public clnClient As New Collection

Public Function OpenAClient(FormName As String, Optional inputCaption As String = "") As Form_f_myForm
    'Purpose:   Open an independent instance of form f_myForm
    On Error GoTo Err_OpenAClient

    Dim frm As Form

    'Open a new instance, show it, and set a caption.
    Set frm = New Form_f_myForm
    frm.Visible = True
    frm.Caption = inputCaption
    frm.Tag = FormName

    'Append it to our collection.
    clnClient.Add Item:=frm, Key:=FormName

    Set OpenAClient = frm
    Set frm = Nothing
    Exit Function

Err_OpenAClient:
    If Err.Number = 457 Then
        MsgBox "A Form with this name already exists."
    Else
        MsgBox Err.Number & " " & Err.Description
    End If
End Function

我尝试过

Public ActiveForm As Form_f_myform
Set activeForm = OpenAClient("ExampleForm","Example caption")

或简单

Set activeForm = clnClient.Item(2) 'just to test the second instance

，然后使用ActiveForm代替"Me"并以这种方式进行操作，但它似乎不起作用.任何想法/帮助将不胜感激.或以其他方式解决此问题的想法.谢谢！

and then use ActiveForm instead of "Me" and manipulate it that way, but it does not seem to work. Any ideas/help would be appreciated. Or ideas to tackle this in a different way. Thanks!

有点背景.我是VBA和Access的新手，正在使用数据库(Access Office365)进行故障报告.已预先评估所有故障，然后才交付给用户.用户将识别出故障的特定组件，并相应地填写表格(日期，故障类型等).他们不会更改包含预评估的表格.一切都很好并且可以正常工作.问题在于添加用户报告多个故障的功能时.

A bit of background. I am very new to VBA and Access, working on a database (Access Office365) for failure reporting. All failures have been assessed beforehand delivery to the user. The user will identify the specific component that is failing and fill the form accordingly (date, type of failure etc..). They will not change the table containing the pre-assessment. This is all fine and working. The issue is when adding the ability for the user to report multiple failures.

推荐答案

首先，让我们澄清一些误解:

First, let's clear up some misconceptions:

我知道新实例是只读的...

I understand that new instances are read-only...

它们确实可以读写.它们只是不能附加到同一查询.原因是当打开多个 f_myform 实例时，诸如 Forms！f_myform！cmb1 之类的查询表达式不起作用.您必须为每个实例的记录源构造自定义SQL，以获得真正的独立操作.

They can indeed be read-write. They just can't be attached to the same query. The reason for this is query expressions like Forms!f_myform!cmb1 don't work when there is more than one instance of f_myform open. You must construct custom SQL for each instance's record source to get true independent operation.

{Me.Requery}将仅且始终在Forms类中重新查询表单...

{Me.Requery} will only and always requery the form in the Forms class...

在窗体的代码中使用时， Me.Requery 实际上只会重新查询该窗体的该实例.当表单的所有实例共享同一查询时，就会出现问题.原因和解决方法如上.

When used in a form's code, Me.Requery will indeed requery only that instance of the form. The problem comes when all instances of the form share the same query. The reason and fix are above.

就编码策略而言，请删除全局的公共ActiveForm as Form_f_myform .那只是解决问题的方法.

As far as coding strategy, get rid of the global Public ActiveForm As Form_f_myform. That's only a recipe for problems.

表单实例本身包含使代码正常工作所需的一切.尝试使用表单引用本身调用函数(就像您之前所做的那样).例如:

The form instance itself carries all you need to get your code to work. Try calling functions with the form reference itself (like you did earlier). For example:

    Public Sub ShowComponentFailures(thisForm As Form_f_myform)
        Dim varCompID As Variant
        Dim strSQL As String
        ' get Component ID from given instance's cmb1
        varCompID = thisForm.cmb1.Value
        ' create SQL to retrieve possible failures for CompID 
        strSQL = GetComponentFailuresSQL(varCompID)
        ' set cmb2's record source to SQL
        thisForm.cmb2.RowSource = strSQL
    End Sub

这篇关于在VBA(访问)中独立地重新查询表单的多个实例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！