Displaying specific details in a report

[benjee]

Hello and happy easter!

Have a reports menu, and one of the options when clicked displays all invoices for every customer (in a report).

When this particular button is clicked is it possible for some kind of list box to appear, displaying customers names, so i don't have to remember them each time.

Thus enabling a specific invoice to appear for the selected customer.

 

[Pat Hartman]

This form doesn't toggle the project combo visible and hidden although some other more complex forms do. But for simplicity I choose this one. To show/hide the combo, set it as not visible in the form properties and put something like the following in the AfterUpdate event of the control that will control the toggle:
If something then
Me.mycombo.visible = True
Else
Me.mycombo.visible = False
End If

The attached picture shows what the selection form looks like and the following code shows how to work with the presence/absence of a specific selection.

Private Sub cmdPreview_Click()
On Error GoTo Err_cmdPreview_Click

    Dim strDocName As String
    Dim PrintMode As Integer
    Dim strWhere As String

    strWhere = ""
    
    If Me.fraPrintOption = 2 Then
        PrintMode = acNormal
    Else
        PrintMode = acPreview
    End If
    
    Select Case Me.fraReportName
        Case 1
            strDocName = "rptArtifactStatus"
            strWhere = "ProjectID = " & Me.cmbProjectVersion
        Case Else
            MsgBox "Please select a report", vbOKOnly
            Exit Sub
    End Select
    
    If IsNull(Me.cmbProjectVersion) Then
        DoCmd.OpenReport strDocName, PrintMode
    Else
        DoCmd.OpenReport strDocName, PrintMode, , strWhere
    End If
    
Exit_cmdPreview_Click:
    Exit Sub

Err_cmdPreview_Click:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_cmdPreview_Click
    
End Sub

 

[benjee]

Pat code works on the preview but not the print option.

Here is my amended code: -

Private Sub Command3_Click()

On Error GoTo Err_Command3_Click

Dim strDocName As String
Dim PrintMode As Integer
Dim strWhere As String

strWhere = ""

If Me.fraprintoption = 2 Then
PrintMode = acNormal
Else
PrintMode = acPreview
End If

