Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Indiana
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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?
    Charlotte

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    Indiana
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

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

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

  5. #5
    Lounger
    Join Date
    Jan 2001
    Location
    Indiana
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  6. #6
    Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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
    dblCommission = dblCommission + rst!Commission
    rst.MoveNext
    Wend
    End If

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

  7. #7
    Lounger
    Join Date
    Jan 2001
    Location
    Indiana
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Thank you.

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

  8. #8
    Lounger
    Join Date
    Jan 2001
    Location
    Indiana
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

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

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •