如何从要交互式选择的 VBA 表单中编辑记录？

How do you edit records from a VBA form that you want to interactively select?

提问by Tim Visher

I have a set of ComboBox's in an MS Access 2003 DB that are all bound to fields in a single table. However, the data that they allow you to select doesn't come from that table and instead comes from various other tables. This works fine for the record creation story but now I want to be able to edit the record retroactively. The problem is that I can't figure out how to refill the form elements without writing a bunch of custom code.

我在 MS Access 2003 DB 中有一组 ComboBox，它们都绑定到单个表中的字段。但是，它们允许您选择的数据并非来自该表，而是来自各种其他表。这适用于记录创建故事，但现在我希望能够追溯编辑记录。问题是我不知道如何在不编写一堆自定义代码的情况下重新填充表单元素。

My initial inclination is to provide a combo box that limits your choices to record IDs and then do a custom query and use that to set the selected values in all of different form elements. However, I feel like I should be able to do something as simple as DoCmd.GoToRecord , , , IDand the form should repopulate just fine. I'm not opposed to doing the busy work but I'm sure I'm just missing something in my relatively puny knowledge of VBA and Access.

我最初的倾向是提供一个组合框，将您的选择限制为记录 ID，然后进行自定义查询并使用它来设置所有不同表单元素中的选定值。但是，我觉得我应该能够做一些简单的事情，DoCmd.GoToRecord , , , ID并且表单应该重新填充就好了。我并不反对做忙碌的工作，但我确信我只是在我对 VBA 和 Access 的相对微不足道的知识中遗漏了一些东西。

回答by David-W-Fenton

Just to add to the mix, I would offer two approaches, one recommended, the other not.

只是为了增加组合，我会提供两种方法，一种推荐，另一种不推荐。

Approach 1: If you've bound your form to the whole data table (this is the non-recommended approach), you can use the combo box wizard to navigate to the requested record, but I wouldn't recommend it in recent versions of Access:

方法 1：如果您已将表单绑定到整个数据表（这是不推荐的方法），您可以使用组合框向导导航到请求的记录，但我不建议在最近版本的使用权：

a. it doesn't allow you to properly name the combo box before it creates code.

一种。它不允许您在创建代码之前正确命名组合框。

b. the code is just WRONG.

湾 代码是错误的。

Here's the code I just produced in my test database:

这是我刚刚在我的测试数据库中生成的代码：

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[InventoryID] = " & Str(Nz(Me![Combo2], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

This is wrong in so many ways it's just remarkable. This is what the code should be:

这在很多方面都是错误的，这非常了不起。代码应该是这样的：

With Me.RecordsetClone
  .FindFirst "[ID]=" & Me!cmbMyComboBox
  If Not .NoMatch Then
     If Me.Dirty Then Me.Dirty = False
     Me.Bookmark = .Bookmark
  Else
     MsgBox "Not Found!"
  End If
End With

There is no need to clone the form's recordset when the RecordsetClone already exists.

当 RecordsetClone 已经存在时，不需要克隆表单的记录集。

There is no reason to use an object variable when you can just directly use the pre-existing object.

当您可以直接使用预先存在的对象时，没有理由使用对象变量。

There needs to be a check for a dirty record before departing the record because if you don't force the save, errors in the save process can lead to lost data.

在离开记录之前需要检查脏记录，因为如果不强制保存，保存过程中的错误可能会导致数据丢失。

But the better approach is this:

但更好的方法是这样的：

Approach 2: Use the combo box to change the form's underlying recordsource.

方法 2：使用组合框更改表单的基础记录源。

The AfterUpdate event of your combo box would look something like this:

组合框的 AfterUpdate 事件如下所示：

If Not IsNull(Me!cmbMyComboBox) Then
   Me.Recordsource = Me.Recordsource & " WHERE [ID]=" & Me!cmbMyComboBox
End If

Now, this only works the first time, as on the second resetting of the Recordsource, you end up with two WHERE clauses, which is not good. There are two approaches:

现在，这只适用于第一次，因为在第二次重置 Recordsource 时，您最终会得到两个 WHERE 子句，这并不好。有两种方法：

a. assuming that the form opens without a WHERE clause, store the opening recordsource value in a module-level variable in the form's OnLoad event:

一种。假设表单打开时没有 WHERE 子句，将打开的记录源值存储在表单的 OnLoad 事件中的模块级变量中：

   Private Sub Form_Load()
     strRecordsource = Left(Me.Recordsource,Len(Me.Recordsource)-1)
   End Sub

