how to pass multiple openargs

hi,

i have an order form that i want to search to narrow down on retrieved records, basically i have a search form, which has a text box (txtSearchKey) and 2 combo boxes (cboSearchCriteria and cboStatus) the text box will be used as the value to search on, cbosearchcriteria is used to define what to search through and cbostatus is what status the order is in to search. so for instance if i wanted to search for order number 17 and i know it is live, i would type - 17 (in the text box) select "order id" (from cbosearchcriteria) and select "live" (from cbostatus).......i then have a few variables to hold the selections, strSearchKey, strSearchCriteria and strStatus.....these will get the values from the objects and i want to pass all three through to the order form as openargs.....when these are recieved at the orderform i want to use them in an sql statement to change the recordsource of the object.....something that will do the following  (sorry, im new to sql so this may be wrong):

Me.RecordSource = "SELECT tblOrder.*, tblOrder.CustomerID, tblOrder.OrderStatus FROM tblOrder WHERE (((strcriteria)=strsearchkey) AND ((tblOrder.OrderStatus)=strstatus));"

as you can see, the first where statement is saying, locate the searchkey based on the search criteria so strcriteria would be "orderid" and strsearchkey would be "17" after the AND statement the order status would be "Live"....

i am using the following code to send the open args:

DoCmd.OpenForm strDocName, acNormal, , , , acDialog, strSearchKey & ";" & strCriteria & ";" & strStatus

and the following to recieve open args in the open form event of the order form:
(variables declared)
strSearchKey = Left(OpenArgs, InStr(OpenArgs, ";") - 1)
strCriteria = Mid(OpenArgs, InStr(OpenArgs, ";") + 1)
strStatus = right(OpenArgs, InStr(OpenArgs, ";") + 2)

the above code doesnt work very well at all, i have tested it with text boxes to see what i am recieving through the openargs, and i am getting, the search key fine, then the text box that should just recieve the strcriteria is getting "orderid;live" insetad of just "orderid" and the last text box is getting ";live" instead of just "live"

help please! im tearing ma hair out
mrBrightsideAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TextReportCommented:
Pass a single string separated by ; and then use the split function
Cheers, Andrew

0
TextReportCommented:
Here's an example
Private Sub Form_Open(Cancel As Integer)
Dim ary As Variant
Dim cnt As Long
 
    ary = Split(Nz(Me.OpenArgs, ""), ";")
    For cnt = LBound(ary) To UBound(ary)
        Debug.Print ary(cnt)
    Next cnt
End Sub

Open in new window

0
mrBrightsideAuthor Commented:
im sorry i dont really understand, how do i then get my desired variables?

does the array split all my arguements into a list like the following:

0 - searchkey
1 - searchcriteria
2 - status

and if so how do i then use that data in an sql statement?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

TextReportCommented:
ary(0) would be the first part of the OpenArgs, searchkey
ary(1) would be the second part of the OpenArgs, searchcriteria
ary(2) would be the third part of the OpenArgs, status
0
mrBrightsideAuthor Commented:
brilliant, works a treat that does. do you know how i would feed those values into an sql statement or should i repost in sql section?
0
TextReportCommented:
It depends on the how you are building up your SQL statement and what you are trying to achieve. For example in the DoCmd.OpenForm you can pass a where condition that could be something like

strWHERE = strCriteria & "=" & Chr(39) & strSearchKey & " AND tblOrder.OrderStatus=" & Chr(39) & strStatus & Chr(39)
DoCmd.OpenForm strDocName, acNormal, , strWHERE, , acDialog

Cheers, Andrew
0
mrBrightsideAuthor Commented:
thanks, i have tried your code in the on click event of a command button and it generates the following error:

run-time error '3075':

syntax error (missing operarator) in query expression 'customerid=1' AND tblorder.orderstatus='live'".
0
TextReportCommented:
If searchkey is a number then

strWHERE = strCriteria & "=" & strSearchKey & " AND tblOrder.OrderStatus=" & Chr(39) & strStatus & Chr(39)

so strWHERE should then read

customerid=1 AND tblorder.orderstatus='live'

Cheers, Andrew
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mrBrightsideAuthor Commented:
yes you are ofcourse correct, that works very well thanks.....chr(39) act as punctuation marks?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.