Results 1 to 13 of 13
  1. #1
    FriQenstein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    98

    Display Text Values instead of ID numbers

    Greetings all,



    I recall watching a video about this last year at some point, but I cannot find it again and do not remember how I stumbled upon it in the first place.

    Scenario:
    The main form has text boxes that a user can use to search based on a query. When the Search button is pressed, the results are displayed in the subform, which was generated from the query.
    However, in the subform results, it shows ID reference numbers for relationship data rather than the actual text values. For example, instead of the Client Name, it will show 1, 2, 15, etc.
    How do I get the displayed results to show the client name instead of the ID number associated with that client name?

    Here is a screenshot of what I am referring to:
    As you can see, I am using drop down lists in the main form to select the company name which helps the user by selecting a name rather than an arbitrary number. The results need to show the same text values.

    Thanks.Click image for larger version. 

Name:	Capture.PNG 
Views:	16 
Size:	26.7 KB 
ID:	42634

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    depends on what you are doing with the subform.

    If it needs to be updateable so users can change and add new records, change the relevant controls to combo boxes with a rowsource the fetches for example clientID and client name

    If not, modify your recordsource to include the relevant tables and bring for example the client name through rather than the ID

  3. #3
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Why do you need to show the client name in the subform? It will always be the correct client if your form/subform linkage is correct.

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Why do you need to show the client name in the subform? It will always be the correct client if your form/subform linkage is correct.
    good point!

  5. #5
    FriQenstein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    98
    Quote Originally Posted by Ajax View Post
    depends on what you are doing with the subform.

    If it needs to be updateable so users can change and add new records, change the relevant controls to combo boxes with a rowsource the fetches for example clientID and client name

    If not, modify your recordsource to include the relevant tables and bring for example the client name through rather than the ID
    Hi Ajax, Thank you for the response.
    As indicated by the title on the main form, this is just a search form that updates the results in the subform from the query once the user hits the button. There is no edits or anything the user can do to alter the data from this form. It is only for searching purposes. I just want the text values to display in the subform as they appear in the main form.

    Hope that helps clarify it a bit.

    As for modifying the record source, I'm not certain what you mean here. When I created it, I used the following criteria:
    Code:
    Row Source: SELECT [tblClientsC].[ClientID], [tblClientsC].[Company] FROM tblClientsC ORDER BY [Company];
    This is how the data source is defined in the main form for the drop down list for Client Name.

    In the query, I used the criteria to pull from the main form's value in the corresponding field (a.k.a. the name of the textbox ClientNameSearch)
    Code:
    Like "*" & [Forms]![frmSearchClientInventory]![ClientNameSearch] & "*"

  6. #6
    FriQenstein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    98
    Quote Originally Posted by davegri View Post
    Why do you need to show the client name in the subform? It will always be the correct client if your form/subform linkage is correct.
    How so? First of all, "Why do you need to show the client name in the subform" because I want the client name to be displayed in the subform.. who wouldn't want to see the actual name rather than a placeholder number?
    What do you mean by "... if your form/subform linkage is correct..."? Everything appears to be okay except for how it is displayed. I made standard relationship links for the tables, so I am not certain how this could be 'wrong'.
    Please elaborate?

    Thanks.

  7. #7
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    How so? First of all, "Why do you need to show the client name in the subform" because I want the client name to be displayed in the subform.. who wouldn't want to see the actual name rather than a placeholder number?
    What do you mean by "... if your form/subform linkage is correct..."? Everything appears to be okay except for how it is displayed. I made standard relationship links for the tables, so I am not certain how this could be 'wrong'.
    Please elaborate?
    My suggestion would show neither the placeholder number nor the name. I didn't suggest that your linkage was wrong, I was just saying that if it is right, the subform will always show the correct records.
    If you *must* see the name for comfort, then convert the client name textbox to a combo box and have it lookup the name from the client table.

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I meant the recordsource of the subform, which has fields like item, qty etc

    this

    SELECT [tblClientsC].[ClientID], [tblClientsC].[Company] FROM tblClientsC ORDER BY [Company];

    is what you would use as a rowsource to a combobox to replace the client name control on the subform

  9. #9
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    It will always be the correct client if your form/subform linkage is correct.
    on reflection - and I wasn't really paying attention, this is a search form - so user might search on a part number which goes to many clients - so you would want to see the client name

  10. #10
    FriQenstein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    98
    Quote Originally Posted by Ajax View Post
    on reflection - and I wasn't really paying attention, this is a search form - so user might search on a part number which goes to many clients - so you would want to see the client name
    Correct. As we deal with equipment specific to a certain industry, different customers can have the same type of units with similar part numbers, model numbers, and some identical serial numbers as well.

  11. #11
    FriQenstein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    98
    Quote Originally Posted by Ajax View Post
    I meant the recordsource of the subform, which has fields like item, qty etc

    this

    SELECT [tblClientsC].[ClientID], [tblClientsC].[Company] FROM tblClientsC ORDER BY [Company];

    is what you would use as a rowsource to a combobox to replace the client name control on the subform
    So, if I am understanding this correctly, you are saying I need to convert the client name textbox on the subform to a combobox and use the recordsource code from above?
    If that is the case, I do not see how it will actually reference what the user selects from the form's Client Name combobox, which is how the query is built... just like when a user searches for a serial number.

    I apologize if I am not grasping what you are saying... I'm just trying to further understand how it all pieces together and functions so that later on when I start doing similar tasks with reports it will show correct names/values instead of ID numbers.

    Regards

  12. #12
    FriQenstein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    98
    Quote Originally Posted by davegri View Post
    My suggestion would show neither the placeholder number nor the name. I didn't suggest that your linkage was wrong, I was just saying that if it is right, the subform will always show the correct records.
    If you *must* see the name for comfort, then convert the client name textbox to a combo box and have it lookup the name from the client table.
    Ahh, I see what you were referring to now. I misunderstood what you had originally said. Thank you for clarifying it.

    Yes, the results will always be correct, but for the purposes of end-user-recognition, it would be beneficial if they see the client name as apposed to a digit.

    The same can be applied when I start designing the reports from searches and whatnot. I'll need to have the actual names of things instead of ID placeholders. I could generate reports for each individual client thus negating a few steps, but that would also mean more forms, queries, and reports to be created too.

    Regards

  13. #13
    FriQenstein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    98
    I think I have figured it out...

    What I did was:
    1. Edited the query to include the ClientName table
    2. Edited the subform's ClientName text box to reference to the ClientName table instead of the inventory table that the rest of the form is queried upon
    3. Now the Client Name is actually displayed instead of the ID number of the Client's name.

    I'm not certain if step #1 was necessary, but when I tried to set the source for the textbox to something other than what was directly referenced from the table in use, it kept giving me an error message which led be to adding the tbl source to the query.

    At any rate, it works, for now. I will continue to test it and fiddle a bit to see if it is truly corrected.

    I appreciate your replies. They definitely helped to steer my brain onto the correct path which led to the current solution.

    After I verify it a bit, and any possible feedback, I'll come back and mark this post as resolved.
    Again, thanks.

    Regards

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

Similar Threads

  1. Replies: 7
    Last Post: 05-15-2017, 09:41 PM
  2. Replies: 7
    Last Post: 08-18-2016, 07:41 AM
  3. Replies: 11
    Last Post: 10-28-2014, 10:39 AM
  4. Replies: 19
    Last Post: 09-09-2014, 01:36 AM
  5. Replies: 1
    Last Post: 06-29-2012, 01:22 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