访问连续表格:添加控件而不修改基础表? [英] Access continuous form: Add a control without modifying the underlying table?

问题描述

我正在制作一个简单的Access表单(连续视图).在详细信息"部分有一个复选框，在页脚中有一个命令"按钮.

I'm making a simple Access form (continuous view). This has a checkbox in the Details section and a Command button in the footer.

这样，用户可以使用复选框选择"多个记录，然后单击该按钮上的命令按钮以运行更新所选记录的脚本.无需永久存储这些检查值.

This way, the user can use the checkbox to "select" multiple records then click the command button at the button to run a script which updates the selected records. There's no need to permanently store these check values.

通常，我会向基础表中添加一个布尔字段，并将复选框与该字段相关联.但是有没有一种方法可以在不修改表的情况下做到这一点?即将复选框值存储在内存中?

Normally, I'd add a boolean field to the underlying table and associate the checkbox to that field. But is there a way to do this without modifying the table? i.e. store the checkbox values in memory?

推荐答案

您可以在基于以下内容的表单中包括记录选择"复选框 断开连接的记录集.那是您在内存中创建的ADO记录集，未绑定到任何数据源.使用记录集中的主键，您的命令按钮的单击过程可以遍历记录集以检索选定"记录的主键列表.如果该方法听起来有用，请参阅Database Journal上Danny Lesandrini的这篇文章:

You could include record selection check boxes in a form based on a disconnected recordset. That's an ADO recordset you create in memory, not bound to any data source. With the primary key in the recordset, your command button's click procedure can walk the recordset to retrieve a list of primary keys of the "selected" records. If that approach sounds useful, see this article by Danny Lesandrini at Database Journal: Create In-Memory ADO Recordsets

我根据该文章中的代码创建了此表单.主窗体包括一个基于断开连接的记录集的子窗体，该记录集是在子窗体的Form_Open期间加载的.

I created this form based on code from that article. The main form includes a subform based on a disconnected recordset which is loaded during the subform's Form_Open.

请注意，您实际上不需要以表格形式显示主键(ID)；只要它包含在记录集中，就可以在遍历记录集时对其进行检索.

Note you don't actually need to display the primary key (ID) in the form; as long as it's included in the recordset, you can retrieve it when walking the recordset.

Private Sub Form_Open(Cancel As Integer)
    Dim dbs As DAO.Database
    Dim fld As ADODB.Field
    Dim rstAdo As ADODB.Recordset
    Dim rstDao As DAO.Recordset
    Dim strSql As String

    Set rstADO = New ADODB.Recordset
    With rstAdo
        .Fields.Append "EmployeeID", adInteger, , adFldKeyColumn
        .Fields.Append "FirstName", adVarChar, 10, adFldMayBeNull
        .Fields.Append "LastName", adVarChar, 20, adFldMayBeNull
        .Fields.Append "Selected", adBoolean
        .CursorType = adOpenKeyset
        .CursorLocation = adUseClient
        .LockType = adLockPessimistic
        .Open
    End With

    Set dbs = CurrentDb
    strSql = "SELECT EmployeeID, FirstName, LastName " & _
             "FROM Employees ORDER BY LastName, FirstName"
    Set rstDao = dbs.OpenRecordset(strSql, dbOpenSnapshot)

    Do Until rstDao.EOF
        rstAdo.AddNew
        rstAdo!EmployeeID = rstDao!EmployeeID
        rstAdo!FirstName = rstDao!FirstName
        rstAdo!LastName = rstDao!LastName
        rstAdo!Selected = False
        rstAdo.Update
        rstDao.MoveNext
    Loop

    Set Me.Recordset = rstAdo
    rstDao.Close    
    Set rstDao = Nothing
    Set dbs = Nothing
End Sub

该代码示例对ADO使用早期绑定，这需要为 Microsoft ActiveX数据对象版本设置引用.但是，如果对后期绑定进行适当的修改，它仍然可以正常工作.

That code sample uses early binding for ADO which requires setting a reference for a version of Microsoft ActiveX Data Objects. However, it can work fine with the appropriate modifications for late binding.

这种方法并不十分轻巧.但是，它允许您具有选择复选框，而无需将其绑定到实际数据表中的是/否"字段.当用户可能会覆盖共享表中的彼此选择时，在多用户应用程序中这将是一个挑战.断开连接的记录集可以避免此类冲突.

This approach is not exactly light-weight. However it allows you to have selection check boxes without binding them to a Yes/No field in the actual data table. That would be a challenge in a multi-user application when users might overwrite each others selections in the shared table. The disconnected recordset neatly avoids such conflicts.

这篇关于访问连续表格:添加控件而不修改基础表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！