'Dim dbFile
'dbFile = txtAddress.text
strDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & itxtAddress.text

Const adVarWChar = 202
Const adSingle = 4
Const adLockOptimistic = 3
Const adOpenStatic = 3
Const adOpenDynamic = 2
Const adCmdTable = &H0002
Dim actArray
Dim position
Dim myArray(15)


'-------------------------------------------------------------------------------
Sub Initform
   [_Template].color = clmenu
lstID.clear
lstImgURL.clear
lstImgProxyURL.clear
lstBox.clear

call readMDB("tbl_Election", "ID", "Candidate", "Image_URL", "Image_Proxy_URL")

End Sub

'-------------------------------------------------------------------------------
' READ DATABASE AND POPULATE IT TO LISTBOX
'-------------------------------------------------------------------------------
Sub readMDB(dbTable, dbcol1, dbcol2, dbcol3, dbcol4)

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open strDSN

objRecordSet.Open "SELECT * FROM " & dbTable & " ORDER BY ID ASC", _
        objConnection, adOpenDynamic, adLockOptimistic
With objRecordSet

    If Not .BOF And Not .EOF Then

   if lstBox.items.count > 0 then
        objRecordSet.MoveFirst
   end if

Do Until objRecordSet.EOF
    if dbcol1 <> false then lstID.Items.Add(objRecordSet.Fields.Item(dbcol1))
    if dbcol2 <> false then lstBox.Items.Add(objRecordSet.Fields.Item(dbcol2))
    if dbcol3 <> false then lstImgURL.Items.Add(objRecordSet.Fields.Item(dbcol3))
    if dbcol4 <> false then lstImgProxyURL.Items.Add(objRecordSet.Fields.Item(dbcol4))
    objRecordSet.MoveNext
Loop
   End if
End With
objRecordSet.Close
objConnection.Close

End Sub
'-------------------------------------------------------------------------------
' WRITE LISTBOX TO DATABASE
'-------------------------------------------------------------------------------
Sub writeMDB(dbTable, dbcol1, dbcol2, dbcol3, dbcol4)
Dim x
x = 1
Set rs = CreateObject("ADODB.Recordset")
    rs.Open dbTable, strDSN, adOpenDynamic, adLockOptimistic, adCmdTable
        DO WHILE NOT rs.EOF

            if dbcol1 <> false then rs(dbcol1).value = x
            if dbcol2 <> false then rs(dbcol2).value = lstBox.items(x-1)
            if dbcol3 <> false then rs(dbcol3).value = lstImgURL.items(x-1)
            if dbcol4 <> false then rs(dbcol4).value = lstImgProxyURL.items(x-1)

            x = x + 1
            rs.MoveNext
        Loop

   rs.close
   Set rs = Nothing
End sub
'-------------------------------------------------------------------------------
' SELECTING ITEM IN LISTBOX
'-------------------------------------------------------------------------------
Sub lstBoxClick(Sender)

        lstID.itemindex = lstBox.itemindex
        lstImgURL.itemindex = lstBox.itemindex
        lstImgProxyURL.itemindex = lstBox.itemindex

End sub

'-------------------------------------------------------------------------------
Sub emptyCheck(txtbox)
        if Len(Trim(txtbox)) = 0 then
           cmdAdd.enabled = false
        else
           cmdAdd.enabled = true
        end if
End sub

'-------------------------------------------------------------------------------
' IS LAST ITEM SELECTED?
'-------------------------------------------------------------------------------
Function lastItem
    If lstBox.itemindex = lstBox.items.count -1 then
       lastItem = true
    else
       lastItem = false
    end if
End Function
'-------------------------------------------------------------------------------
' IS FIRST ITEM SELECTED?
'-------------------------------------------------------------------------------
Function firstItem
    If lstBox.itemindex = 0 or lstBox.items.count = 0 then
       firstItem = true
    else
       firstItem = false
    end if
