Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jul 2003
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sub Form Updating Other Sub Forms (Access 2002)

    I have a form with Tab Control; each tab has a Subfrom that is used to update a table. I want one Subform to update the another Subform. Tab A has a subform that I enter hours worked by each employee on all projects, they will identify if theRegular Hour and/or Overtime Hours. For example lets Say Employee A works 40 Regular Hours on Project A, 40 Regular Hours on Project B, 40 Regular Hours on Project C, 40 Regular Hours on Project D and 20 Overtime Hours on Project E. These 5 Records will be displayed on Tab A. When I click on Tab B; I would like there to be two records for this employee one record for 160 Regular hours and on record for 20 Overtime Hours. In addition; I like these records to be updated to the table that is supported in Tab B and displayed in the Subform that is controlled in Tab B.

    I tried an update query; however I get a error message that will not allow me to execute the update query.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sub Form Updating Other Sub Forms (Access 2002)

    You can create a Totals query that totals hours worked, grouped by employee and by type (Regular/Overtime).
    The subform in Tab B could be bound to this query.
    In the On Change event of the tab control, requery the subform in Tab B if the value of the tab control is 1 (i.e. the second tab is activated; tabs are numbered starting at 0)

    See attached demo.

  3. #3
    2 Star Lounger
    Join Date
    Jul 2003
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub Form Updating Other Sub Forms (Access 2002)

    Thank You Hans; this concept was very easy to follow. But I guess what I failed to mentioned is that I still need the Tab B Source to be a table that I can update via the subform in Tab B. I used your logic 'On Tab Change' to run an update query to update my table; however the query would not run because the subform has the table opened; I incurred a runtime error.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sub Form Updating Other Sub Forms (Access 2002)

    I keep on repeating: why do you insist on storing a derived, calculated value in a table? It violates the rules for good design of relational databases.

  5. #5
    2 Star Lounger
    Join Date
    Jul 2003
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub Form Updating Other Sub Forms (Access 2002)

    I will respond in more detail early next week; at the moment I'm under a very tight deadline. However, in a brief statement. I need to be able to update values in the Tab B via the subform. The hours that we totaled will be used to calculate a cost for the employee; I want the subform to be used to change the hourly rate if the employee gets a raise. I will be more specific next week. Thank you very much for your assistance; as always it is very insightful.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sub Form Updating Other Sub Forms (Access 2002)

    If that is the case, I doubt that you would want to store the values each time you activate Tab B, even if the data in Tab A haven't changed.

    What I would probably do in such a situation is update the appropriate value in "table B" in the Before Update or After Update event of the subform in Tab A, so that "table B" is only updated if the user changed something in the subform in Tab A.

Posting Permissions

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