Populating text box w/multiple values or For...Next Loops

[hobbes]

Hello! On my form I have the option to choose several different account numbers and run one report to display information for all of them. Since these reports can get quite long, I have an unbound text box, next to the title of the report, that I would like populated with the referenced account numbers (separating them with a comma). I am told I have to write a For...Next Loop, but I don't know how to do this. Is there an easier way to populate this text box? Or any guidance on writing For...Next Loops would be great too.
Thanks!

 

[RichO]

Hello fellow Wisconsinite, Brrrr....

I'm not sure if I understand your needs completely...

How are you selecting the multiple account numbers for the report? What type of control?

I'm thinking that the numbers can concatenated into a string as you select them, and stored in an unbound invisible text box control on the form. Then the report can reference that control for the string of account numbers.

This is pretty easy to do. Just need some more information.

 

[hobbes]

I have a form that has a list box and a text box. When you highlight an account from the list box and click a command button to select it, it adds that account to the text box. The accounts listed in the text box are stored in a temp table that is attached to a query that a report is generated off of. It is on this report that I need to list all of the accounts that were moved to the text box to generate the report. Does that make sense? I can post screenprints of my form and report if it would make it easier to understand. But what I'm trying to do is find a way to get all of the accounts selected to show up in the title of the report. RichO, I'm assuming the way you suggested is the easiest way to get this done, but I am very new at writing code. How would you suggest I go about carrying out your suggestion?
Thanks!
By the way, it's suppose to warm up around here, but the Burrrr comment fits the weather the last few days! I'm ready for spring....Wisconsin winters are rough!

 

[RichO]

I'm surprised you're not using a multi select list box like Pat had mentioned. That would be the best control for what you are doing.

I guess I'd like to see the code in the command button event. The code you would need would just be added to this event. Not sure what your background is on this so just in case:

In design view, select the command button, then select the event tab of the properties box. Click to the right of the On Click Event Procedure, then copy the piece of code and paste it here.

 

[hobbes]

Ok, I tried the multi-select list box and it will populate the title on the report with the accounts chosen for the report, but it lists them vertically. Is there a way to make it list horizontally?
Thanks for all your help with this!

 

[RichO]

It's easy to concatenate the account numbers into a string. I assume you use a command button to run the report after selecting the accounts?

Create an invisible text box on your form, name it txtAccountNumbers.
In the code for the command button, try:

Dim i        As Integer
Dim strList  As String
For i = 0 To MyListBox.ListCount
      If MyListBox.Selected(i) Then
          strList = strList & MyListBox.ItemData(i) & ", "
      End If
Next i
strList = Left(strList,Len(strList)-2)
Me.txtAccountNumbers = strList

This will concatenate the selected account numbers into a string and place it in your invisible control. Then from the report, you can reference this control to get the string.