combo box filter 1

[Trudye]

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

 

[galar]

I'm a little bit confused. Correct me if I wrong, but it seems that the subForm contains some information about the family and the combo should contain only the kids that are in this family.
Ok, the easiest way is to define the RowSource property of the field this way:
select child from Children where family_id = [f_id]

The f_id is the field on your form by which you want to filter the Combo.
Sorry I gave my names, but I didn't understand your's.

 

[Trudye]

Thank you so much Galar you waded through all of the garbage put your finger on the problem and came up with a succinct solution.


Be well,
Trudye

 

[Trudye]

I am trying to add the same funciton (combo box on a subform) to another form. The problem is more records are involved in my current subform.

The first combo Box/subform I created it was ok to use the DlookUp function because the record count was on average 2 records. But now my record count on average 10 records. I am getting the following error msg.

Run-time error '6
Overflow

Here is my code:
Private Sub Command150_Click()
Dim src As Integer

src = DLookup([MAWBID], "HAWB_Imports", "MAWBid = " & Me.MAWBID)

'This updates the combo box of my subform
Me.Combo158 = src
Me.Combo158.Requery

'This makes the combo box, subform and main form equal
Combo158_AfterUpdate
End sub


I tried a CLng function but couldn't get it to work (it could have and probably was me).

Does anyone have any suggestions?

Thank you
Trudye