Option Explicit
 
Private Sub cmdAdd_Click()
    frmNewList.Show
    frmList.Hide
End Sub
 
Private Sub cmdFilter_Click()
    'this button changes between filter and unfilter commands
    'if the caption says filter, show the filter form
    'if it says unfilter, update the listview to contain all entries in the 
    ' database
 
    If blnFilterOn Then
        blnFilterOn = False
        cmdFilter.Caption = "Filter"
        updateList ("SELECT * from Bestandsliste")
    Else
        frmFilter.Show
        frmList.Hide
 
    End If
End Sub
 
Private Sub cmdRemove_Click()
    Dim rs As ADOCE.Recordset
    Dim strSql As String
    Dim itemTemp As Item
 
 
    If lvMusic.SelectedItem Is Nothing Then
        'do nothing, nothing selected
    Else
 
        Set itemTemp = lvMusic.SelectedItem
        Set cn = CreateObject("ADOCE.Connection.3.0")
        'open database
        cn.Open theDB
        Set rs = CreateObject("ADOCE.Recordset.3.0")
        strSql = "SELECT * from Bestandsliste WHERE Artist = '" & itemTemp.Text _
          & "' AND Title = '" & itemTemp.SubItems(1) & "' "
 
        'filter for the record to remove
        'addOpenKeyset opens recordset for bidrectional movement, allowing 
        ' additions and deletions
        'adLockOptimistic locks records only during updates
 
        rs.Open strSql, cn, adOpenKeyset, adLockOptimistic
 
        If rs.RecordCount = 0 Then
            MsgBox ("Löschen konnte nicht ausgeführt werden.")
        Else
            'update the filter form combo boxes by removing the Artist
            frmFilter.cboArtist.RemoveItem (frmFilter.locateArtist( _
              itemTemp.Text))
 
            'and removing the Title
            frmFilter.cboTitle.RemoveItem (frmFilter.locateTitle( _
              itemTemp.SubItems(1)))
 
            'und lösche den Status
            'frmFilter.cboStatus.RemoveItem (frmFilter.locateStatus( 
            ' itemTemp.SubItems(1)))
 
            'update the listview on the main screen
            lvMusic.ListItems.Remove (lvMusic.SelectedItem.Index)
 
           'remove from the database
            rs.MoveFirst
            rs.Delete
            rs.Close
            cn.Close
            Set cn = Nothing
            Set rs = Nothing
        End If
    End If
End Sub
 
Sub updateList(strSql As String)
    Dim rs As ADOCE.Recordset
    Dim itemTemp As Item
    'first clear all entries from the listview
    lvMusic.ListItems.Clear
 
    'connect to the database and fill in next data
    'the filter criteria is passed as sqlString
    Set cn = CreateObject("ADOCE.Connection.3.0")
    cn.Open theDB
    Set rs = cn.Execute(strSql)
    Do While Not rs.EOF
        Set itemTemp = lvMusic.ListItems.Add(, , rs(0).Value)
        itemTemp.SubItems(1) = rs(1).Value
        itemTemp.SubItems(2) = rs(2).Value
        itemTemp.SubItems(3) = rs(3).Value
        rs.MoveNext
    Loop
 
    cn.Close
    Set cn = Nothing
 
End Sub
 
Private Sub form_load()
    Dim rs As ADOCE.Recordset
    Dim clmhdr As ColumnHeader
 
 
 
   'this will delete the database each time the form reloads
   ' fs.Kill (theDB)
 
   'check if the database exists
    If fs.Dir(theDB) = "" Then
 
        'create the database in the My Documents folder
        Set rs = CreateObject("ADOCE.Recordset.3.0")
        rs.Open "CREATE DATABASE '" & theDB & "'"
        Set rs = Nothing
        'Now connect to the db to create the table
        Set cn = CreateObject("ADOCE.Connection.3.0")
        cn.Open theDB
        cn.Execute "CREATE TABLE Bestandsliste (Title varchar(128),Artist" & _
          "varchar(128), Time varchar(128),Status varchar(128))"
        'now the table exists in the database
        cn.Close
        Set cn = Nothing
    End If
 
    'set up the listview headers
    Set clmhdr = lvMusic.ColumnHeaders.Add(, "Title", "Bauteil")
    Set clmhdr = lvMusic.ColumnHeaders.Add(, "Artist", "Kategorie")
    Set clmhdr = lvMusic.ColumnHeaders.Add(, "Time", "Beschreibung")
    Set clmhdr = lvMusic.ColumnHeaders.Add(, "Status", "Status")
 
    'set
    blnFilterOn = False
 
    'put the database contents in the listview
    Call updateList("SELECT * from bestandsliste")
 
End Sub
 
Private Sub Form_OKClick()
    App.End
End Sub