Calculation within a subform

[ashows]

I'm working on a database for oil well check disbursements. The table tblOwners stores the investor names and their investment interest percent for each oil well. When a royalty check is to be disbursed, the appropriate lease is selected and the total check amount [CheckAmt] is entered by the user in a main form called frmChecks. The owner names and interest percents are displayed in a subform called fsubChecksWritten. The subform automatically calculates the estimated check amount by multiplying [CheckAmt] by [InterestPercent].

Now for the problem: sometimes an investor's check amount needs to be adjusted for expenses incurred. I would like to be able to enter the expense amount in the subform by the appropriate owner's name and have the subform calculate the actual check amount. I originally created the adjustment field in the subform as an unbound text box, but when I enter a value for one investor, the amount is distributed to *all* of the investors. How can I get the subform to let me enter different adjustment amounts for each owner?

 

[gusbrunston]

Hi:

If your field remains unbound, the same data entered in the control will indeed be displayed for that field in all records.

I didn't notice a table like "tblChecks", but I assume there is one such table to which your check writing report is bound.

I suggest you add a field to "tblChecks" named "fldExpenseAdjust" and bind the text box to that field. Then each check record can have a unique adjustment if necessary.

Again, after only a cursory glance at your project, and unasked, I think I would have limited tblOwners to nothing but Owners name, ID Number, address, telephone, etc. Then I would put each owner's percentage of ownership of each well into "tblLeases" or "tblWells" (by fldOwnerID). Then your db would be driven by Leases or by Wells rather than by Owners.

However, you have a many-to-many relationship, don't you. One owner could have an interest in many leases, and one lease can benefit many owners. Right? There may be more here than meets the eye...

Well, if this is helpful, there's nothing I can do about that. Gus Brunston An old PICKer, using Access2000.