How to reference a combo box in a SQL statement and run the statement in VBA

Question

I am attempting to let users use a form to insert data into the table Inventory2Locaton in Microsoft access. They will select data from two combo boxes and then press an add button to add the data to the table.

The query I use is

INSERT INTO Inventory2Location
VALUES (ComboPart.value, ComboOver.value);

but it won't actually pull ComboPart.value or ComboOver.value from the combo boxes.

I also cannot figure out how to run this query by clicking the button. I have the module with

Private Sub CommandAdd_Click()

but don't know what VBA code will run the query.

I am quite familiar with VBA from excel experience and have taken a class on SQL but I am very new to access and don't understand how to put the two together.

DHW DHW · Accepted Answer · 2016-12-17T22:38:17

I would offer that a better solution would be to set the Form's "Record Source" property to Inventory2Location. Then, on the Data tab of the Form Properties, set "Data Entry" to "Yes". This will make it so the form can only add records to the table.

Next, for each control that represents a value in the table you want the user to add, go into that control's properties and set the Control Source to the field you want updated.

Then, on the Event tab of the "Save" button, remove [Event Procedure] and click the ... button. Select Macro Builder. This will create an embedded Macro in the form. The macro is simply two commands. One to save the current record and the other to close the form.

RunMenuCommand
       Command  SaveRecord
CloseWindow
   Object Type  Form
   Object Name  =[Screen].[ActiveForm].Name
          Save  Prompt

Be sure to put the equals sign in front of [Screen].

The benefits of this approach are:

If an error occurs due to duplicate keys or bad input, Access will take care of notifying the user of the problem. Using VBA you would need to trap for those type of errors.
By using the Embedded macro, you should be able to outright eliminate a code module attached to the form (Turn Has Module to No in the Other tab for the form's properties). This turns it into a lightweight form which means it runs quicker and does not suffer from the usual code corruption that creeps into Access.
Lastly, you should be able to run the form without elevated trust settings granted to the database. So then you don't need to worry about people clicking the "Enable" button in order for the form to work properly.

How to reference a combo box in a SQL statement and run the statement in VBA

2 Answers