Originally Posted by
Bulzie
So for a general summary, I would try to keep it at 2 tables, Customers and Checks. Link them by CustomerNumber(so add that field to the Checks table). Make a MainForm with tblCustomers as the RecordSource and add those fields to the Form at the top. Create a 2nd Form (Continuous records) for the Checks which will be a SubForm in the MainFrm in the Detail section. Use the CustomerID in the Parent/Child Link on the Subform. This will mean when you select a new Customer it will show their specific checks at the bottom.
On the Check Subform, I would just put all the fields you need in that table. You could create a 3rd table for NSF Payments but I'm not sure that is a must. I would add these fields:
NSF (Yes or No) combo box default to No
NSFPaymentType (Cash, Money Order) combo box leave as default to null
NSFPaymentAmount
So idea would be
1. When check comes in, you enter the check info. NSF is already defaulted to "No", NSFPaymentType = Null, NSFPaymentAmount = 0, Fees = 0, Balance = 0.
2. If check comes back NSF, change field NSF = "Yes" and in AferUpdate of that NSF combo box, update the Fees and Balance:
Me.Fees = CheckAmount * .10 or whatever the calculation is for Fees.
Me.Balance = Me.CheckAmount + Me.Fees
3. When they give you say cash to pay it off, update NSFPaymentAmount to the amount they gave you, NSFPaymentType = "Cash" and in AfterUpdate on NSFPaymentType:
Me.Fees = 0 (may want to just leave this at the fee amount and not zero out.
Me.Balance = Me.Balance - NSFPaymentAmount. If they pay the exact amount Balance is 0, if they pay Partial, Balance is what they have left.
Others might offer something different, this is just one approach from what I understood from the process.