如何使用等效于连接的数据构建可编辑表单? [英] How to build an editable form with data equivalent to a join?

问题描述

我有一个表 Tbl 如下:

+----+------+----------+
| ID | Item | ItemDate |
+----+------+----------+
|  1 | xv   | 7/23     |
|  2 | drc  | 3/15     |
|  3 | fna  | 3/15     |
|  4 | fna  | 1/19     |
+----+------+----------+

用户已请求基于此表的表单 TblForm，其中包含列 maxDate，该列给出了每个 最新的 ItemDate>项目.表单必须允许用户编辑 Tbl 数据，因此我不能仅基于连接查询构建表单，因为 Access 不允许您编辑连接的结果.此外，表单必须可根据 maxDate 列排序.

A user has requested a form TblForm based on this table that includes a column maxDate that gives the most recent ItemDate for each Item. The form must allow the user to edit Tbl data, so I can't just build a form based on a join query, as Access doesn't allow you to edit the results of a join. In addition, the form must be sortable based on the maxDate column.

我构建了一个单独的 maxDate 聚合查询，然后将一个控件添加到 TblForm 并将其 ControlSource 设置为:

I built a separate maxDate aggregate query, then added a control to TblForm and set its ControlSource as:

=DLookUp("maxDate","maxDate","Item=" & [Item])

但在生成的数据表中，我无法根据此列进行排序；我认为这是因为它不是 TblForm 的记录源的一部分.所以我尝试构建一个包含 DLookUp 的查询:

But in the resulting datasheet, I can't sort based on this column; I assume that's because it's not part of TblForm's record source. So I tried building a query that includes the DLookUp:

select *,=DLookUp("maxDate","maxDate","Item=" & [Item]) as maxDateField from tbl

基于此查询的表单非常慢.

The form based on this query is extremely slow.

关于如何构建我正在寻找的东西有什么想法吗?

Any ideas on how I can build what I'm looking for?

推荐答案

这可以通过使用临时表来实现.

This can be achieved by using temporary tables.

您需要两个相等的临时表，我们称它们为 tmpTbl1 和 tmpTbl2.

You need two equal temp tables, let's call them tmpTbl1 and tmpTbl2.

要刷新这些表，您需要创建两个查询:vwMaxDate1 和 vwMaxDate2.

To refresh these tables, you create two queries: vwMaxDate1 and vwMaxDate2.

您还创建了两个查询作为报告的来源:vwTbl1 和 vwTbl2.您将在它们之间切换，因为您无法在表单打开时更新表格.

You also create two queries as origins for your report: vwTbl1 and vwTbl2. You'll switch between them, because you cannot update a table while open by a form.

这是视图的内容:

' CreateTmpTable1
SELECT Tbl.Item, Max(Tbl.ItemDate) AS maxdate INTO TmpTable1
FROM Tbl
GROUP BY Tbl.Item;

' CreateTmpTable2
SELECT Tbl.Item, Max(Tbl.ItemDate) AS maxdate INTO TmpTable2
FROM Tbl
GROUP BY Tbl.Item;

' vwMaxDate1
INSERT INTO tmpTbl1 ( Item, maxdate )
SELECT Tbl.Item, Max(Tbl.ItemDate) AS maxdate
FROM Tbl
GROUP BY Tbl.Item;

' vwMaxDate2
INSERT INTO tmpTbl2 ( Item, maxdate )
SELECT Tbl.Item, Max(Tbl.ItemDate) AS maxdate
FROM Tbl
GROUP BY Tbl.Item;

' vwTbl1
SELECT Tbl.*, T.maxdate
FROM Tbl LEFT JOIN tmpTbl1 AS T ON Tbl.Item = T.Item;

' vwTbl2
SELECT Tbl.*, T.maxdate
FROM Tbl LEFT JOIN tmpTbl2 AS T ON Tbl.Item = T.Item;

现在，执行 CreateTmpTable1 和 CreateTmpTable2.您将不再需要这些查询.

