Nhập môn hệ quản trị cơ sở dữ liệu Phần 2

Option Compare Database Dim editingCID As Integer Dim db As DAO.Database

Private Sub cmdAddNew_Click()

'Lay cac du lieu nguoi dung nhap tu form 'Dua vao bang Course

'Hien thi ket qua ra ListBox

cmdEdit.SetFocus: cmdEdit.Caption = "Edit": editingCID = -1 Dim CN As String: txtCN.SetFocus: CN = txtCN.Text

Dim Duration As String

txtDuration.SetFocus: Duration = txtDuration.Text

Dim Des As String: txtDes.SetFocus: Des = txtDes.value If (CN = "") Then

MsgBox "The name of the course must be required!" Exit Sub

End If

Có thể bạn quan tâm!

Xem toàn bộ 114 trang tài liệu này.

Dim rec As Recordset If (db Is Nothing) Then

Set db = CurrentDb End If

If (IsNull(db)) Then Set db = CurrentDb

End If

If (IsEmpty(db)) Then Set db = CurrentDb

End If

Set rec = db.OpenRecordset("SELECT * FROM Course") rec.AddNew

rec.Fields("CName").value = CN

'Kiem tra Duration phai la mot so nguyen nho hon 500 If (Duration <> "") Then

If (IsNumeric(Duration) = False) Then

MsgBox "Duration must be less than or equal 500!"

Exit Sub End If

Dim pos1, pos2 As Integer pos1 = -1

pos2 = -1

pos1 = InStr(1, Duration, ",", vbTextCompare) pos2 = InStr(1, Duration, ".", vbTextCompare) If (pos1 > 0) Or (pos2 > 0) Then

MsgBox "Duration must be less than or equal 500!" Exit Sub

End If

Dim num As Integer num = CInt(Duration)

If (num = Null) Or (num > 500) Then

MsgBox "Duration must be less than or equal 500!" Exit Sub

End If rec.Fields("DurationInHour").value = num

End If

If (Des <> "") Then rec.Fields("Description").value = Des

End If rec.Update

Set db = Nothing LoadDataToListBox

End Sub

Private Sub cmdDelete_Click()

'Lay ra cac dong (item) ma nguoi dung chon trong ListBox 'Xoa ban ghi do trong bang Course

'Hien thi lai ket qua trong ListBox

cmdEdit.SetFocus: cmdEdit.Caption = "Edit": editingCID = -1 If (lbCourse.ItemsSelected.count = 0) Then

MsgBox "You must select at least 1 item in the ListBox to delete!"

Exit Sub End If

Dim count As Integer: count = lbCourse.ItemsSelected.count Dim varitem As Variant, i As Integer

Dim sql As String

sql = "SELECT * FROM Course WHERE (CID = " & CStr(lbCourse.Column(0, lbCourse.ItemsSelected.item(0))) & ")"

For i = 1 To lbCourse.ItemsSelected.count - 1

sql = sql & " OR (CID=" & CStr(lbCourse.Column(0, lbCourse.ItemsSelected(i))) & ")"

Dim rec As Recordset If (db Is Nothing) Then

Set db = CurrentDb End If

If (IsNull(db)) Then Set db = CurrentDb

End If

If (IsEmpty(db)) Then Set db = CurrentDb

End If

Set rec = db.OpenRecordset(sql) While (rec.EOF = False)

rec.Delete rec.MoveNext

Wend rec.Close

LoadDataToListBox End Sub

Private Sub cmdEdit_Click()

'Thay lai Text trong Caption cua nut cho phu hop 'kiem tra nguoi dung chi duoc chon 1 item de sua 'tai item do vao cac dieu khien tren form

'nguoi dung sua cac gia tri

'cap nhat lai bang Course cac gia tri moi 'Hien thi ket qua sua item do

Dim cap, CN, Dur, Des As String CN = "": Dur = "": Des = ""

cmdEdit.SetFocus: cap = cmdEdit.Caption cap = Trim(cap): cap = LCase(cap)

If (cap = "edit") Then cap = "Save Edit"

If (lbCourse.ItemsSelected.count = 0) Then

MsgBox "You must select only 1 item in the ListBox to edit!" Exit Sub

End If

Dim count As Integer: count = lbCourse.ItemsSelected.count If (count > 1) Then

MsgBox "You must select only 1 item in the ListBox to edit!" Exit Sub

End If

editingCID = lbCourse.Column(0, lbCourse.ItemsSelected.item(0)) If (lbCourse.Column(1, lbCourse.ItemsSelected.item(0)) <> "") Then

CN = lbCourse.Column(1, lbCourse.ItemsSelected.item(0)) End If

If (lbCourse.Column(2, lbCourse.ItemsSelected.item(0)) <> "") Then Dur = lbCourse.Column(2, lbCourse.ItemsSelected.item(0))

End If

