Quick summary; this database was created to be able to store drivers information. All fields and buttons were working, until i went ahead and added a new field manually for phone number.
Now random errors occur without displaying an error message.
1. The 'ADD' driver button should be available whenever the social field is blank, but now does not show up until I purposely cause an error by leaving it blank and pressing update. Then the 'ADD" appears as it should.
2. Random entries now refuse to edit, when selecting an existing data, the update button works on some, but not others.
this is the best way i can explain my issue.
Option Compare Database
Code:
Private Sub cmdAdd_Click()
'when we click on button Add there are two options
'1. for insert
'2. for update
If Me.txtSocial.Tag & "" = "" Then
' this is for insert new
'add data to table
CurrentDb.Execute "INSERT INTO tblDrivers(tbLast, tbFirst, tbDOB, tbCDL, tbCDLEXP, tbCDLST, tbMC, tbMCEXP, tbCOM, tbHire, tbSocial, tbdrug, tbPhone) " & _
" VALUES('" & Me.txtLast & "', '" & Me.txtFirst & "', '" & Me.txtDOB & "', '" & Me.txtCDL & "', '" & Me.txtCDLEXP & "', '" & Me.txtCDLST & "', '" & _
Me.txtMC & "', '" & Me.txtMCEXP & "', '" & Me.txtCMP & "', '" & Me.txtHIRE & "', '" & Me.txtSocial & "', '" & Me.txtDrug & "', '" & Me.txtPhone & "')"
Else
'otherwise (Tag of txtSocial store to the Social of Drivers to be notified)
CurrentDb.Execute "UPDATE tblDrivers " & _
" SET tbSocial=" & Me.txtSocial & _
", tbfirst='" & Me.txtFirst & "'" & _
", tblast='" & Me.txtLast & "'" & _
", tbDOB='" & Me.txtDOB & "'" & _
", tbCDL='" & Me.txtCDL & "'" & _
", tbCDLEXP='" & Me.txtCDLEXP & "'" & _
", tbMC='" & Me.txtMC & "'" & _
", tbMCEXP='" & Me.txtMCEXP & "'" & _
", tbCOM='" & Me.txtCMP & "'" & _
", tbHire='" & Me.txtHIRE & "'" & _
", tbDrug='" & Me.txtDrug & "'" & _
", tbPhone='" & Me.txtPhone & "'" & _
" WHERE tbSocial=" & Me.txtSocial.Tag
End If
'clear form
cmdClear_Click
'refresh data in list on form
frmDriverSub.Form.Requery
End Sub
Private Sub cmdClear_Click()
Me.txtLast = ""
Me.txtFirst = ""
Me.txtDOB = ""
Me.txtCDL = ""
Me.txtCDLEXP = ""
Me.txtCDLST = ""
Me.txtMC = ""
Me.txtMCEXP = ""
Me.txtCMP = ""
Me.txtHIRE = ""
Me.txtSocial = ""
Me.txtDrug = ""
Me.txtPhone = ""
'focus on ID text Box
Me.txtSocial.SetFocus
'set button edit to enable
Me.cmdEdit.Enabled = True
'change caption of button add to Add
Me.cmdAdd.Caption = "Add"
'clear tag on txtSocial for reset new
Me.txtSocial.Tag = ""
End Sub
Private Sub cmdClose_Click()
DoCmd.Close
End Sub
Private Sub cmdEdit_Click()
'check whether data exists in list
If Not (Me.frmDriverSub.Form.Recordset.EOF And Me.frmDriverSub.Form.Recordset.BOF) Then
'get data to text box control
With Me.frmDriverSub.Form.Recordset
Me.txtSocial = .Fields("tbSocial")
Me.txtLast = .Fields("tbLast")
Me.txtFirst = .Fields("tbFirst")
Me.txtDOB = .Fields("tbDOB")
Me.txtCDL = .Fields("tbCDL")
Me.txtCDLEXP = .Fields("tbCDLEXP")
Me.txtCDLST = .Fields("tbCDLST")
Me.txtMC = .Fields("tbMC")
Me.txtMCEXP = .Fields("tbMCEXP")
Me.txtCMP = .Fields("tbCOM")
Me.txtHIRE = .Fields("tbHIRE")
Me.txtSocial = .Fields("tbSOCIAL")
Me.txtDrug = .Fields("tbDRUG")
Me.txtPhone = .Fields("tbPHONE")
'store social of Driver in Tag of txtSocial in case Social is modified
Me.txtSocial.Tag = .Fields("tbSocial")
'change caption of button add to Update
Me.cmdAdd.Caption = "Update"
'disable buton edit
Me.cmdEdit.Enabled = False
'refresh data in list on form
frmDriverSub.Form.Requery
End With
End If
End Sub