Skip over blank report in code

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Barce5 - 25 Feb 2005 15:43 GMT

I have set up code to send out our new purchase orders on a nightly basis to
the vendor. I would like to skip over any purchase order that is blank so it
doesn't fax. I am not sure how to go about doing this. Any help would be
appreciated.

Thanks,
Lisa

I have attached the code below:

Option Explicit
Public strPurchaseOrderWhere As String

Function FaxInvoices()
'VendorFax is the table that holds the vendor fax numbers

Dim LBNightly As Database
Dim rstvendorfax As Recordset

Set LBNightly = CurrentDb()
Set rstvendorfax = LBNightly.OpenRecordset("vendorfax", dbOpenDynaset)

With rstvendorfax
Do Until .EOF

'Filter to find purchase orders belonging to each vendor in vendor fax
strPurchaseOrderWhere = "[Vendorname] ='" & ![VendorName] & "'"

DoCmd.SendObject acReport, "PurchaseOrder", acFormatSNP, "[FAX: " &
![Fax] & "]", , , , , False

.MoveNext
Loop
End With
rstvendorfax.Close
End Function

Reply to this Message

Troy - 25 Feb 2005 16:32 GMT

Add a DCount() function in your loop prior to sending the report.

Air code below:
If DCount("OrderID", "vendorfax", "[Vendorname] ='" & ![VendorName] & "'") >
0 then
'send report
else
'Don't send report
End if

Signature

Troy

Troy Munford
Development Operations Manager
FMS, Inc.
www.fmsinc.com

I have set up code to send out our new purchase orders on a nightly basis to
the vendor. I would like to skip over any purchase order that is blank so it
doesn't fax. I am not sure how to go about doing this. Any help would be
appreciated.

Thanks,
Lisa

I have attached the code below:

Option Explicit
Public strPurchaseOrderWhere As String

Function FaxInvoices()
'VendorFax is the table that holds the vendor fax numbers

Dim LBNightly As Database
Dim rstvendorfax As Recordset

Set LBNightly = CurrentDb()
Set rstvendorfax = LBNightly.OpenRecordset("vendorfax", dbOpenDynaset)

With rstvendorfax
Do Until .EOF

'Filter to find purchase orders belonging to each vendor in vendor
fax
strPurchaseOrderWhere = "[Vendorname] ='" & ![VendorName] & "'"

DoCmd.SendObject acReport, "PurchaseOrder", acFormatSNP, "[FAX: " &
![Fax] & "]", , , , , False

.MoveNext
Loop
End With
rstvendorfax.Close
End Function

Reply to this Message

David C. Holley - 26 Feb 2005 01:07 GMT

If you examine the data behind the Purchase Orders you should be able to
identify the critirea that indicate if a PO is blank (ex Total Items =
0, Total Price = $0.00). From there modify the reports record source
accordingly.

David H

> I have set up code to send out our new purchase orders on a nightly basis to
> the vendor. I would like to skip over any purchase order that is blank so it
[quoted text clipped - 33 lines]
> rstvendorfax.Close
> End Function

Reply to this Message