รบกวนช่วยรวม Code VBA 2 ตัวนี้เข้าด้วยกันหน่อยครับ



0 สมาชิก และ 1 บุคคลทั่วไป กำลังดูหัวข้อนี้

26 ก.พ. 63 , 14:00:30
อ่าน 268 ครั้ง

Tatchawin

สวัสดีครับทุกท่าน ผมมี Code สองตัวนี้ครับ ต้องการที่จะรวมกัน ตามโจทย์ผมด้านล่างนี้ จะมีวิธีการรวมอย่างไรครับ
ตัวแรก: เป็น Code PromptUserSaving ก็คือถ้าหน้าฟอร์มมีการแก้ไข อัพเดต ลบ แล้วเราจะเลื่อนไปหน้าอื่น หรือกดปิดฟอร์ม ระบบก็จะฟ้องขึ้นมาว่าจะ Save หรือไม่
ตัวสอง: หลังจากที่เราตอบ Save จากข้อที่ 1 ให้บันทึกลงไปที่ Field เลยว่า Record นี้ถูกแก้ไข Update วันไหน กี่โมง

Code แรก:
Private Sub Form_BeforeUpdate(Cancel As Integer)

   ' This procedure checks to see if the data on the form has
   ' changed. If the data has changed, the procedure prompts the
   ' user to continue with the save operation or to cancel it. Then
   ' the action that triggered the BeforeUpdate event is completed.

   Dim ctl As Control

   On Error GoTo Err_BeforeUpdate

   ' The Dirty property is True if the record has been changed.
   If Me.Dirty Then
      ' Prompt to confirm the save operation.
      If MsgBox("Do you want to save?", vbYesNo + vbQuestion, _
              "Save Record") = vbNo Then
         Me.Undo
      End If
   End If

Exit_BeforeUpdate:
   Exit Sub

Err_BeforeUpdate:
   MsgBox Err.Number & " " & Err.Description
   Resume Exit_BeforeUpdate
End Sub

Code สอง:
'------------------------------------------------------------
' Form_BeforeUpdate
'
'------------------------------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Form_BeforeUpdate_Err

    Modified = Now()
    ModifiedBy = CurrentUser()


Form_BeforeUpdate_Exit:
    Exit Sub

Form_BeforeUpdate_Err:
    MsgBox Error$
    Resume Form_BeforeUpdate_Exit

End Sub

ตามโจทย์ข้างต้น วอนท่านผู้รู้ ช่วยเขียน Code สองตัวนี้รวมกันหน่อยครับ ผมพยายามแล้ว ทำไม่ได้เลยครับ

ขอบคุณครับ

 

26 ก.พ. 63 , 15:16:20
ตอบกลับ #1

PNR

โค๊ด: [Select]
Private Sub Form_BeforeUpdate(Cancel As Integer)
   Dim ctl As Control
   Dim msg As String
   On Error GoTo Err_BeforeUpdate
   If Me.Dirty Then
   msg = (MsgBox("คุณต้องการบันทึกหรือไม่?", vbYesNo + vbQuestion, "Save Record"))
   If msg = vbNo Then
         Me.Undo
    End If
   If msg = vbYes Then
         Modified = Now()
         ModifiedBy = CurrentUser()
      End If
   End If
Exit_BeforeUpdate:
   Exit Sub
Err_BeforeUpdate:
   MsgBox Err.Number & " " & Err.Description
   Resume Exit_BeforeUpdate
End Sub


ยุบรวมกัน เมื่อเราตอบ Yes ครับ
โดย จากโค้ดนี้ ต้องมี textbox  Modified และ ModifiedBy ไว้รับค่าด้วยนะ
         Modified = Now()   คือ Modified จะจัดเก็บวันที่และเวลาตอนนั้นไว้
         ModifiedBy = CurrentUser() คือ ModifiedBy จะจัดเก็บUser ที่ทำการบันทึก

แต่ปกติผมไม่ค่อยใช้ Event BeforeUpdate ครับ
ถ้ามีตัวอย่างผมเพิ่มเติมให้ได้ครับ
« แก้ไขครั้งสุดท้าย: 26 ก.พ. 63 , 15:39:26 โดย PNR »
:meaw: :grin: :shout:
 
โพสต์นี้ได้รับคำขอบคุณจาก: Tatchawin

26 ก.พ. 63 , 23:07:26
ตอบกลับ #2

Tatchawin

ขอบคุณมากๆครับ มีอีก 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

 

27 ก.พ. 63 , 07:58:17
ตอบกลับ #3

PNR

Private Sub cmdSave_Click()
Dim ctrl As Control
Dim mSave As Boolean
Dim DontDuplicate As String
Dim str1 As String

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
   
   
'---------------------------------------------------------------------------------------------
'CheckDuplicate
   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
       exit sub
   else
' Insert into table
   If IsNull(str) Or str = "" Then
           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 if
End Sub
:meaw: :grin: :shout:
 
โพสต์นี้ได้รับคำขอบคุณจาก: Tatchawin

27 ก.พ. 63 , 10:12:16
ตอบกลับ #4

Tatchawin

ขอบคุณท่าน PNR มากๆครับ ที่ช่วยรวม Code ให้ผม ทั้งสองกรณีครับ

