frmsel.frm

一个资金管理系统的成品 开发环境：VB

         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H000000FF&
      Height          =   375
      Left            =   9600
      TabIndex        =   28
      Top             =   2760
      Width           =   4455
   End
End
Attribute VB_Name = "frmsel"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim conn As New ADODB.Connection
Dim rskjyw As New ADODB.Recordset
Dim rspzlb As New ADODB.Recordset
Dim rsgkglbm As New ADODB.Recordset
Dim rsyskmlb As New ADODB.Recordset
Dim rsyskm As New ADODB.Recordset
Dim rsfygsbm As New ADODB.Recordset
Dim rs As New ADODB.Recordset
Dim addrecord As Boolean
Private Sub adorefresh()
Dim fieldname(14) As Variant
Dim wide(14) As Variant
Dim str As String
fieldname(0) = "序号"
fieldname(1) = "票据号码"
fieldname(2) = "票据类别名称"
fieldname(3) = "发生日期"
fieldname(4) = "归口管理部门代码"
fieldname(5) = "归口管理部门"
fieldname(6) = "资金科目类别代码"
fieldname(7) = "资金科目类别"
fieldname(8) = "资金科目代码"
fieldname(9) = "资金科目名称"
fieldname(10) = "费用归属部门代码"
fieldname(11) = "费用归属部门"
fieldname(12) = "业务金额"
fieldname(13) = "备注"
wide(0) = 400
wide(1) = 1000
wide(2) = 1000
wide(3) = 1000
wide(4) = 1000
wide(5) = 1400
wide(6) = 1000
wide(7) = 1400
wide(8) = 1000
wide(9) = 1400
wide(10) = 1000
wide(11) = 1400
wide(12) = 1400
wide(13) = 2000
'str = "Provider=SQLOLEDB.1;Password=090309;Persist Security Info=True;User ID=cw;Initial Catalog=ysgl2004;Data Source=CWSERVER"
If conn.State <> 1 Then
    conn.CursorLocation = adUseClient
    conn.Open nowconnectstring
End If
str = "SELECT a.xuhao, a.pzhm,a.pzlbmc, a.fsrq , e.dm as glbmdm,a.glbmmc,b.dm AS yskmlbdm, a.yslbmc, c.dm AS yskmdm,a.yskmmc, d.dm AS gsbmdm, a.gsbmmc, a.ywje, a.bz FROM kjyw a INNER JOIN  yskmlb b ON a.yslbmc = b.yslbmc INNER JOIN  yskm c ON a.yskmmc = c.yskmmc INNER JOIN  fygsbm d ON a.gsbmmc = d.gsbmmc INNER JOIN  gkglbm e ON a.glbmmc = e.glbmmc ORDER BY a.pzhm"
rskjyw.Open str, conn, adOpenStatic, adLockBatchOptimistic
rsyskm.Open "select * from yskm order by dm", conn, adOpenStatic, adLockBatchOptimistic
rsyskmlb.Open "select * from yskmlb order by dm", conn, adOpenStatic, adLockBatchOptimistic
rspzlb.Open "select * from pzlb ", conn, adOpenStatic, adLockBatchOptimistic
rsfygsbm.Open "select * from fygsbm order by dm", conn, adOpenStatic, adLockBatchOptimistic
rsgkglbm.Open "select * from gkglbm order by dm", conn, adOpenStatic, adLockBatchOptimistic
Set DataGrid1.DataSource = rskjyw
For i = 0 To 12
    DataGrid1.Columns(i).Caption = fieldname(i)
    DataGrid1.Columns(i).Width = wide(i)
    DataGrid1.Columns(i).DataField = rskjyw.Fields(i).Name
Next i

Set Dacompzhm.RowSource = rskjyw
Dacompzhm.ListField = rskjyw.Fields("pzhm").Name

Set Dacompzlbmc.RowSource = rspzlb
Dacompzlbmc.ListField = rskjyw.Fields("pzlbmc").Name

Set Dacomglbmdm.RowSource = rsgkglbm
Dacomglbmdm.ListField = rsgkglbm.Fields("dm").Name

Set Dacomglbmmc.RowSource = rsgkglbm
Dacomglbmmc.ListField = rsgkglbm.Fields("glbmmc").Name

Set Dacomyslbdm.RowSource = rsyskmlb
Dacomyslbdm.ListField = rsyskmlb.Fields("dm").Name

Set Dacomyslbmc.RowSource = rsyskmlb
Dacomyslbmc.ListField = rsyskmlb.Fields("yslbmc").Name

Set Dacomyskmdm.RowSource = rsyskm
Dacomyskmdm.ListField = rsyskm.Fields("dm").Name

