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