MS Access data validation between form and subform

Question

I have an MS Access database which tracks invoices. In the form I use to enter the data, I have the control [Amount] for the invoice amount (e.g. $100). In that form there is also a subform which tracks parts of the invoice (e.g. food supplies for $50 and cleaning supplies for $50). The main form also has the control [Breakdown Total] which sums up the breakdown amounts in the subform.

I am trying to make it so that, unless [Amount] = [Breakdown Total], you can't change records within the form. Because of the two-table structure, it seems that a data validation rule isn't working quite the way I want because it really only works when you fill in the [Amount] control and not if you then change the breakdown.

I thought of building an event to handle this, but I don't know what event to use--BeforeUpdate doesn't let me leave the control which doesn't match, which isn't helpful--what if that's the correct one?--and AfterUpdate doesn't seem to let me require that the suspicious record remain on the screen. Could someone please recommend the event I should be using?

Expand a little on how your Form/Subform is bound? Is it Table/Query based or VBA controlled? — GoldBishop
Right now it's Table/Query based. There's a table for payables and a separate table for payables breakdown which links each record (of a portion of an invoice) to the invoice number from the payables table. — ryanmrubin
At this point, I think I just don't know exactly how to put together the VB to make it happen just right, nor the event to put the code in. — ryanmrubin
What i have done in the past is built the Parent form to hold the base information and if i needed a subform i then developed that subform with the basis of a selection from the Parent. It is very development intensive to account for selections and situations. — GoldBishop

GoldBishop GoldBishop · Accepted Answer · 2012-10-19T12:29:33

Here was a solution i implemented at a client, recently. Not sure if it is exactly what you need but implements two ListBoxes, one to select the Top-Category and the other is Column'ed to display a Cross-Tab display of the Top-Category's children.

enter image description here

The Left Listbox is programmatically, in VBA, loaded information from an Access View and then the main-body Listbox is bound by a Cross-Tab creation algorithm. Now yours does not necessarily have to be as intracate or detailed as mine required. But you can get this done without a subform.

MS Access data validation between form and subform

1 Answers