Solved

access 2k13 vba concatenate multiple items in openargs

Posted on 2014-12-24
8
210 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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…

757 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

18 Experts available now in Live!

Get 1:1 Help Now