Sending a report based on a combo box

[dark11984]

Hi there can someone please help me out with this? It is really doing my head in.

I am trying to send a order confirmation report to our vendors.
Vendor is selected from a combo box then a command clicked to send the report.

I nearly have it working (I think) but i keep getting an error "invalid use of null". However column 2 is not null it contains an email address???

Private Sub CmdEmailPrint_Click()
DoCmd.OpenReport "rptorderconf", acViewPreview
    Dim strVendor As String
    Dim strSend As String
If IsNull(Me.CboVendor.Value) Then
        strVendor = "Like '*'"
    Else
        strVendor = "='" & Me.CboVendor.Value & "'"
    End If
 
    strSend = "[vendor]" & strVendor
 
    With Reports![rptorderconf]
         .Filter = strSend
         .FilterOn = True
         .txtrpttitle.Value = _
            "Order Confirmation - " & Format(Date, "dd mmm yyyy") _
            & vbCrLf & Me.CboVendor.Column(1)
    End With
 
    Dim sToName As String
    Dim sSubject As String
    Dim sMessageBody As String
If IsNull(Me.CboVendor.Value) = False Then
    sToName = Me.CboVendor.Column(2)
    sSubject = Me.CboVendor.Column(1) & " " & "Order Confirmation Test" & " " & Format(Now, "DD-MMM-YYYY")
    sMessageBody = "Hi There"
 
    DoCmd.SendObject acSendReport, "RptOrderConf", "snapshot format", _
    sToName, , , sSubject, sMessageBody, True, False
    End If
End Sub

Thanks in advance for your help!

 

[pbaldy]

You didn't say what line threw the error, but are you aware that the column property is zero based? In other words, Column(2) would refer to the third column in the rowsource. If there were only two, that might be your error.

 

[dark11984]

Oops it is

sToName = Me.CboVendor.Column(2)

Yes i am aware of that.
column(0) = Vendor
column(1) = vendorname
column(2) = email

If i change the code to read column(1) it inserts the vendor name in the recipient line of the email so i'm not sure why it won't work by putting column(2)? Any ideas?

cheers

 

[pbaldy]

If you go into debug mode and hover over the combo reference, does it have a value? Are you sure every record has a value for the email address? Can you post the db?

 

[dark11984]

Success! i had the column count as 1. I changed it to 3 and it worked. But that doesn't explain why i was able to change my code to column(1) and get it to work?

I also have another problem now, my filter is not working. The report should be filtered as per cbovendor.column(0) but no filter is applied at all? It works if i change the column count back to 1 though? now i'm confused!?!?

 

[pbaldy]

I've never tried it that way, so not sure you can apply a filter after the report is open. I'd open it this way and then send:

http://www.baldyweb.com/wherecondition.htm

 

[JANR]

If IsNull(Me.CboVendor.Value) Then
        strVendor = "Like '*'"
    Else
        strVendor = "='" & [COLOR=red]Me.CboVendor.Value[/COLOR] & "'"
    End If

Just remember that the value of your combo is the value it is bound to and not what is displayed. Look at your Bound property, and i am certain it is bound to vendor and not vendorname. (if that was your intent)

Perhaps change to Me.CboVendor.Column(1), and see if that invokes the right filter.

JR

 

[dark11984]

Thanks Baldy that worked great!!

 

[pbaldy]

Excellent, glad to hear it.

 

[dark11984]

ok need your help again on this same code.
Some of our vendors are not setup with Email. So i have added an if statement into either print or email depending on what has been entered int he vendor table. This works fine except i can't get the title of the report to shwo up when the print option is selected.

Private Sub CmdEmailPrint_Click()
On Error GoTo Err_CmdEmailPrint_Click
If (Me.CboVendor.Column(3)) = "Print" Then
DoCmd.OpenReport "rptorderconf", , , "vendor='" & Me.CboVendor & "'"
 
    With Reports![rptorderconf]
            .txtrpttitle.Value = _
            "Order Confirmation - " & Format(Date, "dd mmm yyyy") _
            & vbCrLf & Me.CboVendor.Column(1)
    End With
 
ElseIf (Me.CboVendor.Column(3)) = "Email" Then
DoCmd.OpenReport "rptorderconf", acViewPreview, , "vendor='" & Me.CboVendor & "'"
 
    With Reports![rptorderconf]
            .txtrpttitle.Value = _
            "Order Confirmation - " & Format(Date, "dd mmm yyyy") _
            & vbCrLf & Me.CboVendor.Column(1)
    End With
 
    Dim sToName As String
    Dim sSubject As String
    Dim sMessageBody As String
If IsNull(Me.CboVendor.Value) = False Then
        sToName = Me.CboVendor.Column(2)
        sSubject = Me.CboVendor.Column(1) & " " & "Order Confirmation Test" & " " & Format(Now, "DD-MMM-YYYY")
        sMessageBody = "Hi There"
 
    DoCmd.SendObject acSendReport, "RptOrderConf", "snapshot format", _
    sToName, , , sSubject, sMessageBody, True, False
    End If
End If
 
Exit_CmdEmailPrint_Click:
    Exit Sub
Err_CmdEmailPrint_Click:
    MsgBox "You have chosen not to send an order confirmation for " & Me.CboVendor.Column(1) & ". Please try again."
    Resume Exit_CmdEmailPrint_Click
 
End Sub

The part where it is going wrong is:

If (Me.CboVendor.Column(3)) = "Print" Then
DoCmd.OpenReport "rptorderconf", , , "vendor='" & Me.CboVendor & "'"
 
    With Reports![rptorderconf]
            .txtrpttitle.Value = _
            "Order Confirmation - " & Format(Date, "dd mmm yyyy") _
            & vbCrLf & Me.CboVendor.Column(1)
    End With

Thanks

 

[pbaldy]

The report is on the printer by the time that code runs. I would do that in the open event of the form. Depending on version you can pass the desired string in OpenArgs or just build it in the open event referring back to the calling form to get the value from the combo.