End Function
'-------------------------------------------------------------------------------
Sub itxtAddChange(Sender)
   call emptyCheck(itxtAdd.text)
End sub
'-------------------------------------------------------------------------------
Sub reorderButtons
        if lstBox.items.count > 1 then
                cmdUp.enabled = true
                cmdDown.enabled = true
        else
                cmdUp.enabled = false
                cmdDown.enabled = false
        end if
End sub
'-------------------------------------------------------------------------------
' ADD NEW RECORD TO THE DATABASE
'-------------------------------------------------------------------------------
Sub cmdAddClick(Sender)

Set rs = CreateObject("ADODB.Recordset")
position = lstBox.itemindex
        rs.Open "tbl_Election", strDSN, adOpenDynamic, adLockOptimistic, adCmdTable
        rs.AddNew
        rs("Candidate").value = itxtAdd.text
        rs("Image_URL").value = ithumbIMG.PicFilename
        rs("Image_Proxy_URL").value = ithumbIMG.ThumbFilename

        rs.update
        rs.Close
        Set rs = Nothing
            lstBox.items.insert lstBox.itemindex +1, itxtAdd.text
            lstImgURL.items.insert lstImgURL.itemindex +1, ithumbIMG.PicFilename
            lstImgProxyURL.items.insert lstImgProxyURL.itemindex +1, ithumbIMG.ThumbFilename
        for i = 1 to lstBox.items.count
            lstID.Items.Add(i)
        Next

        call save

        if lstBox.items.count = 1 then
                lstBox.itemindex = 0
                lstID.itemindex = 0
                lstImgURL.itemindex = 0
                lstImgProxyURL.itemindex = 0
                position = lstBox.itemindex
        else
                lstBox.itemindex = position +1
                lstID.itemindex = position +1
                lstImgURL.itemindex = position +1
                lstImgProxyURL.itemindex = position +1
                position = lstBox.itemindex
        end if

        itxtAdd.clear
        ithumbIMG.ThumbFilename = ""
        ithumbIMG.PicFilename = ""
        ithumbIMG.AssetUri = ""
        ithumbIMG.Picture
        cmdDel.Enabled = true


call reorderButtons
call fillAll
End sub
'-------------------------------------------------------------------------------
' DELETE FROM DATABASE
'-------------------------------------------------------------------------------
Sub dbDel(dbTable, selected)

        Set rs = CreateObject("ADODB.Recordset")
        sql = "SELECT * FROM " & dbTable & " WHERE ID =" & selected
        rs.Open sql, strDSN, adOpenDynamic, adLockOptimistic
        With rs

                If Not .BOF And Not .EOF Then
                .MoveLast
                .MoveFirst

                        If .Supports(adDelete) Then

                        .Delete

                        End If
                End If
        End With
        rs.Close
        Set rs = Nothing

End sub
'-------------------------------------------------------------------------------
' APPLY CHANGES TO DATABASE
'-------------------------------------------------------------------------------
Sub save

'timer1.enabled = true
'lblSave.visible = true

        call writeMDB("tbl_Election", "ID", "Candidate", "Image_URL", "Image_Proxy_URL")
        lstBox.clear
        lstID.clear
        lstImgURL.clear
        lstImgProxyURL.clear

        call readMDB("tbl_Election", "ID", "Candidate", "Image_URL", "Image_Proxy_URL")



End sub
'-------------------------------------------------------------------------------

'-------------------------------------------------------------------------------
' DELETE SELECTED RECORD FROM THE DATABASE
'-------------------------------------------------------------------------------
Sub cmdDelClick(Sender)

   call lastItem

        If lastItem = true then
                position = lstBox.itemindex -1
        else
                position = lstBox.itemindex
        end if

        sel = lstID.items(lstBox.itemindex)
        call dbDel("tbl_Election",sel)

        lstBox.items.delete = lstBox.itemindex
        lstID.items.delete = lstID.itemindex
        lstImgURL.items.delete = lstImgURL.itemindex
        lstImgProxyURL.items.delete = lstImgProxyURL.itemindex

        call save

        lstBox.itemindex = position
        lstID.itemindex = position
        lstImgURL.itemindex = position
        lstImgProxyURL.itemindex = position

