Taking multiselected items from listbox and adding to bookmark location in Word

[drmsktchr]

So I am back with another (what I hope is a simple solution). I understand how to pass text boxes to bookmarked locations in Word, but when it comes to combo boxes, list boxes, or option buttons, I am lost. So, my problem this time is the following:

I have an access user form that is asking the user to input data and make selections. Once entered I am trying to get everything to export directly to respective bookmarked locations in a Word Report. I have my text boxes working and I have the combo box now working. The issue I am experiencing is with the user making multiple selections from a list box and I am not really sure how to get that to export to the word document.



Here is what I have:

Dim strNames As String
Dim ctl As Control
Dim varItem as Variant

'ensure the user has made a selection from the testers name text box
If Me.testersNamesText.ItemsSelected.Count = 0 Then
  MsgBox "You must select at least 1 Capability Testers Name"
  Exit Sub
End if 

'Add the selected values to a string
Set ctl = Me.testersNamesText
For Each varItem in ctl.ItemsSelected
  strNames = strNames & ctl.ItemData(varItem) & ", "
  strNames = strNames & "'" & ctl.ItemData(varItem) & "'"
Next varItem

'trim the trailing comma off
strNames = Left(strNames, Len(strNames) -1 )


'Move to each bookmark and insert text from the form
.ActiveDocument.Bookmarks("testersNames").Select
.Selection.Text = (CStr(Forms!WebBasedIFV!testersNamesText))
  'reapply the bookmark name to the selection
  .ActiveDocument.Bookmarks.Add Name:="testersNames", Range:=Selection.Range

I am very new to trying to code with the Visual Basic side of things, I know this is probably the best method to do this but the issue that I am experiencing is receiving a Null error for the line with the .Selection.Text = (CStr(Forms!WebBasedIFV!testersNamesText)) and when I attempt to pass the strNames in place of the testersNamesText I receive that the user form can't find the field "strNames" referred to in my expression. I am pretty confused on how to fix this, any help?

[pbaldy]

For starters, I think you want to get rid of this line:

strNames = strNames & "'" & ctl.ItemData(varItem) & "'"

the line you're having trouble with would become

.Selection.Text = strNames

[drmsktchr]

Hi there again Paul. Again, thank you for your help and support with my questions.

So, I made the changes you recommended. However, when I submit the form it doesn't pull the actual values through, it only returns the comma with no text. What would I be missing to get the values? Does it make a difference if I am filling the list box with values from a query? This is also a First Name and Last Name type field.

[pbaldy]

That would pull from the bound column; if you need to pull from a different column:

ctl.Column(x, varItem)

where x is the zero based column number. The comments in code are calling that a textbox though; is it a listbox?

[drmsktchr]

Sorry bout that, yes it is a listbox, I happened to label it as the testersNamesText which should have been more along the lines of testersNamesLB. Ok, I will try your suggestion and see what I get.

[pbaldy]

If that doesn't help, can you post the db here?

[drmsktchr]

No need for that Paul, you are truly a Master and very helpful. That resolved the issue I was seeing and it is passing the way it should now. Another thread considered completed.

[pbaldy]

Happy to help!