?
Solved

access 2k13 vba concatenate multiple items in openargs

Posted on 2014-12-24
8
Medium Priority
?
233 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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 600 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 700 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 700 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

839 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