Just A Little Nudge: Autofill Subform

[Alex2015]

I've tried searching the forums for an answer but there were a lot of dead ends unfortunately.

I have a main form (data entry) which my users are able to use to add new employees. Within the main form is a subform (also data entry) which my users utilize to add the new employee's work area & training modules. Since there are various training modules that an employee is responsible for, my users essentially have to select the work area every time they add a new training module in the subform.

What I want to accomplish, upon creating a new employee, is to have the work area field in the subform autofill somehow. I tried doing that with a combobox in the main form (which I would really like to implement) but I couldn't get it to work.

VBA is new to me so please take that into consideration. Thank you for any help.

 

[Ranman256]

(use the wizard to code this for you, so turn it on)
put a 'save & Add Code' button on the main form (save button)
(because you cant add sub records until the MasterID is created)
this button will do 2 things
keep the 'save' code intact, then after it,
add an append query. This will add all the default items to the new employee using the employee id.
something like:
insert into

([empID],

) values (" & forms!frmEntry!txtID & ",'CODE')

[code]
'vb code to save record
 DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

docmd.setwarnings false
docmd.openquery "qaAddNewEmpDflt"
docmd.setwarnings true
  'refresh the sub form
 me.subForm.requery

 

[Alex2015]

(use the wizard to code this for you, so turn it on)
put a 'save & Add Code' button on the main form (save button)
(because you cant add sub records until the MasterID is created)
this button will do 2 things
keep the 'save' code intact, then after it,
add an append query. This will add all the default items to the new employee using the employee id.
something like:
insert into

([empID],

) values (" & forms!frmEntry!txtID & ",'CODE')

[code]
'vb code to save record
 DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

docmd.setwarnings false
docmd.openquery "qaAddNewEmpDflt"
docmd.setwarnings true
  'refresh the sub form
 me.subForm.requery

I'm going to try this early tomorrow. I'll let you know how it goes.

 

[Alex2015]

I'm going to try this early tomorrow. I'll let you know how it goes.

Yeah I'm not going to lie; I built the command button, got into the VBA console, & I'm not sure if I built the append query correctly. Help?

 

[Alex2015]

Did some more troubleshooting & I got it to work this way:

1. In the main form, create a combobox for the item (for example, Unit)
2. Open the subform in design view
3. Create another combobox for Unit
4. Go to the properties of the new combobox
5. Under Data, make sure the control source is pointing to the appropriate field (in this case, unit again) & row source is pointing to the appropriate table (in this case, tblUnits)
6. Under default value, click the "..." & select the expression that points to the main form & field in question (for example, mine was =[Forms]![frmTEST2]![SelectUnit])
7. I then created a save record button to be pushed before the end user begins to type info into the subform. What if the end user skips the button? Well, I was thinking of making the subform a popup linked to the button, but I'm still tinkering.

All the data I entered using a form with this implemented created the records in the junction table just fine. Whew! This was setting me back so much; how frustrating.