ขอบคุณมากๆครับ มีอีก Code ที่ต้องการรวมครับ
โจทย์คือ ต้องการรวมทั้งสองโค้ด เพื่อให้มีเช็คก่อน Save(INSERT INTO) ว่า NationalID ที่กรอกใน txtNationalID1 ซ้ำกับของที่มีอยู่เดิม
ในตาราง tblContractor หรือไม่ ยาวหน่อยนะครับ อิอิ
Code แรก: โค้ด Save
Private Sub cmdSave_Click()
Dim ctrl As Control
Dim mSave As Boolean
str = Empty
'---------------------------------------------------------------------------------------------
' Copy Part 2 = Part 1
Me.txtNationalID2 = Me.txtNationalID1
Me.txtNamePrefixThai2 = Me.txtNamePrefixThai1
Me.txtFirstNameThai2 = Me.txtFirstNameThai1
Me.txtLastNameThai2 = Me.txtLastNameThai1
Me.txtNamePrefixEng2 = Me.txtNamePrefixEng1
Me.txtFirstNameEng2 = Me.txtFirstNameEng1
Me.txtLastNameEng2 = Me.txtLastNameEng1
Me.txtNickName2 = Me.txtNickName1
Me.txtAddessNo2 = Me.txtAddessNo1
Me.txtVillageNo2 = Me.txtVillageNo1
Me.txtRoad2 = Me.txtRoad1
Me.txtSubDistrict2 = Me.txtSubDistrict1
Me.txtDistrict2 = Me.txtDistrict1
Me.txtProvince2 = Me.txtProvince1
Me.txtPostcode2 = Me.txtPostcode1
Me.txtTelephoneMobile2 = Me.txtTelephoneMobile1
'---------------------------------------------------------------------------------------------
' Check IsNullOrEmpty
For Each ctrl In Me.Controls
If TypeOf ctrl Is TextBox Then
If isnullorEmptyTbx(ctrl) Then
ctrl.BackColor = RGB(119, 192, 212)
ctrl.BorderColor = RGB(157, 187, 97)
str = str & ctrl.Tag & vbNewLine
Else
ctrl.BackColor = vbWhite
ctrl.BorderColor = RGB(192, 192, 192)
End If
End If
Next ctrl
For Each ctrl In Me.Controls
If TypeOf ctrl Is ComboBox Then
If isnullorEmptyCbx(ctrl) Then
ctrl.BackColor = RGB(119, 192, 212)
ctrl.BorderColor = RGB(157, 187, 97)
str = str & ctrl.Tag & vbNewLine
Else
ctrl.BackColor = vbWhite
ctrl.BorderColor = RGB(192, 192, 192)
End If
End If
Next ctrl
If IsNull(str) Or str = "" Then
'---------------------------------------------------------------------------------------------
' Insert into table
If MsgBox("Are you sure you want to save?", vbQuestion + vbYesNo, "Save Confirmation") = vbYes Then
mSave = True
On Error Resume Next
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tblContractor([NationalID],[NamePrefixThai],[FirstNameThai],[LastNameThai],[NamePrefixEng],[FirstNameEng],[LastNameEng],[NickName],[BirthDate],[BloodGroup],[AddessNo],[VillageNo],[Road],[SubDistrict],[District],[Province],[Postcode],[TelephoneMobile],[ImagePath])" & _
"Values ('" & Me.txtNationalID1 & "', '" & Me.txtNamePrefixThai1 & "', '" & Me.txtFirstNameThai1 & "', '" & Me.txtLastNameThai1 & "', '" & Me.txtNamePrefixEng1 & "', '" & Me.txtFirstNameEng1 & "', '" & Me.txtLastNameEng1 & "', '" & Me.txtNickName1 & "', '" & Me.txtBirthDate & "', '" & _
Me.txtBloodGroup & "', '" & Me.txtAddessNo1 & "', '" & Me.txtVillageNo1 & "', '" & Me.txtRoad1 & "', '" & Me.txtSubDistrict1 & "', '" & Me.txtDistrict1 & "', '" & Me.txtProvince1 & "', '" & Me.txtPostcode1 & "', '" & Me.txtTelephoneMobile1 & "', '" & Me.txtImagePath & "');"
DoCmd.RunSQL "INSERT INTO tblWork([NationalID],[NamePrefixThai],[FirstNameThai],[LastNameThai],[NamePrefixEng],[FirstNameEng],[LastNameEng],[NickName],[AddessNo],[VillageNo],[Road],[SubDistrict],[District],[Province],[Postcode],[TelephoneMobile],[CompanyID],[PlantID],[DepartmentID],[SectionID],[SubSectionName],[JobAreaName]," & _
"[WorkDetail],[ContractType],[WorkContractID],[CompanyHiringDate],[JobAreaEntryDate])" & _
"Values ('" & Me.txtNationalID2 & "', '" & Me.txtNamePrefixThai2 & "', '" & Me.txtFirstNameThai2 & "', '" & Me.txtLastNameThai2 & "', '" & Me.txtNamePrefixEng2 & "', '" & Me.txtFirstNameEng2 & "', '" & Me.txtLastNameEng2 & "', '" & Me.txtNickName2 & "', '" & Me.txtAddessNo2 & "', '" & _
Me.txtVillageNo2 & "', '" & Me.txtRoad2 & "', '" & Me.txtSubDistrict2 & "', '" & Me.txtDistrict2 & "', '" & Me.txtProvince2 & "', '" & Me.txtPostcode2 & "', '" & Me.txtTelephoneMobile2 & "', '" & Me.txtCompanyID & "', '" & Me.txtPlantID & "', '" & Me.txtDepartmentID & "', '" & _
Me.txtSectionID & "', '" & Me.txtSubSectionName & "', '" & Me.txtJobAreaName & "', '" & Me.txtWorkDetail & "', '" & Me.txtContractType & "', '" & Me.txtWorkContractID & "', '" & Me.txtCompanyHiringDate & "', '" & Me.txtJobAreaEntryDate & "');"
MsgBox ("Your record has been successfully saved!")
'---------------------------------------------------------------------------------------------
' Clear Textbox
Me.txtNationalID1 = ""
Me.txtNamePrefixThai1 = ""
Me.txtFirstNameThai1 = ""
Me.txtLastNameThai1 = ""
Me.txtNickName1 = ""
Me.txtNamePrefixEng1 = ""
Me.txtFirstNameEng1 = ""
Me.txtLastNameEng1 = ""
Me.txtBirthDate = ""
Me.txtBloodGroup = ""
Me.txtAddessNo1 = ""
Me.txtVillageNo1 = ""
Me.txtRoad1 = ""
Me.txtSubDistrict1 = ""
Me.txtDistrict1 = ""
Me.txtProvince1 = ""
Me.txtPostcode1 = ""
Me.txtTelephoneMobile1 = ""
Me.txtImagePath = ""
Me.txtNationalID2 = ""
Me.txtNamePrefixThai2 = ""
Me.txtFirstNameThai2 = ""
Me.txtLastNameThai2 = ""
Me.txtNamePrefixEng2 = ""
Me.txtFirstNameEng2 = ""
Me.txtLastNameEng2 = ""
Me.txtNickName2 = ""
Me.txtAddessNo2 = ""
Me.txtVillageNo2 = ""
Me.txtRoad2 = ""
Me.txtSubDistrict2 = ""
Me.txtDistrict2 = ""
Me.txtProvince2 = ""
Me.txtPostcode2 = ""
Me.txtTelephoneMobile2 = ""
Me.txtCompanyID = ""
Me.txtPlantID = ""
Me.txtDepartmentID = ""
Me.txtSectionID = ""
Me.txtSubSectionName = ""
Me.txtJobAreaName = ""
Me.txtWorkDetail = ""
Me.txtContractType = ""
Me.txtWorkContractID = ""
Me.txtCompanyHiringDate = ""
Me.txtJobAreaEntryDate = ""
Me.Requery
Else
mSave = False
Me.Undo
Exit Sub
End If
Else
' MsgBox "Please enter data for all the required fields below" & vbNewLine & _
' String(52, "-") & vbCrLf & str, vbInformation + vbOKOnly, "Form is not fullfilled completly"
MsgBox "Please enter data for all the required fields", vbInformation + vbOKOnly, "Form is not fullfilled completly"
Exit Sub
End If
End Sub
Code สอง: เช็คว่าชื่อ NationalID ซ้ำหรือไม่ก่อน Save
Dim DontDuplicate As String
Dim str1 As String
DontDuplicate = Me.txtNationalID1.Value
str1 = "[National]=" & "'" & DontDuplicate & "'"
If Me.txtNationalID1 = DLookup("[NationalID]", "tblContractor", str1) Then
MsgBox "A record " & txtNationalID1 & " is duplicate. " & "Please check record.", vbInformation
Me.Undo
