uwenjianguidang.ctl

公文管理系统》是一套通过行文收发

      .TypeText Text:="顺序号"
      .MoveRight unit:=12
      .ParagraphFormat.Alignment = 1
      .Cells(1).SetWidth ColumnWidth:=50, RulerStyle:=0
      .TypeText Text:="文  号"
      .MoveRight unit:=12
      .ParagraphFormat.Alignment = 1
      .Cells(1).SetWidth ColumnWidth:=50, RulerStyle:=0
      .TypeText Text:="责任者"
      .MoveRight unit:=12
      .ParagraphFormat.Alignment = 1
      .Cells(1).SetWidth ColumnWidth:=190, RulerStyle:=0
      .TypeText Text:="题                  名"
      .MoveRight unit:=12
      .ParagraphFormat.Alignment = 1
      .Cells(1).SetWidth ColumnWidth:=64, RulerStyle:=0
      .TypeText Text:="日  期"
      .MoveRight unit:=12
      .ParagraphFormat.Alignment = 1
      .Cells(1).SetWidth ColumnWidth:=20, RulerStyle:=0
      .TypeText Text:="页 次"
      .MoveRight unit:=12
      .ParagraphFormat.Alignment = 1
      .Cells(1).SetWidth ColumnWidth:=20, RulerStyle:=0
      .TypeText Text:="备 注"
      For i = 1 To rsTmp.RecordCount
        .Tables(1).Rows(i + 4).SetHeight RowHeight:=30, HeightRule:=2
        .MoveRight unit:=12
        .ParagraphFormat.Alignment = 3
        .Cells(1).SetWidth ColumnWidth:=24, RulerStyle:=0
        .TypeText Text:=CStr(IIf(IsNull(rsTmp.Fields("顺序号").Value), "", rsTmp.Fields("顺序号").Value))
        .MoveRight unit:=12
        .ParagraphFormat.Alignment = 3
        .Cells(1).SetWidth ColumnWidth:=50, RulerStyle:=0
        .TypeText Text:=CStr(IIf(IsNull(rsTmp.Fields("文号").Value), "", rsTmp.Fields("文号").Value))
        .MoveRight unit:=12
        .ParagraphFormat.Alignment = 3
        .Cells(1).SetWidth ColumnWidth:=50, RulerStyle:=0
        .TypeText Text:=IIf(IsNull(rsTmp.Fields("责任者").Value), "", rsTmp.Fields("责任者").Value)
        .MoveRight unit:=12
        .ParagraphFormat.Alignment = 3
        .Cells(1).SetWidth ColumnWidth:=190, RulerStyle:=0
        .TypeText Text:=IIf(IsNull(rsTmp.Fields("题名").Value), "", rsTmp.Fields("题名").Value)
        .MoveRight unit:=12
        .ParagraphFormat.Alignment = 3
        .Cells(1).SetWidth ColumnWidth:=64, RulerStyle:=0
        .TypeText Text:=CStr(IIf(IsNull(rsTmp.Fields("日期").Value), "", Format(rsTmp.Fields("日期").Value, "yyyy-mm-dd")))
        .MoveRight unit:=12
        .ParagraphFormat.Alignment = 3
        .Cells(1).SetWidth ColumnWidth:=20, RulerStyle:=0
        .TypeText Text:=CStr(IIf(IsNull(rsTmp.Fields("页次").Value), "", rsTmp.Fields("页次").Value))
        .MoveRight unit:=12
        .ParagraphFormat.Alignment = 3
        .Cells(1).SetWidth ColumnWidth:=20, RulerStyle:=0
        .TypeText Text:=CStr(IIf(IsNull(rsTmp.Fields("备注").Value), "", rsTmp.Fields("备注").Value))
        rsTmp.MoveNext
      Next
      
      .Tables(1).Select
      .Cells.VerticalAlignment = 1

      .HomeKey unit:=6

    End With

    Set oWrd = Nothing
    Set conn = Nothing
    Set rsTmp = Nothing
End Sub

Private Sub Command1_Click()
    Dim str保管卷号 As String
    Dim int顺序号 As Integer
    Dim strSQL As String
    Dim conn As New ADODB.Connection
    Dim rsTmp As New ADODB.Recordset
    Dim i As Integer
    
    On Error GoTo errHandler
    
    conn.ConnectionString = pConn
    
    conn.Open
    
    str保管卷号 = txtFields(2).Text
    strSQL = "select max(顺序号) from 卷内文件目录 WHERE 保管卷号 = " & str保管卷号    '& " order by 顺序号"
    
    
'    With rsTmp
'        .ActiveConnection = conn
'        .CursorLocation = adUseClient
'        .CursorType = adOpenStatic
'        .LockType = adLockOptimistic
'        .Open strSQL
'    End With

    
    
    Set rsTmp = conn.Execute(strSQL)
    If IsNull(rsTmp(0)) Then
        int顺序号 = 0
    Else
        int顺序号 = rsTmp(0)
    End If
    
    Err.Clear
