Geek asks and answers

How to set SQL INSERT query parameter values ​​through VBA in Microsoft Access?

I am new to Access and I come from C #, SQL Server and .Net. There is a project that came to my mind, and I need to fill in some parts.

The scenario can be described as:

  • Access form with subordinate form
  • Access request, the source data subforms above, with two parameters displayed as Parametername1 String(255),Parametername2 String(255).
  • VBA code module (s)

My plan is to set the values ​​of the above query parameters in a procedure in my VBA code module. I believe this should update my subform, as the request is the data source for the subform.

The problem is that I do not know how to implement this plan.

I want to use a query because I do not want to spoil VBA code with embedded SQL.

I am using Access 2010.

+5
source share
4 answers

I had exactly this question, I wanted to use the same "saved" update request, but execute it from two different forms, so I wanted to pass this parameter at runtime. 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
+6
source

The whole point of the subform is that it is controlled by the record source and the child and main fields of the link. Let's say a form is a company, and a subform is Employees, the source of the record for the subform can be:

SELECT EmployeeID, CompanyID, Position, Etc FROM Employees

CompanyID. , , . , , , :

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

, , combobox, , -, :

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

, :

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

, , .

, :

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

SQL , ADO, SQL , SQL ADO .

, , .

:

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

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

( ) .

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

:

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;

, , , PositionParam() Requery .

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

.

, , SQL sql, WHACKS , , ?

, , . , .

- , :

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

, - sql, , . , , 2 , , 2 ( ).

Therefore, leave the parameters outside the request and the same money and time and paid hours.

The easiest way is to complete the SQL query as described above and add criteria for SQL. However, if you want to insert data into a subformat, you can directly use the subform data source and reocrdset. 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
0
source

More articles:


All Articles