Limiting records entered in a subform (Access 97 release 1)

Topic

New Reply

I have a parent record that has the total commission based on total sales. I need to break this commission out into separate profit centers via a subform. I want to limit the records entered in the subform so the breakdown does not exceed the total commission in the parent record. How can I do this.

Thanks in advance.

Tom Farrington

Reply | Quote

Replies

Sorry, but I don’t understand. Are you talking about data *entry* into a subform? You wouldn’t ordinarily store a total in a parent record, you would sum the child records on the fly to display a total. Or are you talking about simply displaying records that add up to the total, or what?

Reply | Quote

Charlotte,

The total commission is calculated via square footage * commission rate. This total is then broke down to different profit centers in the organization. In other words, everyone getting their piece of the pie. I would love to use the subform and add up to a total, but they want to use the calculated total then break that down.

Reply | Quote

I’m still confused. Are you entering records into the subform and allocating a value to each? If so, how do you intend to do it? Is there a specified percentage for each profit center or is it based on a value specific to each profit center or what? If the relationship can be defined mathematically, you can do it with an append or update query rather than fumbling with a subform, but you’ll have to explain more about what you actually want to do.

Reply | Quote

Charlotte,

When a property is leased a total price is calculated. This is based on time and square footage and price/sqft. With a leasted property there can be one record to many records that derive the price. Once the price is calculated, this is were the commission gets broke out. Unfortunately there is no set percentage per profit center. Let me see if I can lay this out below:

Property Table
PropertyID, Property Name, ExtendedTotalPrice, ExtendedCommission <- these last two fields are derived from the records below. (main record) [One Side]

Leased Table
PurchaseID, PropertyID, MonthsLeased, SqFt, SqFtPrice, TotalPrice [Many Side]

Commission Table
CommissionID, PropertyID, ProfitCenter, Commission, PercentageofCommission [Many Side]

Once the total commission is calculated it then needs separated. Can I knowing the total commission allow the user to enter the breakdowns in a subform. If the total from those records exceeds the total commission highlight the last record entered as an error or display a msgbox stating they exceeded the total commission allowed and setfocus back to that record?

I know this is confusing, if they had set business rules i.e. 75% goes to sales person, 10% marketing, 10% department, 5% assistant. Life would be easy.

Reply | Quote

I think I understand your request. You want one or more records entered into a subform to break down the commission. You want the total of the values in the subform to not exceed the total commission entered on the parent form (do you also want it to not be less than the total?).

In the BeforeUpdate property of your subform record, put the following Event Procedure coding:

Dim rst as RecordSet
dim dblCommission as Double

dblCommission = 0
Set rst = Me.RecordsetClone
If Not rst.RecordCount = 0 Then
rst.MoveFirst
While Not rst.EOF And dblCommission Me.Parent!Commission Then
Msgbox “Commission breakout (” & dblCommission & “) exceeds total commission!”
CancelEvent
End If

What this does is, before each subform record is committed to the table, adds up all of the subform recordset’s commissions. If the total exceeds the parent form’s commission a message box is displayed and the event is cancelled, preventing the current record from being committed to the table.

Reply | Quote

Thank you.

I will give it a try Monday when I get in.

Reply | Quote

I gave it a try but I’m receiving an error on the Set rst = Me.RecordsetClone line. The Error I receive is Run-time error ’13:’ Type mismatch I think I understand the concept just bewildered on the error for the RecordsetClone method.

Reply | Quote

You need to dim rst as Recordset. Did you do that?

Reply | Quote