Task: When opening a form, a value should be passed as start parameter.


Solution: Microsoft Access forms can be passed in parameters when opened.

The parameter is named OpenArgs in the vba parameters


Set OpenArgs in VBA

Passing takes place at the 7th parameter.

Private Sub tab_Change(ByVal sTab As String)

    '----< tab_Change >----

    DoCmd.OpenForm sTab, , , , , , "ID_Projekt=" & ctlID_Projekt

    DoCmd.Close acForm, Me.Name

    '----</ tab_Change >----

End Sub



Access: Open Form with OpenArgs


When opening a form

When opening the form, the OpenArgs parameters in the Me.OpenArgs property are available as Variant String.

Note: After the event Form_Open the Me.OpenArgs will be deleted.

Therefore you can transfer the OpenArgs to a global variable when opening it and continue to use it

sID_Projekt = Me.OpenArgs

Access: Open Form with OpenArgs


Option Compare Database

Option Explicit On


'=========================================< Formular >=========================================

Private Sub Form_Open(Cancel As Integer) '1.event


    '< init >

    Dim sID_Projekt As String

    sID_Projekt = get_OpenArg_by_Parameter(Me.OpenArgs, "ID_Projekt")

    '</ init >



    '< Neu Eingabe >

    If Me.DataEntry = True Then sID_Projekt = 0

    '</ Neu Eingabe >


    '< check >

    If sID_Projekt Like "" Then

        MsgBox "Es ist kein Projekt ausgewählt in der Liste", vbCritical, "Projekt in Liste auswählen"

        DoCmd.Close acForm, Me.Name

        Exit Sub

    End If

    '</ check >


    '< set >

    fg_Load_Recordset_byID sID_Projekt

    '</ set >


End Sub



Function for evaluating the OpenArgs variables.

The easiest way to read opening parameters is to use a function like this:

Value = Get_OpenArg_by_Paramter (parameter name).

sID_Projekt = get_OpenArg_by_Parameter(Me.OpenArgs, "ID_Projekt")


Then you just have to name the parameter and the value will be read back.

Public Function get_OpenArg_by_Parameter(ByVal varOpenArgs As Variant, ByVal sParameter As String) As String

    '--------------< get_OpenArg_by_Parameter() >--------------

    '------< OpenArgs >------

    '< read openArgs >

    'Dim varOpenargs As Object

    'varOpenargs = Me.OpenArgs

    '</ read openArgs >


    Dim sReturn

    sReturn = ""


    '------< Evaluate OpenArgs  >------

    If Not Nz(varOpenArgs, "") Like "" Then

        '-----< Has OpenArgs >-----

        '*like ID_Projekt=123;


        Dim intPos As Integer

        intPos = InStr(1, varOpenArgs, sParameter & "=", vbTextCompare)

        '----< find Paramenter >----

        If intPos > 0 Then

            '--< get Value >--

            Dim lenParameter As Integer

            lenParameter = Len(sParameter)

            intPos = intPos + lenParameter + 1



            If InStr(intPos, varOpenArgs, ";", vbTextCompare) > 0 Then

                sReturn = Mid(varOpenArgs, intPos, InStr(intPos, varOpenArgs, ";", vbBinaryCompare) - intPos)


                sReturn = Mid(varOpenArgs, intPos)


            End If

            '--</ get Value >--

        End If

        '----</ find Paramenter >----

        '------</ Has OpenArgs >------

    End If

    '------</ Evaluate OpenArgs  >------


    get_OpenArg_by_Parameter = sReturn

    '------</ OpenArgs >------

    '--------------</ get_OpenArg_by_Parameter() >--------------

End Function




Show form

Then the form can be loaded and displayed

To do this, you must reassign the RecordSource with an SQL string

Public Sub fg_Load_Recordset_byID(ByVal ID_Projekt As Long)

    '--------------------< fg_Load_Recordset() >--------------------

    Dim sSQL As String

    sSQL = "SELECT TOP 1 * FROM tbl_Projekte WHERE ID_Projekt=" & ID_Projekt

    Me.RecordSource = sSQL

    '--------------------</ fg_Load_Recordset() >--------------------

End Sub


Access: Open Form with OpenArgs

Software Entwicklung Stuttgart NĂ¼rtingen
Suche Projekte C#, WPF, Windows App,ASP.Net, vb.Net, WinForms, SQL Server, Access, Excel