Exporting Reports by Group into Individual Folders

[MJ_Wilkinson]

Hi all,

I have a report that is grouped on a group code (separated to one page per group), and have a piece of code set up below that exports the whole report into my desired location, listed in TBL_FILELOC.

Private Sub CommA_Click()

Dim FTP As String
EXP = DLookup("[FileLocation]", "Tbl_FILELOC", "[LocId]='L03'")

Dim strPathAndFile As String
strPathAndFile = "" & EXP & "" & "All Group Statements- Q1 2017.pdf"

DoCmd.OutputTo acOutputReport, "Rep_Grps", acFormatPDF, strPathAndFile

End Sub

What can I add to this to export each groups statement report into its individual folder? I have set up these folders on my desktop, and have created a table TBL_EXPORTLINK that has a column listing these locations for each group, along with that groups group code.

I'm not that skilled on VBA at the minute so clearly need to watch YouTube videos if I can't do something like this which is probably quite simple!

Would appreciate any help you guys can offer.

Thanks

 

[isladogs]

Private Sub CommA_Click()

Dim FTP As String
EXP = DLookup("[FileLocation]", "Tbl_FILELOC", "[LocId]='L03'")

Dim strPathAndFile As String
strPathAndFile = "" & EXP & "" & "All Group Statements- Q1 2017.pdf"

DoCmd.OutputTo acOutputReport, "Rep_Grps", acFormatPDF, strPathAndFile

End Sub

Why are you defining FTP? You aren't using it.

You need to modify the LocID criteria to give the specific folder for each report in the EXP expression.

You MAY need a backslash \ inside the ""after EXP in the strPathandFile line
Test this by adding this line

 Debug.Print strPathandFile

.
This will display the output in the VBE immediate window

 

[MJ_Wilkinson]

Sorry it was meant to be;

Private Sub CommA_Click()

Dim EXP As String
EXP = DLookup("[FileLocation]", "Tbl_FILELOC", "[LocId]='L03'")

Dim strPathAndFile As String
strPathAndFile = "" & EXP & "" & "All Group Statements- Q1 2017.pdf"

DoCmd.OutputTo acOutputReport, "Rep_Grps", acFormatPDF, strPathAndFile

End Sub

 

[isladogs]

OK - the rest of my post still applies

 

[MJ_Wilkinson]

Thanks,

I have created a new LocId line "L01", and a column in a new table TBL_EXPORTLINK where I have a column that lists each unique code and the folder name that the report should be added to in the TBL_FILELOC location I have specified in "L01". FullGrpCode is the groups code with the brand at the front, and GrpCode is the regular group code.

I have therefore then tried the below and failed miserably;

Private Sub Comm_AIndiv_Click()

Dim DB As Database
Dim VAL As DAO.Recordset
Dim LOC As String
Dim RES As String

Set DB = CurrentDb()
   
LOC = "SELECT TBL_EXPORTLINK.FullGrpCode " & vbCrLf & _
"FROM TBL_EXPORTLINK " & vbCrLf & _
"WHERE (((TBL_EXPORTLINK.Brand)=""A""));"

Set VAL = DB.OpenRecordset(LOC)
Do While Not VAL.EOF
RES = VAL("FullGrpCode")

Dim lgrpcode As String
lgrpcode = DLookup("[GrpCode]", "TBL_EXPORTLINK", "[FullGrpCode]='" & RES & "'")

TextRec.Value = lgrpcode


Dim EXP As String
EXP = DLookup("[EXPFile]", "TBL_EXPORTLINK", "[BDlrNo]='" & RES & "'")


Dim strPathAndFile As String
strPathAndFile = DLookup("[FileLocation]", "TBL_FILELOC", "[LocId]='L01'") & "" & EXP & "" & "Q1 2017 Grp Statement " & "" & lgrpcode & "" & ".pdf"

DoCmd.OutputTo acOutputReport, "Rep_Grps", acFormatPDF, strPathAndFile


VAL.MoveNext
Loop
VAL.Close


End Sub

When I debug I get the error 424 object required on TextRec.Value = lgrpcode.

I'm thinking I've maybe either over-complicated things, or messed up somewhere.

 

[isladogs]

Yes you are over complicating things and possibly have messed up somewhere.

Before I look at your code in detail, I'm thinking that it may well be possible to do what you want with a query linking your 2 tables

Could you upload the details of your 2 tables -field names and datatypes plus some sample data. Better still upload a stripped down version of your db with just the relevant bits

As you have less than 10 posts you will need to zip your file

 

[MJ_Wilkinson]

I've attached a really basic version of the database, I hope it works. A lot of this is confidential so I've had to change a lot of names etc but I think it still makes sense.

As you can see the reports print out every site in one, and they're split one site for each page. However I want to export them into the folders in the EXPFile column in TBL_EXPORTLINK which are located in the file locations listed in TBL_FILELOC.

Thanks for taking a look.

 

[isladogs]

I've spent an hour or so looking at the db you uploaded.
This is a VERY detailed response!

First of all, there are some serious problems with your data structure which need to be fixed.
The list below may not be what you want to hear but the good news is that the end result should be MUCH simpler / better

1. NONE of the 5 tables currently have a primary key field as a unique identifier.
This is a SERIOUS error which needs to be rectified as a priority
Access will be unable to carry out various things such as update queries until this is fixed

Using your data, these appear to be OK as PK fields:
- Tbl_A_Data & Tbl_B_Data - use GrpCode
- tbl_ExportLink - use EXPFile or FullGrpCode (don't need both!)
- tblFileLoc - use LocID
- tblIdent - use FullGrpCode (but see below)

2. There is a lot of repetition in various tables which goes against good database design and makes maintenance more difficult.
Fields should normally only appear in one table unless they are foreign keys for another table e.g. FullGrpCode?

3. Am I right in thinking FullGrpCode is just Brand & GrpCode concatenated?
If so, get rid of FullGrpCode OR get rid of the other two fields
Again, unnecessary duplication

4. tbl_ExportLink only needs 2 fields & 2 records (with the supplied data):
Brand EXPFile
A J2060
B M2400

The rest of the current EXPFile data is just the above values combined with GrpCode

4. Why do you need tblIdent at all?
The only unique fields it contains are:
- Region - could this be moved to Tbl_A_Data & Tbl_B_Data
- the Yes/No field CIIncl (which is ALWAYS true - at least for the data for supplied.
If its never false, you can delete it & the tblIdent is superfluous
If it could be false in some cases, just add the field to Tbl_A_Data & Tbl_B_Data & then scrap tblIdent

5. Taking this further, I think you need just 2 tables:

a) It seems to me that Tbl_A_Data & Tbl_B_Data can be merged into one table (tblGroupData) as all fields are the same
I think the combined table would have fields:
- GrpCode/Region/Brand/Name & all the Result fields
- PK field could be an autonumber ID field or if you prefer a combined PK based on GrpCode & Brand

b) tblFilLoc & tblExportLink can also be merged as tblExportLoc with fields
LocID (PK), Brand, FileLocation, EXPFile

It needs 4 records - those in tblGFileLoc plus the simplified EXPFile data

Some other things:

6. You should ALWAYS add these 2 lines at the top of each code module
Option Compare Database
Option Explicit

7. Error handling should also be added for each procedure

8. Do you really need 2 reports?
They look identical except for the header Brand A / Brand B
Why not just filter for the brand (etc) and add that as a field in the header
Or if you are decide to use FullGrpCode as a PK, get the brand from Left(FullGrpCode,1).

If that's so then the form can also be simplified
One button could do the whole lot as all you are doing is changing the folder using the LocID field

===========================================

Having got that lot out of the way, can you confirm I understand your requirements correctly:
Each PDF file to be a 1 page report showing data for 1 GrpCode in 1 region on 1 site (Name field) for a specified date range (quarter year)
... and you want each to be in its own folder
e.g.
Brand A with GrpCode A1368, region 1 at site 1 with LocID=3 should have PDF file in folder:
C:\Users\mwilkin2\Desktop\Margin Files\A Portal\J2060A1368\

Brand A with GrpCode A1680, region 1 at site 1 with LocID=3 should have PDF file in folder:
C:\Users\mwilkin2\Desktop\Margin Files\A Portal\J2060A1368\

If I've got this right, then its easy to achieve (you'll be glad to know).
The code just needs modifying to loop through each record in the table and send the 1 page report to the correct folder based on GrpCode

Attached is my version with simplified structure :
- 2 tables (as explained above)
- 1 query (used both for report record source & export location)
- 1 merged report
- your form (mostly unaltered so far)

I've added 'dummy' EXPFile values for LocID L01 & L02 for testing.

Just need you to confirm that I'm doing this correctly.
If so, having got this far, I'm happy to update your form next....!

 

[MJ_Wilkinson]

Hi Ridders,

Thanks for taking the time to look through the db, and the detailed response is great, might as well tackle all the issues!

I need to keep the two brands separate, as I need to report separately and the Tbl_(Brand)_Data for the two brands are picked from different sources, and they need to be exported to different locations. If it's best to split then could just have a db for each brand?

Primary key and simplified changes are made on the attached.

Exactly right FullGrpCode is Brand and GrpCode concatenated. I need to keep Site Name and Grp Code information so I can use them on the report, however I can just keep it on Tbl_Ident so will do that. I need to keep Tbl_Ident because there will be instances where Sites will appear in Tbl_Ident but not the data. The numbers only match at the moment as I cleansed non live sites from Tbl_Ident, in future this could change so need it set up to include sites as data for any fields drops out. I realised I didn't have the queries set up to include all records from Tbl_Ident and only those records from Tbl_B where they're equal. This has been rectified.

Some of these fields are also legacy from other reports, e.g CIIIncl, so I've removed these.

I've tried using Option Compare Database and Option Explicit but I get the error "Invalid inside procedure".

Requirements

I'm pretty sure you've understood correctly.

Essentially, using Brand A as an example, each GrpCode needs to have a one page PDF report exported to its own folder in the FileLocation quoted in TBl_EXPORTLOC.

e.g A1368 a one page pdf into folder J2060A1368\ in FileLocation C:\Users\mwilkin2\Desktop\Margin Files\A Portal\FixedReporting\

A1680 a one page pdf into folder J2060A1680 in FileLocation
C:\Users\mwilkin2\Desktop\Margin Files\A Portal\FixedReporting\

etc

and for Brand B;

e.g A1246 a one page pdf into folder M2400A1246\ in FileLocation C:\Users\mwilkin2\Desktop\Margin Files\B Portal\FixedReporting\

A2156 a one page pdf into folder M2400A2156\ in FileLocation C:\Users\mwilkin2\Desktop\Margin Files\B Portal\FixedReporting\

etc

All at the click of the button. As currently my code exports the whole report for each brand and I'd have to go through the laborious task of splitting the PDFs, which I obviously want to avoid!

Obviously I'm being pretty dumb but I can't see the db you've attached, where do I find it?! I've attached updated db with the changes, if you could let me know where I go next with the code here that would be awesome.

Once again really appreciate you tackling this in so much detail.

 

[isladogs]

Obviously I'm being pretty dumb but I can't see the db you've attached, where do I find it?! I've attached updated db with the changes, if you could let me know where I go next with the code here that would be awesome.

Once again really appreciate you tackling this in so much detail.

Umm .. mine is at the same place as yours ...
....its at the bottom of my post, just above my signature line!

I'll have a look but it won't be before this evening UK time
I'm going to be out enjoying the heatwave all day ...

In the meantime, have a look at my updated db.
You may decide you should modify yours further.
If so, upload again - I'll look at the latest upload when I do return to this

I need to keep the two brands separate, as I need to report separately and the Tbl_(Brand)_Data for the two brands are picked from different sources, and they need to be exported to different locations. If it's best to split then could just have a db for each brand?

I don't see any reason to keep them separate as queries used in the report(s) will be used to filter the required data

Primary key and simplified changes are made on the attached.
...
Some of these fields are also legacy from other reports, e.g CIIIncl, so I've removed these.

Good

I've tried using Option Compare Database and Option Explicit but I get the error "Invalid inside procedure".

These MUST be the first two lines in any code module

Requirements

I'm pretty sure you've understood correctly.

Essentially, using Brand A as an example, each GrpCode needs to have a one page PDF report exported to its own folder in the FileLocation quoted in TBl_EXPORTLOC.

e.g A1368 a one page pdf into folder J2060A1368\ in FileLocation C:\Users\mwilkin2\Desktop\Margin Files\A Portal\FixedReporting\

A1680 a one page pdf into folder J2060A1680 in FileLocation
C:\Users\mwilkin2\Desktop\Margin Files\A Portal\FixedReporting\

etc

and for Brand B;

e.g A1246 a one page pdf into folder M2400A1246\ in FileLocation C:\Users\mwilkin2\Desktop\Margin Files\B Portal\FixedReporting\

A2156 a one page pdf into folder M2400A2156\ in FileLocation C:\Users\mwilkin2\Desktop\Margin Files\B Portal\FixedReporting\

etc

All at the click of the button. As currently my code exports the whole report for each brand and I'd have to go through the laborious task of splitting the PDFs, which I obviously want to avoid!

Makes sense.
The way to do this is filter the report data to select one record & output that to its own folder
Then using code, move to the next record & repeat in a loop using a recordset

It isn't that difficult and there are plenty of similar examples online if you want to try doing this yourself before I have time to do so.

Two more bits of advice...
1. Don't save files to the desktop - that should be used for shortcuts only
Adding loads of files to the desktop will dramatically slow down your PC.

2. All the data shown is for one quarter.
Presumably you will want to repeat every 3 months & have a slightly modified PDF title. So don't hard code the file name as

All Group Statements- Q1 2017.pdf

Instead add a new text field to the data table called Qtr or similar
It will have values like 'Q1 2017', 'Q2 2017' etc
Then your file name will be something like:

strFile = "All Group Statements- " & [Qtr] & ".pdf"

HTH

 

[MJ_Wilkinson]

No probs mate, no rush I'm not against a timeline or anything.

Haha told you I was dumb, didn't check inside the thread!

Thanks I will have a look this afternoon. The example here is exporting to my desktop but normally I export to a Network Drive, just needed to use the Desktop to test.