Concatenate Multi-Select List Box Items

I need to use the chosen items in a multi-select List Box as concatenated 
text in another control on a subform.  This text will be part of a large 
amount of concatenated text.  How do I do this?  I did find info here about 
using such items in a query but it was way over my head and I couldn't figure 
out how to convert that idea to this issue so please don't just refer me to 
that w/o some other explanation for this novice.  
For example, if the user selects:  Frt Bumper, Grille, & Headlamp in the 
List Box, I want to then somehow concatenate those selections into a sentence 
like:
There was damage to the Frt Bumper, Grille and Headlamp.
Thanks so much for your help!!

Pamela

0
Utf
12/30/2009 3:02:01 AM
access.forms 6864 articles. 2 followers. Follow

3 Replies
1469 Views

Similar Articles

[PageSpeed] 45

On Tue, 29 Dec 2009 19:02:01 -0800, Pamela <Pamela@discussions.microsoft.com>
wrote:

>I need to use the chosen items in a multi-select List Box as concatenated 
>text in another control on a subform.  This text will be part of a large 
>amount of concatenated text.  How do I do this?  I did find info here about 
>using such items in a query but it was way over my head and I couldn't figure 
>out how to convert that idea to this issue so please don't just refer me to 
>that w/o some other explanation for this novice.  
>For example, if the user selects:  Frt Bumper, Grille, & Headlamp in the 
>List Box, I want to then somehow concatenate those selections into a sentence 
>like:
>There was damage to the Frt Bumper, Grille and Headlamp.
>Thanks so much for your help!!
>
>Pamela

Let's say the listbox is named lstDamage on form MyForm. Put the following
into a Module:

Public Function ConcatDamage() As String
Dim varRow As Variant
ConcatDamage = "There was damage to the "
For Each varRow In Forms![MyForm]![lstDamage].ItemsSelected
   ConcatDamage = ConcatDamage & varRow & ", "
Next varRow
ConcatDamage = Left(ConcatDamage, Len(ConcatDamage) - 1) & "."
End Function

Air code, untested...
-- 

             John W. Vinson [MVP]
0
John
12/30/2009 4:34:19 AM
Thanks, John.  But could I get a little more help on how to call this from my 
form? I saved it as ConcatDamage in Modules which I thought mirrored your 
example.

Thanks so much!

"John W. Vinson" wrote:

> On Tue, 29 Dec 2009 19:02:01 -0800, Pamela <Pamela@discussions.microsoft.com>
> wrote:
> 
> >I need to use the chosen items in a multi-select List Box as concatenated 
> >text in another control on a subform.  This text will be part of a large 
> >amount of concatenated text.  How do I do this?  I did find info here about 
> >using such items in a query but it was way over my head and I couldn't figure 
> >out how to convert that idea to this issue so please don't just refer me to 
> >that w/o some other explanation for this novice.  
> >For example, if the user selects:  Frt Bumper, Grille, & Headlamp in the 
> >List Box, I want to then somehow concatenate those selections into a sentence 
> >like:
> >There was damage to the Frt Bumper, Grille and Headlamp.
> >Thanks so much for your help!!
> >
> >Pamela
> 
> Let's say the listbox is named lstDamage on form MyForm. Put the following
> into a Module:
> 
> Public Function ConcatDamage() As String
> Dim varRow As Variant
> ConcatDamage = "There was damage to the "
> For Each varRow In Forms![MyForm]![lstDamage].ItemsSelected
>    ConcatDamage = ConcatDamage & varRow & ", "
> Next varRow
> ConcatDamage = Left(ConcatDamage, Len(ConcatDamage) - 1) & "."
> End Function
> 
> Air code, untested...
> -- 
> 
>              John W. Vinson [MVP]
> .
> 
0
Utf
12/30/2009 2:41:01 PM
On Wed, 30 Dec 2009 06:41:01 -0800, Pamela <Pamela@discussions.microsoft.com>
wrote:

>Thanks, John.  But could I get a little more help on how to call this from my 
>form? I saved it as ConcatDamage in Modules which I thought mirrored your 
>example.

Use

=ConcatDamage()

