I would like to create a combo box on a subform that list just the items that pertain to the selection made on the main form.
The form is working great, it always has. The form was created over a year ago this is just a bells and whistle kind of thing the User wanted. For instance if a family has 5 kids, if you select that family on the main form and look at the navigation bar you will see "of 5" so that part is working just fine.
The User wanted to add a combo box to the subform so they did not have to cycle through all the records. The combo box on the subform is working ok too except it lists all of the kids in the table because its Row Source is the kids table. I want to create a filter (to filter out all other names except for those related to the current record).
The subform combo box is displaying the current family kids at the top of the combo box and I can switch between family members. I just want to clean up the combo box so that only the current family names show. Right now all of the names are showing.
Here is the code:
Private Sub Kid_LookUP2_AfterUpdate() 'Combo box
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Scccc_childs_ID] = " & Str(Me!Kid_LookUP2)
Me.Bookmark = rs.Bookmark
End Sub
Private Sub UpdateLookUp_Click() 'button event to isolate the current kids
Dim src As Integer
src = DlookUP("[scccc_childs_id]", "children", "scccc_id = " & Me.Scccc_id)
'This update the field in the combo box of my subform
Me.Kid_LookUP2 = src
Me.Kid_LookUP2.Requery
End Sub
I tried clearing the the combo box before Dlookup
I tried removing the requery
I tried to apply these filters:
DoCmd.ApplyFilter , Me.Kid_LookUP2 = Me.Scccc_id
DoCmd.ApplyFilter , Me.Kid_LookUP2 = src
Thank you
Trudye
The form is working great, it always has. The form was created over a year ago this is just a bells and whistle kind of thing the User wanted. For instance if a family has 5 kids, if you select that family on the main form and look at the navigation bar you will see "of 5" so that part is working just fine.
The User wanted to add a combo box to the subform so they did not have to cycle through all the records. The combo box on the subform is working ok too except it lists all of the kids in the table because its Row Source is the kids table. I want to create a filter (to filter out all other names except for those related to the current record).
The subform combo box is displaying the current family kids at the top of the combo box and I can switch between family members. I just want to clean up the combo box so that only the current family names show. Right now all of the names are showing.
Here is the code:
Private Sub Kid_LookUP2_AfterUpdate() 'Combo box
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Scccc_childs_ID] = " & Str(Me!Kid_LookUP2)
Me.Bookmark = rs.Bookmark
End Sub
Private Sub UpdateLookUp_Click() 'button event to isolate the current kids
Dim src As Integer
src = DlookUP("[scccc_childs_id]", "children", "scccc_id = " & Me.Scccc_id)
'This update the field in the combo box of my subform
Me.Kid_LookUP2 = src
Me.Kid_LookUP2.Requery
End Sub
I tried clearing the the combo box before Dlookup
I tried removing the requery
I tried to apply these filters:
DoCmd.ApplyFilter , Me.Kid_LookUP2 = Me.Scccc_id
DoCmd.ApplyFilter , Me.Kid_LookUP2 = src
Thank you
Trudye