Avatar of pdvsa
pdvsa
Flag for United States of America asked on

OpenArgs - add 2nd criteria

Hello,

I have a form with a button on it to open another form based on 1 criteria for OpenArgs.
I need to add another criteria [Trade_No]....its a string. I am not sure if being a string is my problem.

I get a runtime error 3075 (syntax) and the debugger highlights the code in the form that  is opening.  I hover over [Buy_CP] and the value is correct but [Trade_No] is also the same value as [Buy_CP] and that is not accurate.
Below is the error::

RuntimeError
I have the following in the form that has the button:

Private Sub btnIssueLC2_Click()

      DoCmd.OpenForm "frmLetterOfCredit", acFormDS, , , , , Form_subfrmProjects_Extended_List.Buy_CP & ";" & Me.Trade_No & ";"
    
End Sub

Open in new window


In the form that opens:

Private Sub Form_Load()

       If Nz(Me.OpenArgs, "") <> "" Then
       
            Me.Filter = "[Buy_CP] = " & Me.OpenArgs & " AND [Trade_No] = " & Split(Me.OpenArgs, ";")(1)
            Me.FilterOn = True
       
           Else
     
     End If
End Sub 

Open in new window



if I missed something let me know....Thank you
Microsoft Access

Avatar of undefined
Last Comment
Nick67

8/22/2022 - Mon
Daniel Pineault

Based on the error message, Me.Trade_No doesn't seem to hold any value?  Have you validated (Debug.Print, Breakpoint) that a value is being returned?


For strings, you need to surround the value with single or double quotes.


Also, I'm assuming you forgot to adjust your initial Filter criteria, shouldn't it be 

Me.Filter = "[Buy_CP] = " & Split(Me.OpenArgs, ";")(0) & " AND [Trade_No] = '" & Split(Me.OpenArgs, ";")(1) & "'"

Open in new window

But you should probably harden your code to first check if the Split() are returning actual values before using them to filter.


Lastly, I find it helpful to build the query criteria within a query, switch to SQL View and then I have the proper syntax to replicate in my VBA code.

pdvsa

ASKER

I am guessing but if Buy_CP is a string not sure if code needs to be modified. 

After implementing the code above, I get a msgbox asking for the value of Buy_CP.  

It is returning a value though.  You  can see in the pic above that the value is Chevron.  There is a value for Trade_No as well.

John Tsioumpris

OpenArgs...just passes a "value"...what this value is...its up to you.
Sometimes i also have the need to pass 2 or more values
In that case i construct a special String ( i use "|" for delimiting
Dim strOpenArgsValues as String
strOpenArgsValues = vbNullString
'Now for the values you want to "include" .e.g 
strOpenArgsValues =strOpenArgsValues  & SomeValue1 & "|"
strOpenArgsValues =strOpenArgsValues  & SomeValue2 & 

Open in new window

So you pass the concatenated multivalues
Docmd....,strOpenArgsValues 

Open in new window

and on the form that "receives" the OpenArgs i perform a split
Dim splittedOpenArgs() as String
splittedOpenArgs = split(Me.OpenArgs,"|")
i leave the rest for you ...:)

Open in new window

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Fabrice Lambert

OpenArgs is a string, and you can pass whatever you want, but:
You'll need to take care of parsing the string.
You'll need to take special care of Null values because VBA implicitly convert Null values to empty strings, and this is not the same thing.

Mark Edwards

Test have shown that OpenArgs can be a null if you pass a Null for an OpenArgs value.
OpenArgs-Can-Be-A-Null.PNGBe sure you code to handle Nulls.  Any other data type passed comes across as a string.
Nick67

OK.

You are using your OpenArgs to build a filter

A filter is the WHERE clause of a query without the word 'where' stuck in front of it

Your error says [BuyCP] = Chevron

Chevron being a string means you clearly have some syntax misbehaving as what it need to be is

[BuyCP] = "Chevron"

Then you have the problem of those extra couple of semi-colons

[BuyCP] = Chevron;;

as they were delimiters in your string and should have gone away

Finally you have AND [Trade_No] = and then nothing, so you have some null/blank/error handling issues


So let's take your Open command 

