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

Answer 4

The sub form is bound to a query and that query should not have any parameters at all.

You can certainly store the SQL for the sub form in a query and that eliminates the in-line sql but then turning around and writing WHACKS of code to get around this issue makes no sense at all does it?

As noted the filtering and display of sub form recodes is automatic and occurs without code if you setup the master link and child settings of the sub from control. You likely do NOT need ANY code here.

However if for some real reason you need to write code or want more billable hours then you can use this code:

Dim strSql     As String

strSql = Split(CurrentDb.QueryDefs("name of query").SQL, ";")(0)

strSql = strSql & "where Field1 = " & "'your p1 value'" & _
                  " and Field2 = " & "' your p2 value'"

Me.custChild.Form.RecordSource = strSql

So you do not need any parameters in the sql but just use the CURRENT and SAME query the sub form is based on. Besides if you add parameters then you are hard coding the 2 values in the query and then the query cannot be used for other code and tasks unless you provide the 2 SAME hard coded parameters (so this give little if any flexibility in using that query elsewhere with differnt choices).

So leave the parameters out of the query and same money and time and billable hours.

It is a simple matter to pull the query SQL as per above and add criteria to the SQL. However, if you looking to insert data to the sub form, then you can use the subform data source and reocrdset directly. eg:

  Dim rst     As DAO.Recordset

  Set rst = Me.custChild.Form.RecordsetClone

  rst.AddNew
  rst!SomeField = "some valjue"
  rst!SomeField2 = "some value"
  rst.Update

  Me.custChild.Requery