And at the module level, define strRecordsource accordingly:

在模块级别，相应地定义 strRecordsource：

   Dim strRecordsource As String

Then in the combo box's AfterUpdate event, you have this instead:

然后在组合框的 AfterUpdate 事件中，您可以改为：

   Me.Recordsource = strRecordsource & " WHERE [ID]=" & Me!cmbMyComboBox

Now, if your form opens with a WHERE clause already defined, it gets more complicated, but I'll not go into that and leave it as an exercise to the reader what the best approach might be.

现在，如果您的表单打开时已经定义了一个 WHERE 子句，它会变得更加复杂，但我不会深入讨论，而是将其作为练习留给读者，最好的方法可能是什么。

回答by BIBD

I presume that you've already set up the row sources for each combo box. So long as you haven't limited the combo box to that list; it should display what you have stored in that column.

我假设您已经为每个组合框设置了行源。只要您没有将组合框限制在该列表中；它应该显示您在该列中存储的内容。

However, if your Combo Box changes its list for each row you can do something like this in the record's OnCurrent event or the field's GotFocus event:

但是，如果您的组合框更改了每一行的列表，您可以在记录的 OnCurrent 事件或字段的 GotFocus 事件中执行以下操作：

Me.combo_box_name.Requery 

回答by Mike Spross

After re-reading your question, I think I see what you are trying to achieve. You're on the right track with GotoRecord, although I would probably use OpenFormin this case, because it has a WhereConditionproperty that allows you to use SQL to specify exactly what record to open. It sounds like you want to implement a "jump to record" type functionality in your form, where the user selects a record ID from a list and the form changes to display the selected record.

重新阅读您的问题后，我想我明白您想要达到的目标。GotoRecord虽然我可能会OpenForm在这种情况下使用 ，但您在正确的轨道上，因为它具有WhereCondition允许您使用 SQL 来准确指定要打开的记录的属性。听起来您想在表单中实现“跳转到记录”类型的功能，用户从列表中选择一个记录 ID，然后表单更改以显示所选记录。

One possibility is to switch to the new record each time the user selects an item in the ComboBox. You can handle this in the ComboBox's Clickevent.

一种可能性是每次用户选择 ComboBox 中的项目时切换到新记录。您可以在 ComboBox 的Click事件中处理此问题。

I'll use a simple example: suppose you have a Studentstable, and a StudentFormfor viewing/editing records in the Studentstable. The StudentFormhas a ComboBox cboStudentIDthat is bound to the Students.IDcolumn via it's RowSourceproperty. When you select a student ID in the ComboBox, the StudentsFormwill switch to display the corresponding student record.

我将使用一个简单的示例：假设您有一个Students表，并且有一个StudentForm用于查看/编辑Students表中的记录。该StudentForm有一个ComboBoxcboStudentID绑定到Students.ID通过它的列RowSource属性。当您在 ComboBox 中选择一个学生 ID 时，StudentsForm将切换到显示相应的学生记录。

In the Clickevent handler for the ComboBox, you can code this "jump to record" functionality with something like the following:

在ClickComboBox的事件处理程序中，您可以使用如下代码编写此“跳转到记录”功能：

Private Sub cboStudentID_Click() 
    Dim recordID As Long
    'The ItemData property will return the value of the bound'
    'column at the specified index.'
    recordID = cboStudentID.ItemData(cboStudentID.ListIndex)
    'Jump to the record. This assumes we want to use the same form.'
    'You can change the form name if you want to open a different form when'
    'the user selects an ID from the ComboBox.'
    DoCmd.OpenForm "StudentForm", WhereCondition:="Student.ID=" & recordID
End Sub

As David W. Fenton points out in the comments, you can shorten the following line:

正如 David W. Fenton 在评论中指出的那样，您可以缩短以下行：

recordID = cboStudentID.ItemData(cboStudentID.ListIndex)

to this:

对此：

recordID = Me!cboStudentID

or just:

要不就：

recordID = cboStudentID

since the default value of the ComboBox in this case will be the value of the bound column at the current ListIndex. In this case, you could just remove recordIDaltogether and code the Clickevent as follows:

因为在这种情况下 ComboBox 的默认值将是当前ListIndex. 在这种情况下，您可以完全删除并按如下方式recordID对Click事件进行编码：

Private Sub cboStudentID_Click() 
    DoCmd.OpenForm "StudentForm", WhereCondition:="Student.ID=" & cboStudentID
End Sub