Help with creating a Debtor invoice allocation form (1 Viewer)

I have been plugging away at Access and – to a lesser extent- VBA for the last couple of months now.

Whilst on the last major hurdle I am stuck and am unsure of how to proceed. The problem concerns my debtors account receivable allocation form.

In short, I am trying to cobble together an Access DB for my commercial real estate business, here in Australia. For a bit of background, I have summarised the reasons for my project in my introduction post here:

http://www.access-programmers.co.uk/forums/showthread.php?t=287834

I’m really happy with my level of independence with this project, having only asked for help a couple of times from you good folks here on the forum.

At the beginning I was a total noob. Now, after 2 months of full time work I feel much more at home with Access. Anyway, back to the question at hand.

If this development were for any other business I wouldn’t bother with allocating my payments received with my debtor invoices. But this is Real Estate and so it’s important to be able to see which ‘line item’ (i.e. Rent, Outgoings, Rates etc) remains unpaid.

I have pushed the above task to the end of my development plans because it is easily the most challenging for my ability level (Intermediate Access/Novice Intermediate VBA). But now I must confront the beast and push on through to the end of my development.

To assist in completion of the task I have created a test DB – with a simplified set of objects-, in which to practice (I enclose a screenshot and a copy)

Invoices are created in tblInvoice and the ‘line items’ in tblInvoiceLineItems.

Payments received are recorded in tblReceipts and tblInLineItemsM2M. There is a foreign key requirement in the M2M table which requires the line item Primary Key. This is to tie a payment to a line item. From there I plan to calculate the differences between the line items and the payments received on the fly, but I haven’t reached that bit yet.

The plan is to create a form which documents the receipts. In a subform I plan to display a list of outstanding debtor invoices. Ideally Id like to be able to use unbound controls in the subform (checkboxs etc) and to have VBA do the calculations in the form before writing to the underlying tables.

I am running into problems regardless of the two directions that I take with this:

1. If I place unbound controls in the subform (one for each record) I can only select all or none, there is no independent ON/OFF behaviour in which to drive the Allocation process.
2. If I place the above controls as fields in the respective tables then I find that I am having problems creating editable recordsets when creating Queries.

I suspect the second option is probably the way forward but I was hoping someone on the forums might be able to give me a couple of pointers on how best to proceed.

The form which best illustrates my issue would be [frmReceipts] as its kinda half way there.

If anyone could offer any suggestions as to the best way to proceed with this then I would be most grateful.

A good rule for forms is that for every table whose records you want to act on (add/edit/delete), then there should be 1 form . You have 4 tables in that relationship, so it would necessitate 4 forms if you want to be able to act on all of their data. I see no way to do this with just 2 forms (main & sub).

Also, you've got a weird structure for invoices/payments.

1. In most instances you pay invoices, not line items. By tying payments to line items you've in effect made every line item an entire invoice. Your invoice table seems irrelevant.

2. tblInLineItemsM2M further complicates things. This table allows me to pay Line Item 1 on Invoice #3 and Line Item #7 on Invoice #2 with the same payment and without paying the rest of the line items on those invoices. This could be a potential nightmare if anyone ever disputes a payment or you make an error that needs to be backed out:

"No, $50 of Payment #8 was to go to Line Item 3 on Invoice #60 and $60 was to go to Line Item #8 on Invoice #50."

Maybe that's how detailed you need things, but I would seriously reconsider if it is. Again though, 1 table=1 form. If you need to be able to interact with each of those 4 tables, you are going to need 4 forms.

Hi Plog,
Thanks for taking the time out to look at my situation and provide some feedback. It’s most appreciated.

With regards to the number of tables used to allow proper data flow. I had planned to use two main forms, both with subforms. In my head (and therefore in the table design) I see it as data flow from two opposite ends of the table structure, depending on whether you are raising an invoice to a customer (or tenant) or whether you are receipting a payment from that tenant to pay off the invoice.

When entering a payment receipt I will only have to link the payment to the right Primary Key and so the payment forms can display read only data on the invoices that are outstanding.

I have never constructed a set of tables like the above so am in unchartered territory. The important thing to remember is that I must be able to associate a payment with a particular invoice (or line item). Whether that requirement is met by creating a formal relationship or whether it is done during the query creation process (a temp link) I am not sure.

On your numbered points:

1. In my area of work it is quite common to see invoices being part paid, especially when the customer (tenant) is disputing a line item (rent etc.). This is a bit different from normal situations where the invoice itself is nearly always paid off in full. In my structure I see the invoice itself as merely a vehicle in which to deliver the line items to the tenant. Multiple payments can then be made towards multiple line items, hence the M2M relationship.
a. I should point out here that there is an additional M2M table in the production DB, meaning that there are 5 tables in the chain instead of 4, which does make things quite complicated.

2. With regards to the over-complication of the table structure you may have a point. My angle on this was that we are often in a situation where several invoices remain outstanding and then we will receive payment for 2/3’s of the sum of the invoices. In my situation we have to be able to pay off the oldest debts first and in the order of our choosing. What the tenant/customer intends to pay off and what we (the managing agent) actually pay off are usually very different. It was for this reason that I have gone along with a more complicated option.

“This table allows me to pay Line Item 1 on Invoice #3 and Line Item #7 on Invoice #2 with the same payment and without paying the rest of the line items on those invoices.”

This is a good point that you have made. I suppose that it will ultimately boil down to VBA written in the forms to prevent the same value being paid against two separate line items.

I’m interested to learn whether I have addressed some of you concerns in my notes above.

Thanks once again for the time that you have put into this.

I’m interested to learn whether I have addressed some of you concerns in my notes above.

Click to expand...

Yes you have. And again, my points were more thinking out loud than saying "you've absolutely done this wrong". The things I saw, just seemed to make things more complicated than they needed--but it turns out they need to be that complicated.

I do have one more point/question:

In my situation we have to be able to pay off the oldest debts first and in the order of our choosing. What the tenant/customer intends to pay off and what we (the managing agent) actually pay off are usually very different. It was for this reason that I have gone along with a more complicated option.

Click to expand...

You discuss 2 payment application methods--Yours and Customers. Just to be clear, you do understand the structure you have laid out implements the Customers method?

Heres how I would approach the problem. Ididn't go down to line items as I was dealing with high value goods. The prinicple would be the same. When processing the cash slect the invoice and display outstanding line items. On the line items store have a flag Status
Paid = Y
Part = P
Unpaid = N

When you input the payment you are only looking for Unpaid or Partially Paid Line Items. If the payment amount does not equal the Invoice Line Item then the line item Status = P. In my case instalments were made so I create a Dialogue Form for Part Payments and Displayed the Balance Outstanding and allowed the input of an Amount and then updated the Payment Entry.

If this Payment did not clear the balance the Line Item would become P for Partial Payment, if the balance was cleared then the Line Item become Y for paid. This as I say was to handle instalments.

The important aspect of this process was to only identify unpaid items.

Simon

just in case you hadn't had enough of my issues, I have posted my latest issue with this in the query section.
http://www.access-programmers.co.uk/forums/showthread.php?p=1504719#post1504719

Thanks for the suggestions so far