如何使用 VBA 在 MS Access 中添加新记录？

How to use VBA to add new record in MS Access?

提问by New2This

I'm using bound forms for the user to update information on new or existing customers. Right now I'm using a Add New Record macro on the submit button (because I'm not sure how to add or save a new record through VBA).

我正在为用户使用绑定表单来更新有关新客户或现有客户的信息。现在我在提交按钮上使用添加新记录宏（因为我不确定如何通过 VBA 添加或保存新记录）。

I added a before update event (using VBA) to have the user confirm they want to save changes before exiting the form. For some reason this is overriding the add record button and now users cannot add new record until exiting the forms.

我添加了一个更新前事件（使用 VBA）让用户在退出表单之前确认他们想要保存更改。出于某种原因，这覆盖了添加记录按钮，现在用户在退出表单之前无法添加新记录。

How can I use VBA to add new customer information to the correct table? Is this something that should be done with macros instead?

如何使用 VBA 将新客户信息添加到正确的表中？这是应该用宏来完成的吗？

Form BeforeUpdate Code:

表格更新前代码：

Private Sub Form_BeforeUpdate(Cancel As Integer) 
Dim strmsg As String 
strmsg = "Data has been changed." 
strmsg = strmsg & " Save this record?" 
If MsgBox(strmsg, vbYesNo, "") = vbNo Then 
    DoCmd.RunCommand acCmdUndo 
Else 
End If 
End Sub

Add Record Button:

添加录制按钮：

Private Sub btnAddRecord_Click() 
Dim tblCustomers As DAO.Recordset 

Set tblCustomers = CurrentDb.OpenRecordset("SELECT * FROM [tblCustomers]") 
tblCustomers.AddNew 
tblCustomers![Customer_ID] = Me.txtCustomerID.Value 
tblCustomers![CustomerName] = Me.txtCustomerName.Value 
tblCustomers![CustomerAddressLine1] = Me.txtCustomerAddressLine1.Value 
tblCustomers![City] = Me.txtCity.Value 
tblCustomers![Zip] = Me.txtZip.Value 
tblCustomers.Update 
tblCustomers.Close 
Set tblCustomers = Nothing 
DoCmd.Close 
End Sub

采纳答案by Michael

In order to submit a record using VBA, create an On Clickevent for the button, and in that Subrun the following command:

为了使用 VBA 提交记录，请On Click为按钮创建一个事件，然后Sub运行以下命令：

Private Sub submitButton_Click()

'All the code to validate user input. Prompt user to make sure they want to submit form, etc.

DoCmd.RunSQL "INSERT INTO tblCustomers (txtCustomerID.Value, txtCustomerName.Value, txtCustomerAddressLine1.Value, txtCity.Value, txtZip.Value)"

End Sub

In this Sub, you can add all the code you want to validate the values that the user entered and choose whether or not you want to submit the record. There's a lot of control using VBA to submit your forms, so you do not need a BeforeUpdateevent.

在此 Sub 中，您可以添加要验证用户输入的值的所有代码，并选择是否要提交记录。使用 VBA 提交表单有很多控制，因此您不需要BeforeUpdate事件。

Also, do NOT use bound forms with this method. I don't know what the repercussions are but I wouldn't try it. Access is great for starting off, but as you want to do more complex things, it is easier to just use VBA.

此外，请勿在此方法中使用绑定表单。我不知道后果是什么，但我不会尝试。Access 非常适合刚开始使用，但是当您想要做更复杂的事情时，只使用 VBA 会更容易。

回答by technoman23

It seems strange that you would create a before update event for your form to create a prompt before closing. Perhaps you should try the on close event instead. If you want to use vba to add a new record from the form you can simplify your statement. I came across a similar situation when designing my own form for my Access DB. This code is tested and working:

您会为表单创建 before update 事件以在关闭之前创建提示，这似乎很奇怪。也许你应该尝试 on close 事件。如果您想使用 vba 从表单中添加新记录，您可以简化您的语句。在为我的 Access DB 设计自己的表单时，我遇到了类似的情况。此代码经过测试并正常工作：

Dim sVIN As String
Dim sMake As String
Dim sModel As String
Dim sColor As String
Dim sType As String
Dim intYear As Integer

sVIN = Me.txtVIN.Value
sMake = Me.txtMake.Value
sModel = Me.txtModel.Value
sColor = Me.txtColor.Value
sType = Me.comboType.SelText
intYear = Me.txtVehicleYear.Value

DoCmd.RunSQL "INSERT INTO Vehicles (VIN, Make, Model, VehicleYear, Color, Type) VALUES ('" & sVIN & "', '" & sMake & "', '" & sModel & "', " & intYear & ", '" & sColor & "', '" & sType & "')"

If you are just using one DB in your project and you're connecting on start up you can sometimes run simple DoCmd.RunSQL statements like this. You can take the syntax here and adapt it to your own project. I myself got the basic syntax from W3 schools. Good site for learning to write SQL queries. It should also be noted that validation testing is not included here. You should make sure it is included in the form validation rules or in vba code. One more thing... in your SQL it looks like you are attempting to assign a value to the ID column from a text box entry; if ID is an auto number column, don't do this. ID columns are usually assigned a number automatically, so you don't need to (or want to) specify an insert value for that.

如果您只在项目中使用一个 DB 并且您在启动时连接，您有时可以像这样运行简单的 DoCmd.RunSQL 语句。您可以采用此处的语法并将其调整到您自己的项目中。我自己从 W3 学校获得了基本语法。学习编写 SQL 查询的好网站。还应注意，此处不包括验证测试。您应该确保它包含在表单验证规则或 vba 代码中。还有一件事……在您的 SQL 中，您似乎试图从文本框条目中为 ID 列分配一个值；如果 ID 是自动编号列，请不要这样做。ID 列通常会自动分配一个编号，因此您无需（或不想）为此指定插入值。

回答by Krzychu C

As a side note: I noticed that adding records via VBA functionality(as in your "Add Record Button" code) works ~400 times faster than using DoCmd.Run SQL "INSERT INTO...": ~55k records/s compared to 140 records/s for a table with 5 columns(ID+4 short strings).

附带说明：我注意到通过 VBA 功能添加记录（如在您的“添加记录按钮”代码中）比使用 DoCmd.Run SQL "INSERT INTO..." 快约 400 倍：约 55k 记录/秒相比5 列（ID + 4 个短字符串）的表为 140 条记录/秒。

This has no practical meaning in terms of a form, where data is entered manually but if the form is generating more records, this saves a lot of time.

这对于手动输入数据的表单没有实际意义，但如果表单生成更多记录，则可以节省大量时间。