sending listbox contents to access

[CuriousGeorg]

Good afternoon,

I have a user form in Excel that sends the results to an access database. Most of it works except I have a listbox in which multiple entries can be selected. :dunno

When a user inputs more than 1 option it does not pull the results through to access..

Having researched this I dont quite understand how listbox works :/

How should I code this?

Just for clarity there is one Column with approx 9 rows (maybe more)

[Kenneth Hobs]

Depends on how you are storing it in Access I guess.

Use the Selected property to see if the item was selected. Add a listbox, commandbutton, and set the multiselect option in the listbox to test this code.


Private Sub UserForm_Initialize()
ListBox1.List = Array(1, 2, 3, 4, 5)
End Sub

Private Sub CommandButton1_Click()
Dim i As Integer
For i = 0 To ListBox1.ListCount - 1
Debug.Print i, ListBox1.List(i), ListBox1.Selected(i)
Next i
Unload Me
End Sub

[CuriousGeorg]

Ideally id like each selection to be in a different column. But I was thinking that wouldn't be possible. It's part of a longer code where other items are added as well. I'll give that a go

[youngt465]

I have the same problem like yours for my excel but I have the tutorials on youtube.

[CuriousGeorg]

You have any direct links?

I've tried looking there and they aren't quite what I'm after.

I have 2 combo boxes and a list box (due to need for multi-select) so I'd need the data from the combos AND the listbox to transfer to the access database. I'll play with Kenneth code when I get on and see how I can manipulate it. Unless I hit a wall

[CuriousGeorg]

basically this is the code i was using before Kenneth's suggestion



Private Sub cmdOK_Click()


If cbohandler.Value = "" Or txtClaim.Value = "" Or txtrecs.Value = "" Or cboCHO.Value = "" Or lstissue.Value = "" Or txtcommentry.Value = "" Then

MsgBox "Please enter all fields before continuing!", vbCritical

Exit Sub
Else

If txtcommentry.Value = "" Then txtcommentry.Value = "N/A"
If txtrecs.Value = "" Then txtreason.Value = "N/A"
' exports data from the active form to a table in an Access database
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset


' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & "Data Source=cho.mdb;"


' open a recordset
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM [chotable]", cn, adOpenDynamic, adLockOptimistic


With rs


.AddNew
.Fields("Handler Name") = cbohandler.Value
.Fields("Claim Number") = txtClaim.Value
.Fields("CHO") = cboCHO.Value
.Fields("Issue(s)") = lstissue.Value
.Fields("Commentry") = txtcommentry.Value
.Fields("Recommendations") = txtrecs.Value

.Update ' stores the new record
End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End If
Unload Me
MsgBox "Entry Added Successfully", vbOKOnly
End Sub

[snb]

Private Sub M_snb()
If cbohandler.Value = "" Or txtClaim.Value = "" Or txtrecs.Value = "" Or cboCHO.Value = "" Or lstissue.Value = "" Or txtcommentry.Value = "" Then
MsgBox "Please enter all fields before continuing!", vbCritical
Exit Sub
Else
For j = 0 To cboCHO.ListCount
If cboCHO.Selected(j) Then c00 = c00 & ";" & cboCHO.List(j)
Next

With New Recordset
.Open "SELECT * FROM [chotable]", "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=cho.mdb;"
.AddNew
.Fields("Handler Name") = cbohandler.Value
.Fields("Claim Number") = txtClaim.Value
.Fields("CHO") = Mid(c00, 2)
.Fields("Issue(s)") = lstissue.Value
.Fields("Commentry") = txtcommentry.Value
.Fields("Recommendations") = txtrecs.Value

.Update
.Close
End With
End If
Unload Me
End Sub

[CuriousGeorg]

Sorry confused which conditions you mean. The email sent gave me a different reply to whats showing on the forum.

lstissue is the listbox. I'm assuming that's what you meant to have with the condition with. So i made an amendment but get
"Could not get the selected item property, Invalid argument"

FYI this is what I was working with. I havent updated it to yours SNB (i dont think). but might be able to see what ive got.

[Kenneth Hobs]

When debugging code, use Debug.Print or press F8 to execute one line at a time. I suspect that you will find that you are iterating one too many list items. Subtract one as I demonstrated.

Change your For loop too:

For J = 0 To lstissue.ListCount - 1

Using snb's method, you can Split() the c00 to create a string array. You can then use array methods to get the items.

[CuriousGeorg]

Ok i'm completely lost now..

Im pretty sure you guys are right and give me the answer I need.. maybe im jsut tired but i dont have a clue.

Thanks for the help. Ill class it as solved because its not fair to say unsolved because I dont understand the answers.

[CuriousGeorg]

After all that i HAVE got snb's working.. Thanks..

Now I need to figure (assuming with Kenneth's arrays). How to make each selection go into a different column.

Aka if A selected in list box it goes to the column A in access
and A and C = A and C or something..

YOu two have been a great help though. Thank you

[Kenneth Hobs]

Modify the code in the Select Case structure to fit your needs.



Option Explicit

Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub cmdClear_Click()
Dim ctl As Control
' Clear the form
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl
End Sub

Private Sub cmdOK_Click()
Dim c00 As String, J As Integer, I As Integer, a() As String
If cbohandler.Value = "" Or txtClaim.Value = "" Or txtrecs.Value = "" Or _
cboCHO.Value = "" Or lstissue.Value = "" Or txtcommentry.Value = "" Then
MsgBox "Please enter all fields before continuing!", vbCritical
Exit Sub
End If

For J = 0 To lstissue.ListCount - 1
If lstissue.Selected(J) Then c00 = c00 & ";" & lstissue.List(J)
a() = Split(c00, ";")
Next

With New Recordset
.Open "SELECT * FROM [chotable]", "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=S:\HO Shared\P1 HX & B'ham joint folder\2014\cho.mdb;"
.AddNew
.Fields("Handler Name") = cbohandler.Value
.Fields("Claim Number") = txtClaim.Value
.Fields("CHO") = cboCHO.Value

If UBound(a) <> -1 Then
'.Fields("Issue(s)") = Mid(c00, 2)
For I = 0 To UBound(a)
Select Case a(I)
Case "A"
.Fields("IssueA") = a(I)
Case "B"
.Fields("IssueB") = a(I)
Case Else
End Select
Next I
End If

.Fields("Commentry") = txtcommentry.Value
.Fields("Recommendations") = txtrecs.Value
.Update
.Close
End With

Unload Me
End Sub

[CuriousGeorg]

perfect!