VBA + Access Report (Loop Query?) (1 Viewer)

M

mesh1o

Registered User.
Local time
Yesterday, 16:15
Joined
Mar 29, 2014
Messages
12
Hello all,

Thank you for taking time out to read this

Before I start investigating was wondering if anyone can assist in telling me if it is possible and advise on what route i should take to accomplish my aim.

Basically i'm trying to get a Access Report to export to PDF but would like the report to be split based on a distinct table called cost centres. So basically the output is a seperate report for each cost centre saved with the filename as the costcentre.pdf


Code: 
Private Sub Create_PDF_Click()

Dim myPath As String
Dim strReportName As String

DoCmd.OpenReport "NominalRollCC", acViewPreview

myPath = "C:\Documents and Settings\"
strReportName = NominalRollCC.[CostCentre] + ".pdf"

DoCmd.OutputTo acOutputReport, "", acFormatPDF, myPath + strReportName, True
DoCmd.Close acReport, "NominalRollCC"

End Sub

But don't think the code does what i want it to do :/ is it possible and any tips on what i should look and read on to assist me in completing it...

currently the process done is literally have a report setup and one by one type in the cost centre, preview and print to PDF (process can take upwards of an hour!!)

Thanks in advance for taking time to read this :) much appreciated

mEsh
 
S

sneuberg

AWF VIP
Local time
Yesterday, 16:15
Joined
Oct 17, 2014
Messages
3,506
I don't know how you have the record source of the report set up but if you set it up so that it produces a report with all cost center than you can select the specific one in the WHERE clause of the report open. Docmd.OutputTo doesn't have a WHERE clause argument so the way to do this is to open the report hidden, output it to PDF and then close it. To do this for all cost centers you can open a record set with the cost centers and loop through them. I've set up a simple database that demonstrates these ideas. The database is attached and has this code to output the report. This will hopefully get you started.

Code: 
Private Sub Create_PDF_Click()

Dim myPath As String
Dim strReportName As String
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Table1")
myPath = "C:\Documents and Settings\"
strReportName = "NominalRollCC"
Do While Not rs.EOF
    DoCmd.OpenReport strReportName, acViewPreview, , [COLOR="royalblue"]"[CostCentre] = " & rs!CostCentre[/COLOR], acHidden
    DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, myPath & strReportName & rs!CostCentre & ".PDF"
    DoCmd.Close acReport, strReportName
    rs.MoveNext
Loop
rs.Close
Set rs = Nothing

End Sub
 

Attachments

  • OutputReportAsPDF.accdb
    424 KB · Views: 210
Last edited:
M

mesh1o

Registered User.
Local time
Yesterday, 16:15
Joined
Mar 29, 2014
Messages
12
Thank you ever so much for responding so quickly Sneuberg!

That work's perfect in the example but adopting the same script (tweaking it for datafield names and table names) the result throws up a prompt asking me for cost centre.

I initially thought it had something to do with multiple rows within the data with same cost centres, so i tweaked the example and created a similar dataset (multiple rows with the same cost centre scattered all over the place) but the query still ran.

Any ideas why it's bringing up a prompt? I've set the table to where the raw data is and changed the rs = to the new tables :/

If i put in a cost centre within the prompt it exports the whole dataset into one pdf :/

It's probably something really obvious but no idea what :/

Thanks
 
S

sneuberg

AWF VIP
Local time
Yesterday, 16:15
Joined
Oct 17, 2014
Messages
3,506
Could you upload you database? If not then post the SQL of the record source query of the report. Also the Create_PDF_Click code as you have changed it.
 
M

mesh1o

Registered User.
Local time
Yesterday, 16:15
Joined
Mar 29, 2014
Messages
12
Thanks Steve.

Please see attached - it works but the prompt makes you enter each cost centre (that's the bit trying to elimate) even though the process has improved as i no longer need to do each one and save as print to PDF :)

Thank you:D
 
Last edited:
S

sneuberg

AWF VIP
Local time
Yesterday, 16:15
Joined
Oct 17, 2014
Messages
3,506
The cost centre in your case is text and needs to be in single quotes in the WHERE clause. Just add them to the open report line of code like:

Code: 
   DoCmd.OpenReport strReportName, acViewPreview, , "[Cost Centre] =[COLOR="Red"][B] '[/B][/COLOR]" & rs![Cost Centre] [COLOR="Red"][B]& "'"[/B][/COLOR], acHidden


As an aside since you are using the text of the cost centre for a file name it's a good idea to make sure it doesn't have any illegal characters in it that Windows will complain about. Below is a function which you might find useful. It removes any illegal character from a string.

Code: 
Public Function RemoveIllegalFileCharacters(strFileName As String) As String
 
strFileName = Replace(strFileName, "<", "")
strFileName = Replace(strFileName, ">", "")
strFileName = Replace(strFileName, ":", "")
strFileName = Replace(strFileName, """", "")
strFileName = Replace(strFileName, "/", "")
strFileName = Replace(strFileName, "\", "")
strFileName = Replace(strFileName, "|", "")
strFileName = Replace(strFileName, "?", "")
strFileName = Replace(strFileName, "*", "")
RemoveIllegalFileCharacters = strFileName
 
End Function
 
M

mesh1o

Registered User.
Local time
Yesterday, 16:15
Joined
Mar 29, 2014
Messages
12
Thank you ever so much :) I've added to your rep for the help!! works perfect.. :)
 
NauticalGent

NauticalGent

Ignore List Poster Boy
Local time
Yesterday, 19:15
Joined
Apr 27, 2015
Messages
6,365
Cool function Steve. Have you noticed any degradation in speed when this is used in a loop? Also, I have seen the Replace function used in nested format:

Replace(Replace(strfileName,"<","")">",""))

Do you know if there is a limit to how many nests can be used? I have read the IIF statements can be nested 9 times, wondering if Replace has a similar limit.

Also, does nesting vs individual statements offer any advantages or disadvantages?
 
S

sneuberg

AWF VIP
Local time
Yesterday, 16:15
Joined
Oct 17, 2014
Messages
3,506
NauticalGent said:
Cool function Steve. Have you noticed any degradation in speed when this is used in a loop? Also, I have seen the Replace function used in nested format:

Replace(Replace(strfileName,"<","")">",""))
Click to expand...

I've run some tests which you can try for yourself with the attached database. On my system the function as I have written it, when run 10000 time by the following program, takes 274 milliseconds.

Code: 
Sub RemoveIllegalFileCharactersSpeedTest()
Dim StartTime As Long
Dim FileName As String
Dim i As Long
FileName = "TestFile<>:""/\|?*"
StartTime = GetClock
For i = 1 To 10000
    Call RemoveIllegalFileCharacters(FileName)
   ' Call RemoveChars(FileName, "<>:""/\|?*")
Next i
Debug.Print "Time in milliseconds: " & GetClock - StartTime


End Sub

If you change it to the nested way like:

Code: 
Public Function RemoveIllegalFileCharactersV2(strFileName As String) As String

RemoveIllegalFileCharactersV2 = Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(strFileName, "<", ""), ">", ""), ":", ""), """", ""), "/", ""), "\", ""), "|", ""), "?", ""), "*", "")

End Function

It takes 332 milliseconds

And this generic version

Code: 
Public Function RemoveChars(strInput, strRemoveChars)

Dim i As Long
If Len(strRemoveChars) < 1 Or Len(strInput) < 1 Then
    Exit Function
End If

For i = 1 To Len(strRemoveChars)
     strInput = Replace(strInput, Mid(strRemoveChars, i, 1), "")
Next i
RemoveChars = strInput
End Function

takes 320.

Do you know if there is a limit to how many nests can be used? I have read the IIF statements can be nested 9 times, wondering if Replace has a similar limit.
Click to expand...

In VBA I would think that the limit would depend on the stack size and would be a lot. I can't think of any easy way to test it. In the query grid there does seem to be an arbitrary limit but if you google the topic you get all kind of opinions ranging from 7 to the 1024 character limit of the grid. I played with a simple IIF expression like:

Code: 
A:  IIf(0, 1, IIf(0, 1, IIf(0, 1, IIf(0, 1, IIf(0, 1, IIf(0, 1, IIf(0, 1, IIf(0, 1, IIf(0, 1, IIf(0, 1, IIf(0, 1, IIf(0, 1, IIf(0, 1, IIf(0, 1, 0))))))))))))))

which has 14 levels of nesting and it worked. I found if I go to 15 I get the "expression is too complex" error. However, I found that an expression with 15 nested replace functions works. I really hate undocumented crap like this.

Also, does nesting vs individual statements offer any advantages or disadvantages?
Click to expand...
You can see that the nested version is slower which I would expect because of the recursive call.
 

Attachments

  • PutzingWithReplace.accdb
    404 KB · Views: 111
NauticalGent

NauticalGent

Ignore List Poster Boy
Local time
Yesterday, 19:15
Joined
Apr 27, 2015
Messages
6,365
Steve, that was impressive to the least. Thanks for the reply, it was on point and I really appreciate it!
 
You must log in or register to reply here.

Similar threads

J
Auto Email
Replies
3
Views
692
arnelgp
arnelgp
mike60smart
Error 2501
2
Replies
29
Views
770
mike60smart
mike60smart
mike60smart
PDF Attachments
Replies
5
Views
739
mike60smart
mike60smart
G
Help with string across multiple private subs
Replies
9
Views
663
Pat Hartman
P
P
Error code solution
Replies
14
Views
523
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom