的MS Access VBA - 显示动态生成的SQL结果在数据表窗体窗体、数据表、结果、动态

2023-09-09 21:24:12 作者：別捅了莪１刀還問莪疼不疼

我有VBA几年的经验，在MS Office应用程序（用于自动化和ETL过程），但还没有必要惹形式在MS Access，直到最近。我铺设了设计一些简单的数据提取表的数据库我已经设计和我挂了什么似乎是一个简单的任务。

I have several years experience with VBA in MS Office applications (for automation and ETL processes) but have not had the need to mess with Forms in MS Access until recently. I'm laying out the design for some simple data extraction forms for a database I have designed and am hung up on what seems to be a simple task.

目标：我需要一个数据表子窗体显示从动态生成的SQL语句在主窗体上返回的控制记录

Objective: I need a datasheet subform to display the records returned from a dynamically built SQL statement from controls on the main form.

在我的主要形式，我有一个按钮，当用户单击该按钮将编译由其他用户表单控件的用户到SQL查询中指定的信息，然后运行该查询，使子窗体显示生成记录。

On my main form, I have a button that when a user clicks it the button will compile the information specified by the user in other user form controls into a SQL query, and then run that query so that a subform displays the resulting records.

无论我做什么，我不能得到这个工作。我不断收到（大部分时间是这样）的Microsoft Visual Basic运行时错误'2467'：您输入的EX pression指的是关闭或不存在的对象这是我得到如下所示的code中的错误。我想不通，如果我以某种方式需要尽快启动子窗体的任何code获取运行还是什么。我已经试过了code，它也没有从其他code论坛工作过的一些其他的变化，但我似乎已经发现了几个论坛，主题包括一些对堆栈溢出的建议code我下面要的工作。

No matter what I do, I cannot get this to work. I keep getting (most of the time anyway) the microsoft visual basic run-time error "'2467': The expression you entered refers to an object that is closed or doesn't exist." That's the error I get with the code shown below. I can't figure out if I somehow need to initiate the subform as soon as any code gets run or what. I've tried some other variations of code that have also not worked from other code forums, but I seem to have found several forum threads including some on Stack Overflow that suggest the code I have below should work.

所附的图像显示了基本的主要形式的模样。我已经标记，用户可以点击（btnDisplaySWData），以编译会从尚未要被列入管制创建SQL按钮，但这不是问题。我只是硬编码的SQL语句在试图弄清楚这个问题显示在code段。正如我想的记录在名为dataDisplaySubform子窗体显示。垃圾是的，我可以合法地与下面的SQL code我只是用出于测试目的，直到我算出这个查询的访问数据库中的表。所有code所示（名为frmDataExtract）的数据格式包括什么是低于code窗口。

The attached image shows what the basic main form looks like. I have labeled the button that the user would click (btnDisplaySWData) to compile the SQL that gets create from yet-to-be-included controls, but that is not the issue. I'm just hard-coding a SQL statement as shown in the code snippet in trying to figure out this issue. As mentioned I want the records to display in the subform named dataDisplaySubform. "JUNK" is a table in the Access database that I can legitimately query with the SQL code below that I am just using for testing purposes until I figure this out. All the code in the data form shown (named frmDataExtract) consists of what is in the code window below.

Option Compare Database
Option Explicit
Public Sub btnDisplaySWData_Click()
    Dim pSQL As String
    pSQL = "SELECT JUNK.agency_ID, JUNK.agency_desc FROM JUNK"
    Me.dataDisplaySubform.Form.RecordSource = pSQL
End Sub

该窗体名为dataDisplaySubform，如在下面的截图中的属性与所选择的子窗体的。

The form is named dataDisplaySubform, as shown in the below screenshot of the properties with the subform selected.

这是什么样的整体形式布局看起来像

This is what the overall form layout looks like

我已经走遍多个论坛网站，并曾尝试与搜索堆栈溢出来找到潜在的解决方案，我的问题方面的每一个变化，但即使在原帖的特点是谁张贴的人解决没有工作过。我花了太多的时间，约2个工作日内，试图找出什么我做错了，但尚未能。

I have scoured several forum sites and also have tried every variation of terms with searching Stack Overflow to find potential solutions for my issue, but none have worked even when the original thread was marked solved by the person who posted it. I've spent way too much time, about 2 workdays, trying to figure out what I am doing wrong and have not yet been able to.

我AP preciate任何人都可以帮助引导我朝着正确的方向发展，这是推动我疯了。

I appreciate anyone that can help steer me in the right direction, this is driving me mad.

谢谢， --TB

解决方案编辑由TURKISHGOLD

嗯，我想我理解了它，我自己虽然HansUp帮助引导我沿着小路与提子窗体源对象没有任何分配给它的。在我的情况下，分配的源对象到形式是不是正确的解决方案，而这正是HansUp了建议。取而代之的是保存的查询，似乎让它做我想做的。

Well I think I figured it out on my own though HansUp helped lead me down the path with mention of the subform Source Object not having anything assigned to it. In my case, assigning the Source Object to a form was not the correct solution which is what HansUp was suggesting. Instead a saved query seems to get it to do what I want.

不知道是否有更好的方式来做到这一点，但它好像你需要建立一个虚拟的，几乎是占位符的查询，这样你就可以设置窗体源对象给它的VBA。像这样的占位符查询：

Not sure if there is a better way to do this, but it seems like you need to set up a dummy, almost placeholder query, so you can set the subform Source Object to it in VBA. A placeholder query like this:

SELECT * FROM JUNK WHERE JUNK.agency_ID ="_";

以上Access查询保存为名为TESTQUERY。它不显示任何内容，但满足需要有源对象分配到的东西，在查看窗体视图的主要形式基本上实例子窗体。所以，用占位符保存的查询，您可以再重新分配将记录到任何SQL字符串是通过用户界面控件放在一起的主要形式，是这样的：

The above Access query is saved as the name "TESTQUERY". It doesn't display anything, but satisfies the need to have Source Object assigned to something, essentially instantiating the subform when looking at the main form in form view. So, with the placeholder saved query, you can then reassign the RecordSource to whatever SQL String is put together via user interface controls on the main form, like this:

Public Sub btnDisplaySWData_Click()
    Dim pSQL As String
    pSQL = "SELECT JUNK.agency_ID, JUNK.agency_desc FROM JUNK"
    Me.dataDisplaySubform.SourceObject = "Query.TESTQUERY"
    Me.dataDisplaySubform.Form.RecordSource = pSQL
    Me.dataDisplaySubform.Requery
End Sub

这时候，形式是在生产，显示存储在PSQL字符串变量硬codeD SQL语句，通过用户输入将被放在一起的控件的主要形式。

which when the Form is in production, the shown hard-coded SQL statement stored in the pSQL string variable will be put together via user input on controls on the main form.

所以，现在，该btnDisplaySWData被点击时，它做什么，我试图做的，显示的记录。

So now, when the btnDisplaySWData is clicked, it does what I was trying to do and displays records.

的MS Access VBA - 显示动态生成的SQL结果在数据表窗体窗体、数据表、结果、动态

推荐答案