Adding records to table without using inline query in VBA/Access

Question

I am completely new to VBA. I have been told to add records to a table by using a form and a Save button and given some very basic instructions. While I have achieved this with inline query, I have been told to follow some strict methods like usage of QueryDef/QueryDefs and .Parameters.

So far I am trying a very basic project, just to grasp the concepts, but I am unable to add any record to an empty table. In case the table is not empty(I manually enter a record), whenever I click the Save button for saving newer records, the number of records added are somehow doubling with each instance. For example, when I Save for the 1st time, 1 record is added, 2nd time 2 records of the same type is added, 3rd time 4 are added and so on.

The table(tbl_test) has 2 fields --> ID(primary key), Source(Long Text) and Reg No (Number).

The query(qry_test) is made with the Append feature and I have been told to add expressions which makes the code like this -

INSERT INTO tbl_test ( Source, [Reg No] )
SELECT [strSource] AS Expr1, [lngRegNo] AS Expr2
FROM tbl_test;

The form has 2 fields for Source(txt_Source) and Reg No(txt_RegNo) which have blank Record Sources (Unbound). The Save button has the following Event Procedure -

Private Sub btn_save_Click()

Dim qdf As QueryDef

Set qdf = CurrentDb.QueryDefs("qry_test")

qdf.Parameters("strSource") = Me.txt_Source
qdf.Parameters("lngRegNo") = Me.txt_RegNo

qdf.Execute

End Sub

I have zero knowledge about VBA and would gladly accept ANY help. It would be great if I get any sort of source code that will explain to all the details about saving records from forms and editing them using these querydef, parameter and recordset stuff.

Matt Hall Matt Hall · Accepted Answer · 2015-11-20T17:00:17

Welcome to StackOverflow!

If you are using a form to collect data for records that are stored in a table, running a saved append query by QueryDefs to do this is, in my opinion, not the best method.

Whilst an append query does add new records to an existing table, I would tend to use an append query where I've got multiple established records that I want to add to an existing table.

If I'm setting up a data entry form that is designed to collect new data for new records 1-at-a-time, an append query doesn't really suit this purpose.

Instead, there are a number of features built in to the design of MS-Access forms to help collect data and save it to a table. This is because forms are very much intended to be set up so that users can interact with records from a table in a controlled, user-friendly way rather than interact directly with the table object itself.

The first and most important feature of a form in this context is probably the form's record source property. When you create a new form, go in to design view for the form and open the form's property sheet (F4 key). In the "Data" tab of the form's property sheet you'll find the record source property:

The record source essentially connects your form with a set of records, whether those be records in a table object, a query object or an sql query string.

In your case it would be better, in my opinion, to bind your tbl_Test table to your form by referring to it in your form's record source:

It will seem like nothing has happened to your form, but if you now open the "Add Existing Fields" panel (alt + F8), you'll notice that the fields associated with your tbl_Test table are available to you:

Drag them to the detail section of your form...

Then put your form in to Form View:

Essentially what you and your users are seeing is the first blank record in your tbl_Test, but displayed on a form instead.

Entering data in these fields on the form...

...will put that data in to the table we specified in the form's record source...

So hopefully you can see that setting the form's record source property to that of your table, is much cleaner than trying to get an append query to collect data from your form and deliver it your table.

At this point you're probably asking a few questions:

When I have filled in the fields for a record on my form, how do I save that record?

More can be said about this, but for brevity, I'd recommend using a command button for running some vba to save the record; similar to what you've done, but utilising the form's Dirty property instead using an append query:

Here's the click event VBA for my save button example:

Private Sub cmdSave_Click()

    If Me.Dirty Then

        Me.Dirty = False

    End If

End Sub

Me.Dirty is a Boolean (True or False) setting for the form; essentially it is automatically set to True when a user changes something on the form. To save those changes, the Me.Dirty setting will have to be set to False.

Me.Dirty is a bit like the swing gate on a sheep pen. When a shepherd puts a sheep (data) in the pen (form) they will open the gate to the pen. The open gate is like the form's Me.Dirty being set to True. To lock the sheep (data) in, the gate needs to be closed, or in the case of forms, the Me.Dirty property needs to be set to False. The VBA above essentially checks to see if the gate was opened and if it was to close it.

How do I move to a new record in the form once I have saved the current one?

Again, I'd give the user a command button to do this and run some VBA on its click event:

Here's the VBA for moving to a new record:

Private Sub cmdNew_Click()

    DoCmd.GoToRecord , , acNewRec

End Sub

Summary

There is a lot more to consider than what I've outlined here, such as:

validating data before it is saved
checking other form events (such as close) to ensure data entry is not lost
navigating to an existing record

But hopefully what I've given you here is at least pointing you in a better direction. Best of luck!

Adding records to table without using inline query in VBA/Access

3 Answers