Access VBA, open several instances of the same report at the same time (1 Viewer)

AccOUCH

Registered User.
Local time
Today, 07:40
Joined
Sep 27, 2018
Messages
25
From a form, I'm aiming at opening several instances of a report with only a change of the Id's being shown.

So, the VBA code is:

DoCmd.OpenReport "ordentallerSobre", acViewPreview, , "id = 20370"
DoCmd.OpenReport "ordentallerSobre", acViewPreview, , "id = 20371"
And then, only the last report is shown as a tab. But I need to show both of them. Any solution to this apparently trivial issue?
 

isladogs

MVP / VIP
Local time
Today, 14:40
Joined
Jan 14, 2017
Messages
18,186
Why not just open the report filtered to all the id values you need using the IN operator?
 

AccOUCH

Registered User.
Local time
Today, 07:40
Joined
Sep 27, 2018
Messages
25
Why not just open the report filtered to all the id values you need using the IN operator?

This seems quite interesting, never heard about before. How to implment that. Like this?

Code:
DoCmd.OpenReport "ordentallerSobre", acViewPreview, , "id = 20371 IN id=20372"
 

isladogs

MVP / VIP
Local time
Today, 14:40
Joined
Jan 14, 2017
Messages
18,186
The IN operator is just a more concise way of writing OR conditions e.g. 20371 or 20372

If you have just two fixed NUMBER values, then the syntax would be
Code:
DoCmd.OpenReport "ordentallerSobre", acViewPreview, , "ID IN (20371, 20372)"
If using text fields, enclose the values in quotes

More generally, Allen Browne explains how to do this based on selections in a listbox
http://allenbrowne.com/ser-50.html
 

AccOUCH

Registered User.
Local time
Today, 07:40
Joined
Sep 27, 2018
Messages
25
The IN operator is just a more concise way of writing OR conditions e.g. 20371 or 20372

If you have just two fixed NUMBER values, then the syntax would be
Code:
DoCmd.OpenReport "ordentallerSobre", acViewPreview, , "ID IN (20371, 20372)"
If using text fields, enclose the values in quotes

More generally, Allen Browne explains how to do this based on selections in a listbox

Wow! I'm going to try it. And what happens if I have 'N' numbers to add? Is those selections case?
 

isladogs

MVP / VIP
Local time
Today, 14:40
Joined
Jan 14, 2017
Messages
18,186
Wow! I'm going to try it. And what happens if I have 'N' numbers to add? Is those selections case?

You can add as many as you like all separated by commas.
However, better to use a multiselect listbox and get the values from that.
Not sure what your last sentence means.
 

AccOUCH

Registered User.
Local time
Today, 07:40
Joined
Sep 27, 2018
Messages
25
You can add as many as you like all separated by commas.
However, better to use a multiselect listbox and get the values from that.
Not sure what your last sentence means.

Well, in this application everything starts with a product's forms connected with a table.

There are hundreds of products. Everyone has its Id number. And some of them are packed in boxes, who in turn, have its Id number: Id_box

My aim is to show in a report the Id of each product and the Id_box they share.
So that each time that I call a record to be shown on the screen, I create a Recordset who lists with an SQL filter all the products who share Id_box.

The big problem starts here: Due to the different number of product Id's and the different amount of products that can be inside a box, I need to know how to open a report with those numbers and the kind of notation you gently discovered to me.

In other words: Having different ids each time, and different amount of them, how to tell this instruction:
Code:
DoCmd.OpenReport "ordentallerSobre", acViewPreview, , "ID IN (20371, 20372)"

to solve this issue?

Maybe there is a way to define "ID IN xxx,xxx,xxx,..." so It's possible to provide it any amout of numbers. Or at least, from 1 to 10...

¿Any clue to reach that objective?
 

isladogs

MVP / VIP
Local time
Today, 14:40
Joined
Jan 14, 2017
Messages
18,186
I'm still not clear what you are suggesting but I don't think you need a recordset to do this.

On your form use a multiselect listbox to select all the items you want.
From your description, this could have two columns for ID and id_box.

Make your selections then adapt the listbox code from Allen Browne's website that I referenced in my first reply.
The record source for your report should include all the product ID and ID_box values. The code will then filter the report so only the selected values are included.

Does that make sense?
 

AccOUCH

Registered User.
Local time
Today, 07:40
Joined
Sep 27, 2018
Messages
25
I'm still not clear what you are suggesting but I don't think you need a recordset to do this.

On your form use a multiselect listbox to select all the items you want.
From your description, this could have two columns for ID and id_box.

Make your selections then adapt the listbox code from Allen Browne's website that I referenced in my first reply.
The record source for your report should include all the product ID and ID_box values. The code will then filter the report so only the selected values are included.

Does that make sense?

Ok, I'm going to try that solution.

Also, another way to show the products in the report could be to change by code the recordset associated to that report. As in an DoCmd.OpenReport you can give the parameter OpenArgs the value of the RecordSource: "SELECT ... "

What do you think?
 

isladogs

MVP / VIP
Local time
Today, 14:40
Joined
Jan 14, 2017
Messages
18,186
Ok, I'm going to try that solution.

Also, another way to show the products in the report could be to change by code the recordset associated to that report. As in an DoCmd.OpenReport you can give the parameter OpenArgs the value of the RecordSource: "SELECT ... "

What do you think?

I think you are making this unnecessarily complicated.
Try the other method first. It's easy.
 

AccOUCH

Registered User.
Local time
Today, 07:40
Joined
Sep 27, 2018
Messages
25
Thanks! It has been THE SOLUTION. Now my report works fine thanks to your clues and help. Tumbs up!
 

isladogs

MVP / VIP
Local time
Today, 14:40
Joined
Jan 14, 2017
Messages
18,186
"Tumbs" up indeed.
Glad its now working for you!
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:40
Joined
Jul 9, 2003
Messages
16,244
I did a Blog a few years ago in answer to a similar question on this forum. You can see YouTube videos and a presentation explaining the process on my website here:- Generate Multiple Reports
 

Users who are viewing this thread

Top Bottom