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:
Record: ProjectID = 1
ProjectName, ProjectType, etc.
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.
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.
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).