If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Code giving undesired results
Another helpful person here gave me a sample, untested code to try to
concatenate List Box Multi-Selections into a sentence. I've tweaked it a bit to attach it to the On Enter event of another control on the same form but am not quite getting the result I need. This result seems to count the # of selections made vs. returning the text of the selections. Here is the code: Dim varRow As Variant For Each varRow In Forms![frmDamage]![lbDamagedParts].ItemsSelected Me.Text36 = "There is damage to " & varRow & ", " Next varRow Me.Text36 = Left(Me.Text36, Len(Me.Text36) - 1) & "." It produces this result: "There is damage to 4,." (In this case I have selected 4 of the items) I need it to instead include the text of the items selected. Thanks so much for your help! Pamela |
#2
|
|||
|
|||
Code giving undesired results
Pamela
A quick read looks like you're telling Access to take the value of the row and concatenate it. This will work ONLY if the bound column in the listbox is the text you wish to concatenate... and I have a strong hunch the bound column in your listbox is a rowID field... More info, please... Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Pamela" wrote in message ... Another helpful person here gave me a sample, untested code to try to concatenate List Box Multi-Selections into a sentence. I've tweaked it a bit to attach it to the On Enter event of another control on the same form but am not quite getting the result I need. This result seems to count the # of selections made vs. returning the text of the selections. Here is the code: Dim varRow As Variant For Each varRow In Forms![frmDamage]![lbDamagedParts].ItemsSelected Me.Text36 = "There is damage to " & varRow & ", " Next varRow Me.Text36 = Left(Me.Text36, Len(Me.Text36) - 1) & "." It produces this result: "There is damage to 4,." (In this case I have selected 4 of the items) I need it to instead include the text of the items selected. Thanks so much for your help! Pamela |
#3
|
|||
|
|||
Code giving undesired results
You need something like:
Dim varRow As Variant, strText as string strText = vbNullString For Each varRow In Forms![frmDamage]![lbDamagedParts].ItemsSelected strText = strText & Forms![frmDamage]![lbDamagedParts].Column(0,varRow) Next varRow strText then contains conatenated text from each row. You probably also should first check Forms![frmDamage]![lbDamagedParts].ItemsSelected is greater than zero to ensure at least one row is selected. -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lifetime". "Pamela" wrote: Another helpful person here gave me a sample, untested code to try to concatenate List Box Multi-Selections into a sentence. I've tweaked it a bit to attach it to the On Enter event of another control on the same form but am not quite getting the result I need. This result seems to count the # of selections made vs. returning the text of the selections. Here is the code: Dim varRow As Variant For Each varRow In Forms![frmDamage]![lbDamagedParts].ItemsSelected Me.Text36 = "There is damage to " & varRow & ", " Next varRow Me.Text36 = Left(Me.Text36, Len(Me.Text36) - 1) & "." It produces this result: "There is damage to 4,." (In this case I have selected 4 of the items) I need it to instead include the text of the items selected. Thanks so much for your help! Pamela |
#4
|
|||
|
|||
Code giving undesired results
On Wed, 30 Dec 2009 10:15:01 -0800, Dorian
wrote: You need something like: Dim varRow As Variant, strText as string strText = vbNullString For Each varRow In Forms![frmDamage]![lbDamagedParts].ItemsSelected strText = strText & Forms![frmDamage]![lbDamagedParts].Column(0,varRow) Next varRow Thanks Dorian... and apologies, Pamela! I posted in haste and Dorian corrected my error. -- John W. Vinson [MVP] |
#5
|
|||
|
|||
Code giving undesired results
Thanks, Guys but it still isn't working. I am now getting a #Name? error in
my field where it is supposed to be the concatenated text. I tried both ways running it with the new text from Dorian, and then taking into consideration Jeff's note about the bound column - it had been an ID field, but since the underlying table is solely for this lookup and each entry is, by design, unique, I tried an alternate table where the text is the PK and bound column but the result is the same. I was getting an error that the system couldn't find the "field" frmdamage (which is - for sure- the correct form name) so I changed it to the Me option (although I don't really understand what implications this may have) and now I'm not getting a code error but just a result error. At this point, I am so frustrated, I am open to other suggestions. I have more than 50 possible options for the user to select from - as many as needed and I am really against an open text field for the user to list them manually as I cannot then control the quality of spelling, proper capitalization, punctuation, etc. Since my idea of using a list box and concatenating the selections is proving to be such a headache for me at the novice level I am, what other options are there? Thanks again, especially you, John, you've really helped me on this project so much the past few days - I'm glad you didn't take off for the holidays! *smile* Pamela "John W. Vinson" wrote: On Wed, 30 Dec 2009 10:15:01 -0800, Dorian wrote: You need something like: Dim varRow As Variant, strText as string strText = vbNullString For Each varRow In Forms![frmDamage]![lbDamagedParts].ItemsSelected strText = strText & Forms![frmDamage]![lbDamagedParts].Column(0,varRow) Next varRow Thanks Dorian... and apologies, Pamela! I posted in haste and Dorian corrected my error. -- John W. Vinson [MVP] . |
#6
|
|||
|
|||
Code giving undesired results
Pamela
It sounds like you are saying Access is telling you it can't find a FIELD named "frmdamage", and you are saying you KNOW the FORM name is correctly spelled. Are you (two) talking about the same thing? Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Pamela" wrote in message ... Thanks, Guys but it still isn't working. I am now getting a #Name? error in my field where it is supposed to be the concatenated text. I tried both ways running it with the new text from Dorian, and then taking into consideration Jeff's note about the bound column - it had been an ID field, but since the underlying table is solely for this lookup and each entry is, by design, unique, I tried an alternate table where the text is the PK and bound column but the result is the same. I was getting an error that the system couldn't find the "field" frmdamage (which is - for sure- the correct form name) so I changed it to the Me option (although I don't really understand what implications this may have) and now I'm not getting a code error but just a result error. At this point, I am so frustrated, I am open to other suggestions. I have more than 50 possible options for the user to select from - as many as needed and I am really against an open text field for the user to list them manually as I cannot then control the quality of spelling, proper capitalization, punctuation, etc. Since my idea of using a list box and concatenating the selections is proving to be such a headache for me at the novice level I am, what other options are there? Thanks again, especially you, John, you've really helped me on this project so much the past few days - I'm glad you didn't take off for the holidays! *smile* Pamela "John W. Vinson" wrote: On Wed, 30 Dec 2009 10:15:01 -0800, Dorian wrote: You need something like: Dim varRow As Variant, strText as string strText = vbNullString For Each varRow In Forms![frmDamage]![lbDamagedParts].ItemsSelected strText = strText & Forms![frmDamage]![lbDamagedParts].Column(0,varRow) Next varRow Thanks Dorian... and apologies, Pamela! I posted in haste and Dorian corrected my error. -- John W. Vinson [MVP] . |
Thread Tools | |
Display Modes | |
|
|