'    conn.BeginTrans
    For i = 1 To DataGrid2.SelBookmarks.Count
        DataGrid2.Bookmark = DataGrid2.SelBookmarks.Item(i - 1)
        strSQL = "insert into 卷内文件目录(保管卷号,顺序号,文号,题名,日期) values(" & _
                 str保管卷号 & "," & CStr(int顺序号 + i) & ",'" & DataGrid2.Columns(2).Text & "','" & DataGrid2.Columns(4).Text & "','" & DataGrid2.Columns(5).Text & "')"
        conn.Execute strSQL
        strSQL = "update 发文登记 set 归档=1 where 年度 = " & DataGrid2.Columns(0).Text & " and 序号 = " & DataGrid2.Columns(1).Text
        conn.Execute strSQL
    Next
'    conn.CommitTrans
    Adodc1.Refresh
    Adodc2.Refresh
    SSTab1.Tab = 0
    Exit Sub
errHandler:
    If Err > 0 Then
        conn.RollbackTrans
    End If
End Sub

Private Sub Command2_Click()
    Dim str保管卷号 As String
    Dim int顺序号 As Integer
    Dim strSQL As String
    Dim conn As New ADODB.Connection
    Dim rsTmp As New ADODB.Recordset
    Dim i As Integer
    
    On Error GoTo errHandler
    
    conn.ConnectionString = pConn
    
    conn.Open
    
    str保管卷号 = txtFields(2).Text
    strSQL = "select max(顺序号) from 卷内文件目录 WHERE 保管卷号 = " & str保管卷号    '& " order by 顺序号"
    
    
'    With rsTmp
'        .ActiveConnection = conn
'        .CursorLocation = adUseClient
'        .CursorType = adOpenStatic
'        .LockType = adLockOptimistic
'        .Open strSQL
'    End With

    
    
    Set rsTmp = conn.Execute(strSQL)
    If IsNull(rsTmp(0)) Then
        int顺序号 = 0
    Else
        int顺序号 = rsTmp(0)
    End If
    
    Err.Clear
'    conn.BeginTrans
    For i = 1 To DataGrid4.SelBookmarks.Count
        DataGrid4.Bookmark = DataGrid4.SelBookmarks.Item(i - 1)
        strSQL = "insert into 卷内文件目录(保管卷号,顺序号,文号,题名,日期) values(" & _
                 str保管卷号 & "," & CStr(int顺序号 + i) & ",'" & DataGrid2.Columns(2).Text & "','" & DataGrid2.Columns(4).Text & "','" & DataGrid2.Columns(5).Text & "')"
        conn.Execute strSQL
        strSQL = "update 发函登记 set 归档=1 where 年度 = " & DataGrid2.Columns(0).Text & " and 序号 = " & DataGrid2.Columns(1).Text
        conn.Execute strSQL
    Next
'    conn.CommitTrans
    Adodc1.Refresh
    Adodc3.Refresh
    SSTab1.Tab = 0
    Exit Sub
errHandler:
    If Err > 0 Then
        conn.RollbackTrans
    End If
End Sub

Private Sub Command3_Click()
    Dim str保管卷号 As String
    Dim int顺序号 As Integer
    Dim strSQL As String
    Dim conn As New ADODB.Connection
    Dim rsTmp As New ADODB.Recordset
    Dim i As Integer
    
    On Error GoTo errHandler
    
    conn.ConnectionString = pConn
    
    conn.Open
    
    str保管卷号 = txtFields(2).Text
    strSQL = "select max(顺序号) from 卷内文件目录 WHERE 保管卷号 = " & str保管卷号    '& " order by 顺序号"
    
    
'    With rsTmp
'        .ActiveConnection = conn
'        .CursorLocation = adUseClient
'        .CursorType = adOpenStatic
'        .LockType = adLockOptimistic
'        .Open strSQL
'    End With

    
    
    Set rsTmp = conn.Execute(strSQL)
    If IsNull(rsTmp(0)) Then
        int顺序号 = 0
    Else
        int顺序号 = rsTmp(0)
    End If
    
    Err.Clear
'    conn.BeginTrans
    For i = 1 To DataGrid5.SelBookmarks.Count
        DataGrid5.Bookmark = DataGrid5.SelBookmarks.Item(i - 1)
        strSQL = "insert into 卷内文件目录(保管卷号,顺序号,文号,题名,日期) values(" & _
                 str保管卷号 & "," & CStr(int顺序号 + i) & ",'" & DataGrid5.Columns(2).Text & "','" & DataGrid2.Columns(4).Text & "','" & DataGrid2.Columns(5).Text & "')"
        conn.Execute strSQL
        strSQL = "update 收文登记 set 归档=1 where 年度 = " & DataGrid2.Columns(0).Text & " and 序号 = " & DataGrid2.Columns(1).Text
        conn.Execute strSQL
    Next
