Option Explicit
Dim VolRecCnt As Integer
Public Gilroy As adodb.Connection
Public Volunteers As adodb.Recordset
Public rstTeachers As adodb.Recordset
Public AdvSearchRS As adodb.Recordset
Dim NameQuery As String
Dim Search As String
Public keydrop As Integer
Dim CheckCaption As String
Dim i As Integer
Dim OpIndex As Integer
Dim varReturn As String * 50
Dim varReturn2 As String * 50
Dim varConvert As Double
Dim varConvert2 As Double
Dim CheckValue As Integer
Dim varKey As Integer
Private Sub Command1_Click()
End
End Sub
Private Sub cmdDelete_Click()
If Admin = False Or User = True Then
MsgBox "Sorry, unfortunately only someone with Administrator Access can perform that operation. Please contact your administrator for further queries."
Else
Text1.Visible = True
If MsgBox("Are you sure you want to delete this volunteer's record?", vbYesNo, "Volunteers") = vbYes Then
Volunteers.Delete
If lstNames.ListIndex = 0 Then
lstNames.ListIndex = 1
End If
ListNamesUpdate
Else
Exit Sub
End If
End If
End Sub
Private Sub cmdExit_Click()
End
End Sub
Private Sub cmdNew_Click()
If Admin = False Or User = True Then
MsgBox "Sorry, unfortunately only someone with Administrator Access can perform that operation. Please contact your administrator for further queries."
Else
frmNew.Show
End If
End Sub
Private Sub Form_Load()
Dim ListNamesID As Integer
ListNamesID = 0
On Error GoTo Form_Load_Err
i = 1
'Below code connects prgram to database
Set Gilroy = New adodb.Connection
Gilroy.Open "Provider=MSDASQL.1;Persist Security Info=False;Data Source=MS Access Database;Initial Catalog=gilroy4.mdb"
'below code sets recordet (where to get info from)
Set Volunteers = New adodb.Recordset
Volunteers.Open "SELECT * FROM Volunteers", Gilroy, adOpenDynamic, adLockOptimistic
Set AdvSearchRS = New adodb.Recordset
AdvSearchRS.Open "SELECT * FROM AdvSearch", frmMain.Gilroy, adOpenDynamic, adLockOptimistic
If Volunteers.EOF Then
MsgBox "Please Add Someone To The Database"
Else
Volunteers.MoveFirst
End If
Form1Display 'displays informtaion of first record
lstNames.Clear
'below code adds all volunteers to list box on main form
If Not Volunteers.BOF Then
Volunteers.MoveFirst
Do Until Volunteers.EOF
lstNames.AddItem Volunteers("FullName")
lstID.AddItem Volunteers("ID")
Volunteers.MoveNext
Loop
Else
MsgBox "Please add someone to the database."
End If
lstNames.ListIndex = 0
Volunteers.MoveFirst
User = True
'the below code sets another recordset that is used to store the encrypted passwords
Set rstTeachers = New adodb.Recordset
rstTeachers.Open "SELECT * FROM Teachers", Gilroy, adOpenDynamic, adLockOptimistic
rstTeachers.MoveFirst
'The following section of code adds the names to the list box
Call Sort_it 'sorts list
Form_Load_Err:
Select Case Err.Number
Case 94
Exit Sub
End Select
End Sub
Private Sub cmdPrev_Click()
If lstNames.ListIndex > 0 Then
lstNames.ListIndex = lstNames.ListIndex - 1
End If
End Sub
Private Sub cmdNext_Click()
If lstNames.ListIndex < lstNames.ListCount - 1 Then 'Because listindex is 0 based, if there is 165 entries, then the last will be 164.
lstNames.ListIndex = lstNames.ListIndex + 1
End If
End Sub
Public Sub Form1Display()
On Error Resume Next
Dim intValue As Integer
Dim intValue2 As Integer
Dim intValue3 As Integer '
Dim i As Integer
'Below lines put the Database field information
'into text boxes.
txtFirstName.Text = ""
txtSurName.Text = ""
On Error Resume Next
txtFirstName.Text = Volunteers.Fields("Name") & ""
txtSurName.Text = Volunteers.Fields("LastName") & ""
txtAddress.Text = Volunteers.Fields("Address") & ""
txtPhone.Text = Volunteers.Fields("Phone") & ""
If User = True Then
User = False 'This code temporarily turns User mode off if it is on so that when it displays the checkboxes you don't get a message box each time saying that you are a user and you can't change them.
WasUser = True
Else
WasUser = False
End If
'The below code decodes the binary from the Access DB
'and displays it in the correct checkboxes.
intValue = Volunteers.Fields("PandF")
End Sub
Private Sub Frame2_DragDrop(Source As Control, X As Single, Y As Single)
lstNames.Refresh
End Sub
Private Sub txtSurName_lostfocus()
'The code in this sub saves information
'if it is edited in the textboxes
'by an admin.
Dim ListIndexTemp As Integer
If Admin = True Then
ListIndexTemp = lstNames.ListIndex
'The below code saves the changed information in to the database.
Volunteers.Fields("Lastname") = txtSurName.Text & ""
Volunteers.Fields("Name") = txtFirstName.Text & ""
Volunteers.Fields("Address") = txtAddress.Text & ""
Volunteers.Fields("Phone") = txtPhone.Text & ""
Volunteers.Update
Volunteers.Fields("FullName") = Volunteers.Fields("Name") & " " & Volunteers.Fields("Lastname") 'Sets the FullName field in the database
Volunteers.Update
Call ListNamesUpdate 'Updates the listbox on the main form
lstNames.ListIndex = ListIndexTemp
lstNames.Text = Volunteers.Fields("Name") & " " & Volunteers.Fields("Lastname") 'Changes the text that appears on the main listbox, this is so that if the admin changes the first or last name, this change will appear in the main listbox.
lstNames.ListIndex = ListIndexTemp
Else
Exit Sub
End If
End Sub
Private Sub Sort_it()
' sort them alphabetically
' uses a bubble sort
Dim a As Integer, b As Integer
For a = 0 To lstNames.ListCount - 2
For b = a + 1 To lstNames.ListCount - 1
' compare and swap if necessary
If lstNames.List(b) < lstNames.List(a) Then
Call SwapPeople(a, b)
End If
Next b
Next a
Call lstNames_Click ' show it all up now again
Text1.Visible = False
End Sub
Private Sub SwapList(lst As ListBox, a As Integer, b As Integer)
'This code swaps the listbox information if needed.
Dim temp As String
temp = lst.List(a)
lst.List(a) = lst.List(b)
lst.List(b) = temp
End Sub
Private Sub SwapPeople(a As Integer, b As Integer)
' used by the sort to swap two values
Call SwapList(lstNames, a, b)
Call SwapList(lstID, a, b)
End Sub
Private Sub lstNames_Click()
'Displays the name when you click on it.
lstID.ListIndex = lstNames.ListIndex
Volunteers.MoveFirst
Volunteers.Find "ID like '" & lstID.Text & "'"
Form1Display
End Sub
Public Sub ListNamesUpdate()
lstNames.Clear
Text1.Visible = True
If Not Volunteers.BOF Then
Volunteers.MoveFirst 'Moves to first record
Do Until Volunteers.EOF
lstNames.AddItem Volunteers.Fields("FullName") 'Changes the text that appears on the main listbox, this is so that if the admin changes the first or last name, this change will appear in the main listbox.
lstID.AddItem Volunteers("ID") 'Adds record ID's to the ID list box
Volunteers.MoveNext 'Moves to the next record
Loop
Else
MsgBox "Please add someone to the database." 'If there is no one in the database.
End If
lstNames.ListIndex = 0 'goes to first name in list box
Call Sort_it 'Sorts in alphabetical order
End Sub