as the Control Source of a textbox on a form or report.
-- 

             John W. Vinson [MVP]
0
John
12/30/2009 5:05:48 PM
Reply:

Similar Artilces:

recover outbox items from user's pst
We had an exec's laptop crash while working offline with several emails she wants recovered sitting in the outbox. We were able to get the .ost file off the laptop. Is the solution as simple as recreating outlook in cached exchange mode and replacing the OST? found the answer in thread ost to pst. "knightly" wrote: > We had an exec's laptop crash while working offline with several emails she > wants recovered sitting in the outbox. We were able to get the .ost file off > the laptop. Is the solution as simple as recreating outlook in cached > exchange mode...

Exact Validation Rules for a combo boxes
Hi All, I want to validate to either alllow or deny a user's entry to certain combo boxes in the form. The comboxes have the lists from another table. So this is what I have in the BeforeUpdate if cboPlace <> "Away" or cboPlace <> "Home" or cboPlace <> "Dont Know" then me.undo cboPlace.SetFocus end if but that's not working. Please help. And other cbos are pulling from a table, which has a lot more data, how do i go about validating that without having to hardcode it? Thanks. I was able to use the validation rule for the tables,...

How to delete most recently used file list
Can someone please tell me how to delete a file from the most recently used file list in Money 2003? The file is already physically deleted so I don't want it in the listing. I am talking about the "File" pull- down list. Thanks!! On Feb 6, 3:50=A0pm, bajpd <ba...@peak.org> wrote: > Can someone please tell me how to delete a file from the most recently > used file list in Money 2003? =A0The file is already physically deleted > so I don't want it in the listing. I am talking about the "File" pull- > down list. Thanks!! Nevermind, i figu...

Error 8331 -Address list synchronization (MSExchangeAL)
I have install and configured Small Business Server 2003. Everything seems to be working fine expect for Exchange 2003 which keeps filling the application logs with the following error. Source: MSExchangeAL Error: Address List Synchronization 8331 Description: "The service threw an unexpected exception which was caught at f:\titanium\dsa\src\lra\abv_dg\lservagnet.cpp(4511)" This error appears literally every min. The application log fills and has to be cleared only to fill up again. Does anybody know what is causing this?????? This can be caused by a number of things. Primar...

When concatenating concatenates don't concatenate...
Hi List, Can anyone help? When concatenating already-concatenated cells, th result displays perfectly well in the Excel spreadsheet, but truncate when the cell is pasted into a .txt file. It doesn't seem to be due t Data Validation limits (having said that, selecting the entir worksheet and doing Alt > Data > Validation > Validation criteria Allow = Any value" did seem to solve the problem once, but only to com back next time round). The truncation occurs sometimes after 8 or 1 chars, and sometimes after 20 or so, always at the same spot. If I cop the cell into a fresh Excel...

