I have 24 reports that ask for two values, "Enter Parameter Value", one is the month and year of the report and the other is the Salesman. I am trying to write a macro to print these 24 (x 6 Salesmen) reports, but I can't figure out how to fill in the two parameters when using the "OpenReport" in the macro. I have the values in two tables, [tblMonths] with field [Report Month] and [tblSalesmen] with field [Saleman code]. Thank you in advance.

Access 2016 Macro - How to Enter Parameter Value

Answer accepted by question author

Anonymous

I don't guess there is a way to close or not even open the PDF when it is created? Meaning, when it prints the PDF reports, each one is left open onscreen as well as saved to the correct folder.

You can easily create the files without opening them, by setting the final argument of the SendObject method to FALSE rather than TRUE.

0 comments

Answer accepted by question author

Anonymous

1. You can return the path to a folder selected by the user by means of the FileDialog property of the Application object. The InvoicePDF demo does this with the following function:

Function GetFolder(Optional varInitialFolder As Variant) As String

Const FOLDER_PICKER = 4

With Application.FileDialog(FOLDER_PICKER)

.Title = "Select Folder"

If Not IsMissing(varInitialFolder) Then

.InitialFileName = varInitialFolder

End If

If .Show Then

GetFolder = .SelectedItems(1)

End If

End With

End Function

In the demo it is called by means of the Browse button on the opening form, with the following code:

Dim strFolder As String

strFolder = GetFolder()

If Len(strFolder) > 0 Then

Me.FolderPath = strFolder

End If

In your case you would call it in your button's code to select a folder path to which the PDF file is to be output.

2. A table of report names would be a simple solution as it would then merely be a matter of looping through a recordset. Another way would be to loop through the Document objects in the Reports Container. The following is code which does this to populate a list box with all reports which have a value in the report's Description property. To return a specific subset of reports a suitable naming convention could be used in the Description property to identify the subset. The code could then return only those reports where the naming convention matches a string expression which could be the first n characters of the Description property for instance. In your case the report name returned, strReportName, instead of populating a list box, would be used when calling the OpenReport method of the DoCmd object.

On Error GoTo Err_Handler

Const NODESCRIPTION = 3270

Dim ctrl As Control

Dim dbs As DAO.Database

Dim ctr As DAO.Container

Dim doc As DAO.Document

Dim strReportname As String

Dim strDescription As String

Set dbs = CurrentDb

Set ctr = dbs.Containers("Reports")

Set ctrl = Me.lstReports

ctrl.RowSourceType = "Value List"

ctrl.ColumnWidths = "0"

ctrl.BoundColumn = 1

ctrl.ColumnCount = 2

For Each doc In ctr.Documents

strReportname = doc.Name

On Error Resume Next

strDescription = doc.Properties("Description")

Select Case Err.Number

Case 0

' no error

Case NODESCRIPTION

' report has no description

' so ignore and go to next report

GoTo EndLoop

Case Else

' unknown error

GoTo Err_Handler

End Select

Err.Clear

On Error GoTo Err_Handler

ctrl.AddItem strReportname & ";" & strDescription

EndLoop:

Next doc

Exit_Here:

Exit Sub

Err_Handler:

MsgBox Err.Description, vbExclamation, "Error"

Resume Exit_Here

3. The Column property of a list box or combo box references a column in the control's RowSource which is not the bound column. The property is zero-based, so Column(1) references the second column. In my demo the lstInvoices control's RowSource property is:

SELECT InvoiceNumber, Customer, InvoiceDate

FROM qryInvoices

ORDER BY Customer, InvoiceDate;

So, Column(1) in my case would reference the Customer column, and return the customer name. This is concatenated with the invoice number into the filename, e.g. ACME Flanges 20111208103010.pdf.

0 comments

Answer accepted by question author

Anonymous

I'm now working on building a macro tied to a button on the Form that will run and print all of the needed reports to a PDF...another challenge, but I think I found articles on how to print to PDF and automatically save to the correct place.

On this issue you might like to take a look at Invoice.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

In this little demo file the code (I doubt this would be possible with a macro) for outputting the multiple invoices to separate files is as follows:

Private Sub cmdCreateInvoices_MultiFiles_Click()

On Error GoTo Err_Handler

Const FOLDER_EXISTS = 75

Const MESSAGE_TEXT_1 = "No folder set for storing PDF files."

Const MESSAGE_TEXT_2 = "No invoice(s) selected."

Dim strFullPath As String

Dim varFolder As Variant

Dim varFolder_1 As Variant

Dim varItem As Variant

' build path to save PDF file

varFolder = DLookup("Folderpath", "pdfFolder")

If IsNull(varFolder) Then

MsgBox MESSAGE_TEXT_1, vbExclamation, "Invalid Operation"

Else

With Me.lstInvoices

If .ItemsSelected.Count > 0 Then

For Each varItem In .ItemsSelected

Me.txtInvoiceList = .ItemData(varItem)

' create folder if does not exist

varFolder_1 = varFolder & "" & .Column(1, varItem)

MkDir varFolder_1

strFullPath = varFolder_1 & "" & .Column(1, varItem) & " " & .ItemData(varItem) & ".pdf"

DoCmd.OutputTo acOutputReport, "rptInvoiceMultiple", acFormatPDF, strFullPath, True

Next varItem

Else

MsgBox MESSAGE_TEXT_2, vbExclamation, "Invalid Operation"

End If

End With

End If

Exit_Here:

Exit Sub

Err_Handler:

Select Case Err.Number

Case FOLDER_EXISTS

Resume Next

Case Else

MsgBox Err.Description

Resume Exit_Here

End Select

End Sub

I 'd draw your attention to this line in particular:

Me.txtInvoiceList = .ItemData(varItem)

What this does is assign the invoice number to a hidden text box control in the form. The reason it is named txtInvoiceList is because another option in the form is to output multiple invoices to a single PDF file, in which case the control is assigned a list of invoice numbers. In both cases the control is referenced as a parameter by the report's query, restricting the report to the specific invoice (in the above code) or to multiple selected invoices (in another button's code). As your reports reference parameters, you could do similarly by making those parameters references to hidden controls in the form from which the reports are opened. The key thing is that a different value is assigned to the parameter before outputting the report to a PDF file each time, so in your case you would need to do similarly, but not on the basis of a multi-select list box, as in my case. You would, if I understand your set-up correctly, loop through nested recordsets of months and salespeople.

0 comments

Answer accepted by question author

Anonymous

It's not necessary to "rewrite the database" to correct this.

However, it is necessary to correct the Query upon which the report is based.

As Duane correctly said, if your query contains parameter prompts, it's going to... ask for those parameters. That's how Parameter prompts work, and a Macro cannot change that. It was a mistake to create the report using parameter prompts ten years ago, and that mistake has never been corrected.

However, if you're willing to do some (somewhat tedious with all these reports, but once only) editing of the Query upon which the report is based, you can solve this annoyance.

If you're willing to consider solving the problem, rather than attacking those who are trying to help you, please post the SQL view of the Report's recordsource and one of us will give you a solution that you can use. If you're not willing to take yes for an answer, then I don't know what to suggest.

0 comments

Share via

Access 2016 Macro - How to Enter Parameter Value

9 additional answers