I have a main form called frmItemDetail. Inside frmItemDetail, I have a subform called frmActivity. Nested within frmActivity is a subform called frmCategory. frmActivity is linked to the frmItemDetail by ItemID and frmCategory is linked to both its parents with ItemID and StageID and things work as they should.
Here is what I would like to do:
When a user selects a 'type' from the combo box called Type, I would like to automatically generate records in both the subform and the nested subform, as shown in the attached picture.
For example, if the user selects "Test" from the Type combo box, I would like to generate 8 records in the Activity subform with only the 'Stage' field filled out.
There would be 8 records as follows:
1. Stage 1
2. Stage 2
3. Stage 3
4. Stage 4
5. Stage 5
6. Stage 6
7. Stage 7
8. Stage 8.
Here is the really difficult part: For each of these records, I would like to generate 1 or more records in the nested datasheet with these exact strings in the Category field
1. Stage 1
Plan
2. Stage 2
Draw
3. Stage 3
Consult
Review
4. Stage 4
Edit
Estimate
5. Stage 5
Model
Render
Review
6. Stage 6
Consult
7. Stage 7
Final
8. Stage 8.
Show
I found a way to crudely add the records in the frmActivity subform by setting the focus on the frmActivity subform, setting the value of the 'Stage' field and then using DoCmd.GoToRecord acNewRec. It isn't pretty but it worked. I'm sure it isn't the best way to do it. However, I am at a complete loss as to how to create the records in the nested subform, frmCategory and link them to the parent records. Should this be done within the form? Should it be done behind the scenes with something like 'AddNew? I have no idea.
Any help with this would be greatly appreciated. Seriously. I'm stuck
Here is what I would like to do:
When a user selects a 'type' from the combo box called Type, I would like to automatically generate records in both the subform and the nested subform, as shown in the attached picture.
For example, if the user selects "Test" from the Type combo box, I would like to generate 8 records in the Activity subform with only the 'Stage' field filled out.
There would be 8 records as follows:
1. Stage 1
2. Stage 2
3. Stage 3
4. Stage 4
5. Stage 5
6. Stage 6
7. Stage 7
8. Stage 8.
Here is the really difficult part: For each of these records, I would like to generate 1 or more records in the nested datasheet with these exact strings in the Category field
1. Stage 1
Plan
2. Stage 2
Draw
3. Stage 3
Consult
Review
4. Stage 4
Edit
Estimate
5. Stage 5
Model
Render
Review
6. Stage 6
Consult
7. Stage 7
Final
8. Stage 8.
Show
I found a way to crudely add the records in the frmActivity subform by setting the focus on the frmActivity subform, setting the value of the 'Stage' field and then using DoCmd.GoToRecord acNewRec. It isn't pretty but it worked. I'm sure it isn't the best way to do it. However, I am at a complete loss as to how to create the records in the nested subform, frmCategory and link them to the parent records. Should this be done within the form? Should it be done behind the scenes with something like 'AddNew? I have no idea.
Any help with this would be greatly appreciated. Seriously. I'm stuck