'   call emptyList
   call reorderButtons
   cmdAdd.enabled = false
   itxtAdd.clear
   call fillAll
End Sub

'-------------------------------------------------------------------------------
' REORDER
'-------------------------------------------------------------------------------
Sub reorder(listboxArray, step, pos)
step = Cint(step)
pos = Cint(pos)
position = listboxArray(1).itemindex
for i = 0 to ubound(listboxArray)
    listboxArray(i).items.insert listboxArray(i).itemindex + step, listboxArray(i).items(listboxArray(i).itemindex)
    listboxArray(i).Items.delete = listboxArray(i).itemindex
next
call save
for i = 0 to ubound(listboxArray)
    listboxArray(i).itemindex = position + pos
next
position = listboxArray(1).itemindex
'call emptylist
end sub
'-------------------------------------------------------------------------------
' REORDER UP
'-------------------------------------------------------------------------------
Sub cmdUPClick(Sender)

        call firstItem
        if firstItem = true then
        else
        call activeArray
        call reorder(actArray,-1,-1)
        end if
   itxtAdd.clear
   call fillAll
End sub
'-------------------------------------------------------------------------------
' REORDER DOWN
'-------------------------------------------------------------------------------
Sub cmdDOWNClick(Sender)

        call lastItem
        if lastItem = true then
        else
        call activeArray
        call reorder(actArray,+2,+1)
        end if
   itxtAdd.clear
   call fillAll
End sub
'-------------------------------------------------------------------------------
' ACTIVE ARRAY OF LISTBOXES
'-------------------------------------------------------------------------------
sub activeArray
        actArray = Array(lstID, lstBox, lstImgURL, lstImgProxyURL)
end sub
'-------------------------------------------------------------------------------
' FILL IN COMPONENTS
'-------------------------------------------------------------------------------
Function componentsToArray(keyword, count)
TWUniListBox1.clear
Dim components, component_name

Dim j
  for i=0 to [_template].ComponentCount -1
        component_name = [_template].Components(i).name
        'msgbox [_template].Components(i).name
        If StrComp(Left(component_name, count),keyword,1) = 0 Then
       ' msgbox [_template].Components(i).name
         myArray(j) =  [_template].Components(i)
         j=j+1
         TWUniListBox1.items.add([_template].Components(i).name)
         end if


  next

End function
'-------------------------------------------------------------------------------
Sub fillComponents(dbTable, prop)

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open strDSN

objRecordSet.Open "SELECT * FROM " & dbTable & " ORDER BY ID ASC", _
        objConnection, adOpenDynamic, adLockOptimistic
With objRecordSet

    If Not .BOF And Not .EOF Then

   if lstBox.items.count > 0 then
        objRecordSet.MoveFirst
   end if
Dim i
i = 0
Do Until objRecordSet.EOF
    'msgbox MyArray(i).name

   if prop = "thumbfilename" then MyArray(i).thumbfilename = objRecordSet.Fields.Item("Image_Proxy_URL")
   if prop = "text" then MyArray(i).text = objRecordSet.Fields.Item("Source_1")
   if prop = "caption" then MyArray(i).caption = objRecordSet.Fields.Item("Candidate")

    i=i+1

    objRecordSet.MoveNext
Loop
   End if
End With
objRecordSet.Close
objConnection.Close

End sub





Sub fillAll
   call componentsToArray("thumb",5)
   call fillComponents("tbl_Election", "thumbfilename")
   call componentsToArray("txt",3)
   call fillComponents("tbl_Election", "text")
   call componentsToArray("lblname",7)
   call fillComponents("tbl_Election", "caption")
End sub