Skip over blank report in code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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

--
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
 
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
 
Back
Top