If (lbCourse.Column(3, lbCourse.ItemsSelected.item(0)) <> "") Then Des = lbCourse.Column(3, lbCourse.ItemsSelected.item(0))

End If

txtCN.SetFocus: txtCN.Text = CN txtDuration.SetFocus: txtDuration.Text = Dur txtDes.SetFocus: txtDes.value = Des

Else

cap = "Edit"

txtCN.SetFocus: CN = txtCN.Text

txtDuration.SetFocus: Dur = txtDuration.Text txtDes.SetFocus: Des = txtDes.value

Dim sql As String: sql = "SELECT * FROM Course WHERE CID=" & CStr(editingCID)

Dim rec As Recordset If (db Is Nothing) Then

Set db = CurrentDb End If

If (IsNull(db)) Then Set db = CurrentDb

End If

If (IsEmpty(db)) Then Set db = CurrentDb

End If

If (CN = "") Then

MsgBox "The name of the course must be required!" Exit Sub

End If

Set rec = db.OpenRecordset(sql) rec.Edit rec.Fields("CName").value = CN If (Dur <> "") Then

If (IsNumeric(Dur) = False) Then

MsgBox "Duration must be less than or equal 500!" Exit Sub

End If

Dim pos1, pos2 As Integer pos1 = -1

pos2 = -1

pos1 = InStr(1, Dur, ",", vbTextCompare) pos2 = InStr(1, Dur, ".", vbTextCompare) If (pos1 > 0) Or (pos2 > 0) Then

MsgBox "Duration must be less than or equal 500!" Exit Sub

End If

Dim num As Integer num = CInt(Dur)

If (num = Null) Or (num > 500) Then

MsgBox "Duration must be less than or equal 500!" Exit Sub

End If rec.Fields("DurationInHour").value = num

End If

If (Des <> "") Then rec.Fields("Description").value = Des

End If rec.Update rec.Close

End If cmdEdit.SetFocus cmdEdit.Caption = cap LoadDataToListBox

End Sub

Private Sub cmdRefresh_Click() txtCN.SetFocus: txtCN.Text = "" txtDes.SetFocus: txtDes.value = ""

txtDuration.SetFocus: txtDuration.Text = "" editingCID = -1

cmdEdit.SetFocus cmdEdit.Caption = "Edit" LoadDataToListBox

End Sub

Private Sub cmdSearch_Click()

'Lay cac du lieu nguoi dung nhap tu form 'SELECT du lieu theo dieu kien tim kiem 'Hien thi ket qua ben duoi ListBox

cmdEdit.SetFocus: cmdEdit.Caption = "Edit": editingCID = -1

Dim CN As String: txtCN.SetFocus: CN = txtCN.Text

Dim Duration As String: txtDuration.SetFocus: Duration = txtDuration.Text

Dim Des As String: txtDes.SetFocus: Des = txtDes.value Dim foundCN As Boolean: foundCN = False

Dim foundDur As Boolean: foundDur = False Dim foundDes As Boolean: foundDes = False If (CN <> "") Then

foundCN = True End If

If (Duration <> "") Then foundDur = True

End If

If (Des <> "") Then foundDes = True

End If

If (foundCN = False) And (foundDur = False) And (foundDes = False) Then MsgBox "You must input either Name or Duration or Description of

the course!"

Exit Sub End If

Dim sql As String

sql = "SELECT * FROM Course WHERE"

If (foundCN) And (foundDur) And (foundDes) Then sql = sql & " (CName Like '*" & CN & "*')"

sql = sql & " and (DurationInHour = " & Duration & ")" sql = sql & " and (Description LIKE '*" & Des & "*')"

Else

If (foundCN) And (foundDur) And (Not foundDes) Then sql = sql & " (CName Like '*" & CN & "*')"

sql = sql & " and (DurationInHour = " & Duration & ")"

Else

If (foundCN) And (Not foundDur) And (foundDes) Then

Else

sql = sql & " (CName Like '*" & CN & "*')"

sql = sql & " and (Description LIKE '*" & Des & "*')"

If (foundCN) And (Not foundDur) And (Not foundDes) Then sql = sql & " (CName Like '*" & CN & "*')"

Else

If (Not foundCN) And (foundDur) And (foundDes) Then sql = sql & " (DurationInHour = " & Duration & ")"

sql = sql & " and (Description LIKE % '*" & Des & "*')" Else

If (Not foundCN) And (foundDur) And (Not foundDes) Then sql = sql & " (DurationInHour = " & Duration & ")"

Else

If (Not foundCN) And (Not foundDur) And (foundDes) Then sql = sql & "(Description LIKE '*" & Des & "*')"

End If End If End If End If End If End If End If

Dim rec As Recordset If (db Is Nothing) Then

Set db = CurrentDb End If

If (IsNull(db)) Then Set db = CurrentDb

End If

If (IsEmpty(db)) Then Set db = CurrentDb

End If

Set rec = db.OpenRecordset(sql)