Task
The task is to generate pdf documents in the file folder from Access database using the parameters generated by a sql distinct select statement. The parameters will be passed to the Access report preview as the filter value and used to form the file name of the pdf.
Problem 1
Keep getting “Formatting Page” on the status bar and the execution of the VBA seems stuck in that status.
Solution 1
- Check properties of all the headers to make sure “Keep Together” (under Format tab) is equal to “No”
Cause
- Access is trying to calculate how to best fit the contents together whick takes time.
Solution 2
- Remove the “Pages” function in the footer
Cause
- “Pages” function will force Access to determine the last page and the report can’t be previewed until formatting are applied to all the pages of the report and the last page is calculated.
Problem 2
Access was not able to create pdf document and stopped at certain parameter intake.
Solution 1
- Replace the “/” character in the value of the variable
-
var = Replace(rs(“Column_Name”), “/”, “_”)
Cause
- The parameters that were passed to pdf filename include characters like “/” and “:”, which are reserved by MS for other purpose and can’t not be used as part of the MS filename.
###Solution 2###
- check the length of string that was used a parameter for the filename and shorten the string in case it is too long
-
If len(rs(“Column_Name”)) > 35 then var = left(rs(“Column_Name), 35)
Cause
- Microsoft has a limit of 255 characters on filename.