MS Access 根据表单值传递

发布于 2024-10-16 05:44:08 字数 461 浏览 6 评论 0原文

如何将传递查询分配给依赖于表单中另一个值的行源？

本质上我想这样做：

SELECT x.companyid, 
       x.companyname, 
       x.productid
  FROM x
 WHERE (((x.CompanyID) = [Forms]![Reporting]![CompanyID_Control]))
ORDER BY x.productid;

但是传递查询当然不支持对任何表单控件的引用。

我已阅读此处< /a> 有一个通过 VBA 的方法，但是我不知道如何将 VBA 与控件的行源结合使用。

原文

How do I assign a pass-through query to Row Source that is dependent on another value in the form?

Essentially I want to do this:

SELECT x.companyid, 
       x.companyname, 
       x.productid
  FROM x
 WHERE (((x.CompanyID) = [Forms]![Reporting]![CompanyID_Control]))
ORDER BY x.productid;

But of course pass-through queries do not support reference to any form controls.

I have read here that there is a method via VBA, however I do not know how to use VBA in conjunction with the Row Source of a control.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

撩发小公举 2024-10-23 05:44:08

正如 Remou 在他的回答中所说，链接表将使这变得更容易。但是，如果您有名为 MyQuery 的传递查询，则可以执行以下操作，以使 MyComboOrListBox 控件的 RowSource 在 的值变化时动态更新。 CompanyID_Control 更改：

Private Sub CompanyID_Control_AfterUpdate()
Dim SQL As String, qdf AS DAO.QueryDef
    Set qdf = CurrentDB.QueryDefs("MyQuery")
    qdf.SQL = " SELECT  x.companyid, x.companyname, x.productid " & _
              " FROM x " & _
              " WHERE x.CompanyID =" & Me.CompanyID_Control & _
              " ORDER BY x.productid;"
    Me.MyComboOrListBox.RowSource = "MyQuery"
End Sub

您还需要将 CompanyID_Control 的 AfterUpdate 属性设置为：
[事件过程]。

请注意，即使您按照 Remou 建议使用链接表，您仍然需要在 CompanyID_Control 的 AfterUpdate 事件中编写代码来刷新组合框/列表框 RowSource：

Private Sub CompanyID_Control_AfterUpdate()
    Me.MyComboOrListBox.Requery
End Sub

As Remou stated in his answer, linked tables will make this easier. However, if you have a pass-through query named MyQuery, you can do the following to make the RowSource of a MyComboOrListBox control update dynamically when the value of the CompanyID_Control changes:

Private Sub CompanyID_Control_AfterUpdate()
Dim SQL As String, qdf AS DAO.QueryDef
    Set qdf = CurrentDB.QueryDefs("MyQuery")
    qdf.SQL = " SELECT  x.companyid, x.companyname, x.productid " & _
              " FROM x " & _
              " WHERE x.CompanyID =" & Me.CompanyID_Control & _
              " ORDER BY x.productid;"
    Me.MyComboOrListBox.RowSource = "MyQuery"
End Sub

You'll also need to set the AfterUpdate property of the CompanyID_Control to:
[Event Procedure].

Note that even if you use linked tables as Remou suggested, you will still need code in the AfterUpdate event of the CompanyID_Control to refresh your combobox/listbox RowSource:

Private Sub CompanyID_Control_AfterUpdate()
    Me.MyComboOrListBox.Requery
End Sub

回复收藏 0 原文

夜吻♂芭芘 2024-10-23 05:44:08

在某些情况下，通过使用直通查询作为 FROM 源编写 SQL SELECT，您将获得高效的结果：

  SELECT MyPassthrough.*
  FROM MyPassthrough
  WHERE [criteria here]

这很可能与在代码中编辑直通的 QueryDef 一样高效，并且仅当直通不成功时才会失败。 t 在其自己的 SELECT 中返回您需要过滤的字段。是否值得更改取决于您使用直通的目的以及它的复杂程度。

一般来说，我总是避免编辑保存的 QueryDef。考虑一下：您多久会使用 DDL 更改 SQL Server VIEW？不经常！在 Access 中，它可能会导致轻微的膨胀（或者在某些情况下不那么轻微），并且我总是尽可能避免任何导致我的前端膨胀的事情。

In some cases, you will get efficient results by writing a SQL SELECT using your passthrough query as the FROM source:

  SELECT MyPassthrough.*
  FROM MyPassthrough
  WHERE [criteria here]

This will very likely be just as efficient as editing the passthrough's QueryDef in code, and it's going to fail only when the passthrough doesn't return in its own SELECT the fields you need to filter on. Whether or not it's worth changing that depends on what you're using the passthrough for, and how complicated it is.

In general, I always avoid editing saved QueryDefs. Consider this: how often would you alter a SQL Server VIEW with DDL? Not often! In Access, it can lead to minor bloat (or not-so-minor, in some cases), and I always avoid anything that causes my front end to bloat, insofar as that's possible.

回复收藏 0 原文

我们只是彼此的过ke 2024-10-23 05:44:08

如果表已链接，您只需对其运行查询，就像它是 Access 表一样，这包括引用表单。所以：

SELECT * FROM MyLinkedTable
WHERE ID = Forms!MyForm!MyID

会很好地工作。

要永久更改查询的 SQL，可以使用 QueryDef 的 SQL 属性：

 Set qdf = CurrentDB.QueryDefs("MyQuery")
 qdf.SQL = "SELECT * FROM MyLinkedTable " & _
           "WHERE ID = " & Forms!MyForm!MyID  ''Or on MyForm, Me.MyID

您还可以将表单记录源或组合或列表框的行源设置为 SQL 字符串，使用链接表就足够简单了：

 Me.RecordSource = "SELECT * FROM MyLinkedTable " & _
           "WHERE ID = " & Forms!MyForm!MyID  ''Or on MyForm, Me.MyID

或者

 Me.MyCombo.RowSource = "SELECT * FROM MyLinkedTable " & _
           "WHERE ID = Forms!MyForm!MyID"

If a table is linked, you can just run a query against it as if it was an Access table, this includes referring to forms. So:

SELECT * FROM MyLinkedTable
WHERE ID = Forms!MyForm!MyID

Will work fine.

To permanently change the SQL of a query, you can use the SQL property of the QueryDef:

 Set qdf = CurrentDB.QueryDefs("MyQuery")
 qdf.SQL = "SELECT * FROM MyLinkedTable " & _
           "WHERE ID = " & Forms!MyForm!MyID  ''Or on MyForm, Me.MyID

You can also set a forms Record Source or the row source of a combo or listbox to an SQL string, with a linked table it is simple enough:

 Me.RecordSource = "SELECT * FROM MyLinkedTable " & _
           "WHERE ID = " & Forms!MyForm!MyID  ''Or on MyForm, Me.MyID

 Me.MyCombo.RowSource = "SELECT * FROM MyLinkedTable " & _
           "WHERE ID = Forms!MyForm!MyID"

回复收藏 0 原文

jJeQQOZ5 2024-10-23 05:44:08

经过大量尝试后我才发现，这是一个简单的解决方案，用您想要在表单中调用的内容创建直通，但将其留空，以便它调用所有内容，然后保存直通。关闭该查询并创建一个新查询并添加 passthrouh 中的每一列。现在，在调用 passthrough 的新查询的条件中添加 [Forms]![Reporting]![CompanyID_Control]) 和只需确保表单已打开，应该运行得同样快，但现在您可以使用表单

回复收藏 0 原文

~没有更多了~