Set Dacomyskmmc.RowSource = rsyskm
Dacomyskmmc.ListField = rsyskm.Fields("yskmmc").Name

Set Dacomgsbmdm.RowSource = rsfygsbm
Dacomgsbmdm.ListField = rsfygsbm.Fields("dm").Name

Set Dacomgsbmmc.RowSource = rsfygsbm
Dacomgsbmmc.ListField = rsfygsbm.Fields("gsbmmc").Name

DTPicker2.Value = Date
End Sub
Private Sub rsrefresh()
  rskjyw.Requery
  rspzlb.Requery
  rsyskm.Requery
  rsyskmlb.Requery
  rsfygsbm.Requery
  rsgkglbm.Requery
End Sub
Private Sub cmdsearch_Click(Index As Integer)
Dim str As Variant
Dim str1 As Variant
Select Case Index
  Case 0 '查询
     If Dacompzhm.Text <> "" Then
        str = "pzhm = " & Val(Dacompzhm.Text)
     End If
     If Dacompzlbmc.Text <> "" Then
        If str = "" Then
           str = "pzlbmc like '%" & Dacompzlbmc.Text & "%'"
        Else
           str = str & " and pzlbmc like '%" & Dacompzlbmc.Text & "%'"
        End If
     End If
     If Dacomglbmmc.Text <> "" Then
        If str = "" Then
           str = "glbmmc like '" & Dacomglbmmc.Text & "'"
        Else
           str = str & " and glbmmc like '" & Dacomglbmmc.Text & "'"
        End If
     End If
     If Dacomyslbmc.Text <> "" Then
        If str = "" Then
           str = "yslbmc like '" & Dacomyslbmc.Text & "'"
        Else
           str = str & " and yslbmc like '" & Dacomyslbmc.Text & "'"
        End If
     End If
     If Dacomyskmmc.Text <> "" Then
        If str = "" Then
           str = "yskmmc like '" & Dacomyskmmc.Text & "'"
        Else
           str = str & " and yskmmc like '" & Dacomyskmmc.Text & "'"
        End If
     End If
     If Dacomgsbmmc.Text <> "" Then
        If str = "" Then
           str = "gsbmmc like '" & Dacomgsbmmc.Text & "'"
        Else
           str = str & " and gsbmmc like '" & Dacomgsbmmc.Text & "'"
        End If
     End If
    
     If str <> "" Then
        'str = str & " and qdsj>='" & DTPicker1.Value & "' and qdsj<='" & DTPicker2.Value & "' and wgsj >='" & DTPicker3.Value & "' and wgsj <='" & DTPicker4.Value & "'"
        str = str & " and fsrq>='" & Format(DTPicker1.Value, "yyyy-MM-dd") & "' and fsrq<='" & Format(DTPicker2.Value, "yyyy-MM-dd") & "'"
        'str = str & " and qdsj>='" & Format(DTPicker1.Value, "yyyy-MM-dd") & "' and qdsj<='" & Format(DTPicker2.Value, "yyyy-MM-dd") & "'and wgsj >='" & Format(DTPicker3.Value, "yyyy-MM-dd") & "'and wgsj <='" & Format(DTPicker4.Value, "yyyy-MM-dd") & "'"
     Else
        'str = "qdsj>='" & DTPicker1.Value & "' and qdsj<='" & DTPicker2.Value & "' and cast(wgsj as datetime) >='" & DTPicker3.Value & "' and cast(wgsj as datetime) <='" & DTPicker4.Value & "'"
        str = "fsrq>='" & Format(DTPicker1.Value, "yyyy-MM-dd") & "' and fsrq<='" & Format(DTPicker2.Value, "yyyy-MM-dd") & "'"
        'str = "qdsj>='" & Format(DTPicker1.Value, "yyyy-MM-dd") & "' and qdsj<='" & Format(DTPicker2.Value, "yyyy-MM-dd") & "' and wgsj >='" & Format(DTPicker3.Value, "yyyy-MM-dd") & "' and wgsj <='" & Format(DTPicker4.Value, "yyyy-MM-dd") & "'"
     End If
     rskjyw.Filter = str
     strtemp = str
     Labeljl.Caption = "本次查询共有" & rskjyw.RecordCount & "条记录"
     Exit Sub
  Case 1 '清空条件
     Dim setcontrol As Control
     For Each setcontrol In Me.Controls
         If TypeName(setcontrol) = "DataCombo" Then
            setcontrol.Text = ""
         End If
     Next
     DTPicker1.Value = "1980-9-1"
     DTPicker2.Value = Date
     rskjyw.Filter = "pzhm <>0"
     strtemp = "pzhm<>''"
     Labeljl.Caption = "本次查询共有" & rskjyw.RecordCount & "条记录"
     Exit Sub
    Case 2 '退出
      Unload Me
    End Select
