use a list box to populate a text box

[randria]

Hello,
I have table called "manifest" that has clients'names (first name etc...) ,
and I have another table called "programme". I have a form called
"programmefrm" that has a text box called " clients names ". I usually input
manually up 7 first names in this textbox "clients names" by separating them
with commas. Is it possible to use a list box or combo box ( row source:
manifest ) and select more than one name to populate the textbox "clients
names" ? It should look like this. Lita, Raivo,Bera,etc..
I just need this text box to show the names as they appear in a report.

Many thanks.
Alain

 

[Beetle]

It would probably be easiest to use a list box. Set the Multiselect property
of the list box to extended. Then you could build a string of first names to
go into the text box. The text box should be unbound. You could, for
example, code this in the On Click event of a command button;

Private Sub cmdMyButton_Click()

Dim strNames As String
Dim varItem As Variant

For each varItem in Me.lstMyListBox.ItemsSelected
strNames = strNames & Me.lstMyListBox.Column(X, varItem) & ","
Next varItem

txtMyTextBox = strNames

End Sub

Where X represents the numeric value of whichever Column in your list box
that holds the first names. It's a zero based system so the first Column is
Column (0), second is Coumn(1), etc.

You could then select as many, or as few, names as you want in the list box
then click the command button.

 

[randria]

Hi Beetle,

Thanks for your reply, before trying the codes, I did not mention that I
have the text box in a datasheet and bound to a field in the
table"programme". Can I use the listbox instead, bound it to the same field
from tbl "programme",the rowsource of my listbox is a query based on
table"manifest" ? At the moment I have the listbox populated by :
Private Sub Client_s_Name_MouseDown(Button As Integer, Shift As Integer, X
As Single, Y As Single)
Me.Client_s_Name.Requery
End Sub
If this is ok , how would the codes look ? It would be nice if I dont use a
command btn.
Many thanks.
Alain

 

[Beetle]

If the names are stored in your manifest table, then storing a concantenated
string of first names in your programme table is not only unnecessary, it is
poor practice. If you need the string of names to appear on a report, all you
need to do is build the string, put it in an unbound textbox temporarily,
then run
your report using a reference to the unbound text box.

As far as building the string using the click event of the listbox, that
could be
done, but you would still need a way to "clear" the string when you want to
begin building a new one, so you would still probably need a command button
for that purpose.

In regards to the code that you currently have, I'm not clear on what the
purpose is of doing a requery in the MouseDown event of the listbox.

 

[randria]

Thanks for this. The reason why I have "names" in the programme table is
because not always all the clients in the manifest do the same programme each
day so I have to assign that in a subform (programme), not sure if that makes
sense.
The reason why i need to requery is because each client in the manifest
belongs to a group and there are several groups, therefore by requerying the
listbox is populated of clients that belong to the same group only.
I tried the code you suggested, by clicking the textbox "names" to open a
pop up form that has the listbox, an unbound textbox,I used the code you
provided but when I make a selection, nothing goes to the textbox. I have not
used listbox before so I have no idea what is wrong. what I m trying to
achieve is to get the selected names from the list box appearing in the text
box separated by commas. I appreciate your help.
Many thanks.

 

[Beetle]

I understand the reason for doing a requery of the list box, I just don't
understand why you are doing it in the list box MouseDown event.
You should only need to requery the list box when threre is a change
in the data the list box is based on, which usually happens somewhere
outside of the list box itself. For example, let's say you have a combo box
that lists all of the Groups, and the list box displays all of the Clients
that
are in each Group. You would requery the list box in the After Update event
of the combo box. The way you are doing it now, the list box gets requeried
every time you click in it, wether it needs to be or not. Somewhat of a
waste of time and resources.

As far as the code I posted earlier, that was based on the premise of
using a multi-select list box and clicking a command button after you had
selected all the names you wanted in the list box. If you want to send
the names to the text box one at a time as soon as you click on them
in the list box, you'll need to do it a bit differently. Supposing that your
list box is named lstClients and your unbound text box is named txtNames,
you would use some code like the following in the Click (or perhaps the
Double Click) event of the list box;

Private Sub lstClients_Click()

'see if there is anything already in txtNames
If Nz(Me.txtNames, vbNullString) = vbNullString Then
'there is nothing there so add the first name only
Me.txtNames = Me.lstClients.Column(x)
Else
'something is there so add a comma, a space and the next first name
Me.txtNames = ", " & Me.lstClients.Column(x)
End If

End Sub

Again, you will probably want a command button to clear the text box
in case there is a mistake, or when you need to start a new entry;

Private Sub cmdClear_Click()

Me.txtNames = ""

End Sub

You may want to clear the text box in the Close event of your form also.

 

[Beetle]

Oops. Made a mistake in the Else clause of the statement. It should be;

If Nz(Me.txtNames, vbNullString) = vbNullString Then
'there is nothing there so add the first name only
Me.txtNames = Me.lstClients.Column(x)
Else
'something is there so add a comma, a space and the next first name
Me.txtNames = Me.txtNames & ", " & Me.lstClients.Column(x)
End If