Select Case Me.fraprintoption
Case 1
strWhere = "[Surname] = """ & Me.[Combo0] & """"
strDocName = "rptInvoice"
DoCmd.PrintOut "rptInvoice", acPreview, PrintMode
Case Else
MsgBox "Please select a report", vbOKOnly
Exit Sub
End Select

If IsNull(Me.Combo0) Then
DoCmd.OpenReport strDocName, PrintMode
Else
DoCmd.OpenReport strDocName, PrintMode, , strWhere
End If

Exit_Command3_Click:
Exit Sub

Err_Command3_Click:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_Command3_Click

End Sub

If you view the image you should see what im trying to do. Basically i want to print all reports via customer, rather than selecting the report name

 

[Pat Hartman]

Make sure that your print option group is named "fraPrintOption" or change the code. Make sure that preview is the default print option. Your really should use meaningful control names. Who knows what command3 is? or Combo0?

Private Sub Command3_Click() 

On Error GoTo Err_Command3_Click 

Dim strDocName As String 
Dim PrintMode As Integer 
Dim strWhere As String 

strWhere = "" 

If Me.fraprintoption = 2 Then 
PrintMode = acNormal 
Else 
PrintMode = acPreview 
End If 

strWhere = "[Surname] = """ & Me.[Combo0] & """" 
strDocName = "rptInvoice" 
DoCmd.OpenReport strDocName, PrintMode, , strWhere 

Exit_Command3_Click: 
Exit Sub 

Err_Command3_Click: 
MsgBox Err.Number & " - " & Err.Description 
Resume Exit_Command3_Click 

End Sub

 

[benjee]

Pat once again thanks for your post -- most helpful.

But regarding your 'meaningful name' remark, for the purposes of this example i understand what those control names relate to and i intend to change the questionable control names when i release my product for beta testing.

regards

 

[Rich]

Will you remember all the references you've made to them?

 

[benjee]

Hello,

I was going to provide a scarcastic answer then, but his is nor the time or the place.

Yes, i will remember each reference ive made.

thanks.

 

[Rich]

For what possible reason would a genuine question be worthy of a sarcastic response, no worries, we now know how to deal with your questions

 

[Pat Hartman]

When you change a control name AFTER events have been built, the events are orphaned. You'll need to rebuild them after the controls are renamed and delete the old ones.

The first thing I do after I build a form is to change all the control names. If the wizards build the form, they use the field name as the control name. This is fine until you start writing code. Then you run into problems when you need to be able to distingush between a field and a control. So I prefix all the control names with "txt". Therefore, CompanyName becomes txtCompanyName. Then when I add controls manually, I change the control names immediately. That way, the controls are all properly named before I start building event procedures.

 

[benjee]

Hello, in response to Pats post about previewing or printing a report is there a way to email a customer with their specfic invoice report.

I have implemented this code, but it only sends an email to an email address that i have inputted in the code and it sends all records in the report (all customers invoices).

DoCmd.SendObject acSendReport, "rptInvoice", acFormatHTML, "mail@badlard.plus.com", , , "Invoice -- Jon Adlard Surveyor/Estimator", "This is your invoice"

How can i send a report to a customer via the same combo box?

 

[Pat Hartman]

Since the SendObject method does not support the where argument, you need to change your technique. One alternative is to set a filter in the report's open event:

Private Sub Report_Open(Cancel As Integer)
If IsNull(gMultiSelect) Then
Else
    Me.Filter = "CMMLevelID IN(" & gMultiSelect & ")"
    Me.FilterOn = True
End If
End Sub

My code refers to a public function that creates a string from a multi-select list box. You can also just refer to a form field:

Me.Filter = "SomeField" = Forms!YourForm!YourControlName

Here's the function in case someone is interested in how you use a multi-select listbox to filter a report.

Public Function gMultiSelect(ctlRef As ListBox) As Variant
    Dim Criteria As String
    Dim i As Variant

   ' Build criteria string from selected items in list box.
    Criteria = ""
    For Each i In ctlRef.ItemsSelected
        If i > 8 Then
            MsgBox "Only first 9 selections were accepted.  Excess discarded.", vbOKOnly
            gMultiSelect = Criteria
            Exit Function
        End If
        If Criteria <> "" Then
            Criteria = Criteria & ","
        End If
        Criteria = Criteria & Format(ctlRef.ItemData(i), "0000000")
    Next i

gMultiSelect = Criteria
End Function

 

[benjee]

Ive implemented your example Pat, but have decided to develop my appraoach.

There are now three options in fraprintoption, preview, print and email. The email function works with the specific report as the attachment, but when MS Outlook is loaded up the surname of the customer appears in the To: column rather than the email address.

Here is my code Pat i would grately appreciate it if you had a look and see where my problem is occuring.

Also when i select preview -- select a customer -- and click the button MS Outlook still loads up?!?

Private Sub OpenrptInvoice_Click()

On Error GoTo Err_OpenrptInvoice_Click

Dim strDocName As String
Dim PrintMode As Integer
Dim strWhere As String
Dim stDocName As String
Dim stEmail As String
Dim stSubject As String
Dim stBody As String

strWhere = ""

If Me.fraprintoption = 2 Then
PrintMode = acNormal
Else
PrintMode = acPreview
End If

strWhere = "[Surname] = """ & Me.[CmbRetrieveCustomer] & """"
strDocName = "rptInvoice"
DoCmd.OpenReport strDocName, PrintMode, , strWhere

If Me.fraprintoption = 3 Then 'Dodgy bit, have i set the right variables??
PrintMode = acNormal
Else
PrintMode = acNormal
End If

stDocName = "rptInvoice"
stEmail = " = """ & Me.[CmbRetrieveCustomer] & """"
stSubject = "--Invoice-- Jon Adlard Surveying Services"
stBody = "This find enclosed a copy of your invoice"
DoCmd.SendObject acReport, stDocName, acFormatRTF, stEmail, , , stSubject, stBody, True


Exit_OpenrptInvoice_Click:
Exit Sub

Err_OpenrptInvoice_Click:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_OpenrptInvoice_Click
End Sub

 

[Pat Hartman]

stEmail should just contain the actual email address.

stEmail = "myaddress@somewhere.com"

Is that what is in CmbRetrieveCustomer?

 

[benjee]

Yeah the email address is stored in the combo box.

I didnt want to 'hardcode' the email in, because it is subject to change (user could select different customer with a different email address)

 

[Pat Hartman]

I'm sorry, I wasn't suggesting that you hard-code the email address, only that that's what the combo needs to return.