If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Combo box with a subform
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Multiple Options Group | Patty Stoddard | Using Forms | 19 | August 4th, 2005 02:30 PM |
Design help, please | SillySally | Using Forms | 27 | March 6th, 2005 04:11 AM |
Subform Refresh Problem (but only with an unbound combo box control) | Barry Skidmore | Using Forms | 1 | December 21st, 2004 01:19 AM |
Need help with cascading combos | Tom | Using Forms | 19 | July 1st, 2004 11:11 PM |
Data Dependencies between Combo Boxes | Tom | Using Forms | 7 | June 6th, 2004 05:25 PM |