Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2011
    Posts
    27

    Unanswered: Subform for Junction Table

    I have a problem creating a form and subform. The form is for the Projects table, and the subform will hopefully have work Codes and the Hours employees put toward each Code (Programming, Double checking, etc.)

    Hopefully I made a good choice in creating a junction table that has a combined PK of ProjectsFK and CodesFK (one code per project for only codes needed), and an Hours field.

    I'm looking for a Subform that will show all possible Codes for the project, and only save or update those with Hours>0. Here is my best text interpretation:

    Title: Projects
    Record: ProjectID = 1
    ProjectName, ProjectType, etc.

    Subform:
    Code Hours
    Code 1 2
    Code 2 0
    Code 3 1
    Code 4 0

    This would write or update the records:
    Project ID CodeID Hours
    1 1 2
    1 3 1

    Any ideas?

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I have what I suspect is a similar situation. Drivers can take out different cars at different rates. During data entry, I populate the activity table with every combination of driver and rate, with 0 hours. Users just tab down the hours column entering them as desired. When the form closes, I delete any record with 0 hours. So it's basically 2 queries; an append query fired off when they open the form, and a delete query fired off when they close it. Might that work for you?

    Another option would be a subform bound to a temp table that has all the codes. When they "save", you append records to the actual table.
    Paul

  3. #3
    Join Date
    Jan 2011
    Posts
    27
    I like it. It's at least a lot better than what I was going to do, some mumbo jumbo in VBA. Luckily I'm not using autonumber PKs for this table, so I won't have to worry about resetting the seed with your solution either.

    Thanks for the technique, pbaldy!

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Happy to help! I'm sure there are other ways, but that one works for me. It even works when they go back to edit, because the append will silently fail to add any codes that already exist (based on having a compound key on the relevant fields).
    Paul

Tags for this Thread

Posting Permissions

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