asked on

OpenArgs - add 3rd criteria

Experts,

I have an OpenArgs statement with 2 criteria
but I need to add a 3rd: Nz(Form_subfrmProjects_Extended_List.Batch_No, "") .....Its text
I have been trying to do this but its out of my skillset.

Thank you for your kindness.

Dim MyArgs As String

MyArgs = Nz(Form_subfrmProjects_Extended_List.Buy_CP, "")
MyArgs = MyArgs & ";"
MyArgs = MyArgs & Nz(Form_subfrmProjects_Extended_List.Trade_No, "")

    DoCmd.OpenForm "frmLetterOfCredit", , , , , , MyArgs

Open in new window

then on the On Load of the form:

 Dim ArgsSplit() As String 'array to hold the string we've sent in
    If Nz(Me.OpenArgs, "") = "" Then
         Exit Sub 'no arguments were sent in so bail
    End If
    ArgsSplit() = Split(Me.OpenArgs, ";") 'two values, positions 0 and 1 holding strings you want to use
    If ArgsSplit(0) <> "" Then 'there was a first argument, Chr 34 is code for double quotes
        Me.Filter = "[Buy_CP] = " & Chr(34) & ArgsSplit(0) & Chr(34)
    End If
    If ArgsSplit(0) <> "" And ArgsSplit(1) <> "" Then 'two arguments
         Me.Filter = Me.Filter & " AND [Trade_No] = " & Chr(34) & ArgsSplit(1) & Chr(34) 'add on the second WHERE condition
    End If
    If ArgsSplit(0) = "" And ArgsSplit(1) <> "" Then 'only second arguments
         Me.Filter = "[Trade_No] = " & Chr(34) & ArgsSplit(1) & Chr(34) 'Just add the second WHERE condition
    End If

    Me.FilterOn = True

Open in new window

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pdvsa

ASKER

Thank you Ryan. I will test when I am at my computer.

Fabrice Lambert

What about a generic approach ?

Format your OpenArgs in pairs key/value separated by an ampersand.
parse it with the help of a regular expression, and return a collection of arguments (just like how web query string work).

Sample code:

Option Explicit 
 
Public Sub test() 
    Dim arguments As Collection 
    Set arguments = Parse("name=value&name2=value2&name3=value3") 
     
    Debug.Print arguments("name") 
    Debug.Print arguments("name2") 
    Debug.Print arguments("name3") 
End Sub 
 
Public Function Parse(ByVal QueryString As String) As Collection 
    Dim KeyValues As Collection 
    Set KeyValues = New Collection 
     
    Dim rx As VBScript_RegExp_55.RegExp 
    Set rx = CreateObject("VBScript.RegExp") 
    rx.Global = True 
    rx.IgnoreCase = False 
    rx.Pattern = "([^=]+)=([^&]+)&?" 
     
    Dim matchs As VBScript_RegExp_55.MatchCollection 
    Set matchs = rx.Execute(QueryString) 
     
    Dim match As VBScript_RegExp_55.match 
    For Each match In matchs 
        KeyValues.Add match.SubMatches(1), Key:=match.SubMatches(0) 
    Next 
    Set Parse = KeyValues 
End Function

Open in new window

pdvsa

ASKER

Hi Fabrice, thank you for your response. Would I need to change the name, name2, name3 to their respective control source? Rows 5-9

Fabrice Lambert

Of course.

Plus, you can check for the existance of an item in the collection with another generic function:

    '// check if an item exist in a collecion 
Public Function ExistInCollection(ByVal key As String, ByRef col As Object) As Boolean 
    ExistInCollection = ExistInCollectionByVal(key, col) Or ExistInCollectionByRef(key, col) 
End Function 
 
    '// if the item is a value 
Private Function ExistInCollectionByVal(ByVal key As String, ByRef col As Object) As Boolean 
On Error GoTo Error 
    Dim item As Variant 
    item = col(key) 
    ExistInCollection = True 
Exit Function 
Error: 
    ExistInCollectionByVal = False 
End Function 
 
    '// if the item is a reference 
Private Function ExistInCollectionByRef(ByVal key As String, ByRef col As Object) As Boolean 
On Error GoTo Error 
    Dim item As Variant 
    Set item = col(key) 
    ExistInCollectionByRef = True 
Exit Function 
Error: 
    ExistInCollectionByRef = False 
End Function

Open in new window

Dale Fye

I've generally moved away from passing more than the name of the calling object when passing an argument using the OpenForm or OpenReport.

Instead, I use the Tempvars collection to store these values. Read more about tempvars and other methods of handling global variables in my article on "Using 'global' variables in MS Access"

Fabrice Lambert

Erm ..... let's stay away from global stuffs as much as possible.

Global variables must be exceptional.
Giving parameters to a form is not exceptional.

pdvsa

ASKER

Ryan, that is what I needed. I even added a 4th criteria.

Fabrice, I will keep that code. I have never seen anything like it and was a little complicated for me to learn how to modify it like I did with Ryan's code (added a 4th criteria). I also am using a Before Insert code on the same form where the On Load is placed and I wasnt too sure how to modify the Before Insert code. Thanks for your response.

Thanks for the article Dale!