List Box info to a table

[dlewis1002]

I have created a form that contains listboxs. I am unable to select the
information in the listbox and have it populate the appropriate field in my
table. If this takes some sort of marco, I am unable to write the correct
code to do this. Any information about this or even the code to do this
would be helpful.

Thanks

 

[Ofer]

Have you bound the list box to a field in the table, check the ControlSource
Property of the list box, and see if the right field name from the table
apear there.

 

[dlewis1002]

Ofer,

I tried changing the ControlSoure property of the list box as you suggested.
It will not allow me to select any of the info in the list box. The
information in the list box comes from a separate table. I want to be able
to select mulitple things in the list and have them populate the field in the
other table.

 

[xRoachx]

You need to loop through the selections, build a string, then add this to the
field. The following is a code snipit from a module so you need to replace
the list box name and field name with your own:

If Me.lstProviderType.ItemsSelected.Count > 0 Then
For Each strSelectedRecord In lstProviderType.ItemsSelected
strList = strList + lstProviderType.Column(1, strSelectedRecord)
& ","
Next strSelectedRecord

intLen = Len(strList)
[ProviderType] = Left(strList, intLen - 1)
strList = ""
End If

lstProviderType = List Box

 

[dlewis1002]

xRoachx,

I am not very good with code. Can you give me step by step instruction on
how to build a field and where to put the code you sent.

Thanks.

You need to loop through the selections, build a string, then add this to the
field. The following is a code snipit from a module so you need to replace
the list box name and field name with your own:

If Me.lstProviderType.ItemsSelected.Count > 0 Then
For Each strSelectedRecord In lstProviderType.ItemsSelected
strList = strList + lstProviderType.Column(1, strSelectedRecord)
& ","
Next strSelectedRecord

intLen = Len(strList)
[ProviderType] = Left(strList, intLen - 1)
strList = ""
End If

lstProviderType = List Box

I have created a form that contains listboxs. I am unable to select the
information in the listbox and have it populate the appropriate field in my
table. If this takes some sort of marco, I am unable to write the correct
code to do this. Any information about this or even the code to do this
would be helpful.

Thanks

 

[Douglas J. Steele]

You can't. It's not possible to bind a multiselect listbox to a field: that
would be a violation of database normalization rules, so Access doesn't
allow it.

 

[xRoachx]

Without knowing your db structure I'll do my best. My code is listed in the
BeforeUpdate event for the form but you can place where it is needed. Below,
it is listed in the Exit event of the list box.

Private Sub ListBoxName_Exit(Cancel As Integer)

Dim strList as String
Dim intLen as Integer
Dim varSelectedRecord as Variant

If Me.ListBoxName.ItemsSelected.Count > 0 Then
For Each varSelectedRecord In ListBoxName.ItemsSelected
strList = strList + ListBoxName.Column(1, varSelectedRecord) & ","
Next varSelectedRecord

intLen = Len(strList)
[FieldName] = Left(strList, intLen - 1)
strList = ""
End If

End Sub

ListBoxName = The name of your list box
FieldName = The name of the field where you want to store the information

This code builds a string, seperated by commas. For example, say the list
box contains the words one, two, three, four, five and only one, two are
selected. "one, two" (without quotes) would be stored in the field.

xRoachx,

I am not very good with code. Can you give me step by step instruction on
how to build a field and where to put the code you sent.

Thanks.

You need to loop through the selections, build a string, then add this to the
field. The following is a code snipit from a module so you need to replace
the list box name and field name with your own:

If Me.lstProviderType.ItemsSelected.Count > 0 Then
For Each strSelectedRecord In lstProviderType.ItemsSelected
strList = strList + lstProviderType.Column(1, strSelectedRecord)
& ","
Next strSelectedRecord

intLen = Len(strList)
[ProviderType] = Left(strList, intLen - 1)
strList = ""
End If

lstProviderType = List Box

I have created a form that contains listboxs. I am unable to select the
information in the listbox and have it populate the appropriate field in my
table. If this takes some sort of marco, I am unable to write the correct
code to do this. Any information about this or even the code to do this
would be helpful.

Thanks

 

[dlewis1002]

Is there any way to get multiple line of information from a listbox to a
field in a table? There must be or access would not allow you to select
mulitiple rows in a listbox.

 

[Douglas J. Steele]

Access lets you select multiple rows in a listbox because there are lots of
times when it makes sense. See, for example,
http://www.mvps.org/access/forms/frm0007.htm or
http://www.mvps.org/access/reports/rpt0005.htm at "The Access Web".

Inserting multiple values into a single field is NOT one of those times when
it makes sense.

If you're willing to live with the relational database theory violations,
yes, it is possible, but you must use VBA code to do it. You'd have to have
the multiselect box unbound (assume it's named MyMultiselectListbox). You'll
also have to have a simple text box (call it txtMistake) on the form that's
bound to the field where you want to (mistakenly) store the multiple values.
(it can be hidden). In the AfterUpdate event of the listbox, put code like:

Private Sub MyListBox_AfterUpdate()
Dim varItem As Variant
Dim strValue As String

For Each varItem In Me!MyMultiselectListbox.ItemsSelected
strValue = strValue & Me!MyMultiselectListbox.ItemData(varItem) & ",
"
Next varItem

'Trim the extra , space from the end of strValue
strValue=Left$(strValue, Len(strValue)-2))

Me!txtMistake = strValue

End Sub

 

[dlewis1002]

Thanks

That puts the information into a textbox. How do I get the data from the
textbox to the table?

 

[Douglas J. Steele]

The exact opposite.

Use the Split function to take your concatenated list of values and break it
into an array. Then, for each element in the array, set the Selected
property in the listbox to True. Something like the following untested
aircode:

Dim intLoop1 As Integer
Dim intLoop2 As Integer
Dim varValues As Variant

varValues = Split(Me!txtMistake, ",")
If IsNull(varValue) = False Then
For intLoop1 = LBound(varValues) To UBound(varValues)
For intLoop2 = 0 To (Me!MyMultiselectListbox.ListCount - 1)
If varValues(intLoop1) = Me!MyMultiselectListbox(intLoop2) Then
Me!MyMultiselectListbox.Selected(intLoop2) = True
Exit For
End If
Next intLoop2
Next intLoop1
End If

 

[Ross]

Hi there
I noticed you were assisting dlewis1002
I had a similar prob & your solution works great, except I am trying to
change setting from Next to After Update and get Compile Error: "Procedure
declaration does not match description of event or proceedure with the same
name"
Any ideas where I am going wrong please?
Ross

Without knowing your db structure I'll do my best. My code is listed in the
BeforeUpdate event for the form but you can place where it is needed. Below,
it is listed in the Exit event of the list box.

Private Sub ListBoxName_Exit(Cancel As Integer)

Dim strList as String
Dim intLen as Integer
Dim varSelectedRecord as Variant

If Me.ListBoxName.ItemsSelected.Count > 0 Then
For Each varSelectedRecord In ListBoxName.ItemsSelected
strList = strList + ListBoxName.Column(1, varSelectedRecord) & ","
Next varSelectedRecord

intLen = Len(strList)
[FieldName] = Left(strList, intLen - 1)
strList = ""
End If

End Sub

ListBoxName = The name of your list box
FieldName = The name of the field where you want to store the information

This code builds a string, seperated by commas. For example, say the list
box contains the words one, two, three, four, five and only one, two are
selected. "one, two" (without quotes) would be stored in the field.

xRoachx,

I am not very good with code. Can you give me step by step instruction on
how to build a field and where to put the code you sent.

Thanks.

You need to loop through the selections, build a string, then add this to the
field. The following is a code snipit from a module so you need to replace
the list box name and field name with your own:

If Me.lstProviderType.ItemsSelected.Count > 0 Then
For Each strSelectedRecord In lstProviderType.ItemsSelected
strList = strList + lstProviderType.Column(1, strSelectedRecord)
& ","
Next strSelectedRecord

intLen = Len(strList)
[ProviderType] = Left(strList, intLen - 1)
strList = ""
End If

lstProviderType = List Box


:

I have created a form that contains listboxs. I am unable to select the
information in the listbox and have it populate the appropriate field in my
table. If this takes some sort of marco, I am unable to write the correct
code to do this. Any information about this or even the code to do this
would be helpful.

Thanks