Now, execute CreateTmpTable1 and CreateTmpTable2. You wont need these queries again.

在设计模式下输入两个临时表并将项目设置为主键.

Enter both temp tables in design mode and set Item as the Primary Key.

然后，执行 vwMaxDate1 并根据视图 vwTbl1 设计您的表单.完成后，删除注册表的来源.我还要锁定 maxdate 字段的控件，因为这是一个计算字段，不应手动修改.

Then, execute vwMaxDate1 and design your form based on the view vwTbl1. When you're finished, delete the origin of the registry. I'd also lock the control for the maxdate field, since this is a calculated field and shouldn't be manually modified.

现在，在表单中输入以下代码，将表单的Before update"、After update"和Open"事件改为[Event procedure]".

Now, enter the following code into the form and change the "Before update", "After update" and "Open" events of the form to "[Event procedure]".

Option Compare Database
Option Explicit

Private dataChanged As Boolean      ' True whenever Item or ItemDate are changed
Private FirstTable As Boolean       ' If true the origin of the registry is vwTbl1

Private Sub Form_AfterUpdate()

    If dataChanged Then

        DoCmd.SetWarnings False
        Select Case FirstTable

            Case True:  ' vwTbl1 is open: Switch to vwTbl2
                DoCmd.RunSQL "DELETE * FROM TmpTbl2"    ' Delete actual data from tmp table 2
                DoCmd.OpenQuery "vwMaxDate2"            ' Create new data for tmp table 2
                Me.RecordSource = "vwTbl2"              ' Switch to vwTbl2
                FirstTable = False

            Case False: ' vwTbl2 is open: Switch to vwTbl1
                DoCmd.RunSQL "DELETE * FROM TmpTbl1"    ' Delete actual data from tmp table 1
                DoCmd.OpenQuery "vwMaxDate1"            ' Create new data for tmp table 1
                Me.RecordSource = "vwTbl1"              ' Switch to vwTbl1
                FirstTable = True

        End Select

    DoCmd.SetWarnings True
    End If

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

    ' Examine the Item and ItemDate controls to determine whether they've changed or not
    dataChanged = (Me.Item.OldValue <> Me.Item.Value Or Me.ItemDate.OldValue <> Me.ItemDate.Value)

End Sub

Private Sub Form_Open(Cancel As Integer)

    ' Initialize variables and form registry source

    FirstTable = True
    dataChanged = False

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "vwMaxDate1"
    Me.RecordSource = "vwTbl1"
    DoCmd.SetWarnings True

End Sub

此查询对于查询数据和更新与 Item 和 ItemDate 不同的任何字段将很快.

This query will be fast for querying data and for update of any fields different from Item and ItemDate.

如果您需要更新这些特定字段，您会注意到可变延迟，具体取决于 Tbl 表中的寄存器数量.

If you need to update these particular fields, you'll notice a variable delay, depending on the number of registers in your Tbl table.

为了加速临时表的创建，强烈建议基于Item"和ItemDate"字段在 Tbl 表上创建索引.

To accelerate the creation of the temporary table, it would be strongly advisable to create an index on your Tbl table based on both "Item" and "ItemDate" fields.

如果您需要它在多用户环境中工作，那么您应该使用表记录来保存变量 FirstTable.这意味着你必须使用一些指令来查询和更新这个表，比如

If you need this to work in a multiuser environment, then you should use a table record to hold the variable FirstTable. This means you have to query and update this table with some instructions like

FirstTable = DLookup("FirstTable","CtrlTable","ID=1")

DoCmd.RunSQL "UPDATE CtrlTable SET FirstTable=True WHERE ID=1"

而不是简单地使用="将 True 或 False 分配给 FirstTable.

instead of simply assigning True or False to FirstTable with "=".

仅此而已.希望它对你有用.

And that's all. Hope it works for you.

问候，

这篇关于如何使用等效于连接的数据构建可编辑表单?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！