Deleting records in Access table based on multiple items selected in Excel user form List box ?

[S2Kool]

I am trying to use list box (with multiple items) in excel user form to delete the record in access table.

for example:

I have selected multiple items in excel user form list box (Listbox1), let say Wk1 and Wk2

in access, I have a table called "Rawdata" and there is a column called "Weeks".

how to delete all the records in "Rawdata" table on selecting the criteria in excel list box as WK1 and WK2 ?

appreciate your help.

 

[mrmmickle1]

S2Kool,


Try something like this:

[COLOR=#0000ff]Private Sub[/COLOR] CommandButton1_Click()
    
 [COLOR=#0000ff]   Dim[/COLOR] oConn      [COLOR=#0000ff]  As Object[/COLOR]
 [COLOR=#0000ff]   Dim [/COLOR]oRs          [COLOR=#0000ff]As Object[/COLOR]
   [COLOR=#0000ff] Dim[/COLOR] sConn       [COLOR=#0000ff] As String[/COLOR]
  [COLOR=#0000ff]  Dim[/COLOR] strSQL         [COLOR=#0000ff]As String[/COLOR]
[COLOR=#0000ff]    Dim[/COLOR] StrDBPath    [COLOR=#0000ff]As String[/COLOR]
  [COLOR=#0000ff]  Dim[/COLOR] myCriteria   [COLOR=#0000ff]As String[/COLOR]
[COLOR=#0000ff]    Dim[/COLOR] intLp       [COLOR=#0000ff] As Integer[/COLOR]


[COLOR=#008000]    'Get ListBox Values[/COLOR]
  [COLOR=#0000ff]  For [/COLOR]intLp = 0 [COLOR=#0000ff]To [/COLOR]ListBox1.ListCount - 1
     [COLOR=#0000ff]   If[/COLOR] Me.ListBox1.Selected(intLp) = [COLOR=#0000ff]True Then[/COLOR]
            myCriteria = myCriteria & "'" & Me.ListBox1.List(intLp) & "'" & ", "
[COLOR=#0000ff]        End If[/COLOR]
 [COLOR=#0000ff]   Next[/COLOR] intLp
[COLOR=#008000]    'Take off extra 2 characters----> ", "[/COLOR]
    myCriteria = Mid(myCriteria, 1, Len(myCriteria) - 2)
[COLOR=#008000]    'Define DB Path[/COLOR]
    StrDBPath = "C:\Users\Matt\Documents\Database1.accdb"
[COLOR=#008000]    'DB Connection String[/COLOR]
    sConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                 "Data Source=" & StrDBPath & ";" & _
                                 "Jet OLEDB:Engine Type=5;" & _
                                 "Persist Security Info=False;"
                                 
  [COLOR=#0000ff]  Set[/COLOR] oConn = CreateObject("ADODB.Connection")
  [COLOR=#0000ff]  Set [/COLOR]oRs = CreateObject("ADODB.Recordset")
    
[COLOR=#008000]    'Open Connection[/COLOR]
    oConn.Open sConn
    
[COLOR=#008000]    'Define SQL String[/COLOR]
    strSQL = "DELETE * FROM myTable WHERE myTable.Weeks IN (" & myCriteria & ");"
    
[COLOR=#008000]    'Execute Query[/COLOR]
    oConn.Execute strSQL
    
[COLOR=#008000]    'Close Connection[/COLOR]
    oConn.Close
     
[COLOR=#008000]    'Destroy Objects[/COLOR]
  [COLOR=#0000ff]  Set[/COLOR] oConn = [COLOR=#0000ff]Nothing[/COLOR]
   [COLOR=#0000ff] Set[/COLOR] oRs = [COLOR=#0000ff]Nothing[/COLOR]
[COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]

 

[S2Kool]

Thanks a lot ; codes work for me.

 

[mrmmickle1]

You're welcome. Glad you were able to get it working