¾×鹰ҹ㹡ÒÃãªé§Ò¹ MS FlexGrid ¡è͹ÊÃéÒ§¤ÇÒÁÊÑÁ¾Ñ¹¸ìẺ One To Many (ÀÒ¤ 2)

Option Explicit

Global ConnDB As New ADODB.Connection
Global RS As New ADODB.Recordset
Global DS As New ADODB.Recordset
Global Statement As String
Global SQLStmt As String
'
' ¡Ó˹´ÇèÒà»ç¹¡ÒÃà¾ÔèÁ ËÃ×Í á¡é䢢éÍÁÙÅ
Global blnNewData As Boolean
' ãËéà¡Ô´¡Òà Update 㹿ÍÃìÁ·ÕèÁÕ¡ÒÃà»ÅÕè¹á»Å§
Global FormUpdate As Boolean

' àª×èÍÁà¢éÒÊÙèä¿Åì°Ò¹¢éÍÁÙÅ MS Access
Public Sub OpenDataBase()
On Error GoTo Err_Handler
Dim DB_File As String
    DB_File = App.Path
    If Right$(DB_File, 1) <> "\" Then DB_File = DB_File & "\"
    DB_File = DB_File & "ProductDB.MDB"
    ' Open a connection.
    Set ConnDB = New ADODB.Connection
    ConnDB.ConnectionString = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & DB_File & ";" & _
        "Persist Security Info=False"
    ConnDB.Open
    Exit Sub
    
Err_Handler:
    MsgBox "Error : " & Err.Number & " " & Err.Description
    End
End Sub

Public Sub CloseDataBase()
    ' µÃǨÊͺÇèÒÁÕ¡ÒÃàª×èÍÁ⧠- Connect ¢éÍÁÙÅËÃ×ÍäÁè
    If ConnDB.State = adStateOpen Then
        ConnDB.Close
        Set ConnDB = Nothing
    End If
End Sub

' ======================================================
' ¡ÒõÑ駤èҤسÊÁºÑµÔ¢Í§ MS FlexGrid ã¹ÅѡɳТͧ Run Time
' ======================================================
Sub SetupGrid()
    With fgData
        .FixedRows = 1
        .FixedCols = 0
        ' ¡Ó˹´ 7 ËÅÑ¡
        .Cols = 7
        ' ¡Ó˹´ 1 á¶Ç (੾ÒÐ Column Header)
        .Rows = 1
        .ColWidth(0) = 0
        .ColWidth(1) = .Width \ 6 - 100
        .ColWidth(2) = .Width \ 6 + 250
        .ColWidth(3) = .Width \ 6 - 200
        .ColWidth(4) = .Width \ 6 - 100
        .ColWidth(5) = .Width \ 6 - 200
        .ColWidth(6) = .Width \ 6
        
        .TextMatrix(0, 0) = "PK"
        .TextMatrix(0, 1) = "ÃËÑÊÊÔ¹¤éÒ"
        .TextMatrix(0, 2) = "ª×èÍÊÔ¹¤éÒ"
        .TextMatrix(0, 3) = "˹èǹѺ"
        .TextMatrix(0, 4) = "ÃÒ¤ÒÊÔ¹¤éÒ"
        .TextMatrix(0, 5) = "¨Ó¹Ç¹" ' »é͹¢éÍÁÙÅä´éã¹ËÅÑ¡¹Õé
        .TextMatrix(0, 6) = "ÃÇÁ¨Ó¹Ç¹à§Ô¹"
    End With
    
End Sub

' ======================================================
' ¡ÒÃà¡Ô´à˵ءÒóì¤é¹ËÒ¢éÍÁÙÅÃËÑÊÊÔ¹¤éÒ â´Â¡Òá´»ØèÁ Enter
' ======================================================
Private Sub txtSearch_KeyPress(KeyAscii As Integer)
    If KeyAscii = vbKeyReturn Then
        Call CheckDataRow
    End If
End Sub

' ======================================================
' ¡ÒõÃǨÊͺ¤èÒã¹ MS FlexGrid ¡è͹·Õè¨ÐáÊ´§¼ÅÃÒ¡ÒÃãËÁè
' ======================================================
Private Sub CheckDataRow()
    Dim CurrentRow As Integer

    ' µÃǨÊͺÇèÒà»ç¹¤èÒÇèÒ§ËÃ×ÍäÁè ËÒ¡ãªè¡çÍÍ¡¨Ò¡â»Ãá¡ÃÁÂèÍ (User äÁèÁÕ¡ÒäÕÂì¢éÍÁÙÅã´æ)
    If Trim(txtSearch.Text) = "" Or Len(Trim(txtSearch.Text)) = 0 Then
        txtSearch.SetFocus
        Exit Sub
    End If

    ' ¤é¹ËÒ¢éÍÁÙÅÃËÑÊÊÔ¹¤éÒã¹µÒÃÒ§ÊÔ¹¤éÒ (tblProduct)
    ' ºÒ§¤¹ÍÒ¨¨ÐäÁèà¢éÒã¨ã¹Êèǹ¹Õé ¢ÍãËé¡ÅѺä»ÍèÒ¹º·¤ÇÒÁàÃ×èͧ VB6 ¡Ñº Access
    ' µÑé§áµèáá¡è͹¹èФÃѺ à¾ÃÒйÕèäÁèä´éÁҨҡ˹ѧÊ×Í áµèÁÒ¨Ò¡»ÃÐʺ¡ÒóìÅéǹæ¢Í§¼Á
    Set RS = New Recordset
    Statement = "SELECT tblProduct.ProductPK, tblProduct.ProductCode, " & _
                       " tblProduct.Description, tblUnit.UnitName, tblProduct.PriceUnit " & _
                       " FROM tblProduct INNER JOIN tblUnit ON tblProduct.UnitFK = tblUnit.UnitPK " & _
                       " WHERE ProductCode = " & "'" & Trim$(txtSearch.Text) & "'"
    
    ' à¤ÅÕÂÃì¤èÒäÇéÃÍàÅÂ
    txtSearch.Text = ""
    
    RS.CursorLocation = adUseClient
    ' ¡Òäé¹ËÒ¢éÍÁÙŵéͧãªé¡ÒÃÍèÒ¹à´Ô¹Ë¹éÒÍÂèÒ§à´ÕÂÇ ¨Ð·ÓãËé¡ÒÃà¢éÒ¶Ö§¢éÍÁÙÅä´éàÃçÇ¢Öé¹ ... ÍÂèÒÅ×Á
    RS.Open Statement, ConnDB, adOpenForwardOnly, adLockReadOnly, adCmdText
    ' äÁ辺¢éÍÁÙÅ ãËéÍÍ¡¨Ò¡â»Ãá¡ÃÁÂèÍÂä»ä´éàÅÂ
    If RS.RecordCount = 0 Then
        RS.Close:   Set RS = Nothing
        Exit Sub
    End If
    
    
    ' àÃÔèÁµé¹¡Òäé¹ËÒ¢éÍÁÙÅã¹µÒÃÒ§¡ÃÔ´
    For CurrentRow = 1 To fgData.Rows - 1
        ' ¤é¹ËÒÃËÑÊÊÔ¹¤éÒ·ÕèÍÂÙèã¹ËÅÑ¡·Õè 1
        If fgData.TextMatrix(CurrentRow, 1) = RS("ProductCode") Then
            
            ' ËÒ¡¾ºÃÒ¡ÒÃÊÔ¹¤éÒà´ÔÁãÊè¨Ó¹Ç¹ÊÔ¹¤éÒà¾ÔèÁà¢éÒä»ÍÕ¡ 1 ·Ñ¹·Õ ã¹á¶Ç·ÕèÍÂÙè ³ »Ñ¨¨ØºÑ¹
            fgData.TextMatrix(CurrentRow, 5) = Val(fgData.TextMatrix(CurrentRow, 5)) + 1
            
            ' ¤Ù³ÃÒ¤ÒÊÔ¹¤éÒ ¡Ñº¨Ó¹Ç¹ÊÔ¹¤éÒà¢éÒä»ãËÁè
            fgData.TextMatrix(CurrentRow, 6) = Format(Val(fgData.TextMatrix(CurrentRow, 4)) * _
                            Val(fgData.TextMatrix(CurrentRow, 5)), "0.00")
                            
            RS.Close:   Set RS = Nothing
            
            ' ä»â»Ãá¡ÃÁÂèÍÂà¾×èͤӹdz¨Ó¹Ç¹à§Ô¹·Ñé§ËÁ´
            Call CalTotalAmount

            ' ¨º¨Ò¡¡ÒÃǹÃͺ FOR áÅÐ ÍÍ¡¨Ò¡â»Ãá¡ÃÁÂèÍÂä»àŤÃѺ
            Exit Sub
        End If
    Next
    
    ' =================================================
    ' ¡ÒùÓÃÒÂÅÐàÍÕ´¢Í§ÊÔ¹¤éÒà¢éÒÊÙèµÒÃÒ§¡ÃÔ´¹Ñè¹àͧ
    ' =================================================
    ' à¾ÔèÁ¨Ó¹Ç¹á¶Çã¹ MS FlexGrid ÍÕ¡ 1 á¶Ç
    fgData.Rows = fgData.Rows + 1
    
    ' ãËéá¶Ç»Ñ¨¨ØºÑ¹Å´Å§ 1 (à¾×èÍäÁèãËé¹Ñº Column Header)
    CurrentRow = fgData.Rows - 1
    
    ' á¶ÇãËÁè·Õèà¾ÔèÁà¢éÒÁÒ ·Ó¡ÒÃÍèÒ¹¢éÍÁÙŨҡ RecordSet à¢éÒÁÒ
    ' »¡µÔËÅÑ¡áá (ËÅÑ¡ 0) µéͧ¹ÓàÍÒ¤èÒ Primary Key 仫è͹àÍÒäÇé¹èФÃѺ ... ÍÂèÒÅ×Á
    fgData.TextMatrix(CurrentRow, 0) = RS("ProductPK")
    ' áÊ´§ÃËÑÊÊÔ¹¤éÒ
    fgData.TextMatrix(CurrentRow, 1) = "" & RS("ProductCode")
    ' áÊ´§ª×èÍÊÔ¹¤éÒ
    fgData.TextMatrix(CurrentRow, 2) = "" & RS("Description")
    ' áÊ´§Ë¹èǹѺÊÔ¹¤éÒ
    fgData.TextMatrix(CurrentRow, 3) = "" & RS("UnitName")
    ' áÊ´§ÃÒ¤ÒÊÔ¹¤éÒ
    fgData.TextMatrix(CurrentRow, 4) = Format(RS("PriceUnit"), "0.00")
    ' ãÊè¨Ó¹Ç¹ÊÔ¹¤éÒ¤ÃÑé§ÅÐ 1
    fgData.TextMatrix(CurrentRow, 5) = 1
    ' ¤Ó¹Ç³ÃÒ¤Ò¢Ò ¡Ñº ¨Ó¹Ç¹
    fgData.TextMatrix(CurrentRow, 6) = Format(fgData.TextMatrix(CurrentRow, 4) * _
                                fgData.TextMatrix(CurrentRow, 5), "0.00")
    
    RS.Close:   Set RS = Nothing
    
    ' ä»â»Ãá¡ÃÁÂèÍÂà¾×èͤӹdz¨Ó¹Ç¹à§Ô¹·Ñé§ËÁ´
    Call CalTotalAmount
    
End Sub

' =============================================
' ¤Ó¹Ç³ËÒ¼ÅÃÇÁ¢Í§ÃÒ¤ÒÊÔ¹¤éÒ·Ñé§ËÁ´
' =============================================
Sub CalTotalAmount()

    ' ËÒ¡äÁèÁÕÊÑ¡ÃÒ¡ÒáçãËéÍÍ¡¨Ò¡â»Ãá¡ÃÁÂèÍÂä»àÅÂ
    If fgData.Rows = 1 Then Exit Sub
    
    Dim i As Integer
    Dim TotalAmount As Double
    
    ' ¹Ñºä»µÒÁ¨Ó¹Ç¹á¶Ç·ÕèÁÕÍÂÙè»Ñ¨¨ØºÑ¹¢Í§µÒÃÒ§¡ÃÔ´ ¨¹¡ÇèÒ¨ÐËÁ´
    ' áÅéÇÃÇÁ¤èÒã¹ËÅÑ¡·Õè 6 äÇéã¹µÑÇá»ÃàÇÕ¹á·Õ¹ TotalAmount ... 55555+
    For i = 1 To fgData.Rows - 1
        TotalAmount = TotalAmount + Val(fgData.TextMatrix(i, 6))
    Next
    
    lblTotalAmount.Caption = Format(TotalAmount, "#,##0.00")
    
End Sub

' =============================================
' ¾Ô¨ÒóҨҡà˵ءÒóì (Event) 㹡Òà Focus µÃ§µÓá˹è§ã¹ Cell
' =============================================
Sub fgData_EnterCell()
Select Case fgData.Col ' àÅ×Í¡ Column ·Õèµéͧ¡ÒÃ
    Case 0, 1, 2, 3, 4, 6: ' àÃÒäÁèä´é»é͹¢éÍÁÙÅã¹ËÅÑ¡·ÕèÃкØ
        txtData.Visible = False ' ¨Ö§µéͧÊÑè§ãËé»Ô´¡ÒÃÁͧàËç¹ txtData

        
    Case 5: ' âÍठ¶Ù¡µéͧ Cell ·Õè¡ÓÅѧ Focus ÁÒÍÂÙèËÅÑ¡·Õè 5 ¡çàÃÔèÁµé¹¡Ãкǹ¡Ò÷ӧҹä´é
        txtData.Visible = True ' à»Ô´ãËé txtData ÁͧàËç¹ä´é
        txtData.Text = fgData.Text ' ¤èÒà´ÔÁ·ÕèÍÂÙèã¹ Cell ¨Ð¶Ù¡Ê觵èÍä»ãËé txtData à¾×èÍ·Ó¡ÒÃá¡é䢵èÍä»

        ' ¢Ñ鹵͹¹Õé¤×Í¡ÒÃàÅ×è͹µÓá˹觢ͧ txtData ãËé价ѺÍÂÙ躹µÓá˹觢ͧ Cell (ËÅÑ¡·Õè¡Ó˹´) µÒÁ·ÕèàÃÒµéͧ¡ÒÃ
        ' ¡ÒÃà¤Å×è͹·Õè â´Â fgData.Move µÓá˹觷ҧ«éÒÂ, µÓá˹觺¹, ¤ÇÒÁ¡ÇéÒ§¢Í§à«ÅÅì, ¤ÇÒÁÊÙ§¢Í§à«ÅÅì
        txtData.Move fgData.CellLeft + 60, fgData.Top + fgData.CellTop, fgData.CellWidth, fgData.CellHeight

        ' àÁ×èÍ txtData à¤Å×è͹·Õè价ѺµÓáË¹è§ Cell ·ÕèàÃÒµéͧ¡ÒÃáÅéÇ ãËé Focus ä»·Õè txtData à¾×èÍ·Õè¨ÐÊÒÁÒöá¡é䢢éÍÁÙÅŧ㹠TextBox ä´é
        txtData.SetFocus
End Select

End Sub

' àÁ×èͤèÒã¹ TextBox à»ÅÕè¹ ¤èÒã¹ Cell ·Õè TextBox ·ÑºàÍÒäÇé ¡çµéͧà»ÅÕè¹µÒÁ
Private Sub txtData_Change()
    Dim sRow As Integer
    ' ËÒ¤èÒµÓá˹觢ͧá¶Ç
    sRow = fgData.Row
    
    ' àÁ×èÍ txtData à¡Ô´¡ÒÃà»ÅÕè¹á»Å§ ¡ç¨ÐÊ觤èÒãËé¡Ñº Cell ·ÕèàÃÒµéͧ¡ÒôéÇÂ
    ' áµè ³ ¢³Ð¹ÕéàÃÒÂѧÁͧäÁèàËç¹ËÃÍ¡¤ÃѺ à¾ÃÒÐ txtData Áѹ·Ñº Cell äÇéÍÂÙè -- ÊÓÁФѭ¹èеÑǹÕé
    ' ¤èÒã¹ TextBox ¨ÐµéͧäÁèà»ç¹¤èÒÇèÒ§ ËÃ×Í 0 ... ËÒ¡à»ç¹ãÊè 1 ãËéÁѹ«èÐ
    If Trim(txtData.Text) = "" Or Len(Trim(txtData.Text)) = 0 Or txtData.Text = "0" Then txtData.Text = "1"
    
    fgData.TextMatrix(sRow, 5) = txtData.Text
    ' ¤Ó¹Ç³ÃÒ¤Ò áÅÐ ¨Ó¹Ç¹ÊÔ¹¤éÒ
    fgData.TextMatrix(sRow, 6) = Format(fgData.TextMatrix(sRow, 4) * fgData.TextMatrix(sRow, 5), "0.00")

    ' ä»â»Ãá¡ÃÁÂèÍÂà¾×èͤӹdz¨Ó¹Ç¹à§Ô¹·Ñé§ËÁ´
    Call CalTotalAmount

End Sub

Private Sub txtData_GotFocus()
    ' â»Ãá¡ÃÁÂèÍÂ㹡ÒÃ·Ó High Light ã¹ TextBox
    Call HLText(txtData)
End Sub

' ¡Òôѡ¤ÕÂì·Õ衴ŧº¹ TextBox
Private Sub txtData_KeyDown(KeyCode As Integer, Shift As Integer)
    Select Case KeyCode
        Case vbKeyDown
            SendKeys "{DOWN}"
            Call txtData_KeyPress(vbKeyReturn)
        Case vbKeyUp
            SendKeys "{UP}"
            Call txtData_KeyPress(vbKeyReturn)
        Case vbKeyEscape
            Call txtData_KeyPress(vbKeyReturn)
    End Select
End Sub

Private Sub txtData_KeyPress(KeyAscii As Integer)
    If KeyAscii = vbKeyReturn Then
        txtData.Visible = False
        KeyAscii = 0
    Else
        ' µÃǨÊͺ¡Òá´¤ÕÂìµÑÇàÅ¢ä´éà·èÒ¹Ñé¹
        KeyAscii = CheckDigitOnly(KeyAscii)
    End If

End Sub

' ==========================================================
' ¾Ç¡¿Ñ§¤ìªÑè¹àËÅèÒ¹ÕéàÃÒµéͧãªé§Ò¹ºèÍÂæ ¤ÇùÓä»à¡çºäÇéã¹ Module ¨Ð´Õ¡ÇèÒ¹èФÃѺ
' ==========================================================
' µÃǨÊͺ¡Òûé͹¤èÒä´é੾ÒеÑÇàÅ¢
Function CheckDigitOnly(Index As Integer) As Integer
    Select Case Index
        Case 48 To 57 ' 0 - 9
        Case 8            ' Back Space
        Case 13         ' Enter
        Case Else
            Index = 0
    End Select
    CheckDigitOnly = Index
End Function

' â»Ãá¡ÃÁÂèÍÂ㹡ÒÃ·Ó High Light ã¹ TextBox ... ãªéÃèÇÁ¡Ñº GotFocus
Public Sub HLText(ByRef sText)
    On Error Resume Next
    With sText
        .SelStart = 0
        .SelLength = Len(sText.Text)
    End With
End Sub