?
Solved

access 2k13 vba concatenate multiple items in openargs

Posted on 2014-12-24
8
Medium Priority
?
227 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 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

770 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