filter with combo box

[Deb H]

I have a combo box on a main form that I am trying to use to filter records
in a related subform. The main form uses a table named Families and the
subform is based on a table named Children. Whenever I select a value from
the combo box to find a record in the subform it selects the wrong record.
There is a one to many relationship between the tables. I am using Access
2003.
Here is my code:
Private Sub Combo162_AfterUpdate()
Dim strSQL As String
If IsNull(Me.Combo162) Then
' If the combo is Null, use the whole table as the RecordSource.
Me.RecordSource = "Families"
Else
strSQL = "SELECT DISTINCTROW Families.* FROM Families " & _
"INNER JOIN Children ON " & _
"Families.ID = Children.ID " & _
"WHERE Children.ID = " & Me.Combo162 & ";"
Me.RecordSource = strSQL
End If

End Sub

I've looked at Allen Browne's tip from previous posts to troubleshoot this,
but I'm still missing something to effectively filter the correct record. Any
ideas?

 

[tina]

when you created your tables, looks like you allowed Access to create the
primary key field for you, in each table. there's nothing wrong with doing
that, except that Access always names a primary key field "ID". this can be
very confusing if you don't already have a clear understanding of table
relationships. (in future, suggest you make sure each table's primary key
field has a unique name, not duplicated anywhere else in the database.)

if you go back and look at the design of your tables Families and Children,
i think you'll find that the primary key field of Families is ID, and the
primary key field of Children is ID. those two fields have *nothing to do
with each other*, even though they have the same name.

because table Children is on the many side of a 1:n relationship with table
Families, you need to have a *foreign key* field in Children that matches
the primary key field in Families. the name of the foreign key field does
not have to be the same as the other table's primary key field. i'll call
the foreign key field FamID. the Data Type of the primary/foreign key fields
must be the same - with one exception: i'm guessing that Families.ID has an
Autonumber data type, so Children.FamID must have a data type of Number,
with Field Size set to Long Integer (and the Default Value left blank, not
set to zero (0)).

in the Relationships window, you should have a link *from* table Families.ID
*to* table Children.FamID. and check the box next to Enforce Referential
Integrity, in the Edit Relationships dialog.

when you've done all the above (or verified that you did it correctly when
you originally built your tables), then make the following change to the
strSQL in your form's VBA code, as

strSQL = "SELECT DISTINCTROW Families.* FROM Families " & _
"INNER JOIN Children ON " & _
"Families.ID = Children.FamID " & _
"WHERE Children.FamID = " & Me.Combo162 & ";"

hth