Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Ramtrap is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Posts
    62

    Add multiple values to single textbox

    I've searched a lot of forums but can't find the exact answer I need.



    My db is a Conference Scheduling DB whereby I have four tables to consider:
    Persons / Person-to-Conference / Conference_Main / Conference_Day

    I am unable to post images because when I try to do so on my work computer, it craps out, so I'm sorry for the lack of visual.

    This db will allow someone to create a conference, where it will automatically populate data for Day 1. Not all conferences are multiple days, but some are.

    Let's say there's a three-day conference (Conference X), and it's attended by three people (A, B, C).

    Person A is going Day 1
    Person B is going Day 1 & 3
    Person C is going Day 2 & 3

    I have a query where I want to show what days of the conference people attended and I want to populate a textbox on a form with that information. Is there a simple concatenation I can do, or do I need a For Each loop, because of the question of whether or not a conference will even have multiple days or not?

    I want to see something like this:
    Attendee Days Attended
    Person A 1
    Person B 1, 3
    Person C 2, 3

    *Note "Days Attended" would be the textbox in question where I'd want to see the multiple values, e.g. "2, 3"

    I'd rather not store this in another table, but rather just make it Dynamic based on whatever days I know my person is going to for the conference. Can anyone help me with some vba?
    Last edited by Ramtrap; 11-08-2017 at 11:04 AM. Reason: Added note

  2. #2
    Ramtrap is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Posts
    62
    Wondering now if this should instead be a Query that will concatenate the Days attended. Is that possible?

  3. #3
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You would be needing this http://allenbrowne.com/func-concat.html

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    struggling to understand how your data is organised

    Person A is going Day 1
    Person B is going Day 1 & 3
    Person C is going Day 2 & 3

    the concat related function will work if Persons B and C have two records each (one for each day)

  5. #5
    Ramtrap is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Posts
    62
    This is awesome, and I think I got it for the most part, but I am probably putting in the wrong info for the strWhere argument.

    Right now it looks like this:
    Combo Box = Control Source: =ConcatRelated("Day", "Conference Day", "Conf_Day_ID_PK=" & [Conference Day].[Conf_Day_ID_PK])
    Whereby: strField = Day; this is the name of the field I want to concatenate. It's found in my "Conference Day" table, but also in may other queries
    strTable = qry_ConfAndDays; this query right now will show me the person (Person_ID_FK), the Main Conference (Conf_Main_ID_FK), the Conference Day ID (Conf_Day_ID_FK) and the Day (Day). The ID fields are coming from a junction table (Person-to-Conference) between the Person table and the Conference Main table, which is also hooked into Conference Day table) and the Day field is coming from the Conference Day table, as Days are not represented in the "Person-to-Conference" junction table.
    strWhere = "Conf_Day_ID_PK=" & [Conference Day].[Conf_Day_ID_PK]; this is the part that I think may be wrong--I guess I'm confused as to where it's supposed to be pulling this information from. I realize it's trying to set a condition that populates the field with a value, but I'm blanking on where that value should be derived from.

  6. #6
    Ramtrap is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Posts
    62
    Quote Originally Posted by Ajax View Post
    struggling to understand how your data is organised

    Person A is going Day 1
    Person B is going Day 1 & 3
    Person C is going Day 2 & 3

    the concat related function will work if Persons B and C have two records each (one for each day)
    I have a Persons table. This is the pool of names I draw from (or add to) to add people to a conference.
    I have a have a Persons-to-Conference table, which is just ID numbers from the Person, Conference_Main, and Conference Day table, that show what people went to which conference and what days.
    I have a Conference_Main table. This is the Overall conference (example: Conference X).
    I have a Conference_Day table. This is necessary because not all conferences are only one day. So each conference created will at LEAST have Conference Day 1. Some will have 2 or 3 or more.

    What I'm trying to do, is on a form that shows all the persons going to a conference (e.g., Conference X), I want to see specifically what days they are going to (which is the Person A, B, C example from prior).

    So I want to see the person's name and --in one textbox, which is informational only-- what specific days of the conference they are attending.

    Does that help?

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Does that help?
    not really. this is the bit I struggle with

    I have a Conference_Day table. This is necessary because not all conferences are only one day. So each conference created will at LEAST have Conference Day 1. Some will have 2 or 3 or more.
    Perhaps show some sample data for all 4 tables and the outcome you want from that data. It may be obvious to you, but I can interpret it in a number of different ways

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    If you want to concatenate the days for each attendee/conference, then looks like you need to build a query that joins Persons-to-Conference and Conference_Day tables so the conference days will be available and run the ConcatRelated with that query as source.

    Cross-posted https://stackoverflow.com/questions/...crosoft-access
    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.

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    doesn't look like the first time - @Ramtrap, please read this link about the etiquette of cross posting http://www.excelguru.ca/content.php?184

  10. #10
    Ramtrap is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Posts
    62

    Cross-posting

    Quote Originally Posted by Ajax View Post
    doesn't look like the first time - @Ramtrap, please read this link about the etiquette of cross posting http://www.excelguru.ca/content.php?184
    I’ve posted on both communities because I get value from both people. Im not getting the same answer from both, nor am I not learning any valuable skills with the responses I get. There are two forum posts I search for to find answers and I have had nothing but good experiences.

    Curious why would feel the need to search my posts in another forum in the first place, as it seems you only want to point out that I’m seeking help in two places. Curious if you left me the same reply to the same forum about cross-posting in the other thread?

    Let me be clear that my intent is not to waste anyone’s time. Again, I get a ton of value from the different interactions. Are you going to trust the word of one person or one thread when there are numerous other branches? Why can’t help be given across the board? If someone is doing this to give, six, more forums, I definitely get your point, but I believe my second post was listed a couple hours after the first on this forum, and I was wracking my brain trying to figure it out, and thought I might have luck with some activity on the other. I’m sorry if you feel like that wasted your time, as that was sincerely not the intent.

    Please also note that I stated I am unable to post screenshots or attachments, as the work performed is on an entirely different computer which I cannot copy to, and my Firefox crashes every time I try to post an image. I’ve re-written entire posts, structure and all, only to have it wash away with a “Not Responding” so I apologize I’m unable to give you those resources.

    Again, I apologize if anyone be felt like I was wasting their time, and can only assure you through this digital communication that I am thankful and have learned a lot, but the intent was never to upset anyone or become the subject of others’ search history.

    Thanks.

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    it is not suggested that you should not post on both communities, just have the courtesy to explain what you are doing and why, it takes just a few words and a link.

    Let me be clear that my intent is not to waste anyone’s time.
    but that is exactly what you are doing

    Why can’t help be given across the board?
    this comment is a selfish one. To use an analogy, you are taking up three seats on the train with your bags, preventing others from sitting down.

    but I believe my second post was listed a couple hours after the first on this forum
    forums are a free resource, people have to sleep and often have day jobs, it might take 48 hours to get a response, sometimes longer.

    The point is all forums have a certain expectation with regards behaviour, you may not have been aware of those expectations, but now you are. So lets move on, Apology accepted

    with regards posting images etc, It might be the filesize is over forum limits so trying zipping the files, most forums allow bigger files when they are zipped. Otherwise suggest try a different browser, although I've use firefox without issue.

  12. #12
    Ramtrap is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Posts
    62
    this comment is a selfish one. To use an analogy, you are taking up three seats on the train with your bags, preventing others from sitting down.
    It's selfish to use a free resource? No. And the train isn't the destination, the answer is. There are *multiple* trains (my question on two forums) going to the same destination (the answer to the question).

    forums are a free resource, people have to sleep and often have day jobs, it might take 48 hours to get a response, sometimes longer.
    I realize this. As no one is being contracted, nor are they obligated to help, asking multiple people shouldn't be a contentious issue. I'm not so daft as to think I'm the center of the world or that my question deserves preference--I'm simply using two separate communities for help with my question. There's NFL football and NCAA; do you think one person need only watch one or the other because their needs to watch football should be satiated without the need for the other? Is going to two or more different stores to comparison shop a television frowned upon because one person should be loyal to one place over another? Lunacy.

    The point is all forums have a certain expectation with regards behaviour, you may not have been aware of those expectations, but now you are. So lets move on, Apology accepted
    Passive-aggressive, much? One might say it's unacceptable behavior to call out an etiquette issue in an open forum rather than submitting a simple direct message to that person. If you want attention, though, I get it.

    with regards posting images etc, It might be the filesize is over forum limits so trying zipping the files, most forums allow bigger files when they are zipped. Otherwise suggest try a different browser, although I've use firefox without issue.
    I'm unable to do so for reasons I can't get into on an open forum, but assure you it's a limitation due to the lack of software updates, that I am unable to conduct, myself.

    I've never had a problem with anyone on this forum before, and I don't think it reflects on the site, but rather you. I am nothing but *thankful* to those that offer help at the cost of their own time and expertise, but in *no way* feel entitled to it. If you have an issue with me, I'm sorry. And can we mention how kind of ridiculous it is to point me to a forum post on yet ANOTHER forum, asking me to go through that post? If there's such an issue, why not post it as a sticky on THIS forum with authors from THIS site getting credit (since that seems to be your main argument)? I see NOTHING in the FAQs about "cross-posting" or etiquette insofar as posting to other sites, so other than what you found--on another site--can you please point me to the location of the etiquette rules in regards to the above and aforementioned as it pertains to *this* forum?

    Thanks, pal.

  13. #13
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    The link provided is one used by pretty much everyone because it gets the point across and refers to all forums.

    I'll leave you to get the last word in if you wish, but I will not be responding any further

  14. #14
    Ramtrap is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Posts
    62
    Quote Originally Posted by Ajax View Post
    The link provided is one used by pretty much everyone because it gets the point across and refers to all forums.

    I'll leave you to get the last word in if you wish, but I will not be responding any further
    Despite our objective objections and disagreements with etiquette, I do want to thank you--sincerely--for the help you did attempt to offer early on. I'm sorry that it came to this, but fully respect your wishes. Not a last word, but just want you to know that I am grateful for your earlier comments. Thank you.

  15. #15
    Ramtrap is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Posts
    62
    Quote Originally Posted by Minty View Post
    You would be needing this http://allenbrowne.com/func-concat.html
    This site is super-helpful, thank you! But I am hitting a roadblock with getting it to work. I pulled up a sample database (from Lynda.com) and it's got all the generic requisite tables (Customers, Employees, Orders, Products). I created an Orders form with all the values from that table and input how I'm interpreting the instructions the following on the Control Source of an Unbound textbox:
    =ConcatRelated("OrderDate","Orders","CustomerID=" & [CustomerID])
    But I'm coming up with a #Name? error. I wholeheartedly believe this is a user-error, but I'm just unsure what I'm doing wrong.

    What I *think* should happen is that it should put in the textbox all OrderDates whose customer ID matches whatever is in the current CustomerID field. is my reasoning incorrect?

    Attempting to put in an image; hopefully won't crash!

    Click image for larger version. 

Name:	Capture.jpg 
Views:	17 
Size:	162.3 KB 
ID:	31183

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

Similar Threads

  1. Add Multiple Values To A Textbox
    By vazdajic in forum Programming
    Replies: 3
    Last Post: 06-06-2017, 07:24 AM
  2. Retrieving Multiple Values into a Single Field
    By Simbiose in forum Queries
    Replies: 6
    Last Post: 08-23-2016, 07:54 AM
  3. Multiple Values in textbox
    By frksdf in forum Forms
    Replies: 10
    Last Post: 07-14-2014, 11:41 AM
  4. Replies: 7
    Last Post: 06-04-2013, 01:10 PM
  5. Replies: 1
    Last Post: 12-04-2011, 06:33 PM

Tags for this Thread

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