Code giving undesired results

  • Thread starter Thread starter Pamela
  • Start date Start date
P

Pamela

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
 
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.
 
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".
 
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
Click to expand...

Thanks Dorian... and apologies, Pamela! I posted in haste and Dorian corrected
my error.
 
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 said:
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
Click to expand...

Thanks Dorian... and apologies, Pamela! I posted in haste and Dorian corrected
my error.
Click to expand...
 
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 said:
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 said:
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
Click to expand...

Thanks Dorian... and apologies, Pamela! I posted in haste and Dorian
corrected
my error.
Click to expand...
Click to expand...
 
Back
Top