End Sub


Private Sub DataGrid1_DblClick()
Dim m_col As Integer
   If rskjyw.RecordCount <> 0 Then
      m_col = DataGrid1.Col
      pzhm = DataGrid1.Columns("1").Text
      Load frmywmodi
      frmywmodi.Show vbModal
   End If
   'rsrefresh
End Sub

Private Sub Form_Load()
 adorefresh
End Sub

Private Sub Dacomglbmdm_Change()
 'Set rs = conn.Execute("select * from yskmlb where dm='" & Left(Dacomdm.Text, 1) & "'")
If Trim(Dacomglbmdm.Text) <> "" Then
  rsgkglbm.Filter = "dm ='" & Trim(Dacomglbmdm.Text) & "'"
  If Not rsgkglbm.EOF Then
    Dacomglbmmc.Text = rsgkglbm.Fields("glbmmc").Value
  End If
'  Dacomlbdm.Refresh
End If
End Sub

Private Sub Dacomglbmmc_Change()
If Trim(Dacomglbmmc.Text) <> "" Then
  rsgkglbm.Filter = "glbmmc ='" & Trim(Dacomglbmmc.Text) & "'"
  If Not rsgkglbm.EOF Then
    Dacomglbmdm.Text = rsgkglbm.Fields("dm").Value
  End If
'  Dacomlbdm.Refresh
End If
End Sub
Private Sub Dacomyslbdm_Change()
 'Set rs = conn.Execute("select * from yskmlb where dm='" & Left(Dacomdm.Text, 1) & "'")
If Trim(Dacomyslbdm.Text) <> "" Then
  rsyskmlb.Filter = "dm ='" & Trim(Dacomyslbdm.Text) & "'"
  If Not rsyskmlb.EOF Then
    Dacomyslbmc.Text = rsyskmlb.Fields("yslbmc").Value
  End If
'  Dacomlbdm.Refresh
End If
End Sub

Private Sub Dacomyslbmc_Change()
If Trim(Dacomyslbmc.Text) <> "" Then
  rsyskmlb.Filter = "yslbmc ='" & Trim(Dacomyslbmc.Text) & "'"
  If Not rsgkglbm.EOF Then
    Dacomyslbdm.Text = rsyskmlb.Fields("dm").Value
  End If
'  Dacomlbdm.Refresh
End If
End Sub
Private Sub Dacomyskmdm_Change()
 'Set rs = conn.Execute("select * from yskmlb where dm='" & Left(Dacomdm.Text, 1) & "'")
If Trim(Dacomyskmdm.Text) <> "" Then
  rsyskm.Filter = "dm ='" & Trim(Dacomyskmdm.Text) & "'"
  If Not rsyskm.EOF Then
    Dacomyskmmc.Text = rsyskm.Fields("yskmmc").Value
  End If
'  Dacomlbdm.Refresh
End If
End Sub
Private Sub Dacomyskmmc_Change()
If Trim(Dacomyskmmc.Text) <> "" Then
  rsyskm.Filter = "yskmmc ='" & Trim(Dacomyskmmc.Text) & "'"
  If Not rsyskm.EOF Then
    Dacomyskmdm.Text = rsyskm.Fields("dm").Value
  End If
'  Dacomlbdm.Refresh
End If
End Sub
Private Sub Dacomgsbmdm_Change()
 'Set rs = conn.Execute("select * from yskmlb where dm='" & Left(Dacomdm.Text, 1) & "'")
If Trim(Dacomgsbmdm.Text) <> "" Then
  rsfygsbm.Filter = "dm ='" & Trim(Dacomgsbmdm.Text) & "'"
  If Not rsfygsbm.EOF Then
    Dacomgsbmmc.Text = rsfygsbm.Fields("gsbmmc").Value
  End If
'  Dacomlbdm.Refresh
End If
End Sub
Private Sub Dacomgsbmmc_Change()
If Trim(Dacomgsbmmc.Text) <> "" Then
  rsfygsbm.Filter = "gsbmmc ='" & Trim(Dacomgsbmmc.Text) & "'"
  If Not rsgkglbm.EOF Then
    Dacomgsbmdm.Text = rsfygsbm.Fields("dm").Value
  End If
'  Dacomlbdm.Refresh
End If
End Sub

Private Sub Form_Unload(Cancel As Integer)
 conn.Close
End Sub