Dear all,
May I know how to update the modify data?
I export the list of data from Access, then if the user have modify a data and he/she click "Update" button.
The data will updated.
Below code is my insert data.
Code:
Private Sub cmdadd_Click()
If Me.txtStaffID.Value = "" Then
MsgBox "Please enter the Staff ID", vbExclamation, "Staff Master Form"
Me.txtStaffID.SetFocus
Exit Sub
End If
If Not IsNumeric(Me.txtStaffID.Value) Then
MsgBox "The Staff ID must contain a number.", vbExclamation, "Staff Master Form"
Me.txtStaffID.SetFocus
Exit Sub
End If
If Me.txtName.Value = "" Then
MsgBox "Please enter the Name.", vbExclamation, "Staff Master Form"
Me.txtStaffID.SetFocus
Exit Sub
End If
If Me.cbopayrollentity.Value = "" Then
MsgBox "Please select the Payroll Entity.", vbExclamation, "Staff Master Form"
Me.txtStaffID.SetFocus
Exit Sub
End If
If Me.cbodept.Value = "" Then
MsgBox "Please select the Department.", vbExclamation, "Staff Master Form"
Me.txtStaffID.SetFocus
Exit Sub
End If
If Me.txtsite.Value = "" Then
MsgBox "Please enter the Site.", vbExclamation, "Staff Master Form"
Me.txtStaffID.SetFocus
Exit Sub
End If
If Me.txtNickname.Value = "" Then
Me.txtNickname.Value = "null"
Exit Sub
End If
If Me.cbodeptbu.Value = "" Then
Me.cbodeptbu.Value = "0"
Exit Sub
End If
Dim cn As Object
Dim strQuery As String
Dim staffid As String
Dim name As String
Dim nickname As String
Dim deptbu As String
Dim payrollentity As String
Dim dept As String
Dim site As String
Dim myDB As String
staffid = Me.txtStaffID.Value
name = Me.txtName.Value
nickname = Me.txtNickname.Value
deptbu = Me.cbodeptbu.Value
payrollentity = Me.cbopayrollentity.Value
dept = Me.cbodept.Value
site = Me.txtsite.Value
myDB = "database.mdb"
Set cn = CreateObject("ADODB.Connection")
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = myDB
.Open
End With
strQuery = "INSERT INTO STAFFMASTER ([STAFFID],[NAME],[NICKNAME],[DEPTBU],[PAYROLLENTITY],[DEPT],[SITE])" & _
"VALUES (""" & staffid & """,""" & name & """,""" & nickname & """," & deptbu & ",""" & payrollentity & """,""" & dept & """,""" & site & """);"
cn.Execute strQuery
MsgBox "Successful Add", vbInformation, "STAFF MASTER FORM"
Dim ctl As Control
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl
cn.Close
Set cn = Nothing
End Sub