สำหรับ Code เมื่อวานนี้ ที่ท่านรวมให้ผม แล้วได้หน้าตาประมาณนี้ โดยใช้ Before Update ผมเอาไปลอง แล้วก็ใช้ได้อยู่นะครับ
แต่ผมจะส่งตัวอย่างให้ครับ ตามที่ท่านต้องการ(ตามไฟล์แนบ) เพื่อที่จะอยากทราบว่า ถ้าเปนท่าน ถ้าไม่ใช้ Before Update ท่านจะทำอย่างไร
ผมเองก็อยากได้ไอเดียอีกไอเดียเช่นกันครับ

ปล.เปิด Form ที่ชื่อ frmEditWork ก่อนนะครับ โดยตัว Code จริงๆจะอยู่ที่ frmSubEditWork2(เป็น SubForm ของ frmEditWork) ตรง Before Update

ปล. ไฟล์ผมมันจะเละๆหน่อยนะครับ โดยเฉพาะมี Code ที่ Comment ไว้เยอะ แต่ยังไม่ได้ลบออก

ขอบคุณมากๆครับท่าน



โค๊ด: [Select]
Private Sub Form_BeforeUpdate(Cancel As Integer)
   Dim ctl As Control
   Dim msg As String
   On Error GoTo Err_BeforeUpdate
   If Me.Dirty Then
   msg = (MsgBox("คุณต้องการบันทึกหรือไม่?", vbYesNo + vbQuestion, "Save Record"))
   If msg = vbNo Then
         Me.Undo
    End If
   If msg = vbYes Then
         Modified = Now()
         ModifiedBy = CurrentUser()
      End If
   End If
Exit_BeforeUpdate:
   Exit Sub
Err_BeforeUpdate:
   MsgBox Err.Number & " " & Err.Description
   Resume Exit_BeforeUpdate
End Sub


ยุบรวมกัน เมื่อเราตอบ Yes ครับ
โดย จากโค้ดนี้ ต้องมี textbox  Modified และ ModifiedBy ไว้รับค่าด้วยนะ
         Modified = Now()   คือ Modified จะจัดเก็บวันที่และเวลาตอนนั้นไว้
         ModifiedBy = CurrentUser() คือ ModifiedBy จะจัดเก็บUser ที่ทำการบันทึก

แต่ปกติผมไม่ค่อยใช้ Event BeforeUpdate ครับ
ถ้ามีตัวอย่างผมเพิ่มเติมให้ได้ครับ

 

27 ก.พ. 63 , 10:55:31
ตอบกลับ #5

PNR

อ่อข้อมูลเป็น Bound Control อยู่แล้ว งั้นก็ทำแบบนี้ละครับ
:meaw: :grin: :shout:
 
โพสต์นี้ได้รับคำขอบคุณจาก: Tatchawin

27 ก.พ. 63 , 11:39:43
ตอบกลับ #6

Tatchawin

ท่านครับ สำหรับรวม Code Case ที่ 2 นี้ ผมลอง Test หลายๆกรณี เช่น โดยการกด Save หน้าฟอร์มเปล่าๆ โดยที่ยังไม่ได้กรอกอะไรลงในฟอร์มเลยแล้ว ทีแรก ก็ใช้ได้ครับ พอมาลอง Save Form เปล่าๆ ใหม่อีกที มันขึ้น "Run Time Error 94 Invalid use of Null" (จริงๆต้องขึ้น Popup ฟ้องว่าฟอร์มว่าง พร้อมกับ textbox และ Combobox ต้องเปลี่ยนสี ถ้าหากเป็นค่าว่าง)
รวบกวนท่านช่วยดูให้ผมได้ไหมครับ

ปล. Code จะอยู่ที่หน้า frmInput ตรง Onclick ตรงปุ่ม Save นะครับ ขอบคุณมากครับ

Private Sub cmdSave_Click()
Dim ctrl As Control
Dim mSave As Boolean
Dim DontDuplicate As String
Dim str1 As String

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
   
   
'---------------------------------------------------------------------------------------------
'CheckDuplicate
   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
       exit sub
   else
' Insert into table
   If IsNull(str) Or str = "" Then
           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 if
End Sub

 

27 ก.พ. 63 , 13:20:26
ตอบกลับ #7

PNR

ลองดูครับ

โค๊ด: [Select]
Private Sub cmdSave_Click()
Dim ctrl As Control
Dim mSave As Boolean
Dim DontDuplicate As String
Dim str1 As String
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
   
        DontDuplicate = Me.txtNationalID1.Value
        str1 = "[NationalID]=" & "'" & DontDuplicate & "'"
       
        If Me.txtNationalID1 = DLookup("[NationalID]", "tblContractor", str1) Then
        MsgBox "A record " & txtNationalID1 & " is duplicate. " & "Please check record.", vbInformation
        Me.Undo
        Exit Sub
    Else
        '---------------------------------------------------------------------------------------------
' 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
    End If     
     Else
     MsgBox "Please enter data for all the required fields", vbInformation + vbOKOnly, "Form is not fullfilled completly"
        Exit Sub
           End If
End Sub
:meaw: :grin: :shout:
 
โพสต์นี้ได้รับคำขอบคุณจาก: Tatchawin

28 ก.พ. 63 , 15:19:48
ตอบกลับ #8

Tatchawin

ขอบคุณมากๆครับ ทำได้แล้วครับ

 


บอร์ดเรียนรู้ Access สำหรับคนไทย