I am trying to sort a subform combo box by something other than the primary key. The subform is linked to a query that combines information from two tables. When the user makes a selection in the subform combo box (primary key), the adjacent fields autopopulates with related information.
I have the following tables/queries/forms that relate to this issue:
tblReferenceList (tried sorting this table by RefNum but it still orders by RID in the subform)
RID - Primary Key (AutoNumber)
RefNum - An alphaNumeric designator for the reference
RefTitle - The name of the reference
tblReferenceData
RID - PrimaryKey (That looks up the all three fields in tblReferenceList)
LNumber - ForeignKey (retrieved from another table that associates the reference with a lesson.
qryReferenceData (tried sorting query by RefNum but it still sorts by RID in the subform)
All fields from tblReferenceList and tblReferenceData
SubFormReferences - datasheet view (LNumber is the link field)
this subform is part of a main form that has all the information that pertains to a specific lesson that we teach. This particular subform allows the user to select references that are associated with a lesson. The user clicks a combo box and selects each RID. Then the adjacent fields populate with the RefNum and RefTitle.
The problem with using the primary key to select the record is that there are more than 500 references to choose from and the records are not in any sort of alpha order. I would like to be able to sort the records by the RefNum, however it cannot be the primary key because there are some duplicate RefNum's.
Thanks for the help.
Sean.