Hello, I saw a similar thread on this subject but did not find the answer I was looking for.
I have a report that is used to find undistributed balances of food using a table and a subreport. It includes Date/Donor Company/Bakery/Dairy/Meat/Fruit/Veg/Prod/Prep'd/Bev/Non-Perish/Non-Food/Total
Under that if the "Cross-reference" button is selected it shows the Agency it was delivered to. (Same run through as above "Receiving Agency" instead of Donor Company)
After each pair it has a line that contains the "Undistributed Balance" if any. It simply takes the top donor amount, ex. 100 Bakery, and subtracted each value given to each corresponding agency.
What I want to do is have the report check the undistributed balance, and if it is zero(0) I don't want that Donor Company and its corresponding agency to be displayed. This will allow me to go ahead and re-distribute and balance each month at a glance. Currently it is only effective to view 1 day and 1 donor company at a time otherwise there is atleast 30 pages of report to scan through.
This is the coding under the "Detail" section of the report. (Where the undistributed balance line appears):
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
End Sub
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
End Sub
Private Sub Report_Open(Cancel As Integer)
Dim strCriteria
On Error GoTo eh
If fcnIsFormOpen("fdlgPrintReports") Then
strCriteria = Nz(Forms!fdlgPrintReports!txtEnterBeginningDate, "") & IIf(IsNull(Forms!fdlgPrintReports!txtEnterEndingDa te), "", " Through " & Forms!fdlgPrintReports!txtEnterEndingDate)
Me.lblCriteria.Properties("Caption") = strCriteria
If Forms!fdlgPrintReports!optCrossRefReceipts = True Then
Me.rsubFoodDonationbyReceipt.Properties("Visible") = True
Me.txtUndistributedBakery.Properties("Visible") = True
Me.txtUndistributedBeverage.Properties("Visible") = True
Me.txtUndistributedDairy.Properties("Visible") = True
Me.txtUndistributedMeat.Properties("Visible") = True
Me.txtUndistributedNonFood.Properties("Visible") = True
Me.txtUndistributedNonPerish.Properties("Visible") = True
Me.txtUndistributedFruit.Properties("Visible") = True
Me.txtUndistributedVeg.Properties("Visible") = True
Me.txtUndistributedPrepared.Properties("Visible") = True
Me.txtUndistributedProduce.Properties("Visible") = True
Me.txtUndistributedTotal.Properties("Visible") = True
Me.lblUndistributedBalance.Properties("Visible") = True
Me.lblUndistributedBalance.Properties("Height") = 200
Me.lblCorrespondingReceipt.Properties("Height") = 200
Else
If Forms!fdlgPrintReports!fraInventoryOption = 2 Then
Me.rsubFoodDonationbyReceipt.Properties("Visible") = True
Me.txtUndistributedBakery.Properties("Visible") = True
Me.txtUndistributedBeverage.Properties("Visible") = True
Me.txtUndistributedDairy.Properties("Visible") = True
Me.txtUndistributedMeat.Properties("Visible") = True
Me.txtUndistributedNonFood.Properties("Visible") = True
Me.txtUndistributedNonPerish.Properties("Visible") = True
Me.txtUndistributedPrepared.Properties("Visible") = True
Me.txtUndistributedFruit.Properties("Visible") = True
Me.txtUndistributedVeg.Properties("Visible") = True
Me.txtUndistributedProduce.Properties("Visible") = True
Me.txtUndistributedTotal.Properties("Visible") = True
Me.lblUndistributedBalance.Properties("Visible") = True
Me.lblUndistributedBalance.Properties("Height") = 200
Me.lblCorrespondingReceipt.Properties("Height") = 200
Else
Me.rsubFoodDonationbyReceipt.Properties("Visible") = False
Me.txtUndistributedBakery.Properties("Visible") = False
Me.txtUndistributedBeverage.Properties("Visible") = False
Me.txtUndistributedDairy.Properties("Visible") = False
Me.txtUndistributedMeat.Properties("Visible") = False
Me.txtUndistributedNonFood.Properties("Visible") = False
Me.txtUndistributedNonPerish.Properties("Visible") = False
Me.txtUndistributedPrepared.Properties("Visible") = False
Me.txtUndistributedFruit.Properties("Visible") = False
Me.txtUndistributedVeg.Properties("Visible") = False
Me.txtUndistributedProduce.Properties("Visible") = False
Me.txtUndistributedTotal.Properties("Visible") = False
Me.lblUndistributedBalance.Properties("Visible") = False
End If
End If
Else
Me.lblCriteria.Properties("Caption") = ""
Me.rsubFoodDonationbyReceipt.Properties("Visible") = False
Me.txtUndistributedBakery.Properties("Visible") = False
Me.txtUndistributedBeverage.Properties("Visible") = False
Me.txtUndistributedDairy.Properties("Visible") = False
Me.txtUndistributedMeat.Properties("Visible") = False
Me.txtUndistributedNonFood.Properties("Visible") = False
Me.txtUndistributedNonPerish.Properties("Visible") = False
Me.txtUndistributedPrepared.Properties("Visible") = False
Me.txtUndistributedFruit.Properties("Visible") = False
Me.txtUndistributedVeg.Properties("Visible") = False
Me.txtUndistributedProduce.Properties("Visible") = False
Me.txtUndistributedTotal.Properties("Visible") = False
Me.lblUndistributedBalance.Properties("Visible") = False
End If
Exit Sub
eh:
MsgBox Err.Number & " - " & Err.Description & " From " & Err.SOURCE
Exit Sub
End Sub