How can one set parameter values of an INSERT SQL query through VBA in Microsoft Access?

Question

I am new to Access and I am coming from C#, SQL Server & .Net. There is a project that has come my way and I have to complete some parts.

The scenario may be described as:

1. An Access form with a subform
2. An Access query that is the data source of the aforementioned subform, with two parameters, displayed as: Parametername1 String(255),Parametername2 String(255).
3. VBA code module(s)

My plan is to set the values of the above mentioned query parameters within a procedure in my VBA code module. I believe this should refresh my subform as the query is the datasource for the subform.

The problem is I don't know how to implement this plan.

I want to use a query because I don't want to mess up my VBA Code with inline SQL.

I am using Access 2010.

Answer 1

I had exactly this question, I wanted to use the same 'stored' update query but execute it from two different forms so wanted to pass the parameter to the query at run-time. This is what I found (in another forum) that does exactly what I want:

With CurrentDb.QueryDefs("qry_YourQuery")
   .Parameters("yourParam") = yourVBAvar
   .Execute
End With

Answer 2

The whole point of a subform is that it is controlled by the record source and the link child and master fields. Let us say the form is Company and the subform is Employees, the record source for the subform might be:

SELECT EmployeeID, CompanyID, Position, Etc FROM Employees

The link child and master fields would be CompanyID. As you moved through the master form, only those records relevant to the current company would be displayed. Let us say you then want to display only those employees who are in a technical position, you can change the record source at run time:

SELECT EmployeeID, CompanyID, Position, Etc FROM Employees
WHERE Position = "Technical"

Or if this is always to be a filter on the form, add a combobox, say, to the main form and use that as a second link master field, so you have:

Link Master Fields:  CompanyID; cboPosition
Link Child Fields :  CompanyID; Position

Finally, you can simply set the filter property from the main form :

Me.Employees_subform.Form.Filter = "Position=""Tecnical"""
Me.Employees_subform.Form.FilterOn = True

If this is not what you have in mind, please add some notes to your question.

EDIT

You can supply parameters to a query by referencing a control on a form:

SELECT EmployeeID, CompanyID, Position, Etc FROM Employees
WHERE Position = Forms!MyMainForm!cboPosition

You can completely change the SQL of a query and you can use ADO, however, changing the SQL is similar to setting the record source in that the SQL is changed in code and using ADO is usually not the best bet for forms.

What you cannot do is change a parameter and have it 'stick' with a form or subform.

For example:

DoCmd.SetParameter "@SomeID", "1"
' This works
DoCmd.OpenQuery ("Queryx")

' This will give a prompt for @SomeID and then run
Me.SomeSubform.Form.RecordSource = "Queryx"

Answer 3

You could create a function and use that function (instead of a regular parameter) in your form's record source query.

Public Function PositionParam(Optional ByVal P1 As Variant) As Variant
    Static varP As Variant

    If Not IsMissing(P1) Then
        If IsNull(P1) Then
            varP = Null
        Else
            varP = CStr(P1)
        End If
    ElseIf VarType(varP) = vbEmpty Then
        varP = Null
    End If
    PositionParam = varP
End Function

The record source query:

SELECT y.id_fld, y.another_fld, y.position_fld
FROM YourTable AS y
WHERE
       y.position_fld = PositionParam()
    OR PositionParam() Is Null
ORDER BY y.id_fld, y.another_fld;

Later when you want to display a different set of rows in the subform, change the value assigned to PositionParam() and Requery the subform.

PositionParam "technician"
Forms("YourForm").Requery