Command to select variable input range
New user. Need help with macro. I would like a a list box that gives the end user a list of cell range names (ie range1, range2, range3, etc. After selecting the input range from the list box, that range will be copied to a second worksheet (sheet2) to a fixed location (a named range - Target). The source ranges (range1, range2, range3, etc) and the traget range are the same dimensions. The following works if I manually enter the Source range. I am trying to automate this so end user will not enter invalid range names. Sub test1() Sheets("sheet1").Range(InputBox("Ente...

Contact looses Post Office Box field when synchronized to Outlook
When I sync contacts from CRM 4.0 to Outlook, the Post Office Box field from the contact gets empty. Anyone has the same issue? ...

Multi addressee email privacy
I run a small business and need to send an email to many people. It is important that it be a single email and that the recipients are NOT able to see that all the other addressees also received it, ie so it looks like a personal email and does not give the full list of other recipients. How do I do this on Outlook 2000? Thanks in advance. -- MaggyS Mail merge to electronic maiil. Outlook uses Word for performing mail merge functions such as Form Letters, mailing labels, envelopes, and fax or email merges. For an overview of these functions take a look here: http://www.slipstick.com...

Problems with Reconcilation and Downloaded or Cleared Items
I am having problems with reconcilation on items that have cleared electronically or manually after the statement date. Upon completion of the bank reconcilation all items are showing R and it is making the next reconcilation incorrect. In microsoft.public.money, Sheila wrote: >I am having problems with reconcilation on items that >have cleared electronically or manually after the >statement date. Upon completion of the bank >reconcilation all items are showing R and it is making >the next reconcilation incorrect. Is Tools->Options->OnlineServices->Auto...

Select Query Criteria
I have a table that I need to query to pull out all account numbers that have the characters 921 as the 11th, 12th and 13th character. The table has 90,000+ records a sampling of the format of the account numbers are: 123456789-921-1 234234234-921-4 765874345-921-1 938586747-921-1 354921988-921-1 If I use the criteria of Like "*921" it pulls records where 921 appears in the first 9 characters as well; I just want to select the records with 921 between the dashes...any idea how I would write the criteria for this? Thanks for your help. Try typing this in the Field row...

Data selection on line chart
I'm collecting data but only want to chart the last 60 points on my line chart. To do this I have to manually update the series value information in the source data. for example: I have 176 rows of data and the chart series value is "=sheet1$b$117:$b$176" if I add a new row I have to change this to "=sheet1$b$118:$b$177". Is there any way to use a variable for this? say LRD is the last row of data could I change this value statement to be "=sheet1$b$(LRD-59):$b$(LRD) so that anytime I add a new row it would update the chart? thanks, Q -- Qiset ----------...

Bogus Server List when Create New Mailbox
When trying to create a new mailbox for a user, i go to AD, select the user and select "exchange tasks". I create a new mailbox, and one i get to the dialog box to select the Mailbox Location (server) i see my only exchange server, plus 2 old ones that are no longer running. I have mannually checked with ADSI the configuration>>services>>Microsoft Exchange>>ORG NAME>>Administrative Groups>>ADMIN GROUP NAME>>Servers containger. These servers are not listed in here, only the production server. My question is, how do i get these "old"...

Calendar continues sending appointment messages to the Deleted Items folder (2nd post)
I have a user who reports her Calendar (Outlook 2000) continues sending appointment messages to the Deleted Items folder. Server is Exchange 5.5 SP4 server. These appointments have been added to the calendar before they have been automatically sent to the Deleted Items folder. She does not have the Resource Scheduling option under Tools->Options->Calendar set to automatically accept. There are no Outlook rules defined that affect appointment items. Apparently this is all default behavior. But how do we turn off this default? Outlook keeps sending unwanted calendar appointment mess...

Voiding a PO invoice should open the item rcpt for re-invoicing
If a posted PO invoice has been voided, the receipt on the PO should now be reset to be re-invoiced. You should not have to issue a return document to accomplish this (i.e. the invoice was matched incorrectly -- thus a void is in order). ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" ...

how do i add a list to the existing drop down list?
Please inform me by replying to my email address. thanks. Asked here.........answered here. Simply add items to the current list then re-define the range in Insert>Name>Define. Gord Dibben MS Excel MVP On Thu, 27 May 2010 14:23:37 -0700, Lorelie <Lorelie@discussions.microsoft.com> wrote: >Please inform me by replying to my email address. thanks. ...

Concatenate function
Hi, How can I concatenate these 2 cells: one is time and the other is text: 9:00 and AM and I want the result to be 9:00 AM =CONCATENATE(AD2, " ", AE2) I have tried different formating cells but it doesn't work, this is what I get: 0.375 AM Thanks for your help. NSNR - You must format the time (which is a number) to text: =TEXT(AD2,"H:MM") & " " & AE2 -- Daryl S "NSNR" wrote: > Hi, > How can I concatenate these 2 cells: one is time and the other is text: 9:00 > and AM and I want the result to be ...

Multi Shift Work
I have a project that has four teams. One is Monday thru Thursday (day shift 10 hr per day): the second is Monday thru Thursday (night shift 10 hr per night); the third is Friday thru Sunday (day shift 12 hr per day) and the last is Friday thru Sunday (Night shift 12 hr per night). There are similar reasources in each team. Question 1: Should I set up four calendars, one for each team? Question 2: The night shift goes from 6:00 pm to 4:00 am, is that the proper way to enter it tin the calendar? QUestion 3: When I assign resources to a task, should I assign from all four...

Extracting Pivotable Selections
In a field, if you elect to only include certain items, then when you select all of those items the table displays (Multiple Items) rather than (All). Is there a way to obtain a list of the items that are included (or excluded) from the Multiple Items group? I know how to see the list, but is there a way to move that info to cells in the spreadsheet? The following code will print the page field items that aren't hidden: '============================ Sub ListFieldsVisible() 'lists visible page field items on a new worksheet Dim ws As Worksheet Dim pt As PivotTable Dim pf A...

Q: multi line CRichEditCtrl
I have implemented a "logging" window using CRichEditCtrl - the code being inspired by a snipped in http://www.codeproject.com. For some reason though, instead of having each log message in a separate line, it is being appended again and again into one big long line. I have tried different styles, also I have tried append CRLF (\r\n), LF only (\n), none of the above - nothing worked. Can anyone post a code snipped as to how to generate each log message into a different line... Thanks. John, You might want to set the style ES_MULTILINE to the control. Johan Rosengren Abs...

Concatenating Cells
I have spent hours this afternoon in Excel 2003 trying to concatenate two adjacent text columns into a third column defined as Text format. It doesn't work, the result cell just displays the formula you enter {e.g. =A1&B1 or =CONCATENATE(A1,B1)}. I discovered after a great deal of frustration that this will only work if the cell containing the formula is formatted as '*General'*. All the MS command help refers to the data being concatenated as 'text' data as does the command help that displays as you type. I found no help on this on the MS site and trawling the w...

Moving First E2K3 to New Box
Used KB 822931 and followed all directions. I was able to turn off the first server and all users were able to receive email and I was also able to send e-mail to a mail enabled public folder residing on the same exchange server. However outside users are not able to send mail to the public folder that were able to in the past. When I turn the first exchange server back on, the mail gets delivered to the public folder. I checked to make sure all replication was turned off etc... Not sure what to check next. On Wed, 20 Sep 2006 12:36:02 -0700, hager123 <hager123@discussions.micr...

Can I Use AutoNumber Field In Multi-Table Link?
Is there any problem using the AutoNumber field as the primary key, and then using this field to define a 1-M relationship between two tables? I recall being warned against this. At the time, I was using another db app (Paradox). Would this warning also apply to Access? David Portwood wrote: > Is there any problem using the AutoNumber field as the primary key, > and then using this field to define a 1-M relationship between two > tables? > I recall being warned against this. At the time, I was using another > db app (Paradox). Would this warning also apply to Access? No....

Distribution List
I have changed my distribution list, saved it and updated it. Nevertheless, when I enter the name of the list, the old members of the list show up. It's as if I had two address books, a new and an old. Any suggestions? Thanks. We can only guess since you failed to tell us how you are selecting this DL as a recipient. I would guess you failed to delete the entry for this DL from your autocompletion cache. -- Russ Valentine "Jethro Pull" <jpull@hotmail.com> wrote in message news:0pLtn.304808$OX4.237082@newsfe25.iad... >I have changed my distribution...

Font selection list mix up
I recently upgraded to OS X 10.5.8 and Entourage 2008. I'm using Extensis Suitcase Fusion to handle my font activation on the computer. When I try to change fonts using the font pull down menu in Entourage my font list is not arranged as it used to be in Office 2004 and X. Some fonts, which I know are activated in other applications on the computer, are not listed. Others are only listed by their type such as Medium, Light, Book or Condensed with no mention of what font they are until you actually select them. I posted to Microsoft's forums about this and they suggested turning off WY...

Adding code right after the "Save As" dialog box
I have created an excel application whose footer has -apart from other things- the full path and file name where the excel workbook resides. I would like this information (fullpath + file name) to be reflected in the footer when my user clicks on “Save as”. How can I invoke an event and make decisions –change the footer- based upon what my user has entered in Save As? The ThisWorkbook module has a BeforeSave event for ThisWorkbook where you should be able to do that in. -- Rick (MVP - Excel) "Jess" <Jess@discussions.microsoft.com> wrote in message ne...