Form using data from one table to add to another RRS feed

  • Question

  • I'm relativly new to access and have been put in charge of updating my groups database.

    The issue I have invovles three tables.  The main table stores a list of jobs.  The second table Work Hours has a many to one relationship with the jobs table.  It stores the number of hours worked for each labor code to each job.  There can be multiple labor codes working on a job or only one.  The final table stores the list of available labor codes.

    I currently have a sub form for the hours worked table in my jobs form.  The way it currently works is that there is a combo box for the labor codes and a text field for the hours.  After a labor code is selected and a time is entered another combo box and text field appear below it in the sub form to enter another labor code and hours. 

    However I would prefer it not to work this way. Instead I would like the sub form to display two text fields up front.  The one for labor codes should be pre populated and uneditable, with the hours text box left unchanged.  And for the sub form to have every labor code displayed.

    The problem is when I try to do what I want the only way to display the form as a list of labor codes is to set the record source for the form to the Labor Code table.  But then it wont add the values to the Hours Worked table.  If I do it vice versa it only displays the first labor code in the form but will add the data to the Hours Worked table.  Is there anyway to get access to display the form the way I want?  I don't have a lot of leway in changing the database tables because its already a production system and I can't risk losing data.  Any ideas?

    Tuesday, July 24, 2012 1:36 PM


  • The way you have it now is the preferred way, because it fits nicely with how Access works. If you deviate from that, you will have to do some work.

    The work would entail creating a new "temp" table which you use for data entry. You would delete all existing rows from this table and populate it with new rows (one per labor code) upon the Current event of the main form. While populating this table also write whatever values you have already collected for this job. I'm sure you could write the append query that does this (LaborCodes left outer join WorkHours).

    Then in the row's AfterUpdate event you write the data back to the "real" table, using an Update or Append query.Or perhaps you would rather have a Save button next to your subform that would do this for all rows.

    There would be no changes to the existing tables, just a new table. You would likely keep this table in the front-end rather than in the back-end, so you don't have to worry about other users. If you choose the BE, add a column with MachineName.

    -Tom. Microsoft Access MVP

    • Marked as answer by Dummy yoyo Thursday, August 9, 2012 2:56 AM
    Tuesday, July 24, 2012 1:52 PM