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

8
votes

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).

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.

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 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
3
vbaformsms-accessbutton
I'm pretty confused. Adding a record to a bound form should be as easy as clicking the new record nav button in the bottom pane of the form (right arrow with asterisk). Also, does your BeforeUpdate routine utilize the .OldValue property? That's the only way I know to uncommit a bound field, as the edits are saved to the table as soon as you exit a control. So without .OldValue you are triggering your routine too late, and would probably need to be added to the BeforeUpdate event of each control, not on the form. Post the code for your submit button and beforeupdate event to clarify. – MoondogsMaDawg

3 Answers

7
votes

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

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.

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.

1
votes

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:

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.

0
votes

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).

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.