Results 1 to 11 of 11
  1. #1
    sd2222 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Aug 2017
    Posts
    8

    How could I convert a list box into a text box in the form.


    1) I have a multi value field in a table that is stored as a list box (cboHobbies) with two columns (id, hobbies).
    I'm adding this cboHobbies field in a form. How can I convert it into a text box, and only showing the hobbies ?

    I was able to convert it into text box on the form, but its showing me "id" not "hobbies" after conversion.

    2) Another question is that I have a search form that opens up another form. How can I search for the multivalue field in my case?

    Thanks in advance!

  2. #2
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    How can I search for the multivalue field in my case?
    That is the problem (one of many) about using MFVs'.

    Most experienced programmers do not use look up FIELDS, multi-value fields or calculated fields in tables.

    A multi-value field is not a normalized structure.


    Maybe see https://support.office.com/en-us/art...C-6DE9BEBBEC31
    Look towards the bottom for "How to employ a multivalued field in a query"

  3. #3
    sd2222 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Aug 2017
    Posts
    8
    Yes I agree with you 100%, but its a clients requirement.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Do you want the alias values displayed as a comma separated string in textbox? Review http://allenbrowne.com/func-concat.html. Build a query that joins the data table to the lookup table and expands the MVF. Use that query as source for the ConcatRelated() function and call the function from textbox. Reference the alias field from the lookup table for the field to concatenate. I tested. It works. However, on a form will have to size the textbox to at least two lines high so the text will wrap and the vertical scroll bar will be available or the textbox will have to be wide enough to display all possible values. On a report the CanGrow property will work.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    but its a clients requirement.
    I doubt that. I'm sure the client may have asked for or shown a picture of what the "displayed value" should look like.
    But I don't think they said " and it has to be an MVF in the table".

    Do not confuse storage and presentation. Too many do, but you should differentiate the two.

    If you have a statement of requirements, could you please post a copy? It may help readers offer more focused advice.

    Good luck with your project.

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    Use a combo box instead. It stores ID only, but shows a value connected to this ID instead - hobbies in your case

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Combobox will show the alias values but have to size the combobobox big enough to show longest possible content - there are no scroll bars.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Join Date
    Apr 2017
    Posts
    1,673
    The problem with selecting wide text into form. No good solution there. The best one is maybe the next one:
    1. You have a registry of hobbies, p.e. tblHobbies: HobbyID, ShortName, FullName;
    2. In combo on form, you select HobbyID by ShortName;
    3. You have additionally on form an unbound textbox, where FullName is displayed. The formula will be DLookup, which uses HobbyID (NB! The value of field in record, not the value of control (the combo)) in filter condition - at least when you have continous form. You can have the text box of any height, so you select the height it displays as many rows as you get maximally.

  9. #9
    sd2222 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Aug 2017
    Posts
    8
    Sorry to disappear after asking the question. My apologies.
    June I applied your technique and ran the function. I'm getting a syntax error while using this function in the control source of the textbox.

    =ConcatRelated("[hobbies]","[hobbies Query]","[hobbies_id] = " & [hobbies_id])

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Not hobbies_ID but the ID of the parent record associated with hobbies.

    As example, consider Owners and Pets. Want to show all pets associated with owner as a comma delimited string.

    ConcatRelated("PetName", "qryOwnerPets", "OwnerID=" & [OwnerID])
    Last edited by June7; 08-24-2017 at 12:14 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    sd2222 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Aug 2017
    Posts
    8
    Thank you June. It solved the problem. I appreciate your help.

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

Similar Threads

  1. convert text
    By hemaazez in forum Access
    Replies: 3
    Last Post: 06-06-2017, 02:04 AM
  2. Replies: 5
    Last Post: 04-01-2013, 11:49 AM
  3. Convert a crosstab set of data to a flat list
    By jamesrees02 in forum Queries
    Replies: 3
    Last Post: 02-17-2012, 06:36 PM
  4. Replies: 7
    Last Post: 10-26-2011, 01:47 PM
  5. Replies: 2
    Last Post: 04-07-2011, 10:15 AM

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