[Webinar] Streamline your web hosting managementRegister Today


how to pass multiple openargs

Posted on 2008-01-30
Medium Priority
Last Modified: 2013-11-28

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
Question by:mrBrightside
  • 5
  • 4
LVL 28

Expert Comment

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

LVL 28

Expert Comment

ID: 20776047
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


Author Comment

ID: 20776067
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?
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

LVL 28

Expert Comment

ID: 20776078
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

Author Comment

ID: 20776089
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?
LVL 28

Expert Comment

ID: 20776174
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

Author Comment

ID: 20776918
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'".
LVL 28

Accepted Solution

TextReport earned 2000 total points
ID: 20776961
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

Author Comment

ID: 20777119
yes you are ofcourse correct, that works very well thanks.....chr(39) act as punctuation marks?

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question