Results 1 to 4 of 4
  1. #1
    oldager is offline Novice
    Windows 10 Access 2003
    Join Date
    Aug 2020
    Location
    Ireland
    Posts
    7

    Query to alphabeticalize table populate cotrol o form

    I've got completely stuck on this one.

    I have table [Agents] which is not in alphabetical order and I am adding to it daily. Now that I have over 20 entries I need to get it in order.
    There are 3 fields in the table: AgentID; AgentName and PhoneNumber.

    I have created a query based on the table and this I have sorted into Alphabetical order based on AgentName.



    However, I cannot get the AgentName to populate the [Agent} control on the form - I always get the PhoneNumber irrespective of where I have the order of the query columns: whether [AgentName] is column 1 or 2 in the query.

    In the [Agents] control row source I have:
    Code:
    SELECT [AgentName], [PhoneNumber] FROM [Agents Query] AS Query;
    I am not using AgentsID in the query as I have found that the result is in numerical order not alphabetical order that I want (though this is the only time I got the AgentsName to populate the [Agent ]Control.

    Any help gratefully received and thanks in advance for your trouble.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You don't say what type the control is, so I'm guessing it's a combo because you mentioned row source. The query field order dictates where the data enters the combo column order and the column count has to match the field count. If you don't want to see a particular field (e.g. ID) you set that column width to be zero. If the agent field can have 2 or more identical values, then you might need that ID field if it's the PK.

    I'd also advise against using reserved words (query) for anything in Access.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    oldager is offline Novice
    Windows 10 Access 2003
    Join Date
    Aug 2020
    Location
    Ireland
    Posts
    7
    Thank you micron for your help which has resolved my problem. I did know about the column count and the field count as well as setting the columns to 0 width if I don't want to see them. However, in my old age I tend to forget things very quickly - I can't even remember what I had for breakfast yesterday!

    I used the wizard to create the query which gave me the name "Agents Query" but I have now changed that to "AgentsQy".

    Again, many thanks for your help.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Glad it was that simple and that you can mark this thread as solved!
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 06-27-2018, 05:33 PM
  2. Populate Textbox with different query/table?
    By schulzy175 in forum Programming
    Replies: 1
    Last Post: 03-26-2018, 03:51 PM
  3. Populate Report with Table from Query
    By Ekhart in forum Reports
    Replies: 2
    Last Post: 08-26-2016, 08:21 AM
  4. Replies: 1
    Last Post: 12-23-2015, 08:45 AM
  5. Populate Table with query results
    By sparkyboy2406 in forum Queries
    Replies: 1
    Last Post: 02-23-2010, 03:51 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