sending listbox contents to access

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)

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.

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

---

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

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

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

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

Code:

---

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

---

Code:

---

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

---

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.

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:

Code:

---

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.

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.

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

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

Code:

---

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

---

perfect!