Use a Combo Box To Find A Record on a subform in a tab control

[merv02]

I have a main form with a tab control (TabCtl) containing several tabs. Tab1 contains a subform (employee) used to enter the employee’s data. Tab2 contains a subform (jobs) used to enter the jobs completed by the employee. Each subform in a tab contains the field employee ID. I would like to use a combo box (cboFindRecord) located on the main form to search the selected tab for the employee id selected.

In the past I”ve use the following to find a record on the main form but I can’t seem to get it to work on the tab control


Private Sub cboFindRecord_AfterUpdate()
' Find the record that matches the control.
On Error GoTo ProcError
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[EmpID] = '" & Me![cboFindRecord] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
ExitProc:
Exit Sub
ProcError:
MsgBox "Error: " & Err.Number & ". " & Err.Description
Resume ExitProc
End Sub

Thanks in advance

 

[MajP]

The easiest is to link the subforms to the combobox. So in each subform control set the properties

Link master fields: cboFindRecord
Link child fields: employeeID

Change the combo and all subforms filter to that record.

However, this is fine if you only want to work with one employee at a time. If nothing is selected in the combo do you want to see all the employees? If so you need additional code.

in the combos after update.

if isnull(me.combo) then
'break the linking
me.subformcontrolName.linkMasterFields = ""
me.subformControlName.linkChildFields = ""
'may need a requery
else
'reset them
me.subformcontrolName.linkMasterFields = "cboFindRecord"
me.subformControlName.linkChildFields = "employeeID"
end if

 

[merv02]

The subform is located within a tab control and the "cboFindRecord" combo box is located on the main form. "CboFindRecord" does not show up as an option in the "Link Master Fields"

 

[MajP]

You will have to do type it in manually because the wizard and drop downs will not make it available. But you can link a subform to a control and it is a valuable trick.
The fact that it is on a tab control is not an issue. Referencing a control on a tab is no different than referencing a control on the main for.

 

[merv02]

Thanks