Requery a subform

[UT227]

I have a form [frmClassScheduleEnterNames]. It has two subforms [frmStudentListDeptSubF] & [frmTrainingRosterDeptSubF] I have my list of student in [frmStudentListDeptSubF] which populates the second subform [frmTrainingRosterDeptSubF] by using an append query. I want the subform [frmTrainingRosterDeptSubF] to immediately requery after the append happens. Any ideas that might help me?

[RuralGuy]

If "frmTrainingRosterDeptSubF" is the name of the SubFormControl that displays the frmTrainingRosterDeptSubF then this will work:
Forms!frmClassScheduleEnterNames.frmTrainingRoster DeptSubF.FORM.Requery

http://access.mvps.org/access/forms/frm0031.htm

[UT227]

I really hate asking this. Where do I enter that?

[June7]

In the same procedure running the append query. Are you using VBA? Post the procedure code.

RuralGuy, do you notice the space in your suggested code between Roster and Dept? How did that happen? Randomly thrown in by the forum?

[RuralGuy]

RuralGuy, do you notice the space in your suggested code between Roster and Dept? How did that happen? Randomly thrown in by the forum?

June, the space is a bug in the bulletin board display software. It's been there for many versions.

[June7]

Yes, I've noticed it for quite some time. Annoying.

So, UT227, beware of copy/paste.

[UT227]

Thanks. I will note that.
As for the append query, I have not learned VBA yet. It's on my to do list. So, when you say it's on the same procedure as the append query, are you talking about entering it in the code for VBA? You want a pic of my append query?

[June7]

Don't need a pic, the SQL text pasted into post would be sufficient. Open query object in SQLView and copy/paste.

Yes, VBA procedure.

Exactly how are you executing the append query? Manually from the Navigation pane?

[UT227]

This is the SQL for the append query:
INSERT INTO tblTrainingRoster ( EmployeesID, ScheduleID, ClassID, TCOLEClassListID, InHouseClassListID, ClassTypesID, DOT, DOTEnd, hrs, [Grant] )
SELECT tblEmployees.EmployeesID, tblTrainingSchedule.ScheduleID, tblTrainingSchedule.ClassID, tblTrainingSchedule.TCOLEClassListID, tblTrainingSchedule.InHouseClassListID, tblTrainingSchedule.ClassTypesID, tblTrainingSchedule.DOT, tblTrainingSchedule.DOTEnd, tblTrainingSchedule.Hours, tblTrainingSchedule.Grant
FROM tblEmployees, tblTrainingSchedule
WHERE (((tblEmployees.EmployeesID)=[Forms]![frmClassScheduleEnterNames]![frmStudentListDeptSubF]![EmployeesID]) AND ((tblTrainingSchedule.ScheduleID)=[Forms]![frmClassScheduleEnterNames]![ScheduleID]));

[June7]

I would use VBA. The real trick is figuring out what event to put code into (button Click, form or control AfterUpdate, etc). Exact syntax for referencing controls depends on where the code is located. If it is behind [frmStudentListDeptSubF]:

CurrentDb.Execute "INSERT INTO tblTrainingRoster (EmployeesID, ScheduleID, ClassID, TCOLEClassListID, InHouseClassListID, ClassTypesID, DOT, DOTEnd, hrs, [Grant]) " & _
"SELECT " & Me.EmployeesID & " AS EID, ScheduleID, ClassID, TCOLEClassListID, InHouseClassListID, ClassTypesID, DOT, DOTEnd, Hours, Grant " & _
"FROM tblTrainingSchedule WHERE ScheduleID=" & Me.Parent.ScheduleID

Me.Parent.[frmTrainingRosterDeptSubF].Requery

Or continue to use your manual method and refresh the form with the Refresh button from ribbon. Keep in mind 'the more user-friendly, the more code'.