By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 1,454 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

Report Help

100+
P: 161
I need a report to pull data from 2 queries (qryProduction and qryAggregate. Ive started a report but only has data from qryProduction until I see how to combine the 2 queries. I need it to also to only show the data for the form that is open not all records. Thanks
Attached Files
File Type: zip Database.zip (303.1 KB, 3 views)
2 Weeks Ago #1

✓ answered by NeoPa

We'll deal with this quickly because that's a design request rather than a technical question and that's not really what we do here.

If you need data from multiple sources then you can build a query that incorporates both as sources and also (very importantly) defines the relationship between them. Your Report would then use this new query as RecordSource.

As for selecting just the current record there is a parameter called WhereCriteria of the procedure DoCmd.OpenReport() which allows you to specify filtering to be applied to the report when it's opened. Your code, presumably run from the Form, would set the filtering to include just the current record of the Form. Be careful of the FilterName parameter. This is something else and has been known to confuse not a few.

Another parameter, OpenArgs, is also available for you to pass into the Report anything else you may need. Not always needed for your task but is sometimes helpful.

NB. Please, as a general rule, only ever post your work if it's been requested. Far too many members seem to be of the opinion that :
  1. The experts here have nothing better to do than to spend their time in deep investigation of a problem which, in reality, is the responsibility of the poster to express clearly in the first place.
  2. Everyone is using the same version of Access so they have a system that will even open their work. Generally indicated by not even posting which version their database is stored in.

You can trust that if and when (because it does happen) any individual expert feels prepared to look into your actual database for you then they will be clear about requesting a copy.

Share this Question
Share on Google+
12 Replies


NeoPa
Expert Mod 15k+
P: 31,273
We'll deal with this quickly because that's a design request rather than a technical question and that's not really what we do here.

If you need data from multiple sources then you can build a query that incorporates both as sources and also (very importantly) defines the relationship between them. Your Report would then use this new query as RecordSource.

As for selecting just the current record there is a parameter called WhereCriteria of the procedure DoCmd.OpenReport() which allows you to specify filtering to be applied to the report when it's opened. Your code, presumably run from the Form, would set the filtering to include just the current record of the Form. Be careful of the FilterName parameter. This is something else and has been known to confuse not a few.

Another parameter, OpenArgs, is also available for you to pass into the Report anything else you may need. Not always needed for your task but is sometimes helpful.

NB. Please, as a general rule, only ever post your work if it's been requested. Far too many members seem to be of the opinion that :
  1. The experts here have nothing better to do than to spend their time in deep investigation of a problem which, in reality, is the responsibility of the poster to express clearly in the first place.
  2. Everyone is using the same version of Access so they have a system that will even open their work. Generally indicated by not even posting which version their database is stored in.

You can trust that if and when (because it does happen) any individual expert feels prepared to look into your actual database for you then they will be clear about requesting a copy.
2 Weeks Ago #2

100+
P: 161
That's fine, its easier for me to see what everything is to have a conclusion but my line of work is different and often requires the big picture so its just habit.
2 Weeks Ago #3

100+
P: 161
NeoPa:
As for selecting just the current record there is a parameter called WhereCriteria of the procedure DoCmd.OpenReport() which allows you to specify filtering to be applied to the report when it's opened. Your code, presumably run from the Form, would set the filtering to include just the current record of the Form.
Currently the report opens from my form using a macro. I added DoCmd.OpenReport() to the where condition but it has {a explanation} an exclamation mark now.
Expand|Select|Wrap|Line Numbers
  1. <?xml version="1.0" encoding="UTF-16" standalone="no"?>
  2. <UserInterfaceMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"><UserInterfaceMacro For="Image432" Event="OnClick"><Statements><Action Collapsed="true" Name="OpenReport"><Argument Name="ReportName">rptPlantReport</Argument><Argument Name="WhereCondition">DoCmd.OpenReport()</Argument><Argument Name="WindowMode">Dialog</Argument></Action></Statements></UserInterfaceMacro></UserInterfaceMacros>
2 Weeks Ago #4

NeoPa
Expert Mod 15k+
P: 31,273
I'm struggling. I've written clear details of everything you need to know about this, yet your response may as well be in another language. Believe me, I read everything I write a number of times before posting and make sure it makes good clear sense. Sometimes though, it seems obvious you don't understand what I'm talking about.

I talk about a parameter to a procedure that you call from code (VBA) and you respond after apparently attempting to include the name of the procedure as a parameter to some macro. I never recommend using macros BTW. They get you so far (not very) then you have to start over again with VBA as they're so limited and limiting.

So, where are we? We were never going to have a discussion in this thread anyway. It isn't a properly formed question. I went beyond the requirement to give you an outline of how to handle it. That's lucky because I wouldn't even now how to respond to your last post in any meaningful or helpful way.

Feel free to post another thread, with the same stipulations as before, but actually meet the requirements this time. With something specific I may be able to lead you step by step, but it sounds like your experience in Access is barely-existent so you will have to pay careful attention. If there's something you don't understand then query it but don't push on regardless and just guess. That hurts both of us.

As far as working with macros goes, if this is what you really feel you need, I may well not be able to help. I don't use them for the reasons stated above and I'm not even sure they can do what you want of them. I certainly neither know, nor wish to learn, how to take you forward in that direction.
2 Weeks Ago #5

100+
P: 161
I appreciate everyones help, so don't get me wrong because I do. But I can not explain what I do not know in the way you explain things to me. Far as my other post I was already using a macro so I looked for a where condition and entered what you suggested.
I never claimed I put what you suggested in the correct place and I also copied where I put it in case I had made a error. Its not easy to explain what you do not know. If I knew I would not be asking for help.

Again Thanks for any help that's been given. Its been helpful but with the change to add the subform it left the questions about the report and the subform Sum not working properly. I'm sure its something very simple I just don't know how to explain so there is little reason to post another thread about it.
2 Weeks Ago #6

100+
P: 161
So the last 2 threads are no good. Delete them please. If I can think of a better way to describe this issue Iíll try again to post.
2 Weeks Ago #7

NeoPa
Expert Mod 15k+
P: 31,273
As I explained elsewhere I do appreciate how hard you're finding it to fit into how things need to be done here. I also can sense that you're trying to do things the way we ask, but that it's real struggle for you. I'm really not unsympathetic. The unfortunate truth though, is that we require them to be done that way because it works. Conversely, other ways simply don't. It's a real struggle for me to understand enough about what your describing the way you describe it. In this line of work a clear and full understanding is the only thing between a good & helpful answer, and one that would lead you into trouble.

As you find it hard to think at my level, I also find it hard to get where you're coming from. It may be a lack in me, but I'm frequently confused by your posts. I find nothing fundamental I can build upon, without which I have nowhere to start.

Let me say that in all of this, and I can imagine you must be experiencing at least as much frustration as we have here, your manners have always been exemplary. That's not a light thing to do - especially when experiencing frustration. So, you've impressed in one way at least.
2 Weeks Ago #8

100+
P: 161
Iíve tried to repost. I hope it is understandable and to point. Itís difficult because I donít know whatís relevant or not. All I can do is try to post in a clearer and more focused way. Not sure it is but I tried to be to point.
2 Weeks Ago #9

100+
P: 161
I used this code after putting the main data in the report then added a subreport for the other data. So a little different than how I had started this thread but it works exactly as I need.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Image432_Click()
  2. DoCmd.RunCommand acCmdSaveRecord
  3. DoCmd.OpenReport "rptPlantReport", acViewPreview, , "[ProdID] = " & [ProdID]
  4. End Sub
2 Weeks Ago #10

NeoPa
Expert Mod 15k+
P: 31,273
I hope it's a sign that you're starting to see some benefits from what we're trying to express. It's a steep learning-curve at this stage but it only gets shallower from here.
2 Weeks Ago #11

100+
P: 161
I'm starting to get a few things. Learning what level to address issues might take awhile. Some things I thought needed to be at the query were resolved at the report for example. I'm still having some issues overall but I believe things are starting to fall into place. I'll be asking a new question today lol... But let me get through the ones you've already addressed.
2 Weeks Ago #12

NeoPa
Expert Mod 15k+
P: 31,273
That's good news. Learning is always at its hardest at the start.
2 Weeks Ago #13

Post your reply

Sign in to post your reply or Sign up for a free account.