Continuous SubForm Output based on Combobox Access 2010

0
votes

I'm trying to get a continuous subform, displaying CourseID and CourseCompletionDate, to display output related to the Employee chosen in through the main subform's combobox (this is an employee training profile showing what the employee's completed training on). Here's my VBA:

Private Sub cboEmployee_AfterUpdate()
On Error GoTo Proc_Error
If IsNull(Me.cboEmployee) Then
   Me.subEmployeeCourseTrainingCompletion.EmployeeProfile.Filter = ""
   Me.subEmployeeCourseTrainingCompletion.EmployeeProfile.FilterOn = False
Else
  Me.subEmployeeCourseTrainingCompletion.EmployeeProfile.Filter = "[EmployeeID]=" & Me.cboEmployee
  Me.subEmployeeCourseTrainingCompletion.EmployeeProfile.FilterOn = True
End If

Proc_Exit: Exit Sub Proc_Error: MsgBox "Error " & Err.Number & " in setting subEmployeeCourseTrainingCompletion filter:" & vbCrLf & Err.Description Resume Proc_Exit End Sub

Error that I get when trying to use the combobox on the main subform. Error that I get when trying to use the combobox on the main subform

This is driving me bonkers. Why doesn't this want to work when switching employees in the combobox? How can I make this vba and subform work fawlessly with the combobox?

2
ms-accesscomboboxvbams-access-2010continuous-forms

2 Answers

1
votes

So, in addition to what pteranodon stated, adding the employeeID to the query itself fixed everything. No more message box, and the combobox works perfectly with the subform.

0
votes

Assumptions: (please correct):

EmployeeProfile is the name of the form that you invoke as a subform. subEmployeeCourseTrainingCompletion is the name of the subform control in your main form that has its SourceObject set to EmployeeProfile.

The syntax you are looking for is

Me.subformcontrol.Form.FormProperty so Me.subEmployeeCourseTrainingCompletion.Form.Filter = ""

But actually, it looks like you can acheive this without code at all by setting the subform control's LinkMasterFields to cboEmployee and LinkChildFields to EmployeeID. You can then decide on how to set Filter on Empty Master. All of these properties are on the Data tab of properties window when the subform control is selected (single click).