'    conn.CommitTrans
    Adodc1.Refresh
    Adodc5.Refresh
    SSTab1.Tab = 0
    Exit Sub
errHandler:
    If Err > 0 Then
        conn.RollbackTrans
    End If

End Sub

Private Sub datPrimaryRS_Error(ByVal ErrorNumber As Long, Description As String, ByVal Scode As Long, ByVal Source As String, ByVal HelpFile As String, ByVal HelpContext As Long, fCancelDisplay As Boolean)
  '错误处理程序代码置于此处
  '想要忽略错误，注释掉下一行
  '想要捕获它们，在此添加代码以处理它们
  MsgBox "Data error event hit err:" & Description
End Sub

Private Sub datPrimaryRS_MoveComplete(ByVal adReason As ADODB.EventReasonEnum, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
  
    Dim strSQL As String
    
    If Not (datPrimaryRS.Recordset.EOF Or datPrimaryRS.Recordset.BOF) Then
        strSQL = "SELECT 保管卷号, 顺序号, 文号, 责任者, 题名, 日期, 页次, 备注 From 卷内文件目录 WHERE (保管卷号 = " & CStr(CInt(datPrimaryRS.Recordset("保管卷号").Value)) & " ) ORDER BY 顺序号 "
        With Adodc1
            .ConnectionString = pConn
            .RecordSource = strSQL
            .Refresh
        End With

    End If
  
  
  '为这个 recordset 显示当前记录位置
  datPrimaryRS.Caption = CStr(datPrimaryRS.Recordset.AbsolutePosition)
  lbl记录数.Caption = CStr(datPrimaryRS.Recordset.RecordCount)
End Sub

Private Sub datPrimaryRS_WillChangeRecord(ByVal adReason As ADODB.EventReasonEnum, ByVal cRecords As Long, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
  '验证代码置于此处
  '下列动作发生时该事件被调用
  Dim bCancel As Boolean

  Select Case adReason
  Case adRsnAddNew
  Case adRsnClose
  Case adRsnDelete
  Case adRsnFirstChange
  Case adRsnMove
  Case adRsnRequery
  Case adRsnResynch
  Case adRsnUndoAddNew
  Case adRsnUndoDelete
  Case adRsnUndoUpdate
  Case adRsnUpdate
  End Select

  If bCancel Then adStatus = adStatusCancel
End Sub

Private Sub cmdAdd_Click()
  On Error GoTo AddErr
  datPrimaryRS.Recordset.AddNew
  
  txtFields(4).Text = Now()
  
  If SSTab1.Tab = 0 Then
    txtFields(1).Text = Adodc1.Recordset![文件编号]
  Else
    txtFields(1).Text = Adodc2.Recordset![发文号]
  End If

  Exit Sub
AddErr:
  MsgBox Err.Description
End Sub

Private Sub cmdDelete_Click()
  On Error GoTo DeleteErr
  With datPrimaryRS.Recordset
    .Delete
    .MoveNext
    If .EOF Then .MoveLast
  End With
  Exit Sub
DeleteErr:
  MsgBox Err.Description
End Sub

Private Sub cmdRefresh_Click()
  '只有多用户应用程序需要
  On Error GoTo RefreshErr
  datPrimaryRS.Refresh
  Exit Sub
RefreshErr:
  MsgBox Err.Description
End Sub

Private Sub cmdUpdate_Click()
  On Error GoTo UpdateErr

  datPrimaryRS.Recordset.UpdateBatch adAffectAll
  
'  If SSTab1.Tab = 0 Then
'    Adodc1.Recordset.Fields("归档") = vbTrue
'    Adodc1.Recordset.Update
'    Adodc1.Refresh
'  End If
'
'  If SSTab1.Tab = 1 Then
'    Adodc2.Recordset.Fields("归档") = vbTrue
'    Adodc2.Recordset.Update
'    Adodc2.Refresh
'  End If
  
  
  Exit Sub
UpdateErr:
  MsgBox Err.Description
End Sub


Private Sub UserControl_Initialize()
    pConn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=OA;Data Source=lzy"
    strUserName = GetSetting("JGYOA", "Login", "UserName", "")
    
    Dim strSQL As String
    strSQL = "select 年度,部门id,保管卷号,保管期限,起始日期,截止日期,卷内页数,案卷题名 from 案卷 Order by 保管卷号"
    
    With datPrimaryRS
        .ConnectionString = pConn
        .RecordSource = strSQL
        .Refresh
    End With
    
    strSQL = "SELECT 保管卷号, 顺序号, 文号, 责任者, 题名, 日期, 页次, 备注 From 卷内文件目录 WHERE (保管卷号 = 1) ORDER BY 顺