Combo box with a subform

I have a form and subform with 2 combo boxes on the main form. Each combo box
is used to filter the records for each of the forms. My problem is that the
combo box for the subform does not "remember" the last record that was
selected. It shows a list of last names and first names, but always remembers
the first record when there are duplicate last names. For example, if I
select Doe, John from the box and then drop down the list again, it goes back
to Doe, Adam. This works well for the combo box in the main form I think
because that table has a primary key field. The related table that I use for
the combo box to filter the subform, however, has no primary key. Is there a
way around this without creating a primary key for the related table?
TIA.

Here is my code for the subform combo box:
Private Sub cboFindChild2_AfterUpdate()
Dim strSQL As String
Dim bWasFilterOn As Boolean

If IsNull(Me.cboFindChild2) Then
If Me.RecordSource "Families" Then
Me.RecordSource = "Families"
End If
' If the combo is Null, use the whole table as the RecordSource.
Else
strSQL = "SELECT DISTINCTROW Families.* FROM Families " & _
"INNER JOIN Children ON " & _
"Families.ID = Children.ID " & _
"WHERE Children.ID = " & Me.cboFindChild2 & ";"
Me.RecordSource = strSQL
End If

If bWasFilterOn And Not Me.FilterOn Then
Me.FilterOn = True
End If

Exit_cboFindChild2_AfterUpdate:
Exit Sub

Me!cboFindChild2.Requery
End Sub

--
Deb H

Deb,

No, AFAIK, there isn't; Access needs a means by which to uniquely identify
which row you mean. This is the criteria for 2nd Normal Form.

But it's simple to add an AutoNumber primary key in Design view, then Access
will assign a unique one to each existing record. Include this field as the
first field in the RowSource list, set the Bound Column to 1, and the
ColumnWidths to 0";x;y...etc., where x, y, and any others are a width ample
to display the widest value in each column.

The sources below cover table normalization, including why all tables should
have a primary key.

ACC: Database Normalization Basics
http://support.microsoft.com/?id=100139
http://support.microsoft.com/?id=209534
http://support.microsoft.com/?id=283878

"Understanding Relational Database Design" Document Available in Download
Center:
http://support.microsoft.com/?id=283698
http://support.microsoft.com/?id=164172

ACC2000: "Understanding Relational Database Design"
http://support.microsoft.com/?id=234208

Database Normalization Tips by Luke Chung
http://www.fmsinc.com/tpapers/genacc...abasenorm.html

Support WebCast: Database Normalization Basics
http://support.microsoft.com/default...lurb060600.asp

Sprinks

"Deb H" wrote:

I have a form and subform with 2 combo boxes on the main form. Each combo box
is used to filter the records for each of the forms. My problem is that the
combo box for the subform does not "remember" the last record that was
selected. It shows a list of last names and first names, but always remembers
the first record when there are duplicate last names. For example, if I
select Doe, John from the box and then drop down the list again, it goes back
to Doe, Adam. This works well for the combo box in the main form I think
because that table has a primary key field. The related table that I use for
the combo box to filter the subform, however, has no primary key. Is there a
way around this without creating a primary key for the related table?
TIA.

Here is my code for the subform combo box:
Private Sub cboFindChild2_AfterUpdate()
Dim strSQL As String
Dim bWasFilterOn As Boolean

If IsNull(Me.cboFindChild2) Then
If Me.RecordSource "Families" Then
Me.RecordSource = "Families"
End If
' If the combo is Null, use the whole table as the RecordSource.
Else
strSQL = "SELECT DISTINCTROW Families.* FROM Families " & _
"INNER JOIN Children ON " & _
"Families.ID = Children.ID " & _
"WHERE Children.ID = " & Me.cboFindChild2 & ";"
Me.RecordSource = strSQL
End If

If bWasFilterOn And Not Me.FilterOn Then
Me.FilterOn = True
End If

Exit_cboFindChild2_AfterUpdate:
Exit Sub

Me!cboFindChild2.Requery
End Sub

--
Deb H