Private Sub btnIssueLC2_Click()

      DoCmd.OpenForm "frmLetterOfCredit", acFormDS, , , , , Form_subfrmProjects_Extended_List.Buy_CP & ";" & Me.Trade_No & ";"
    
End Sub

Open in new window

and fix it to handle both issues.

Some guys like lots and lots of "'""""""" when handling strings.

Me,  not so much since it makes code hard to read (i.e. how many single and double-quotes are in my little string there?)

Chr(34) is a double quote.  We're going to wrap Form_subfrmProjects_Extended_List.Buy_CP in double quotes

Chr(34) & Form_subfrmProjects_Extended_List.Buy_CP & Chr(34)

That will result in [BuyCP] = "Chevron" in the filter, just like we want


Then we have Me.Trade_No.

What happens when that is blank or null?

From your sample the other day, I can see it is a string, so we need to wrap it in double-quotes, too

Chr(34) & Me.Trade_No & Chr(34)

Now we have a slight third problem

You built a semi-colon delimited string -- but you ONLY need semi-colons between items and you have tacked on a trailing one.  I think that causes problems.  So, we get

Private Sub btnIssueLC2_Click()

      DoCmd.OpenForm "frmLetterOfCredit", acFormDS, , , , , chr(34) & Form_subfrmProjects_Extended_List.Buy_CP & Chr(34) & ";" & Chr(34) Me.Trade_No & Chr(34)
   
End Sub

Open in new window

That's one way BUT you still have nulls and blanks to handle--so let's do this instead

Private Sub btnIssueLC2_Click()

      DoCmd.OpenForm "frmLetterOfCredit", acFormDS, , , , , Nz(Form_subfrmProjects_Extended_List.Buy_CP,"") & ";"  Nz(Me.Trade_No,"")
   
End Sub

Open in new window

That'll clean up the nulls and blanks.

Now to build your filter

Private Sub Form_Load()
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


'if we got here we have an argument to work with
'be nice to yourself and just split it -- then you can for...next the array if things get wild to see what is happening
ArgsSplit() = Split(Me.OpenArgs, ";") 'two values, positions 0 and 1 holding strings you want to use


'Now deal with the possibilities
if ArgsSplit(0) <> "" then 'there was a first argument      
            Me.Filter = "[Buy_CP] = " & Chr(34) &  ArgsSplit(0) & "  & Chr(34)
end if
'Now, was there a first AND second argument?
if ArgsSplit(0) <> "" AND if 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


'Ok if there was no first argument, we have nothing in the filter yet, so
if ArgsSplit(0) = "" AND if ArgsSplit(1) <> "" then 'only second arguments
    Me.Filter = "[Trade_No] = " & Chr(34) & ArgsSplit(1) & Chr(34) 'Just add the second WHERE condition
End if


'there.  Two possibilities renders 4 cases and they are handled.
'1 Bail on no arguments
'2 Check for the first and add it if it exists
'3 check for the second and tack it on if it exists
'4 check for only a second and build it only

'if we got here, turn on the filter
Me.FilterOn = True


'and done
End Sub 

Open in new window



⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
pdvsa

ASKER

Nick, your answers wash over me like an awesome wave of relief. I will indeed test this when I am back at the computer. I am looking forward to that time. Have a good night...or i wish you a good day!  

Mark Edwards

I find it easier to work with an example.  Take a look at the attached db.  You can play with it and alter the openargs on Form1 to see what you get as a filter on Form2.  There are plenty of comments to explain what's going on.  

One thing you need to figure out is what filter you want if the filter you pass is blank:  filter for the field Null, or no filter at all.
MultipleOpenArgs.accdb
Mark Edwards

Since the form getting the openargs (Form2 in this case) knows what the data type is for the fields being filtered, you can wait to apply any quote or # sign characters when you build the filter.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Nick67

Oops.

Some sloppiness in copy/paste

I got a few misplaced 'ifs' in the code


 

Private Sub Form_Load() 
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 
'if we got here we have an argument to work with
'be nice to yourself and just split it -- then you can for...next the array if things get wild to see what is happening
ArgsSplit() = Split(Me.OpenArgs, ";") 'two values, positions 0 and 1 holding strings you want to use 
'Now deal with the possibilities
if ArgsSplit(0) <> "" then 'there was a first argument              
    Me.Filter = "[Buy_CP] = " & Chr(34) &  ArgsSplit(0) & "  & Chr(34)
