EXCEL VBA STUDENTS DATABASE [on hold]
- by BENTET
I AM DEVELOPING AN EXCEL DATABASE TO RECORD STUDENTS DETAILS. THE HEADINGS OF THE TABLE ARE DATE,YEAR, PAYMENT SLIP NO.,STUDENT NUMBER,NAME,FEES,AMOUNT PAID, BALANCE AND PREVIOUS BALANCE. I HAVE BEEN ABLE TO PUT UP SOME CODE WHICH IS WORKING, BUT THERE ARE SOME SETBACKS THAT I WANT TO BE ADDRESSED.I ACTUALLY DEVELOPED A USERFORM FOR EACH PROGRAMME OF THE INSTITUTION AND ASSIGNED EACH TO A SPECIFIC SHEET BUT WHENEVER I ADD A RECORD, IT DOES NOT GO TO THE ASSIGNED SHEET BUT GOES TO THE ACTIVE SHEET.ALSO I WANT TO HIDE ALL SHEETS AND BE WORKING ONLY ON THE USERFORMS WHEN THE WORKBOOK IS OPENED.ONE PROBLEM AM ALSO FACING IS THE UPDATE CODE.WHENEVER I UPDATE A RECORD ON A SPECIFIC ROW, IT RATHER EDIT THE RECORD ON THE FIRST ROW NOT THE RECORD EDITED.THIS IS THE CODE I HAVE BUILT SO FAR.I AM VIRTUALLY A NOVICE IN PROGRAMMING.
Private Sub cmdAdd_Click()
Dim lastrow As Long
lastrow = Sheets("Sheet4").Range("A" & Rows.Count).End(xlUp).Row
Cells(lastrow + 1, "A").Value = txtDate.Text
Cells(lastrow + 1, "B").Value = ComBox1.Text
Cells(lastrow + 1, "C").Value = txtSlipNo.Text
Cells(lastrow + 1, "D").Value = txtStudentNum.Text
Cells(lastrow + 1, "E").Value = txtName.Text
Cells(lastrow + 1, "F").Value = txtFees.Text
Cells(lastrow + 1, "G").Value = txtAmountPaid.Text
txtDate.Text = ""
ComBox1.Text = ""
txtSlipNo.Text = ""
txtStudentNum.Text = ""
txtName.Text = ""
txtFees.Text = ""
txtAmountPaid.Text = ""
End Sub
Private Sub cmdClear_Click()
txtDate.Text = ""
ComBox1.Text = ""
txtSlipNo.Text = ""
txtStudentNum.Text = ""
txtName.Text = ""
txtFees.Text = ""
txtAmountPaid.Text = ""
txtBalance.Text = ""
End Sub
Private Sub cmdClearD_Click()
txtDate.Text = ""
ComBox1.Text = ""
txtSlipNo.Text = ""
txtStudentNum.Text = ""
txtName.Text = ""
txtFees.Text = ""
txtAmountPaid.Text = ""
txtBalance.Text = ""
End Sub
Private Sub cmdClose_Click()
Unload Me
End Sub
Private Sub cmdDelete_Click()
'declare the variables
Dim findvalue As Range
Dim cDelete As VbMsgBoxResult
'check for values
If txtStudentNum.Value = "" Or txtName.Value = "" Or txtDate.Text = "" Or ComBox1.Text = "" Or txtSlipNo.Text = "" Or txtFees.Text = "" Or txtAmountPaid.Text = "" Or txtBalance.Text = "" Then
MsgBox "There is not data to delete"
Exit Sub
End If
'give the user a chance to change their mind
cDelete = MsgBox("Are you sure that you want to delete this student", vbYesNo + vbDefaultButton2, "Are you sure????")
If cDelete = vbYes Then
'delete the row
Set findvalue = Sheet4.Range("D:D").Find(What:=txtStudentNum, LookIn:=xlValues)
findvalue.EntireRow.Delete
End If
'clear the controls
txtDate.Text = ""
ComBox1.Text = ""
txtSlipNo.Text = ""
txtStudentNum.Text = ""
txtName.Text = ""
'txtFees.Text = ""
txtAmountPaid.Text = ""
txtBalance.Text = ""
End Sub
Private Sub cmdSearch_Click()
Dim lastrow As Long
Dim currentrow As Long
Dim studentnum As String
lastrow = Sheets("Sheet4").Range("A" & Rows.Count).End(xlUp).Row
studentnum = txtStudentNum.Text
For currentrow = 2 To lastrow
If Cells(currentrow, 4).Text = studentnum Then
txtDate.Text = Cells(currentrow, 1)
ComBox1.Text = Cells(currentrow, 2)
txtSlipNo.Text = Cells(currentrow, 3)
txtStudentNum.Text = Cells(currentrow, 4).Text
txtName.Text = Cells(currentrow, 5)
txtFees.Text = Cells(currentrow, 6)
txtAmountPaid.Text = Cells(currentrow, 7)
txtBalance.Text = Cells(currentrow, 8)
End If
Next currentrow
txtStudentNum.SetFocus
End Sub
Private Sub cmdSearchName_Click()
Dim lastrow As Long
Dim currentrow As Long
Dim studentname As String
lastrow = Sheets("Sheet4").Range("A" & Rows.Count).End(xlUp).Row
studentname = txtName.Text
For currentrow = 2 To lastrow
If Cells(currentrow, 5).Text = studentname Then
txtDate.Text = Cells(currentrow, 1)
ComBox1.Text = Cells(currentrow, 2)
txtSlipNo.Text = Cells(currentrow, 3)
txtStudentNum.Text = Cells(currentrow, 4)
txtName.Text = Cells(currentrow, 5).Text
txtFees.Text = Cells(currentrow, 6)
txtAmountPaid.Text = Cells(currentrow, 7)
txtBalance.Text = Cells(currentrow, 8)
End If
Next currentrow
txtName.SetFocus
End Sub
Private Sub cmdUpdate_Click()
Dim tdate As String
Dim tlevel As String
Dim tslipno As String
Dim tstudentnum As String
Dim tname As String
Dim tfees As String
Dim tamountpaid As String
Dim currentrow As Long
Dim lastrow As Long
'If Cells(currentrow, 5).Text = studentname Then
'txtDate.Text = Cells(currentrow, 1)
lastrow = Sheets("Sheet4").Range("A" & Columns.Count).End(xlUp).Offset(0, 1).Column
For currentrow = 2 To lastrow
tdate = txtDate.Text
Cells(currentrow, 1).Value = tdate
txtDate.Text = Cells(currentrow, 1)
tlevel = ComBox1.Text
Cells(currentrow, 2).Value = tlevel
ComBox1.Text = Cells(currentrow, 2)
tslipno = txtSlipNo.Text
Cells(currentrow, 3).Value = tslipno
txtSlipNo = Cells(currentrow, 3)
tstudentnum = txtStudentNum.Text
Cells(currentrow, 4).Value = tstudentnum
txtStudentNum.Text = Cells(currentrow, 4)
tname = txtName.Text
Cells(currentrow, 5).Value = tname
txtName.Text = Cells(currentrow, 5)
tfees = txtFees.Text
Cells(currentrow, 6).Value = tfees
txtFees.Text = Cells(currentrow, 6)
tamountpaid = txtAmountPaid.Text
Cells(currentrow, 7).Value = tamountpaid
txtAmountPaid.Text = Cells(currentrow, 7)
Next currentrow
txtDate.SetFocus
ComBox1.SetFocus
txtSlipNo.SetFocus
txtStudentNum.SetFocus
txtName.SetFocus
txtFees.SetFocus
txtAmountPaid.SetFocus
txtBalance.SetFocus
End Sub
PLEASE I WAS THINKING IF I CAN DEVELOP SOMETHING THAT WILL USE ONLY ONE USERFORM TO SEND DATA TO DIFFERENT SHEETS IN THE WORKBOOK.
