代码如下:
窗体类:
Option ExplicitPrivate WithEvents PrimaryCLS As clsEmployees 'clsEmployees Objects
Dim IsModify As Boolean 'Flag is used for Save after Modify or after Add' [Delete] Button
Private Sub cmdDel_Click()
On Error GoTo DeleteErr
PrimaryCLS.Delete
Exit Sub
DeleteErr:
MsgBox Err.Description
End Sub' [End] Button
Private Sub cmdEnd_Click()
Unload Me 'Unload Frame
End Sub' [<<] button
Private Sub cmdFirst_Click()
On Error GoTo GoFirstError
PrimaryCLS.MoveFirst 'Move the First Record
Exit Sub
GoFirstError:
MsgBox Err.Description
End Sub'[>>] Button
Private Sub cmdLast_Click()
On Error GoTo GoLastError
PrimaryCLS.MoveLast 'Move to the Last Record
Exit Sub
GoLastError:
MsgBox Err.Description
End Sub' [Modify] button
Private Sub cmdModify_Click()
On Error GoTo ModifyErr
SetButtons False 'Set [Save] to enable
SetTexts True 'Enable all textbox
Modify_Flag = True 'Set Flag IsModify to true
txtEmpName.SetFocus 'Set txtEmpName to focus
SetButtons False 'Set [Save] to enable
Exit Sub
ModifyErr:
MsgBox Err.Description
End Sub' [New] button
Private Sub cmdNew_Click()
On Error GoTo AddErr
SetTexts True 'Enable all textbox
AllClear 'Clear all textbox
txtEmpName.SetFocus 'Set txtEmpName to focus
Modify_Flag = False 'Set Flag IsModify to false
SetButtons False 'Set [Save] to enable
' Location list
cmbLocation.Clear
cmbLocation.AddItem ("DaLian")
cmbLocation.AddItem ("ShangHai")
cmbLocation.AddItem ("Tokyo")
cmbLocation.AddItem ("San Fransisico")
' Position list
cmbPosition.Clear
cmbPosition.AddItem ("SE")
cmbPosition.AddItem ("SSE")
cmbPosition.AddItem ("TL")
cmbPosition.AddItem ("STL")
cmbPosition.AddItem ("PM")
cmbPosition.AddItem ("SPM")
Exit Sub
AddErr:
MsgBox Err.Description
End Sub
' [>] Button
Private Sub cmdNext_Click()
On Error GoTo GoNextError
PrimaryCLS.MoveNext 'Move to Next Record
Exit Sub
GoNextError:
MsgBox Err.Description
End Sub' [<] Button
Private Sub cmdPrev_Click()
On Error GoTo GoPrevError
PrimaryCLS.MovePrevious 'Move to Previous Record
Exit SubGoPrevError:
MsgBox Err.Description
End Sub' [Save] Button
Private Sub cmdSave_Click()
On Error GoTo SAVEERR PrimaryCLS.Update IsModify 'Save the current Value
SetButtons True 'Set Save to Enable
SetTexts False 'Set All textbox to disable
cmdNew.SetFocus 'Empname get the focus
Exit Sub
SAVEERR:
MsgBox Err.Description
End Sub' When Load Form, Call Form_Load
Private Sub Form_Load()
Set PrimaryCLS = New clsEmployees
' set data bind to dataControl
txtEmpName.DataField = "Empname"
Set txtEmpName.DataSource = PrimaryCLS.rs
txtAge.DataField = "age"
Set txtAge.DataSource = PrimaryCLS.rs
txtAddress.DataField = "address"
Set txtAddress.DataSource = PrimaryCLS.rs
cmbPosition.DataField = "position"
Set cmbPosition.DataSource = PrimaryCLS.rs
cmbLocation.DataField = "location"
Set cmbLocation.DataSource = PrimaryCLS.rs
txtSalary.DataField = "salary"
Set txtSalary.DataSource = PrimaryCLS.rs
'[Save]button should be enabled
cmdSave.Enabled = False
SetTexts False
End If
End Sub' Recordset MoveComplete event
Private Sub PrimaryCLS_MoveComplete()
'This will display the current record position for this recordset
lblCurrentRecord.Caption = CStr(PrimaryCLS.AbsolutePosition)
lblTotalRecord.Caption = CStr(PrimaryCLS.TotalPosition)
End Sub' Set buttons false or true
Public Sub SetButtons(bVal As Boolean)
cmdNew.Enabled = bVal '[New] Button
cmdModify.Enabled = bVal '[Modify] Button
cmdDel.Enabled = bVal '[Delete] Button
cmdSave.Enabled = Not bVal '[Save] Button
End Sub' Set Texts false or true
Public Sub SetTexts(bVal As Boolean)
txtEmpName.Enabled = bVal 'EmpName TextBox
txtAge.Enabled = bVal 'Age TextBox
txtAddress.Enabled = bVal 'Address TextBox
cmbLocation.Enabled = bVal 'Location ComboBox
cmbPosition.Enabled = bVal 'Position ComboBox
txtSalary.Enabled = bVal 'Salary TextBox
End Sub' Get IsModify
Public Property Get Modify_Flag() As Boolean
Modify_Flag = IsModify
End Property' Set IsModify
Public Property Let Modify_Flag(ByVal New_IsModify As Boolean)
IsModify = New_IsModify
End Property
窗体类:
Option ExplicitPrivate WithEvents PrimaryCLS As clsEmployees 'clsEmployees Objects
Dim IsModify As Boolean 'Flag is used for Save after Modify or after Add' [Delete] Button
Private Sub cmdDel_Click()
On Error GoTo DeleteErr
PrimaryCLS.Delete
Exit Sub
DeleteErr:
MsgBox Err.Description
End Sub' [End] Button
Private Sub cmdEnd_Click()
Unload Me 'Unload Frame
End Sub' [<<] button
Private Sub cmdFirst_Click()
On Error GoTo GoFirstError
PrimaryCLS.MoveFirst 'Move the First Record
Exit Sub
GoFirstError:
MsgBox Err.Description
End Sub'[>>] Button
Private Sub cmdLast_Click()
On Error GoTo GoLastError
PrimaryCLS.MoveLast 'Move to the Last Record
Exit Sub
GoLastError:
MsgBox Err.Description
End Sub' [Modify] button
Private Sub cmdModify_Click()
On Error GoTo ModifyErr
SetButtons False 'Set [Save] to enable
SetTexts True 'Enable all textbox
Modify_Flag = True 'Set Flag IsModify to true
txtEmpName.SetFocus 'Set txtEmpName to focus
SetButtons False 'Set [Save] to enable
Exit Sub
ModifyErr:
MsgBox Err.Description
End Sub' [New] button
Private Sub cmdNew_Click()
On Error GoTo AddErr
SetTexts True 'Enable all textbox
AllClear 'Clear all textbox
txtEmpName.SetFocus 'Set txtEmpName to focus
Modify_Flag = False 'Set Flag IsModify to false
SetButtons False 'Set [Save] to enable
' Location list
cmbLocation.Clear
cmbLocation.AddItem ("DaLian")
cmbLocation.AddItem ("ShangHai")
cmbLocation.AddItem ("Tokyo")
cmbLocation.AddItem ("San Fransisico")
' Position list
cmbPosition.Clear
cmbPosition.AddItem ("SE")
cmbPosition.AddItem ("SSE")
cmbPosition.AddItem ("TL")
cmbPosition.AddItem ("STL")
cmbPosition.AddItem ("PM")
cmbPosition.AddItem ("SPM")
Exit Sub
AddErr:
MsgBox Err.Description
End Sub
' [>] Button
Private Sub cmdNext_Click()
On Error GoTo GoNextError
PrimaryCLS.MoveNext 'Move to Next Record
Exit Sub
GoNextError:
MsgBox Err.Description
End Sub' [<] Button
Private Sub cmdPrev_Click()
On Error GoTo GoPrevError
PrimaryCLS.MovePrevious 'Move to Previous Record
Exit SubGoPrevError:
MsgBox Err.Description
End Sub' [Save] Button
Private Sub cmdSave_Click()
On Error GoTo SAVEERR PrimaryCLS.Update IsModify 'Save the current Value
SetButtons True 'Set Save to Enable
SetTexts False 'Set All textbox to disable
cmdNew.SetFocus 'Empname get the focus
Exit Sub
SAVEERR:
MsgBox Err.Description
End Sub' When Load Form, Call Form_Load
Private Sub Form_Load()
Set PrimaryCLS = New clsEmployees
' set data bind to dataControl
txtEmpName.DataField = "Empname"
Set txtEmpName.DataSource = PrimaryCLS.rs
txtAge.DataField = "age"
Set txtAge.DataSource = PrimaryCLS.rs
txtAddress.DataField = "address"
Set txtAddress.DataSource = PrimaryCLS.rs
cmbPosition.DataField = "position"
Set cmbPosition.DataSource = PrimaryCLS.rs
cmbLocation.DataField = "location"
Set cmbLocation.DataSource = PrimaryCLS.rs
txtSalary.DataField = "salary"
Set txtSalary.DataSource = PrimaryCLS.rs
'[Save]button should be enabled
cmdSave.Enabled = False
SetTexts False
End If
End Sub' Recordset MoveComplete event
Private Sub PrimaryCLS_MoveComplete()
'This will display the current record position for this recordset
lblCurrentRecord.Caption = CStr(PrimaryCLS.AbsolutePosition)
lblTotalRecord.Caption = CStr(PrimaryCLS.TotalPosition)
End Sub' Set buttons false or true
Public Sub SetButtons(bVal As Boolean)
cmdNew.Enabled = bVal '[New] Button
cmdModify.Enabled = bVal '[Modify] Button
cmdDel.Enabled = bVal '[Delete] Button
cmdSave.Enabled = Not bVal '[Save] Button
End Sub' Set Texts false or true
Public Sub SetTexts(bVal As Boolean)
txtEmpName.Enabled = bVal 'EmpName TextBox
txtAge.Enabled = bVal 'Age TextBox
txtAddress.Enabled = bVal 'Address TextBox
cmbLocation.Enabled = bVal 'Location ComboBox
cmbPosition.Enabled = bVal 'Position ComboBox
txtSalary.Enabled = bVal 'Salary TextBox
End Sub' Get IsModify
Public Property Get Modify_Flag() As Boolean
Modify_Flag = IsModify
End Property' Set IsModify
Public Property Let Modify_Flag(ByVal New_IsModify As Boolean)
IsModify = New_IsModify
End Property
Public Event MoveComplete() ' This event for display record count
Public cnn As ADODB.Connection ' Connection
Dim cmd As ADODB.Command ' Command
Public WithEvents rs As ADODB.Recordset ' Recordset
Dim frmEmp As frmTest ' frmTest' Get Absolute Position
Public Property Get AbsolutePosition() As Long
AbsolutePosition = rs.AbsolutePosition ' AbsolutePosition
If rs.RecordCount = 0 Then
AbsolutePosition = 0
End If
End Property' Get Record Count
Public Property Get TotalPosition() As Long
TotalPosition = rs.RecordCount ' RecordCount
End Property' Initialize Class
Public Sub Class_Initialize()
ConnectToDatabase ' Open the Database
selRecordSet ' Select Records from Table
End Sub' Delete records
Public Sub Delete()
Dim rst As VbMsgBoxResult ' Used for MsgBox Result
Dim strSQL As String ' Used for SQL string
On Error GoTo ERRDEL
Set frmEmp = New frmTest ' Instance frmTest object
rst = MsgBox("Are you sure to delete [" & frmEmp.txtEmpName.Text & "] ?(Y/N)", vbQuestion + vbOKCancel + vbDefaultButton1, "Delete Employees")
If rst = 1 Then ' Choose [Yes]
' delete the selected record
cnn.BeginTrans ' Start Transation
strSQL = "DELETE FROM tbl_employees"
strSQL = strSQL & " WHERE empid = " & rs.Fields("empid").Value
cnn.Execute strSQL ' Execute Delete SQL
cnn.CommitTrans ' Commit Transation
selRecordSet ' Refresh RecordSet
If Not rs.EOF Then
MoveLast ' Move to the last Record
End If
End If
Exit Sub
ERRDEL:
MsgBox Err.Description
End Sub' Save records
Public Sub Update(IsModify As Boolean)
Dim strSQL As String ' Used for SQL string
On Error GoTo ERRUPDATE
Set frmEmp = New frmTest ' Instance frmTest object
' Add a new record
If IsModify = False Then
cnn.BeginTrans ' Start Transation
'Add a new record into tbl_employees
strSQL = "INSERT INTO tbl_employees(empid, empname, age, address, position, location, salary)"
strSQL = strSQL & " Values(tbl_empid.NEXTVAL,"
strSQL = strSQL & " '" & frmEmp.txtEmpName.Text & "', "
strSQL = strSQL & frmEmp.txtAge.Text & ","
strSQL = strSQL & " '" & frmEmp.txtAddress & "',"
strSQL = strSQL & " '" & frmEmp.cmbPosition.Text & "',"
strSQL = strSQL & " '" & frmEmp.cmbLocation.Text & "',"
strSQL = strSQL & frmEmp.txtSalary.Text & ")"
cnn.Execute strSQL ' Execute Add SQL
cnn.CommitTrans ' Commit Transation
selRecordSet ' Refresh RecordSet
If Not rs.EOF Then
MoveLast ' Move to the last Record
End If
Else ' Modify Existed Record
cnn.BeginTrans ' Start Transation
'Update record
strSQL = "UPDATE tbl_employees"
strSQL = strSQL & " SET empname = '" & frmEmp.txtEmpName.Text & "',"
strSQL = strSQL & " age = " & frmEmp.txtAge.Text & ","
strSQL = strSQL & " address = '" & frmEmp.txtAddress & "',"
strSQL = strSQL & " position = '" & frmEmp.cmbPosition.Text & "',"
strSQL = strSQL & " location = '" & frmEmp.cmbLocation.Text & "',"
strSQL = strSQL & " salary = " & frmEmp.txtSalary.Text
strSQL = strSQL & " where empid = " & rs.Fields("empid").Value
cnn.Execute strSQL ' Execute update SQL
cnn.CommitTrans ' Commit Transation
selRecordSet ' Refresh RecordSet
End If
Exit Sub
ERRUPDATE:
MsgBox Err.Description
End Sub' Move to the first record
Public Sub MoveFirst()
rs.MoveFirst
End Sub' Move to the last record
Public Sub MoveLast()
rs.MoveLast
End Sub' Move to the next record
Public Sub MoveNext()
If Not rs.EOF Then rs.MoveNext
If rs.EOF And rs.RecordCount > 0 Then
Beep
'moved off the end so go back
rs.MoveLast
End If
End Sub' Move to the Previous record
Public Sub MovePrevious()
If Not rs.BOF Then rs.MovePrevious
If rs.BOF And rs.RecordCount > 0 Then
Beep
'moved off the end so go back
rs.MoveFirst
End If
End Sub'ADORecord MoveComplete Events
Private Sub rs_MoveComplete(ByVal adReason As ADODB.EventReasonEnum, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
RaiseEvent MoveComplete
End Sub'Select
Private Sub selRecordSet()
' Execute Command
Set cmd = New Command
cmd.ActiveConnection = cnn
cmd.CommandText = "select empid, empname, age, address, position, location, salary from tbl_employees order by empid"
cmd.CommandType = adCmdText
' RecordSet of select
Set rs = cmd.Execute
End Sub