Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    usmcgrunt's Avatar
    usmcgrunt is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2010
    Location
    Just outside the gates of freedom
    Posts
    71

    Sorting on field other than primary key

    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.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You should be using queries with an OrderBy clause.

  3. #3
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    a combo box can sort by any column. not only the primary key column.

  4. #4
    usmcgrunt's Avatar
    usmcgrunt is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2010
    Location
    Just outside the gates of freedom
    Posts
    71
    RG, this database of mine would be going no where without your help. I think I will put your name on it instead of mine. once again the fire is lit. Now some research to keep it going.

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    "It is surprising how much work can get accomplished if you don't care who gets the credit." You get all of the credit here. You knew where to look for the answers.

  6. #6
    usmcgrunt's Avatar
    usmcgrunt is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2010
    Location
    Just outside the gates of freedom
    Posts
    71
    That is a great Harry Truman quote and it is so true. I haven't figured out the OrderBy Clause yet but I will report back when I am "cooking with gas".

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Use the Query Builder in design mode to assist. Then look at the SQL view.

  8. #8
    usmcgrunt's Avatar
    usmcgrunt is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2010
    Location
    Just outside the gates of freedom
    Posts
    71
    This is the code I put into the first field of the query in question.

    "SELECT [tblReferenceList].[RID],[tblReferenceList].[RefNum],[tblReferenceList].[RefTitle] FROM tbleReferenceList ORDER BY RefNum"

    When I run the query I get a 'Data type mismatch in criteria expression' error.

    On a side note, does it matter if the field in the query I am trying to apply the ORDER BY clause to is from a field in a table with a lookup expression?

  9. #9
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Oh no, your not using Lookup Fields in tables are you? http://www.mvps.org/access/lookupfields.htm
    All they do is confuse the programmer.

  10. #10
    usmcgrunt's Avatar
    usmcgrunt is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2010
    Location
    Just outside the gates of freedom
    Posts
    71
    Disregard my last. I was associating my form with the wrong query. What a JV mistake.

  11. #11
    usmcgrunt's Avatar
    usmcgrunt is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2010
    Location
    Just outside the gates of freedom
    Posts
    71
    Quote Originally Posted by RuralGuy View Post
    Oh no, your not using Lookup Fields in tables are you? http://www.mvps.org/access/lookupfields.htm
    All they do is confuse the programmer.
    Ohhhh I have got a lot of work to do removing all the lookup fields from my tables.

  12. #12
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Quote Originally Posted by usmcgrunt View Post
    This is the code I put into the first field of the query in question.

    "SELECT [tblReferenceList].[RID],[tblReferenceList].[RefNum],[tblReferenceList].[RefTitle] FROM tbleReferenceList ORDER BY RefNum"
    Did you really mean this line? If so then there is some learning that need to happen here.

  13. #13
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Quote Originally Posted by usmcgrunt View Post
    Ohhhh I have got a lot of work to do removing all the lookup fields from my tables.
    It is very easy to do.

  14. #14
    usmcgrunt's Avatar
    usmcgrunt is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2010
    Location
    Just outside the gates of freedom
    Posts
    71
    RG, before I start, what is the best way to remove the lookup fields and still retain the data that is already in them.

    1. If I change the field to another data type will I loose the data that is current in place? If yes, then should I export the data to an excel file and then import back after I change the data type.

    2. Can you give me hints to another way if #1 is not the most efficient way?

  15. #15
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Changing the Lookup to a TextBox does *not* change the value in the field. Back it up first so you will feel confident. http://www.btabdevelopment.com/ts/removelookups

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Sorting a Report by a Calculated field
    By mulefeathers in forum Reports
    Replies: 1
    Last Post: 05-22-2010, 08:21 PM
  2. sorting a field in report
    By Philangr8 in forum Reports
    Replies: 3
    Last Post: 08-26-2009, 05:38 PM
  3. Primary Key Help
    By phoenix13 in forum Access
    Replies: 4
    Last Post: 07-30-2009, 12:36 PM
  4. Replies: 1
    Last Post: 06-01-2009, 01:09 PM
  5. about the primary key!
    By Yuesko in forum Access
    Replies: 1
    Last Post: 05-29-2009, 04:20 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums