Automatic Query Parameters in VBA in a Report to be sent

[epardo87]

Hello all,

I am working in a code to automatically send a list of reports by clicking one single button, I have successfully written a code to send a report of a query in PDF without any issue

The deal in this moment is that some of the reports, have a configurable parameter through combo boxes in different forms (for an optional manual report generation), but I just need them all to be set with an asterisk so that the report is generated without filtering, in most cases, two parameters are requested,

How could I arrange a code to automatically answer all prompted parameter requests with an asterisk?

Below is the code so far (since I'm testing, this code just sends single report):

Private Sub btn_Send_Click()


Dim oApp As Object
Dim oEmail As Object
Dim fileName As String, todayDate As String


todayDate = Format(Date, "MMDDYYYY")
fileName = "\\mxchi-fs02\danmexshr\BD\Certificaciones\Reports" & "\Report-ExpiringTop_" & todayDate & ".pdf"
DoCmd.OutputTo acReport, "Reporte Vencídos", acFormatPDF, fileName, False


Set oApp = CreateObject("Outlook.Application")
Set oEmail = oApp.CreateItem(0)


oEmail.To = DLookup("
[Lista]", "[tbl-sendlist]", "[ID]= 1")
oEmail.Subject = "Test"
oEmail.Body = "Testing"
oEmail.Attachments.Add fileName
oEmail.Send
MsgBox "Email Sent"
    
End Sub

I'd be very thankful for any help I can get!

[June7]

What do you mean by 'configurable parameter'? The report has a RecordSource that is a dynamic parameterized query? How would this dynamic parameter reference comboboxes of different forms? You would manually open the report in design view and change the reference? Use LIKE operator with wildcard. May require some adjustment to the values for number type field.

I NEVER use dynamic parameterized query and therefore no popup prompts. Too hard to validate input of popup prompt. If parameters reference comboboxes why do you get prompts?

I use VBA to construct filter criteria and set the WHERE CONDITION argument of OpenReport/OpenForm. Example:

        DoCmd.OpenReport "LabReport", acViewPreview, , "Submit.LabNum='" & Me!LABNUM & "'"
        If Dir(strPath & Me!LABNUM & ".pdf") <> "" Then Kill strPath & Me!LABNUM & ".pdf"
        DoCmd.OutputTo acOutputReport, "", acFormatPDF, strPath & Me!LABNUM & ".pdf", False
        DoCmd.Close acReport, "LabReport", acSaveNo

[Micron]

If the forms are open and the controls contain values, there should be no parameter prompt, so I guess one or both of those conditions are not true. You cannot easily code to override a parameter prompt - unless you are quite able to code to test if the form(s) in question is/are open AND the specified controls are not null AND can then base the report on one of two query defs - one with and one without parameters.

IMHO, I think the simplest approach would be to provide an option in the user interface (report generating form?) where you're opening the reports from. This could be checkboxes or option buttons in a grouping frame for example. The setting determines if the reports are based on the sql with parameters or not. You can either construct one query for each option, or build the sql in code according to the setting and open the report based on the desired sql. Hope that's not too confusing of an explanation.

[epardo87]

What do you mean by 'configurable parameter'? The report has a RecordSource that is a dynamic parameterized query? How would this dynamic parameter reference comboboxes of different forms? You would manually open the report in design view and change the reference? Use LIKE operator with wildcard. May require some adjustment to the values for number type field.

I NEVER use dynamic parameterized query and therefore no popup prompts. Too hard to validate input of popup prompt. If parameters reference comboboxes why do you get prompts?

I use VBA to construct filter criteria and set the WHERE CONDITION argument of OpenReport/OpenForm. Example:

        DoCmd.OpenReport "LabReport", acViewPreview, , "Submit.LabNum='" & Me!LABNUM & "'"
        If Dir(strPath & Me!LABNUM & ".pdf") <> "" Then Kill strPath & Me!LABNUM & ".pdf"
        DoCmd.OutputTo acOutputReport, "", acFormatPDF, strPath & Me!LABNUM & ".pdf", False
        DoCmd.Close acReport, "LabReport", acSaveNo

The thing is that there are like 10 different reports available, some of them can be filtered according to the user needs, for example one can filter to see only data regarding a manager, other filters the area, etc. so I've configured all querys to assign "*" when no filtered data was entered, so that they show the full report. that's why they need to have dynamic parameters.

Now, these guys want an email every week with 4 of those reports, so for that purpose I need to have the reports sent in PDF and would like the parameter prompts to be answered with "*" automatically...

[epardo87]

If the forms are open and the controls contain values, there should be no parameter prompt, so I guess one or both of those conditions are not true. You cannot easily code to override a parameter prompt - unless you are quite able to code to test if the form(s) in question is/are open AND the specified controls are not null AND can then base the report on one of two query defs - one with and one without parameters.

IMHO, I think the simplest approach would be to provide an option in the user interface (report generating form?) where you're opening the reports from. This could be checkboxes or option buttons in a grouping frame for example. The setting determines if the reports are based on the sql with parameters or not. You can either construct one query for each option, or build the sql in code according to the setting and open the report based on the desired sql. Hope that's not too confusing of an explanation.

Thanks Micron, that gave me the idea of how to solve this issue,

Since the query automatically assigns "*" when the form is open, I decided to create a DoCmd.Openform before and another one to close it after the file is generated so it looks like below, (in this example, only the first file requires parameters) and it worked fine!

Private Sub btn_Send_Click()

Dim oApp As Object
Dim oEmail As Object
Dim fileName1 As String, todayDate As String, fileName2 As String


DoCmd.OpenForm "frm-Expired"
todayDate = Format(Date, "MMDDYYYY")
fileName1 = "\\mxchi-fs02\danmexshr\BD\Certificaciones\Reports" & "\Report-Expired_" & todayDate & ".pdf"
DoCmd.OutputTo acReport, "Reporte Vencídos", acFormatPDF, fileName1, False
DoCmd.Close acForm, "Frm-Expired", acSaveNo


fileName2 = "\\mxchi-fs02\danmexshr\BD\Certificaciones\Reports" & "\Report-ScheduledTop_" & todayDate & ".pdf"
DoCmd.OutputTo acReport, "Report-ScheduledTop", acFormatPDF, fileName2, False


Set oApp = CreateObject("Outlook.Application")
Set oEmail = oApp.CreateItem(0)


oEmail.To = DLookup("
[Lista]", "[tbl-sendlist]", "[ID]= 1")
oEmail.Subject = "Test"
oEmail.Body = "Testing"
oEmail.Attachments.Add fileName1
oEmail.Attachments.Add fileName2
oEmail.Send
MsgBox "Email Sent"
    
End Sub

[Micron]

Glad you got it solved to your satisfaction. Some opinions:
- You should always reclaim memory space that has been assigned to any object you create when you are done with it. This would generally be anything that you had to allocate memory to via the SET keyword. The method is Set TheObject = Nothing (TheObject being the name of the object variable in question).
- Just about every procedure should have an error handling routine. If not found in the procedure being executed, Access looks for one in whatever procedure called the executing procedure (if applicable) and continues to look backward through the stack. If still not found, Access handles the error itself, which can often be cryptic to the user. Typically, code like this can fail when network locations have been altered, an application cannot be found or loaded (such as Outlook), an attachment cannot be found (could be that it failed to be created), etc. You should at least handle the most obvious.
- what if the form is already opened by some other process/activity? If possible, you should check and close it first before relying on it having the most up to date record set when there are other users who may have created records after it was initially opened.

[epardo87]

Glad you got it solved to your satisfaction. Some opinions:
- You should always reclaim memory space that has been assigned to any object you create when you are done with it. This would generally be anything that you had to allocate memory to via the SET keyword. The method is Set TheObject = Nothing (TheObject being the name of the object variable in question).
- Just about every procedure should have an error handling routine. If not found in the procedure being executed, Access looks for one in whatever procedure called the executing procedure (if applicable) and continues to look backward through the stack. If still not found, Access handles the error itself, which can often be cryptic to the user. Typically, code like this can fail when network locations have been altered, an application cannot be found or loaded (such as Outlook), an attachment cannot be found (could be that it failed to be created), etc. You should at least handle the most obvious.
- what if the form is already opened by some other process/activity? If possible, you should check and close it first before relying on it having the most up to date record set when there are other users who may have created records after it was initially opened.

thanks for your advice Micron, very much appreciated. I actually did think about the already opened situation, I will create an evaluation for it; there might be errors due to access rights of other users in the export location, so I will add an error handling there; and I will investigate further to understand better the SET method.

Best regards!