如何使用VBA在MS Access中添加新记录? [英] How to use VBA to add new record in MS Access?

问题描述

我正在使用绑定表格供用户更新有关新客户或现有客户的信息.现在，我在提交"按钮上使用添加新记录"宏(因为我不确定如何通过VBA添加或保存新记录).

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?

Form BeforeUpdate代码:

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

添加记录按钮:

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

推荐答案

为了使用VBA提交记录，请为按钮创建一个单击时事件事件，然后在该 Sub 运行以下命令:

In order to submit a record using VBA, create an On Click event for the button, and in that Sub run the following command:

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 (CustomerID, CustomerName, CustomerAddressLine1, City, Zip) values (txtCustomerID.Value, txtCustomerName.Value, txtCustomerAddressLine1.Value, txtCity.Value, txtZip.Value)"

End Sub

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

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 BeforeUpdate event.

此外，请勿在此方法中使用绑定形式.我不知道会有什么影响，但我不会尝试.Access非常适合入门，但是当您想做更复杂的事情时，仅使用VBA会更容易.

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.

这篇关于如何使用VBA在MS Access中添加新记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！