CODEWITHSUNDEEP
ms-accesscomboboxvbams-access-2010continuous-forms
JBear
JBear

Reputation: 333

Continuous SubForm Output based on Combobox Access 2010

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

Why doesn't this want to work when switching employees in the combobox? How can I make this VBA and subform work flawlessly with the combobox?

Upvotes: 0

Views: 431

Answers (2)

JBear
JBear

Reputation: 333

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.

Upvotes: 1

pteranodon
pteranodon

Reputation: 2059

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).

Upvotes: 0

Related Questions