Solved

access 2k13 vba concatenate multiple items in openargs

Posted on 2014-12-24
8
222 Views
Last Modified: 2014-12-25
have an existing report driven by a query that works fine. It has a single, openargs variable now. I would like to add an additional variable to the openargs string for some other work on the report. I understand that I have to parse the openargs  variables.

The query that drives the report is:
SELECT MACHINE.MACHINE, MACHINE.DESCRPTION, MACHINE.REG, MACHINE.PICTURE AS PIC, MACHINE.RPM, MACHINE.POINTS, MACHINE.CLIENT_ID, MACHINE.PHOTO, TRENDMAC.SET_DT, TRENDMAC.ACRONYM, TRENDMAC.RATING, TRENDMAC.SUMMARY, MACHINE.COMMENT, TRENDMAC.CHECK_NEXT, MACHINE.HORSE_PWR
FROM (MACHINE LEFT JOIN (SELECT TRENDMAC.MACHINE, Max(TRENDMAC.SET_DT) AS SET_DT FROM TRENDMAC IN 'G:\CAC\DB\PLANT\v6'[dBase IV;] WHERE TRENDMAC.RATING NOT LIKE "N" and TRENDMAC.RATING NOT LIKE "X" GROUP BY TRENDMAC.MACHINE)  AS SubQuery ON MACHINE.MACHINE=SubQuery.MACHINE) LEFT JOIN TRENDMAC ON (SubQuery.MACHINE=TRENDMAC.MACHINE) AND (SubQuery.SET_DT=TRENDMAC.SET_DT) IN 'G:\CAC\DB\PLANT\v6'[dBase IV;]
WHERE MACHINE.DUMMY=False
ORDER BY MACHINE.MACHINE;

the command to run the report is:
    DoCmd.OpenReport "Field Notes", acViewReport, , , acHidden, Me.ACRONYM

what I'd like to do is put an additional variable (set_dt) from the query into the openargs something like this:
... , me.acronym & "|" & me.set_dt

currently doesn't pickup the set_dt field, I get null instead
0
Comment
Question by:jsgould
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 150 total points
ID: 40517063
The way I do this this is I concatenate the string together with a semicolon  and then in the open event, I split the string using Split(OpenArgs ";")  I then use the resulting split as each of the arguments that I needed to use and I can do this with four five six seven different opening arguments
0
 
LVL 7

Expert Comment

by:Robert Sherman
ID: 40517422
How are you executing the command to open the report?  Specifically, where is ME.ACRONYM coming from in the

   DoCmd.OpenReport "Field Notes", acViewReport, , , acHidden, Me.ACRONYM

line you listed above?   Is this being fired from a form that has inputs on it to specify the parameters you want to use when you run the report?   If so, you would need to add an additional textbox to that form to give you a value for Me.set_dt

It all depends on where the DoCmd is being kicked off from, that would be the context for whatever "me" happens to be at that point.
0
 
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 175 total points
ID: 40517559
+1 on the method Nick uses, but I'd use a vertical pipe instead of a semi-colon.   I used a semi-colon in the past, but ran into a problem at one point because it was in the data.

I also took the idea a bit farther in formatting the string as:

<tag1> = <element1> : <element2> | <tag2> =

So I'd split on the vertical pipe, look for the ='s and get the tag, then split the elements on the colon.

The tag would represent a type of data.    For example 'EXITTOFORM='   would indicate the form focus should move to when this called form was closed.   Or  'SETCTRLSTODATA='

 Which would be a list of control names and the data they should get set to (as defaults).

This worked well in passing common arguments around between forms.

Jim.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:jsgould
ID: 40517601
Robert:

the report is driven by a query. the query contains the field ACRONYM, all of which works fine. I just seem to be having an issue with the syntax for adding additional elements to openargs.

Jim:
Thanks. but I like the idea of using the split function into an array which gives me a nice stack of elements ready to use without any further adoo
0
 
LVL 7

Accepted Solution

by:
Robert Sherman earned 175 total points
ID: 40517882
When you say "the report is driven by a query" do you mean the query is the Record Source of the report?

How are you performing the DoCmd?   Via VB Code?   Is the code in a button event on a form?  What is the context of "me" that the value for ACRONYM is coming from?  

You're saying you want to put an additional variable from the QUERY into the openargs, which doesn't make sense if the query you're referring to is the RecordSource of the report.
0
 
LVL 58
ID: 40517991
<<Jim:
Thanks. but I like the idea of using the split function into an array which gives me a nice stack of elements ready to use without any further adoo >>

 Your still using split().   What I'm pointing out is that by formatting arguments in certain ways, you can make a lot of things "automatic" and extend the idea to make it more useful.

 In all my forms and reports, I have a routine called at open, which looks at the open args and based on the tags, carries out certain actions.

 In the long run, that's a big time saver because I don't have to build in logic over and over in a called objects to react to whatever I'm passing.

 For example:

Mode=InquiryOnly|Filter=[CustomerID] = 'ABC123'|ExitToForm=frmOrders

 is how I call a pop-up form to display data.  By 'tagging' the arguments, I can have the form react in certain ways.  

So all I'm pointing out is that you can extend passing multiple arguments in a very nice way.

Jim.
0
 

Author Comment

by:jsgould
ID: 40518025
Robert:
Query is record source
docmd run in vba from button on popup form
me.Acronym is a field in the query result
The additional variable is to be used in a dlookup to extract a field in another table for the report header
0
 

Author Comment

by:jsgould
ID: 40518042
Folks: got it, thanks for ur help

Robert you were on it with the me.acronym. turns out It was coming from the wrong place, that just happened to be ok for the single variable.

Jim:
Thanks for the explain, I'll try it next time around

Merry Christmas & Happy New Year Folks!
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

691 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