Novice help with list boxes

[Col]

Hi - I've never worked with list boxes before. Here's what I'd like to
do - have a list box (or some other control) that allows multiple
selection and stores all the values in one field (can be separated by
commas, line breaks - no preference as long as they are all in one
field). Can a list box do this? If so, how do I get the selections
stored in the field. I've set up the list box with the proper values
and multiple selections enabled, but am clueless about the next step.
And, if a list box won't do what I need it to do, what else could i
used?

Thanks! Colleen

[pietlinden]

Storing denormalized data deliberately? BAD idea. It'll come back to
bite you later on. So make friends with SPLIT, because you're gonna
need it. There's a reason this is a PITA. Because it's a bad idea.
use a subform with a combobox. Then your data is nicely normalized and
everything. If you want to eliminate "used" values, you can do that,
too.

[pietlinden]

this code will do it...

I wouldn't recommend it, though...

Private Sub cmdAddToTextBox _Click()
Dim strList As String
Dim lbx As Control
Dim varItem As Variant

Set ctl = Me.List0
For Each varItem In ctl.ItemsSelect ed
'---you should use a recordset or currentdb.Execu te strSQL to
insert your records.
strList = strList + ", " + ctl.ItemData(va rItem)
Next varItem

strList = Right$(strList, Len(strList) - 2)
Me.txtListedIte ms = strList

Set ctl = Nothing
End Sub

[paii, Ron]

<pi********@hot mail.comwrote in message
news:11******** **************@ m79g2000cwm.goo glegroups.com.. .

this code will do it...

I wouldn't recommend it, though...

Private Sub cmdAddToTextBox _Click()
Dim strList As String
Dim lbx As Control
Dim varItem As Variant

Set ctl = Me.List0
For Each varItem In ctl.ItemsSelect ed
'---you should use a recordset or currentdb.Execu te strSQL to
insert your records.
strList = strList + ", " + ctl.ItemData(va rItem)
Next varItem

strList = Right$(strList, Len(strList) - 2)
Me.txtListedIte ms = strList

Set ctl = Nothing
End Sub

I have been under the impression that "&" should be used when concatenating
stings not "+". Has this changed?

[pietlinden]

no, it's just my crap code. You're right, it should.

[Col]

Hi - can you suggest an alternative for me (since storing multiple
values in one field from a list box is not recommended). My database is
capturing report requests. I want users to identify which fields they
need included in the report - i want them to select from a list of
fields and select as many as they want. I'd like the values stored in
just one field for simplicity for me (I can just look at that field to
know what to include in the report). The database is currently setup so
each potential report field is listed as a yes/no field. It's been very
cumbersome for me to scroll through the list to see what is needed. I
thought have all the report fields listed in one field would be easier
for me to work with. So if they select the following fields - name,
enumber, term date, employment date, BU, location, supervisor - that
those fields can easily stored and retrieved in that record. Thoughts?

pi********@hotm ail.com wrote:

this code will do it...

I wouldn't recommend it, though...

Private Sub cmdAddToTextBox _Click()
Dim strList As String
Dim lbx As Control
Dim varItem As Variant

Set ctl = Me.List0
For Each varItem In ctl.ItemsSelect ed
'---you should use a recordset or currentdb.Execu te strSQL to
insert your records.
strList = strList + ", " + ctl.ItemData(va rItem)
Next varItem

strList = Right$(strList, Len(strList) - 2)
Me.txtListedIte ms = strList

Set ctl = Nothing
End Sub

[William Hindman]

....use a single field bit sum instead ...the idea is to use the binary
representation of an integer (110001101) to store and retrieve the values of
individual checkboxes ...the following code is just a skeleton but should
give you the idea ...you can use this approach to store the results of a
large number of check boxes in a single field and yet easily derive their
individual yes/no values anytime you need to ...hth

Private Sub chkTest_Click(I ndex AS Integer)
Dim iCount AS Integer
For iCount = 1 TO chkTest.Count
If chkTest(iCount) .Value = vbChecked Then
‘The box is selected, calculate the Base 2 value
lblValue.Captio n = lblValue.Captio n + (2^iCount)
End If
Next iCount
End Sub

‘Decode the value provided in the textbox
Private Sub cmdDecode_Click ()
Dim iCount AS Integer ‘Generic Counter
Dim iValue AS Integer ‘Hold the value to decode

iValue = Val(txtValue.Te xt)

‘Browse each checkboxes in reverse order
For iCount = chkTest.Count To 1 Step –1
‘If the difference between the current iValue and the 2^iCount is
positive
If iValue - (2 ^ iCount) >= 0 Then
‘The current box index was selected
chkTest (iCount).Value = vbChecked
‘Remove the current ‘base 2 power’ from iValue
iValue = iValue - (2 ^ iCount)
End If
Next iCount
End Sub

'note the above is from my code library and thus not complete
'the intent is to give you the idea so that you can build from it

William Hindman

"Col" <cm****@hotmail .comwrote in message
news:11******** *************@m 79g2000cwm.goog legroups.com...

Hi - can you suggest an alternative for me (since storing multiple
values in one field from a list box is not recommended). My database is
capturing report requests. I want users to identify which fields they
need included in the report - i want them to select from a list of
fields and select as many as they want. I'd like the values stored in
just one field for simplicity for me (I can just look at that field to
know what to include in the report). The database is currently setup so
each potential report field is listed as a yes/no field. It's been very
cumbersome for me to scroll through the list to see what is needed. I
thought have all the report fields listed in one field would be easier
for me to work with. So if they select the following fields - name,
enumber, term date, employment date, BU, location, supervisor - that
those fields can easily stored and retrieved in that record. Thoughts?

pi********@hotm ail.com wrote:

>this code will do it...

I wouldn't recommend it, though...

Private Sub cmdAddToTextBox _Click()
Dim strList As String
Dim lbx As Control
Dim varItem As Variant

Set ctl = Me.List0
For Each varItem In ctl.ItemsSelect ed
'---you should use a recordset or currentdb.Execu te strSQL to
insert your records.
strList = strList + ", " + ctl.ItemData(va rItem)
Next varItem

strList = Right$(strList, Len(strList) - 2)
Me.txtListedIte ms = strList

Set ctl = Nothing
End Sub