在 MS Access 2010 中使用 VBA 设置表格中的单元格值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20768924/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using VBA in MS Access 2010 to set cell value in table
提问by user1560289
I am trying to dynamically update a table cell value in a metadata table, which is to hold the names of 5 existing forms. This table is used to set the .Captionproperty of 5 respective buttons on frmMainMenu, where each button will in turn open the proper table.
我正在尝试动态更新元数据表中的表单元格值，该表用于保存 5 个现有表单的名称。此表用于设置frmMainMenu上 5 个相应按钮的.Caption属性，其中每个按钮将依次打开正确的表。
If the user renames one of the 5 forms, the Captionfield (field's name is Caption) for the appropriate record in MainMenu metadata table must automatically update, so the frmMainMenu can then pull the form's new name and populate the button name with the new form name.
如果用户重命名 5 个表单之一，则MainMenu 元数据表中相应记录的Caption字段（字段名称为 Caption）必须自动更新，因此 frmMainMenu 可以拉取表单的新名称并使用新表单填充按钮名称姓名。
Current form names: frmBorrower frmCDMedia, frmOrders, frmQuickSearchBorrower, frmQuickSearchMedia, frmMainMenu
当前表单名称： frmBorrower frmCDMedia、frmOrders、frmQuickSearchBorrower、frmQuickSearchMedia、frmMainMenu
Each form except frmMainMenuhas own class module, which assigns the form's name to a Public Variable formPopA, formPopB ...formPopEusing following code:
除了frmMainMenu之外的每个表单都有自己的类模块，它使用以下代码将表单的名称分配给一个公共变量formPopA、formPopB ...formPopE：
Public Function MyNameA()
Form_frmMainMenu.formPopA = Me.Name
End Function
In frmMainMenuI have the following:
在frmMainMenu我有以下内容：
Public formPopA As String
Public formPopB As String
Public formPopC As String
Public formPopD As String
Public formPopE As String
Private Sub Form_Load()
NamePop.PopulateTablesWithFormNames
SetCaptions
End Sub
Private Sub SetCaptions()
mainVars.mainVarsi
End Sub
I then have 2 standard modules, MainVars, which assigns the value in the MainMenu table to the appropriate button in frmMainMenu(this is successful), and then NamePop, which is supposed to set the values of the table cells using user defined PopulateTablesWithFormNames().
然后我有2个标准模块，MainVars，这在MainMenu的表分配的值，以在适当的按钮frmMainMenu（这是成功的），然后NamePop，这是应该设置使用用户定义的表格中的值PopulateTablesWithFormNames（）.
My problem code (I think) is in NamePop, where I cannot find a way to make PopulateTablesWithFormNamesto assign the appropriate form name.
我的问题代码（我认为）在NamePop 中，在那里我找不到让PopulateTablesWithFormNames分配适当的表单名称的方法。
I have tried this:
我试过这个：
Dim x As DAO.Database
Dim myX As DAO.Recordset
Set x = CurrentDb()
Set myX = x.OpenRecordset("MainMenu", dbOpenTable)
myX.FindFirst "ID = 1"
myX![Caption] = Form_frmMainMenu.formPopA
myX.Update
I understand there are significant differences between DAO, ADO and Access objects, but not how they work differently. I created all current objects with commands on the ribbon, however that makes a difference. Experience level with VBA novice.
我知道 DAO、ADO 和 Access 对象之间存在显着差异，但不知道它们的工作方式有何不同。我使用功能区上的命令创建了所有当前对象，但这有所不同。VBA 新手的经验水平。
回答by Gord Thompson
There are at least two problems with your existing code:
您现有的代码至少存在两个问题：
First, you cannot perform .FindFirston a Recordset that has been opened as dbOpenTable. If you try you'll get the error "Operation is not supported for this type of object.". You need to open the Recordset as dbOpenDynaset.
首先，您不能.FindFirst对已作为dbOpenTable. 如果您尝试，您将收到错误“此类型的对象不支持操作。”。您需要将记录集打开为dbOpenDynaset.
Second, with DAO you must invoke the .Editmethod before trying to modify a record in the Recordset. If you omit that step then when you try to .Updateyou'll get the error "Update or CancelUpdate without AddNew or Edit.".
其次，对于 DAO，您必须.Edit在尝试修改 Recordset 中的记录之前调用该方法。如果您省略该步骤，那么当您尝试执行此操作时，.Update您将收到错误“更新或取消更新而不使用 AddNew 或 Edit。”。
Try this code instead:
试试这个代码：
Dim x As DAO.Database
Dim myX As DAO.Recordset
Set x = CurrentDb()
Set myX = x.OpenRecordset("MainMenu", dbOpenDynaset)
myX.FindFirst "ID = 1"
myX.Edit
myX![Caption] = Form_frmMainMenu.formPopA
myX.Update
