Report with a sub report

[Clepto]

I am a self-taught Access developer working in Access 2013, who has created a number of databases, some quite extensive, but I have never needed to use VBA code before.

I am developing a warehouse database to pick and despatch two types of products: packaged goods and perishables. Each type is stored and picked separately and amalgamated at dispatch.

For the picking lists I have created a report with a sub-report, in Design View and an underlying query for each. The report has the details of each customer’s order and the sub-report details the items. They are connected in a parent child relationship via Customer ID. It all works and filters on the queries all work, so now I want to automate this.

What I want to do is print a packaged goods picking list, and a perishable goods picking list, for each customer.

I entered the SQL code for the queries in VBA, which is contained in strings: strOrders and strLines and recordsets for each: rsOrders and rsLines. I have also created a Boolean variable to filter the sub-report for each type of product. Is this the right approach?

I have a loop working that loops through rsOrders and and another inside that switches the boolean variable, but what I can’t work out is how to get the records to filter and appear on the report.

I had expected to find a publication like “All About Reports in Access 2013 VBA” on the internet, but no luck. Can anyone please point me towards a: book, article, internet tutorial or any published material that deals with this subject. It seems to be the subform that complicates things.

Many thanks

 

[vbaInet]

... some quite extensive, but I have never needed to use VBA code before.

Welcome to the world of VBA I'm quite interested to know how you managed to build such quite extensive applications without using VBA.

I entered the SQL code for the queries in VBA, which is contained in strings: strOrders and strLines and recordsets for each: rsOrders and rsLines. I have also created a Boolean variable to filter the sub-report for each type of product. Is this the right approach?

You haven't explained your table structure so I'm going by what you said above. Code is not required in this case. You could create two subreports, one filtered down to show perishables and the other to show packaged goods. Then link them to your parent report via the Customer and Order ID fields.

Can anyone please point me towards a: book, article, internet tutorial or any published material that deals with this subject.

Have a look at this post, there are some good links there:
http://www.access-programmers.co.uk/forums/showthread.php?t=277735&highlight=functionx

 

[Clepto]

It was a shipping system for a pet products company in NZ. It did everything from the original order right through to costing products according to the space they occupied in a container. I never needed to use loops for this, so I never got into VBA.

The the pre filtered subreports is simpler, always a good idea, I just thought there would be a way to toggle a boolean within the code.

I will have a look at the link.

Many thanks

 

[vbaInet]

Interesting! What about macros?

I'm not entirely sure what you would be using the Boolean field for but if it's just to indicate whether it's perishable or packaged, then it won't be necessary. Just filter down in the query.

Happy coding!

 

[Clepto]

Yes. That looks like the best way. I had heard about the power of coding so I thought that would be the way to go.

Many thanks