end if 
'Now, was there a first AND second argument?
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 
'Ok if there was no first argument, we have nothing in the filter yet, so
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
 'there.  Two possibilities renders 4 cases and they are handled.
'1 Bail on no arguments
'2 Check for the first and add it if it exists
'3 check for the second and tack it on if it exists
'4 check for only a second and build it only 
'if we got here, turn on the filter
Me.FilterOn = True 
'and done 
End Sub

Open in new window


pdvsa

ASKER

Hi Nick, thank you much for the code.  I do have a syntax on the form load.  Do you see where it might be?  I have attached a screen print.

 

Mark,

Really neat.  I want to make this work but it didn’t seem to filter.  I am not exactly sure if I am to change the code that  is under the button on form1 that executes to open up form2.  I tried leaving the default code on form1 (I left FilterString1 and FilterString2 as is) and  then I also changed FilterString1 and FilterString2 to [Buy_CP] and [Trade_No]…with and without the brackets.

On the form2 Load event I just simply changed where it referenced [Field1] and [Field2] to [Buy_CP] and [Trade_No].  I don’t get any errors…but the form that opened did not filter (all records were shown).

 

I also added a txtFilter on the form that opens and it populates with:

WHERE [Buy_CP]='FilterString1' AND [Trade_No]='FilterString2'….but not the actual values of those fields.


Daniel Pineault

There's a typo in the code

Me.Filter = "[Buy_CP] = " & Chr(34) &  ArgsSplit(0) & "  & Chr(34)"

Open in new window

should be

Me.Filter = "[Buy_CP] = " & Chr(34) &  ArgsSplit(0) & Chr(34)

Open in new window


⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Nick67

Good catch.

Again, my bad.

Air code rather than written in the IDE and compiled to catch boo-boos.


Thanks

Nick67

pdvsa

ASKER

Ok Daniel... thank you indeed.  Will test most likely late today or tomorrow.  Thanks again...

pdvsa

ASKER

Hi Daniel, 


That modification eliminated the syntax.  Thank you for chiming in. 


Nick:

On the OpenForm code I forgot to mention that  I have a syntax but I tried to fix it myself a few days ago and I dont think it is correct.  While the [Buy_CP] filters, the [Trade_No] doesn't seem to populate on the form that opens (frmLetterOfCredit). I think it is because of "my code".


My Code: 

