Solved

access 2k13 vba concatenate multiple items in openargs

Posted on 2014-12-24
8
213 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 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 57

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
 

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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.

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now