Consulting

Results 1 to 13 of 13

Thread: sending listbox contents to access

  1. #1

    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.

    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)
    Last edited by CuriousGeorg; 10-24-2013 at 06:59 AM.

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    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

  3. #3
    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

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

  5. #5
    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
    Last edited by CuriousGeorg; 10-24-2013 at 09:38 AM.

  6. #6
    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
    Last edited by CuriousGeorg; 10-25-2013 at 12:31 AM.

  7. #7
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    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
    Last edited by snb; 10-25-2013 at 04:26 AM.

  8. #8
    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.
    Attached Files Attached Files

  9. #9
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    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.

  10. #10
    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.

  11. #11
    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

  12. #12
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    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

  13. #13
    perfect!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •