VERSION 5.00 Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "MSFLXGRD.OCX" Object = "{F0D2F211-CCB0-11D0-A316-00AA00688B10}#1.0#0"; "MSDATLST.OCX" Begin VB.Form sum_journal Caption = "โปรแกรมรวมวารสาร" ClientHeight = 8490 ClientLeft = 60 ClientTop = 450 ClientWidth = 11880 LinkTopic = "Form2" MDIChild = -1 'True ScaleHeight = 8490 ScaleWidth = 11880 WindowState = 2 'Maximized Begin MSDataListLib.DataList list_namesumj Height = 2700 Left = 2760 TabIndex = 22 Top = 5160 Visible = 0 'False Width = 7335 _ExtentX = 12938 _ExtentY = 4763 _Version = 393216 BackColor = 12648384 ForeColor = 12582912 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "MS Sans Serif" Size = 9.75 Charset = 222 Weight = 700 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty End Begin VB.CommandButton Cmdadd BackColor = &H00C0C0FF& Caption = "Add" Enabled = 0 'False BeginProperty Font Name = "MS Sans Serif" Size = 9.75 Charset = 222 Weight = 700 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Height = 615 Left = 10080 Style = 1 'Graphical TabIndex = 19 Top = 4800 Width = 1575 End Begin VB.CommandButton Cmdedit BackColor = &H00C0C0FF& Caption = "Edit" Enabled = 0 'False BeginProperty Font Name = "MS Sans Serif" Size = 9.75 Charset = 222 Weight = 700 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Height = 615 Left = 10080 Style = 1 'Graphical TabIndex = 18 Top = 5400 Width = 1575 End Begin VB.CommandButton Cmdsave BackColor = &H00FFFFC0& Caption = "Save" Enabled = 0 'False BeginProperty Font Name = "MS Sans Serif" Size = 9.75 Charset = 222 Weight = 700 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Height = 615 Left = 10080 Style = 1 'Graphical TabIndex = 4 Top = 6000 Width = 1575 End Begin VB.Frame Frame5 Caption = "รายการรวมเล่มวารวาร" BeginProperty Font Name = "MS Sans Serif" Size = 9.75 Charset = 222 Weight = 700 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty ForeColor = &H00C000C0& Height = 2055 Left = 840 TabIndex = 11 Top = 4440 Width = 8775 Begin MSFlexGridLib.MSFlexGrid sum_item Height = 1575 Left = 120 TabIndex = 13 Top = 360 Width = 8295 _ExtentX = 14631 _ExtentY = 2778 _Version = 393216 Rows = 6 Cols = 7 FixedCols = 0 BackColor = 12640511 ForeColor = 12582912 Enabled = 0 'False FocusRect = 0 HighLight = 2 ScrollBars = 2 SelectionMode = 1 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "MS Sans Serif" Size = 8.25 Charset = 222 Weight = 700 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty End End Begin VB.Frame Frame4 Caption = "ฉบับที่ได้รับ" BeginProperty Font Name = "MS Sans Serif" Size = 9.75 Charset = 222 Weight = 700 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty ForeColor = &H00FF00FF& Height = 3615 Left = 5640 TabIndex = 8 Top = 840 Width = 6135 Begin MSFlexGridLib.MSFlexGrid show_item Height = 3255 Left = 120 TabIndex = 10 Top = 240 Width = 5895 _ExtentX = 10398 _ExtentY = 5741 _Version = 393216 Rows = 14 Cols = 6 FixedCols = 0 BackColor = 12648447 Enabled = 0 'False FocusRect = 0 HighLight = 2 SelectionMode = 1 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "MS Sans Serif" Size = 8.25 Charset = 222 Weight = 700 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty End End Begin VB.Frame Frame3 Caption = "รายชื่อวารสาร" BeginProperty Font Name = "MS Sans Serif" Size = 9.75 Charset = 222 Weight = 700 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty ForeColor = &H00FF00FF& Height = 3615 Left = 120 TabIndex = 7 Top = 840 Width = 5415 Begin MSFlexGridLib.MSFlexGrid show_journal Height = 3255 Left = 120 TabIndex = 9 Top = 240 Width = 5175 _ExtentX = 9128 _ExtentY = 5741 _Version = 393216 Rows = 14 Cols = 4 FixedCols = 0 BackColor = 16777152 Enabled = 0 'False FocusRect = 0 HighLight = 2 SelectionMode = 1 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "MS Sans Serif" Size = 8.25 Charset = 222 Weight = 700 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty End End Begin VB.CommandButton Command5 BackColor = &H00FFC0C0& Caption = "Exit" BeginProperty Font Name = "MS Sans Serif" Size = 9.75 Charset = 222 Weight = 700 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Height = 615 Left = 10080 MaskColor = &H00FFFFC0& Style = 1 'Graphical TabIndex = 6 Top = 7200 Width = 1575 End Begin VB.CommandButton Cmdcancel BackColor = &H00FFFFC0& Caption = "Cancel" Enabled = 0 'False BeginProperty Font Name = "MS Sans Serif" Size = 9.75 Charset = 222 Weight = 700 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Height = 615 Left = 10080 MaskColor = &H00FFFFC0& Style = 1 'Graphical TabIndex = 5 Top = 6600 Width = 1575 End Begin VB.Frame Frame2 ForeColor = &H00C000C0& Height = 1455 Left = 840 TabIndex = 3 Top = 6480 Width = 8775 Begin VB.CommandButton Cmdprevious BackColor = &H00C0FFC0& Caption = "Previous" Enabled = 0 'False BeginProperty Font Name = "MS Sans Serif" Size = 8.25 Charset = 222 Weight = 700 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Height = 375 Left = 7080 Style = 1 'Graphical TabIndex = 24 Top = 600 Width = 1575 End Begin VB.CommandButton Cmdnext BackColor = &H00C0FFC0& Caption = "Next" Enabled = 0 'False BeginProperty Font Name = "MS Sans Serif" Size = 8.25 Charset = 222 Weight = 700 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Height = 375 Left = 7080 Style = 1 'Graphical TabIndex = 23 Top = 240 Width = 1575 End Begin VB.CommandButton clear_sumj BackColor = &H00C0E0FF& Caption = "Clear" BeginProperty Font Name = "MS Sans Serif" Size = 8.25 Charset = 222 Weight = 700 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Height = 375 Left = 7080 Style = 1 'Graphical TabIndex = 21 Top = 960 Width = 1575 End Begin VB.CommandButton search_sumj BackColor = &H00C0E0FF& Caption = "Search" BeginProperty Font Name = "MS Sans Serif" Size = 8.25 Charset = 222 Weight = 700 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Height = 375 Left = 4080 Style = 1 'Graphical TabIndex = 20 Top = 120 Visible = 0 'False Width = 1575 End Begin VB.TextBox Text3 BackColor = &H00C0FFFF& BeginProperty Font Name = "MS Sans Serif" Size = 9.75 Charset = 222 Weight = 700 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty ForeColor = &H00C00000& Height = 735 Left = 1920 MaxLength = 255 MultiLine = -1 'True ScrollBars = 2 'Vertical TabIndex = 17 Top = 600 Width = 4815 End Begin VB.TextBox Text2 BackColor = &H00C0C0C0& BeginProperty Font Name = "MS Sans Serif" Size = 9.75 Charset = 222 Weight = 700 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty ForeColor = &H00C00000& Height = 285 Left = 1920 TabIndex = 16 Top = 240 Width = 1575 End Begin VB.Label Label2 Alignment = 1 'Right Justify Caption = "สันวารสาร" BeginProperty Font Name = "MS Sans Serif" Size = 9.75 Charset = 222 Weight = 700 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty ForeColor = &H00C00000& Height = 255 Left = 360 TabIndex = 15 Top = 600 Width = 1455 End Begin VB.Label Label1 Alignment = 1 'Right Justify Caption = "เลขทะเบียนรวมเล่ม" BeginProperty Font Name = "MS Sans Serif" Size = 9.75 Charset = 222 Weight = 700 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty ForeColor = &H00C00000& Height = 255 Left = 120 TabIndex = 14 Top = 240 Width = 1695 End End Begin VB.Frame Frame1 Caption = "ป้อนรายชื่อวารสาร" BeginProperty Font Name = "MS Sans Serif" Size = 9.75 Charset = 222 Weight = 700 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty ForeColor = &H00FF00FF& Height = 735 Left = 360 TabIndex = 0 Top = 120 Width = 11175 Begin VB.CommandButton Cmdclear BackColor = &H00FFFFC0& Caption = "Clear" BeginProperty Font Name = "MS Sans Serif" Size = 8.25 Charset = 222 Weight = 700 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Height = 375 Left = 9120 Style = 1 'Graphical TabIndex = 12 Top = 240 Width = 1695 End Begin VB.CommandButton Cmdsearch BackColor = &H00FFFFC0& Caption = "Search" BeginProperty Font Name = "MS Sans Serif" Size = 8.25 Charset = 222 Weight = 700 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Height = 375 Left = 7320 Style = 1 'Graphical TabIndex = 2 Top = 240 Width = 1695 End Begin VB.TextBox Text1 BackColor = &H00C0FFC0& BeginProperty Font Name = "MS Sans Serif" Size = 8.25 Charset = 222 Weight = 700 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Height = 375 Left = 720 TabIndex = 1 Top = 240 Width = 6135 End End End Attribute VB_Name = "sum_journal" Attribute VB_GlobalNameSpace = False Attribute VB_Creatable = False Attribute VB_PredeclaredId = True Attribute VB_Exposed = False Option Explicit Dim Conn As New ADODB.Connection Dim magazinetb As New ADODB.Recordset Dim list_itemtb As New ADODB.Recordset Dim ctrl_subjtb As New ADODB.Recordset Dim sum_journaltb As New ADODB.Recordset Dim chk_pasa As String Dim sta_buttom As String Dim sumRow As Integer Dim name_sumj As String Dim mem_pointer As String Private Sub clear_sumj_Click() Call cls_sum_item Cmdsearch.Enabled = True Cmdclear.Enabled = True Cmdedit.Enabled = False Cmdnext.Enabled = False Cmdprevious.Enabled = False Text2.Text = "" Text2.Locked = True Text3.Text = "" Text3.BackColor = &HC0FFFF Text3.Locked = False Text3.SetFocus End Sub Private Sub Cmdadd_Click() Dim n As Integer Dim s_point As String With ctrl_subjtb If .State = adStateOpen Then .Close .CursorLocation = adUseClient .Open "select regno_ctrl from ctrlsumj ", Conn, adOpenForwardOnly, adLockReadOnly If Not .EOF Then s_point = Trim(Str(.Fields(0).Value)) For n = 1 To 6 - Len(s_point) s_point = "0" + s_point Next n Text2.Text = "B" + s_point .Close End With sumRow = 1 Text2.Locked = True Text3.BackColor = &HC0FFFF Text3.Locked = False show_item.Enabled = True sum_item.Enabled = True Cmdsave.Enabled = True Cmdcancel.Enabled = True Cmdadd.Enabled = False sta_buttom = "New" End Sub Private Sub Cmdedit_Click() Dim line_item As Integer Dim mem_magno As String sta_buttom = "Edit" Text3.BackColor = &HC0FFFF Text3.Locked = False sum_item.Enabled = True show_item.Enabled = True Cmdedit.Enabled = False search_sumj.Enabled = False clear_sumj.Enabled = False Cmdsave.Enabled = True Cmdcancel.Enabled = True Cmdnext.Enabled = False Cmdprevious.Enabled = False line_item = 1 Call cls_show_listitem If Left(mem_pointer, 1) = "I" Then chk_pasa = "Thai" ElseIf Left(mem_pointer, 1) = "J" Then chk_pasa = "Eng" End If Set list_itemtb = New ADODB.Recordset With list_itemtb If .State = adStateOpen Then .Close .ActiveConnection = Conn .CursorType = adOpenForwardOnly .CursorLocation = adUseClient .Open "select * from tranfile Where pointer='" & mem_pointer & "' and MagStatus <>'Z' order by daily desc" 'and trim(MagStatus) <>'Z' If Not .EOF Then With show_item .Rows = list_itemtb.RecordCount + 1 list_itemtb.MoveFirst Do While Not list_itemtb.EOF .TextMatrix(line_item, 0) = list_itemtb.Fields(0).Value .TextMatrix(line_item, 1) = Format(list_itemtb.Fields(3).Value, "dd/mm/yyyy") .TextMatrix(line_item, 2) = list_itemtb.Fields(4).Value .TextMatrix(line_item, 3) = list_itemtb.Fields(5).Value .ColAlignment(4) = 0 .TextMatrix(line_item, 4) = list_itemtb.Fields(6).Value .TextMatrix(line_item, 5) = Format(list_itemtb.Fields(2).Value, "dd/mm/yyyy") line_item = line_item + 1 list_itemtb.MoveNext Loop End With End If .Close End With Set list_itemtb = Nothing Set magazinetb = New ADODB.Recordset With magazinetb If .State = adStateOpen Then .Close .CursorLocation = adUseClient .Open "select Magazine_Name from magazine Where pointer='" & mem_pointer & "'", Conn, adOpenForwardOnly, adLockReadOnly If Not .EOF Then name_sumj = magazinetb.Fields(0).Value .Close End With Set magazinetb = Nothing End Sub Private Sub Cmdnext_Click() Dim n As Integer Dim s_point As String Dim max_point As Integer With ctrl_subjtb If .State = adStateOpen Then .Close .CursorLocation = adUseClient .Open "select regno_ctrl from ctrlsumj ", Conn, adOpenForwardOnly, adLockReadOnly If Not .EOF Then max_point = .Fields(0).Value .Close End With If max_point > Val(Right(Text2.Text, 6)) + 1 Then s_point = Trim(Str(Val(Right(Text2.Text, 6)) + 1)) For n = 1 To 6 - Len(s_point) s_point = "0" + s_point Next n Text2.Text = "B" + s_point Call search_sumj_Click Else MsgBox "เลขทะเบียนรวมเล่มวารสาร สุดท้ายแล้ว", vbCritical + vbOKOnly, "End of File" End If End Sub Private Sub Cmdprevious_Click() Dim n As Integer Dim s_point As String If Val(Right(Text2.Text, 6)) - 1 > 0 Then s_point = Trim(Str(Val(Right(Text2.Text, 6)) - 1)) For n = 1 To 6 - Len(s_point) s_point = "0" + s_point Next n Text2.Text = "B" + s_point Call search_sumj_Click Else MsgBox "เลขทะเบียนรวมเล่มวารสาร เลขทะเบียนแรกแล้ว", vbCritical + vbOKOnly, "Begin of File" End If End Sub Private Sub cmdsearch_Click() Dim line_showj As Integer line_showj = 1 If Trim(Text1.Text) <> "" Then With magazinetb If .State = adStateOpen Then .Close .ActiveConnection = Conn .CursorType = adOpenForwardOnly .CursorLocation = adUseClient .Open "select magazine.pointer,magazine.Magazine_Name,magazine.department_code,department.department_thai_des from magazine,department Where magazine.Magazine_Name LIKE '%" & Text1.Text & "%' and magazine.department_code=department.department_code" If Not .EOF Then With show_journal .Rows = magazinetb.RecordCount + 1 magazinetb.MoveFirst Do While Not magazinetb.EOF .TextMatrix(line_showj, 0) = magazinetb.Fields(1).Value .TextMatrix(line_showj, 1) = magazinetb.Fields(3).Value .TextMatrix(line_showj, 2) = magazinetb.Fields(0).Value .TextMatrix(line_showj, 3) = magazinetb.Fields(2).Value line_showj = line_showj + 1 magazinetb.MoveNext Loop End With Text1.Locked = True search_sumj.Enabled = False clear_sumj.Enabled = False show_journal.Enabled = True Else MsgBox "ไม่พบรายชื่อวารสารนี้", vbCritical + vbOKOnly, "ไม่พบข้อมูล" Text1.Text = "" Text1.SetFocus End If .Close End With Else MsgBox "กรุณาพิมพ์รายชื่อวารสารที่ต้องการลงทะเบียน", vbCritical + vbOKOnly, "ข้อผิดพลาด" Text1.SetFocus End If End Sub Private Sub Cmdcancel_Click() Dim line_item As Integer If MsgBox("ต้องการยกเลิกข้อมูลการรวมเล่มหรือไม่", vbQuestion + vbOKCancel, "ยกเลิกข้อมูล") = vbOK Then If sta_buttom = "New" Then Call cls_sum_item show_item.Enabled = False sum_item.Enabled = False Cmdsave.Enabled = False Cmdcancel.Enabled = False Cmdadd.Enabled = True Text2.Text = "" Text2.Locked = False Text3.Text = "" Text3.Locked = True sumRow = 1 show_journal.HighLight = flexHighlightWithFocus show_item.HighLight = flexHighlightWithFocus Cmdsearch.Enabled = True Cmdclear.Enabled = True ElseIf sta_buttom = "Edit" Then Call cls_sum_item Set sum_journaltb = New ADODB.Recordset With sum_journaltb If .State = adStateOpen Then .Close .CursorLocation = adUseClient .Open "select * from sum_journal where regno='" & Text2.Text & "'", Conn, adOpenForwardOnly, adLockReadOnly If Not .EOF Then Text2.Text = .Fields(0).Value Text3.Text = .Fields(1).Value .Close With list_itemtb If .State = adStateOpen Then .Close .CursorLocation = adUseClient .Open "select * from tranfile Where regno='" & Text2.Text & "' order by daily asc", Conn, adOpenForwardOnly, adLockReadOnly If Not .EOF Then sum_item.Rows = .RecordCount + 1 .MoveFirst With sum_item mem_pointer = list_itemtb.Fields(1).Value list_itemtb.MoveFirst line_item = 1 Do While Not list_itemtb.EOF .TextMatrix(line_item, 0) = line_item .TextMatrix(line_item, 1) = list_itemtb.Fields(0).Value .TextMatrix(line_item, 2) = Format(list_itemtb.Fields(3).Value, "dd/mm/yyyy") .TextMatrix(line_item, 3) = list_itemtb.Fields(4).Value .TextMatrix(line_item, 4) = list_itemtb.Fields(5).Value .ColAlignment(4) = 0 .TextMatrix(line_item, 5) = list_itemtb.Fields(6).Value .TextMatrix(line_item, 6) = Format(list_itemtb.Fields(2).Value, "dd/mm/yyyy") line_item = line_item + 1 list_itemtb.MoveNext Loop End With End If .Close End With End If End With sumRow = line_item Cmdedit.Enabled = True 'Cmdsearch.Enabled = False Cmdclear.Enabled = False Call cls_show_listitem show_item.Enabled = False sum_item.Enabled = False Cmdadd.Enabled = False Cmdsave.Enabled = False Cmdcancel.Enabled = False Cmdnext.Enabled = True Cmdprevious.Enabled = True clear_sumj.Enabled = True Text2.Locked = True Text3.BackColor = &HC0C0C0 Text3.Locked = True show_journal.HighLight = flexHighlightWithFocus show_item.HighLight = flexHighlightWithFocus Text3.SetFocus End If 'Command6.Enabled = False End If End Sub Private Sub Command5_Click() Unload Me End Sub Private Sub Cmdsave_Click() Dim line_item As Integer Dim sum_regno As Integer If MsgBox("ต้องการบันทึกข้อมูลวารสารเลขทะเบียนนี้หรือไม่", vbQuestion + vbOKCancel, " ยืนยันการบันทึกข้อมูล") = vbOK Then show_item.Enabled = True show_journal.Enabled = True Select Case sta_buttom Case "New" 'บันทึก Table tranfile Set list_itemtb = New ADODB.Recordset For line_item = 1 To sumRow - 1 With list_itemtb If .State = adStateOpen Then .Close .CursorLocation = adUseClient .Open "select * from tranfile where magno='" & sum_item.TextMatrix(line_item, 1) & "'", Conn, adOpenForwardOnly, adLockOptimistic If Not .EOF Then .Fields(8).Value = "Z" .Fields(14).Value = Text2.Text .Update End If .Close End With Next line_item Set list_itemtb = Nothing 'บันทึก Table sum_journal Set sum_journaltb = New ADODB.Recordset With sum_journaltb If .State = adStateOpen Then .Close .CursorLocation = adUseClient .Open "select * from sum_journal", Conn, adOpenForwardOnly, adLockOptimistic .AddNew .Fields(0).Value = Text2.Text .Fields(1).Value = Text3.Text .Fields(2).Value = "ST" .Update .Close End With Set sum_journaltb = Nothing 'บันทึก Table ctrlsumj Set ctrl_subjtb = New ADODB.Recordset With ctrl_subjtb If .State = adStateOpen Then .Close .CursorLocation = adUseClient .Open "select * from ctrlsumj", Conn, adOpenForwardOnly, adLockOptimistic .Fields(0).Value = .Fields(0).Value + 1 .Update .Close End With Set ctrl_subjtb = Nothing MsgBox "บันทึกข้อมูลเรียบร้อยแล้ว", vbInformation + vbOKOnly, "การบันทึกข้อมูล" With list_itemtb If .State = adStateOpen Then .Close .ActiveConnection = Conn .CursorType = adOpenForwardOnly .CursorLocation = adUseClient .Open "select * from tranfile Where trim(pointer)='" & show_journal.TextMatrix(show_journal.Row, 2) & "' and magstatus <>'Z' order by daily desc" If Not .EOF Then With show_item .Rows = list_itemtb.RecordCount + 1 list_itemtb.MoveFirst line_item = 1 Do While Not list_itemtb.EOF .TextMatrix(line_item, 0) = list_itemtb.Fields(0).Value .TextMatrix(line_item, 1) = Format(list_itemtb.Fields(3).Value, "dd/mm/yyyy") .TextMatrix(line_item, 2) = list_itemtb.Fields(4).Value .TextMatrix(line_item, 3) = list_itemtb.Fields(5).Value .ColAlignment(4) = 0 .TextMatrix(line_item, 4) = list_itemtb.Fields(6).Value .TextMatrix(line_item, 5) = Format(list_itemtb.Fields(2).Value, "dd/mm/yyyy") line_item = line_item + 1 list_itemtb.MoveNext Loop show_journal.HighLight = flexHighlightAlways show_item.HighLight = flexHighlightWithFocus End With Text2 = "" Text3 = "" End If .Close End With Call cls_sum_item Cmdsearch.Enabled = True Cmdclear.Enabled = True Cmdedit.Enabled = False Cmdadd.Enabled = True sumRow = 1 show_item.Enabled = False Text2.Locked = False Text3.Locked = True Case "Edit" 'บันทึก Table sum_journal Set sum_journaltb = New ADODB.Recordset With sum_journaltb If .State = adStateOpen Then .Close .CursorLocation = adUseClient .Open "select * from sum_journal where regno='" & Text2.Text & "'", Conn, adOpenForwardOnly, adLockOptimistic If Not .EOF Then .Fields(1).Value = Text3.Text .Update End If .Close End With Set sum_journaltb = Nothing 'บันทึก Table tranfile Set list_itemtb = New ADODB.Recordset For line_item = 1 To sumRow - 1 With list_itemtb If .State = adStateOpen Then .Close .CursorLocation = adUseClient .Open "select * from tranfile where magno='" & sum_item.TextMatrix(line_item, 1) & "'", Conn, adOpenForwardOnly, adLockOptimistic If Not .EOF Then .Fields(8).Value = "Z" .Fields(14).Value = Text2.Text .Update End If .Close End With Next line_item Set list_itemtb = Nothing MsgBox "บันทึกข้อมูลที่ทำการแก้ไขแล้ว", vbInformation + vbOKOnly, "ผลการบันทึก" Call cls_show_listitem show_item.Enabled = False sum_item.Enabled = False Text2.Locked = True Text3.BackColor = &HC0C0C0 Text3.Locked = True Cmdedit.Enabled = True Cmdadd.Enabled = False search_sumj.Enabled = True clear_sumj.Enabled = True Cmdnext.Enabled = True Cmdprevious.Enabled = True End Select Cmdsave.Enabled = False Cmdcancel.Enabled = False End If End Sub Private Sub Cmdclear_Click() show_journal.HighLight = flexHighlightWithFocus show_item.HighLight = flexHighlightWithFocus Call cls_show_listitem Call cls_show_journal Cmdadd.Enabled = False show_journal.Enabled = False search_sumj.Enabled = True clear_sumj.Enabled = True Text1.Locked = False Text1 = "" Text2.Locked = False Text1.SetFocus End Sub Private Sub Form_Load() With show_journal .ColWidth(0) = 3500 .ColWidth(1) = 6000 .TextMatrix(0, 0) = "ชื่อวาสาร" .TextMatrix(0, 1) = "คณะที่บอกรับ" End With With show_item .ColWidth(0) = 950 .ColWidth(1) = 1200 .ColWidth(2) = 600 .ColWidth(3) = 700 .ColWidth(4) = 5000 .TextMatrix(0, 0) = "เลขทะเบียน" .TextMatrix(0, 1) = "ประจำเดือน" .TextMatrix(0, 2) = "ปีที่" .TextMatrix(0, 3) = "เล่มที่" .TextMatrix(0, 4) = "คำอธิบาย" End With With sum_item .ColWidth(0) = 550 .ColWidth(1) = 950 .ColWidth(2) = 1200 .ColWidth(3) = 600 .ColWidth(4) = 700 .ColWidth(5) = 5000 .TextMatrix(0, 0) = "ลำดับ" .TextMatrix(0, 1) = "เลขทะเบียน" .TextMatrix(0, 2) = "ประจำเดือน" .TextMatrix(0, 3) = "ปีที่" .TextMatrix(0, 4) = "เล่มที่" .TextMatrix(0, 5) = "คำอธิบาย" End With Set Conn = New ADODB.Connection Conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _ & "SERVER=192.168.1.15;" _ & "DATABASE=magazine;" _ & "UID=chaiya;" _ & "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384 Conn.CursorLocation = adUseClient Conn.Mode = adModeUnknown Conn.Open sumRow = 1 End Sub Private Sub list_namesumj_Click() list_namesumj.Visible = False Text2.Text = list_namesumj.BoundText If sum_journaltb.State = adStateOpen Then sum_journaltb.Close Set sum_journaltb = Nothing Call search_sumj_Click End Sub Private Sub search_sumj_Click() Dim line_item As Integer Text2.Text = UCase(Text2.Text) Set sum_journaltb = New ADODB.Recordset With sum_journaltb If .State = adStateOpen Then .Close .CursorLocation = adUseClient .Open "select * from sum_journal where regno='" & Text2.Text & "'", Conn, adOpenForwardOnly, adLockReadOnly If Not .EOF Then Text2.Text = .Fields(0).Value Text3.Text = .Fields(1).Value Call cls_sum_item With list_itemtb If .State = adStateOpen Then .Close .CursorLocation = adUseClient .Open "select * from tranfile Where regno='" & Text2.Text & "' order by daily asc", Conn, adOpenForwardOnly, adLockReadOnly If Not .EOF Then sum_item.Rows = .RecordCount + 1 .MoveFirst With sum_item mem_pointer = list_itemtb.Fields(1).Value list_itemtb.MoveFirst line_item = 1 Do While Not list_itemtb.EOF .TextMatrix(line_item, 0) = line_item .TextMatrix(line_item, 1) = list_itemtb.Fields(0).Value .TextMatrix(line_item, 2) = Format(list_itemtb.Fields(3).Value, "dd/mm/yyyy") .TextMatrix(line_item, 3) = list_itemtb.Fields(4).Value .TextMatrix(line_item, 4) = list_itemtb.Fields(5).Value .ColAlignment(4) = 0 .TextMatrix(line_item, 5) = list_itemtb.Fields(6).Value .TextMatrix(line_item, 6) = Format(list_itemtb.Fields(2).Value, "dd/mm/yyyy") line_item = line_item + 1 list_itemtb.MoveNext Loop End With End If .Close End With sumRow = line_item Cmdadd.Enabled = False Cmdedit.Enabled = True Cmdsearch.Enabled = False Cmdclear.Enabled = False Cmdnext.Enabled = True Cmdprevious.Enabled = True Text2.Locked = True Text3.BackColor = &HC0C0C0 Text3.Locked = True Else MsgBox "ไม่พบข้อมูลวารสารรวมเล่ม เลขทะเบียนนี้ ", vbCritical + vbOKOnly, "ไม่พบข้อมูล" Text2.Text = "" Text2.SetFocus End If .Close End With Set sum_journaltb = Nothing End Sub Private Sub show_item_Click() Dim chkitem As String Dim j As Integer chkitem = "F" For j = 1 To sumRow - 1 If sum_item.TextMatrix(j, 1) = show_item.TextMatrix(show_item.Row, 0) Then chkitem = "T" Next j If chkitem = "F" Then sum_item.Rows = sum_item.Rows + 1 With sum_item .TextMatrix(sumRow, 0) = sumRow .TextMatrix(sumRow, 1) = show_item.TextMatrix(show_item.Row, 0) .TextMatrix(sumRow, 2) = show_item.TextMatrix(show_item.Row, 1) .TextMatrix(sumRow, 3) = show_item.TextMatrix(show_item.Row, 2) .TextMatrix(sumRow, 4) = show_item.TextMatrix(show_item.Row, 3) .ColAlignment(5) = 0 .TextMatrix(sumRow, 5) = show_item.TextMatrix(show_item.Row, 4) .TextMatrix(sumRow, 6) = show_item.TextMatrix(show_item.Row, 5) End With sumRow = sumRow + 1 show_item.HighLight = flexHighlightAlways Call gen_callno Else MsgBox "คุณเลือกฉบับซ้ำ กรุณาเลือกฉบับอื่น", vbCritical + vbOKOnly, "ข้อผิดพลาด" End If Cmdsearch.Enabled = False Cmdclear.Enabled = False End Sub Private Sub show_journal_Click() Dim line_item As Integer Dim mem_magno As String If Left(show_journal.TextMatrix(show_journal.Row, 2), 1) = "I" Then chk_pasa = "Thai" ElseIf Left(show_journal.TextMatrix(show_journal.Row, 2), 1) = "J" Then chk_pasa = "Eng" End If line_item = 1 Call cls_show_listitem With list_itemtb If .State = adStateOpen Then .Close .ActiveConnection = Conn .CursorType = adOpenForwardOnly .CursorLocation = adUseClient .Open "select * from tranfile Where trim(pointer)='" & show_journal.TextMatrix(show_journal.Row, 2) & "' and MagStatus <>'Z' order by daily desc" 'and trim(MagStatus) <>'Z' If Not .EOF Then With show_item .Rows = list_itemtb.RecordCount + 1 list_itemtb.MoveFirst Do While Not list_itemtb.EOF .TextMatrix(line_item, 0) = list_itemtb.Fields(0).Value .TextMatrix(line_item, 1) = Format(list_itemtb.Fields(3).Value, "dd/mm/yyyy") .TextMatrix(line_item, 2) = list_itemtb.Fields(4).Value .TextMatrix(line_item, 3) = list_itemtb.Fields(5).Value .ColAlignment(4) = 0 .TextMatrix(line_item, 4) = list_itemtb.Fields(6).Value .TextMatrix(line_item, 5) = Format(list_itemtb.Fields(2).Value, "dd/mm/yyyy") line_item = line_item + 1 list_itemtb.MoveNext Loop name_sumj = show_journal.TextMatrix(show_journal.Row, 0) Label1.Caption = " Magno= " & .TextMatrix(1, 0) & " Daily= " & .TextMatrix(1, 1) & " : " & .TextMatrix(1, 4) show_journal.HighLight = flexHighlightAlways show_item.HighLight = flexHighlightWithFocus Cmdadd.Enabled = True End With Else Label1.Caption = "" MsgBox "รายชื่อวารสารนี้ไม่พบข้อมูลการลงทะเบียน", vbCritical + vbOKOnly, "ไม่พบวารสารที่ลงทะเบียน" End If .Close End With End Sub Sub cls_show_journal() Dim i As Integer With show_journal .Rows = 14 For i = 1 To 13 .TextMatrix(i, 0) = "" .TextMatrix(i, 1) = "" Next i End With End Sub Sub cls_show_listitem() Dim i As Integer With show_item .Rows = 14 For i = 1 To 13 .TextMatrix(i, 0) = "" .TextMatrix(i, 1) = "" .TextMatrix(i, 2) = "" .TextMatrix(i, 3) = "" .TextMatrix(i, 4) = "" .TextMatrix(i, 5) = "" Next i End With End Sub Sub cls_sum_item() Dim i As Integer With sum_item .Rows = 6 For i = 1 To 5 .TextMatrix(i, 0) = "" .TextMatrix(i, 1) = "" .TextMatrix(i, 2) = "" .TextMatrix(i, 3) = "" .TextMatrix(i, 4) = "" .TextMatrix(i, 5) = "" .TextMatrix(i, 6) = "" Next i End With End Sub Private Sub sum_item_DblClick() Dim i As Integer Dim line_item As Integer If sta_buttom = "New" Then With sum_item For i = .Row To sumRow - 1 .TextMatrix(i, 0) = i .TextMatrix(i, 1) = .TextMatrix(i + 1, 1) .TextMatrix(i, 2) = .TextMatrix(i + 1, 2) .TextMatrix(i, 3) = .TextMatrix(i + 1, 3) .TextMatrix(i, 4) = .TextMatrix(i + 1, 4) .TextMatrix(i, 5) = .TextMatrix(i + 1, 5) .TextMatrix(i, 6) = .TextMatrix(i + 1, 6) Next i .TextMatrix(sumRow - 1, 0) = "" .TextMatrix(sumRow - 1, 1) = "" .TextMatrix(sumRow - 1, 2) = "" .TextMatrix(sumRow - 1, 3) = "" .TextMatrix(sumRow - 1, 4) = "" .TextMatrix(sumRow - 1, 5) = "" .TextMatrix(sumRow - 1, 6) = "" End With sumRow = sumRow - 1 Call gen_callno ElseIf sta_buttom = "Edit" Then If MsgBox("ต้องการลบข้อมูลวารสารเลขทะเบียนนี้ ออกจากรายการรวมเล่มหรือไม่", vbQuestion + vbOKCancel, "ยืนยันการลบข้อมูล") = vbOK Then Set list_itemtb = New ADODB.Recordset With list_itemtb If .State = adStateOpen Then .Close .CursorLocation = adUseClient .Open "select * from tranfile where magno='" & sum_item.TextMatrix(sum_item.Row, 1) & "'", Conn, adOpenForwardOnly, adLockOptimistic If Not .EOF Then .Fields(8).Value = "O" .Fields(14).Value = "" .Update End If .Close End With Set list_itemtb = Nothing line_item = 1 Call cls_show_listitem Set list_itemtb = New ADODB.Recordset With list_itemtb If .State = adStateOpen Then .Close .CursorLocation = adUseClient .Open "select * from tranfile Where pointer='" & mem_pointer & "' and MagStatus <>'Z' order by daily desc", Conn, adOpenForwardOnly, adLockReadOnly If Not .EOF Then With show_item .Rows = list_itemtb.RecordCount + 1 list_itemtb.MoveFirst Do While Not list_itemtb.EOF .TextMatrix(line_item, 0) = list_itemtb.Fields(0).Value .TextMatrix(line_item, 1) = Format(list_itemtb.Fields(3).Value, "dd/mm/yyyy") .TextMatrix(line_item, 2) = list_itemtb.Fields(4).Value .TextMatrix(line_item, 3) = list_itemtb.Fields(5).Value .ColAlignment(4) = 0 .TextMatrix(line_item, 4) = list_itemtb.Fields(6).Value .TextMatrix(line_item, 5) = Format(list_itemtb.Fields(2).Value, "dd/mm/yyyy") line_item = line_item + 1 list_itemtb.MoveNext Loop End With End If .Close End With Set list_itemtb = Nothing With sum_item For i = .Row To sumRow - 2 .TextMatrix(i, 0) = i .TextMatrix(i, 1) = .TextMatrix(i + 1, 1) .TextMatrix(i, 2) = .TextMatrix(i + 1, 2) .TextMatrix(i, 3) = .TextMatrix(i + 1, 3) .TextMatrix(i, 4) = .TextMatrix(i + 1, 4) .TextMatrix(i, 5) = .TextMatrix(i + 1, 5) .TextMatrix(i, 6) = .TextMatrix(i + 1, 6) Next i .TextMatrix(sumRow - 1, 0) = "" .TextMatrix(sumRow - 1, 1) = "" .TextMatrix(sumRow - 1, 2) = "" .TextMatrix(sumRow - 1, 3) = "" .TextMatrix(sumRow - 1, 4) = "" .TextMatrix(sumRow - 1, 5) = "" .TextMatrix(sumRow - 1, 6) = "" End With sumRow = sumRow - 1 Call gen_callno Set sum_journaltb = New ADODB.Recordset With sum_journaltb If .State = adStateOpen Then .Close .CursorLocation = adUseClient .Open "select * from sum_journal where regno='" & Text2.Text & "'", Conn, adOpenForwardOnly, adLockOptimistic If Not .EOF Then .Fields(1).Value = Text3.Text .Update End If .Close End With Set sum_journaltb = Nothing End If End If End Sub Private Sub Text1_KeyPress(KeyAscii As Integer) If KeyAscii = 13 Then Call cmdsearch_Click End If End Sub Private Sub Text2_KeyPress(KeyAscii As Integer) If KeyAscii = 13 Then Call search_sumj_Click End If End Sub Sub gen_callno() Dim n_loop As Integer Dim y_min As Integer Dim y_max As Integer Dim cs_min As Integer Dim cs_max As Integer Dim s_month_min As String Dim s_month_max As String cs_max = 0 s_month_max = "" cs_min = Val(sum_item.TextMatrix(1, 4)) s_month_min = dis_mo(sum_item.TextMatrix(1, 5), "Min") y_max = 0 y_min = Val(sum_item.TextMatrix(1, 3)) For n_loop = 1 To sumRow - 1 If Val(sum_item.TextMatrix(n_loop, 3)) > y_max Then y_max = Val(sum_item.TextMatrix(n_loop, 3)) If Val(sum_item.TextMatrix(n_loop, 3)) < y_min Then y_min = Val(sum_item.TextMatrix(n_loop, 3)) If Val(sum_item.TextMatrix(n_loop, 4)) > cs_max Then cs_max = Val(sum_item.TextMatrix(n_loop, 4)) s_month_max = dis_mo(sum_item.TextMatrix(n_loop, 5), "Max") End If If Val(sum_item.TextMatrix(n_loop, 4)) < cs_min Then cs_min = Val(sum_item.TextMatrix(n_loop, 4)) s_month_min = dis_mo(sum_item.TextMatrix(n_loop, 5), "Min") End If Next n_loop If sumRow > 1 Then If cs_min = cs_max Then If Len(s_month_min) > 4 Then If chk_pasa = "Thai" Then Text3.Text = name_sumj & " ป." & y_min & " ฉ." & cs_min & " " & Left(s_month_min, Len(s_month_min) - 4) & Right(s_month_min, 2) ElseIf chk_pasa = "Eng" Then Text3.Text = name_sumj & " Vol." & y_min & " No." & cs_min & " " & Left(s_month_min, Len(s_month_min) - 4) & Right(s_month_min, 2) End If Else Text3.Text = name_sumj & " Vol." & y_min & " No." & cs_min End If Else If y_min = y_max Then If Len(s_month_min) > 4 Then If chk_pasa = "Thai" Then Text3.Text = name_sumj & " ป." & y_min & " ฉ." & cs_min & "-" & cs_max & " " & Left(s_month_min, Len(s_month_min) - 4) & "- " & Left(s_month_max, Len(s_month_max) - 4) & Right(s_month_max, 2) ElseIf chk_pasa = "Eng" Then Text3.Text = name_sumj & " Vol." & y_min & " No." & cs_min & "-" & cs_max & " " & Left(s_month_min, Len(s_month_min) - 4) & "- " & Left(s_month_max, Len(s_month_max) - 4) & Right(s_month_max, 2) End If Else Text3.Text = name_sumj & " Vol." & y_min & " No." & cs_min & "-" & cs_max End If Else If Len(s_month_min) > 4 Then If chk_pasa = "Thai" Then Text3.Text = name_sumj & " ป." & y_min & "-" & y_max & " ฉ." & cs_min & "-" & cs_max & " " & Left(s_month_min, Len(s_month_min) - 4) & "- " & Left(s_month_max, Len(s_month_max) - 4) & Right(s_month_max, 2) ElseIf chk_pasa = "Eng" Then Text3.Text = name_sumj & " Vol." & y_min & "-" & y_max & " No." & cs_min & "-" & cs_max & " " & Left(s_month_min, Len(s_month_min) - 4) & "- " & Left(s_month_max, Len(s_month_max) - 4) & Right(s_month_max, 2) End If Else Text3.Text = name_sumj & " Vol." & y_min & "-" & y_max End If End If End If End If End Sub Function dis_mo(str_month As String, chk_v As String) As String Dim i As Integer Dim chk_found As Boolean Dim a_month(23) As String Dim d_month(23) As String a_month(0) = "มกราคม" a_month(1) = "กุมภาพันธ์" a_month(2) = "มีนาคม" a_month(3) = "เมษายน" a_month(4) = "พฤษภาคม" a_month(5) = "มิถุนายน" a_month(6) = "กรกฎาคม" a_month(7) = "สิงหาคม" a_month(8) = "กันยายน" a_month(9) = "ตุลาคม" a_month(10) = "พฤศจิกายน" a_month(11) = "ธันวาคม" a_month(12) = "January" a_month(13) = "February" a_month(14) = "March" a_month(15) = "April" a_month(16) = "May" a_month(17) = "June" a_month(18) = "July" a_month(19) = "August" a_month(20) = "September" a_month(21) = "October" a_month(22) = "November" a_month(23) = "December" d_month(0) = " ม.ค." d_month(1) = " ก.พ." d_month(2) = " มี.ค." d_month(3) = " เม.ย." d_month(4) = " พ.ค." d_month(5) = " มิ.ย." d_month(6) = " ก.ค." d_month(7) = " ส.ค." d_month(8) = " ก.ย." d_month(9) = " ต.ค." d_month(10) = " พ.ย." d_month(11) = " ธ.ค." d_month(12) = " Jan" d_month(13) = " Feb" d_month(14) = " Mar" d_month(15) = " Apr" d_month(16) = " May" d_month(17) = " Jun" d_month(18) = " Jul" d_month(19) = " Aug" d_month(20) = " Sep" d_month(21) = " Oct" d_month(22) = " Nov" d_month(23) = " Dec" chk_found = False For i = 0 To 23 If InStr(1, str_month, a_month(i), vbBinaryCompare) <> 0 Then dis_mo = Replace(str_month, a_month(i), d_month(i), 1, , vbBinaryCompare) chk_found = True End If Next i If chk_found = False Then dis_mo = str_month If InStr(1, dis_mo, "-", vbBinaryCompare) <> 0 Then If chk_v = "Min" Then dis_mo = Left(dis_mo, InStr(1, dis_mo, "-", vbBinaryCompare) - 1) If chk_v = "Max" Then dis_mo = Right(dis_mo, Len(Trim(dis_mo)) - InStr(1, dis_mo, "-", vbBinaryCompare) - 1) End If End Function Private Sub Text3_DblClick() Set sum_journaltb = New ADODB.Recordset With sum_journaltb If .State = adStateOpen Then .Close .CursorLocation = adUseClient .Open "select * from sum_journal where left(callno,length('" & Text3.Text & "'))='" & Text3.Text & "'", Conn, adOpenForwardOnly, adLockOptimistic If Not .EOF Then list_namesumj.Visible = True Set list_namesumj.RowSource = sum_journaltb list_namesumj.ListField = "callno" list_namesumj.BoundColumn = "regno" Else MsgBox "ไม่พบข้อมูลที่ต้องการค้นหา กรุณาตรวจสอบอีกครั้ง", vbCritical + vbOKOnly, "ผลการค้นหา" Text3.Text = "" Text3.SetFocus End If End With End Sub Private Sub Text3_KeyPress(KeyAscii As Integer) If KeyAscii = 13 Then Call Text3_DblClick End Sub