I added an & right before the Nz(Me.Trade_No, "".  I no longer have a syntax but no filter on [Trade_No] either on frmLetterOfCredit.  I guess there is still something misplaced or missing.

DoCmd.OpenForm "frmLetterOfCredit", acFormDS, , , , , Nz(Form_subfrmProjects_Extended_List.Buy_CP, "") & ";" & Nz(Me.Trade_No, "")

Open in new window


'syntax
DoCmd.OpenForm "frmLetterOfCredit", acFormDS, , , , , Nz(Form_subfrmProjects_Extended_List.Buy_CP,"") & ";" Nz(Me.Trade_No,"")

Open in new window

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Nick67

You are missing  an & after the semicolon too

It is always hard to debug monolithic code.

So BEFORE your DoCmd code put


Dim MyArgs as string

MyArgs =Nz(Form_subfrmProjects_Extended_List.Buy_CP,"")

MyArgs = MyArgs & “;”

MyArgs = MyArgs & Nz(Me.Trade_No,"")


Then replace the big mess in the DoCmd with MyArgs.

You can msgbox MyArgs and breakpoint it to ensure it builds correctly.

Fine tune!


pdvsa

ASKER

Nick, thank you for your patience sir.  Sorry for this being so technical but I want to make sure I have it right.  Kindly see below.  The form that opens (frmLetterOfCredit), [Trade_No] string is being a bugger and is not being populated with anything and its not blank or null but [Buy_CP] is being  populated. 


As instructed above, I replaced the mess on the Docmd with MyArgs:

Just as a note, my field [Trade_No] is named simply Trade_No...same as control source...I didnt change the name to txtTrade_No as some do as I get mixed up on which one to call (txt or the control source)


Maybe you can once over the below...thanks..

Private Sub btnIssueLC2_Click()
Dim MyArgs As String

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

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


End Sub

Open in new window


on the form opening (frmLetterOfCredit) I  have 

Private Sub Form_Load()

'---------------------------
'Nick
'---------------------------
    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
    'if we got here we have an argument to work with
    'be nice to yourself and just split it -- then you can for...next the array if things get wild to see what is happening
    ArgsSplit() = Split(Me.OpenArgs, ";") 'two values, positions 0 and 1 holding strings you want to use
    'Now deal with the possibilities
    If ArgsSplit(0) <> "" Then 'there was a first argument, Chr 34 is code for double quotes
    'typo: Me.Filter = "[Buy_CP] = " & Chr(34) & ArgsSplit(0) & "  & Chr(34)"
        Me.Filter = "[Buy_CP] = " & Chr(34) & ArgsSplit(0) & Chr(34)
    End If
    'Now, was there a first AND second argument?
    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
    'Ok if there was no first argument, we have nothing in the filter yet, so
    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
     'there.  Two possibilities renders 4 cases and they are handled.
    '1 Bail on no arguments
    '2 Check for the first and add it if it exists
    '3 check for the second and tack it on if it exists
    '4 check for only a second and build it only
    'if we got here, turn on the filter
    Me.FilterOn = True
    'and done
   
End Sub

Open in new window


Before Insert: (I tried with both MyArgs and OpenArgs...Trade_No is not being populated on frmLetterOfCredit but Buy_CP is and neither are Null or blank.  

Private Sub Form_BeforeInsert(Cancel As Integer)

  '2 Criteria
        Me.Buy_CP = Split(Me.OpenArgs, ";")(0)
        Me.Trade_No = Split(Me.OpenArgs, ";")(1)
       
End Sub

Open in new window

pdvsa

ASKER

so I msgbox MyArgs and I do not get a Trade_No...only Buy_CP


The msgbox says:  BP;  (not sure if that semi is suppose to be there) there is no Trade_No


⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Nick67

Yes, the semicolon should be there

It’s your string delimiter.

But there is no value after it, and that is your where your trade_no value should be.

So, what is fouled up that myArgs didn’t get a value for trade_no?

Nick67

Msgbox   Nz(Me.Trade_No, “no value dummy!”)

And see what you get.

Your CP value came from some other form, not Me

Where is Trade_No actually coming from?

Nick67

I always insult myself with my debugging code.

Keeps me humble and makes me smile.

The f-bomb in code comments reminds me that something was particularly tough.

When I get something to work I make notes in the code about the thought process 

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
pdvsa

ASKER

< Where is Trade_No actually coming from?

both Trade_No and Buy_CP are not on Me.  they are both on  subfrmProjects_Extended_List

I am now thinking this needs to be changed:

Dim MyArgs As String

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



 Maybe the real issue is that Trade_No is not on Me but on the  subfrmProjects_Extended_List

 in mean time, does the below seem right???



Nick67

If it is not on Me then that is DEFINITELY an logic issue.

Sort that first and then sort the syntax issues of getting the right number of quotes in the right places

pdvsa

ASKER

Ok nick, so what I did was modify the 3rd MyArgs to add location of Trade_No and now the Trade_No is being passed to the MyArgs HOWEVER but on that DoCmd line the MyArgs is only Buy_CP and I suspect it should reference both Buy_CP and Trade_No?


Private Sub btnIssueLC2_Click()
Dim MyArgs As String

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

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

Open in new window


this is what I show when I run it: Bu_CP is correct as well as Trade_No but that DoCmd line might not be correct?


⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
pdvsa

ASKER

that pic, second red text box should say "but DoCmd line refs only Buy_CP and NOT both Buy_CP & Trade_No."  (left off the NOT)

Nick67

The myargs should be 

Myargs =

Myargs = myargs & 

Myargs = myargs &

We’re cumulatively adding to the string

My bad

ASKER CERTIFIED SOLUTION
Nick67

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
pdvsa

ASKER

ahh...it works now.  


Thank you my friend.  Have a wonderful day and thanks for hanging in with me.  

Your help has saved me hundreds of hours of internet surfing.
fblack61
Nick67

You and I have been at this a long time.  I just took a five year break.

You’re welcome