Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Posts
    33

    Unanswered: Design/Programming help

    I have a form that contains a subform. Changes or edits to the main form and the subform are done in a temporary workspace that is not committed until the main form is unloaded or saved. This is to allow the user to rollback edits or changes.

    The main form and subform are used to enter work done/services provided. Each record usually has mark-ups added to them such as general liability, fee etc.

    I am using a seperate table to capture the project mark-ups and its calculation rules. I would like to be able to apply the calculations and insert line items in the subform for each of my mark up items.

    Dose anyone know of a good way to work this? There are three rules of calculations and the mark ups are precentages of the direct work cost.


    Any ideas would greatly be appreciated.
    Thank you,
    Gina

  2. #2
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238

    Re: Design/Programming help

    Originally posted by glee0511
    I have a form that contains a subform. Changes or edits to the main form and the subform are done in a temporary workspace that is not committed until the main form is unloaded or saved. This is to allow the user to rollback edits or changes.

    The main form and subform are used to enter work done/services provided. Each record usually has mark-ups added to them such as general liability, fee etc.

    I am using a seperate table to capture the project mark-ups and its calculation rules. I would like to be able to apply the calculations and insert line items in the subform for each of my mark up items.

    Dose anyone know of a good way to work this? There are three rules of calculations and the mark ups are precentages of the direct work cost.


    Any ideas would greatly be appreciated.
    Thank you,
    Gina
    You could use the BeforeUpdate event of the subform to capture what the user has just entered as record, but before it is actually written to the table. You can then verify their data and make any adjustments, additions or calculations as required. you can wrap all this up in a transaction using ADO

  3. #3
    Join Date
    Jul 2003
    Posts
    33
    I'm sorry I'm not explaining myself very well. I have a button on the main form "Add mark-ups" that allows the user to add a pre-defined group of line items to the details already entered.

    For example: the subform has two line items already entered. 1 For Drywall installation at $1000 and another for painting at $1000. the total cost of work is $2000. I have to add another line item called fee which is a 10% of the $2000. Making the total change order $2200. This is a mark up. There are standard mark ups that are different for each project. As there can be multiple changes and each change requires a mark up. Rather than manually do it I was trying to have the user click the "add mark-ups" and the line items would be calculated and entered in the subform as a new record.


    Thank you.
    Gina

  4. #4
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    Originally posted by glee0511
    I'm sorry I'm not explaining myself very well. I have a button on the main form "Add mark-ups" that allows the user to add a pre-defined group of line items to the details already entered.

    For example: the subform has two line items already entered. 1 For Drywall installation at $1000 and another for painting at $1000. the total cost of work is $2000. I have to add another line item called fee which is a 10% of the $2000. Making the total change order $2200. This is a mark up. There are standard mark ups that are different for each project. As there can be multiple changes and each change requires a mark up. Rather than manually do it I was trying to have the user click the "add mark-ups" and the line items would be calculated and entered in the subform as a new record.


    Thank you.
    Gina
    You could use a SQL statement to make the addition for each of the parent groups. So you don't double count the fee line (unless you wanted to) you would have to have a field which denotes the row as being a fee line and is to be excluded from the calculation. An example of the SQL required would be:

    INSERT INTO SubFormTable(ParentTableID, IsFeeLine, QTY, TOTAL)

    SELECT ParentTableID, 1, 1, (SUM(TOTAL) * .1) FROM SubFormTable WHERE (ParentTableID = {ParentOfInterest}) AND (IsFeeLine = 0) GROUP BY ParentTableID, TOTAL

    The WHERE statement for ParentTableID is optional as you could do this over the whole table. The group by clause will sum the total for each parent, but will exclude the fee line.

    You would have to delete the fee lines first so you don't get repeated feelines. The SQL would be

    DELETE SubFormTable WHERE (IsFeeLine = 0)

  5. #5
    Join Date
    Mar 2003
    Location
    Bogota
    Posts
    67
    I am having very similar design-issue problems right now, nearly identical. I'll have to explore the temporary workspace issue, because the form/subform relationship is playing havoc on my record validations.

    Anyway,

    Your button could insert the appropriate line-items with SQL built in code. This way you can use the existing line-item records to come up with the right values to be inserted, and grab the main id off the main form's recordset. After running the insert, the subform is requeried to display the new items.

  6. #6
    Join Date
    Jul 2003
    Posts
    33
    I'm quite the novice and I might need to back up a bit.

    How would I enter recordes into a subform when a button was clicked on the main form? The form/subform is updated in a temporary workspace and the acutal tables are not updated until the user commits the transaction.

    Can I progammatically add rows to the subform datasheet by clicking a button on the main form???

    thanks for your help.
    Gina

Posting Permissions

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