frm明细查询.frm

适合于中小型企业管理

         BeginProperty Column01 
            DataField       =   "类别编码"
            Caption         =   "物资类别"
            BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} 
               Type            =   0
               Format          =   ""
               HaveTrueFalseNull=   0
               FirstDayOfWeek  =   0
               FirstWeekOfYear =   0
               LCID            =   2052
               SubFormatType   =   0
            EndProperty
         EndProperty
         BeginProperty Column02 
            DataField       =   "物资编号"
            Caption         =   "物资编号"
            BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} 
               Type            =   0
               Format          =   ""
               HaveTrueFalseNull=   0
               FirstDayOfWeek  =   0
               FirstWeekOfYear =   0
               LCID            =   2052
               SubFormatType   =   0
            EndProperty
         EndProperty
         BeginProperty Column03 
            DataField       =   "物资名称"
            Caption         =   "物资名称"
            BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} 
               Type            =   0
               Format          =   ""
               HaveTrueFalseNull=   0
               FirstDayOfWeek  =   0
               FirstWeekOfYear =   0
               LCID            =   2052
               SubFormatType   =   0
            EndProperty
         EndProperty
         BeginProperty Column04 
            DataField       =   "物资型号"
            Caption         =   "物资型号"
            BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} 
               Type            =   0
               Format          =   ""
               HaveTrueFalseNull=   0
               FirstDayOfWeek  =   0
               FirstWeekOfYear =   0
               LCID            =   2052
               SubFormatType   =   0
            EndProperty
         EndProperty
         BeginProperty Column05 
            DataField       =   "数量"
            Caption         =   "数量"
            BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} 
               Type            =   0
               Format          =   ""
               HaveTrueFalseNull=   0
               FirstDayOfWeek  =   0
               FirstWeekOfYear =   0
               LCID            =   2052
               SubFormatType   =   0
            EndProperty
         EndProperty
         BeginProperty Column06 
            DataField       =   "金额"
            Caption         =   "金额"
            BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} 
               Type            =   0
               Format          =   ""
               HaveTrueFalseNull=   0
               FirstDayOfWeek  =   0
               FirstWeekOfYear =   0
               LCID            =   2052
               SubFormatType   =   0
            EndProperty
         EndProperty
         BeginProperty Column07 
            DataField       =   "供货单位"
            Caption         =   "供货单位"
            BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} 
               Type            =   0
               Format          =   ""
               HaveTrueFalseNull=   0
               FirstDayOfWeek  =   0
               FirstWeekOfYear =   0
               LCID            =   2052
               SubFormatType   =   0
            EndProperty
         EndProperty
         SplitCount      =   1
         BeginProperty Split0 
            BeginProperty Column00 
               ColumnWidth     =   1140.095
            EndProperty
            BeginProperty Column01 
               ColumnWidth     =   900.284
            EndProperty
            BeginProperty Column02 
               ColumnWidth     =   870.236
            EndProperty
            BeginProperty Column03 
               ColumnWidth     =   1140.095
            EndProperty
            BeginProperty Column04 
               ColumnWidth     =   1649.764
            EndProperty
            BeginProperty Column05 
               ColumnWidth     =   840.189
            EndProperty
            BeginProperty Column06 
               ColumnWidth     =   1214.929
            EndProperty
            BeginProperty Column07 
               ColumnWidth     =   1755.213
            EndProperty
         EndProperty
      End
      Begin vsElasticLightLibCtl.vsElasticLight vsElasticLight1 
         Left            =   -75000
         OleObjectBlob   =   "Frm明细查询.frx":3D9A
         Top             =   0
      End
   End
End
Attribute VB_Name = "Frm明细查询"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim Rstmp As ADODB.Recordset
Dim Rstmpp As ADODB.Recordset
Dim mrc As ADODB.Recordset
Dim TxtSql As String


Private Sub Command1_Click()
TxtSql = "SELECT a.仓库名称, a.物资编号,b.类别编码,b.物资名称,b.物资型号, SUM(a.数量) AS 数量, SUM(a.金额) AS 金额,a.供货单位 From Fl_采购票据表 as a INNER JOIN fl_物资信息表 as b ON a.物资编号=b.物资编号 WHERE a.票据状态<2 and a.审核状态=1 and a.采购时间>='" _
& txt01.Value & "' and a.采购时间<='" & txt02.Value & "' and a.仓库名称 LIKE '%" & combo1.Text & "%' and a.供货单位 LIKE '%" & TxtGhdw.Text & "%' and b.物资名称 like '%" & TxtWzmc0.Text & "%' and b.物资型号 like '%" & TxtWzxh0.Text & "%' GROUP BY a.仓库名称, a.物资编号,b.类别编码,b.物资名称,b.物资型号,a.供货单位 order by b.类别编码,a.物资编号"
    Set Rstmp = New ADODB.Recordset
    Rstmp.Open TxtSql, Cw_DataEnvi.DataConnect, adOpenStatic, adLockPessimistic, adCmdText
    Set DataGrid1.DataSource = Rstmp
End Sub

Private Sub Command5_Click()

End Sub

Private Sub Command6_Click()
TxtSql = "SELECT a.部门名称, a.物资编号,b.类别编码,b.物资名称,b.物资型号, SUM(a.数量) AS 数量, SUM(a.金额) AS 金额 From FL_辅料出库表 as a INNER JOIN fl_物资信息表 as b ON a.物资编号=b.物资编号 WHERE a.票据状态<2 and a.审核状态=1  and a.出库时间>='" & txt03.Value & "' and a.出库时间<='" & txt04.Value & "' and a.部门名称 LIKE '%" _
& Combo2.Text & "%' and b.物资名称 like '%" & TxtWzmc.Text & "%' and b.物资型号 like '%" & TxtWzxh.Text & "%' GROUP BY a.票据状态,a.部门名称, a.物资编号,b.类别编码,b.物资名称,b.物资型号 order by b.类别编码,a.物资编号"
    Set Rstmp = New ADODB.Recordset
    Rstmp.Open TxtSql, Cw_DataEnvi.DataConnect, adOpenStatic, adLockPessimistic, adCmdText
    Set DataGrid2.DataSource = Rstmp
End Sub

Private Sub Command7_Click()
On Error GoTo errs
    Dim Rs As ADODB.Recordset
    Dim ExcelApp As Excel.Application
    Dim ExcelBook As Excel.Workbook
    Dim ExcelSheet As Excel.Worksheet

    Set ExcelApp = New Excel.Application
    ExcelApp.Visible = False
    Set ExcelBook = ExcelApp.Workbooks.Add
    Set ExcelSheet = ExcelBook.Worksheets.Item(1)
    
    Set Rs = New ADODB.Recordset
     
    Rs.Open TxtSql, Cw_DataEnvi.DataConnect, , adLockPessimistic, adCmdText
    RecordsetToExcel Rs, ExcelSheet
    If OutTxt2.Text = "" Then
      MsgBox "请指定输出文件位置和文件名！", 16, "严重错误"
      Exit Sub
    End If
    On Error GoTo ErrSave
     ExcelBook.Close True, OutTxt2.Text
     MsgBox "输出成功！文件位于" & OutTxt2.Text
     Rs.Close

Exit Sub
errs:
    MsgBox "Select 语句错误！", 16, "严重错误"
    ExcelBook.Close False
     Exit Sub
ErrSave:
    MsgBox "输出错误！", 16, "严重错误"
End Sub

Private Sub Command8_Click()
    Cdlg.DialogTitle = "另存为Excel文件："
    Cdlg.Filter = "Excel文件|*.Xls|所有文件|*.*"
    Cdlg.ShowSave
    If Cdlg.FileName = "" Then Exit Sub
         OutTxt2.Text = Cdlg.FileName
End Sub

Private Sub DataGrid2_DblClick()
    TxtSql = "SELECT a.出库时间,a.部门名称, a.物资编号,b.类别编码,b.物资名称,b.物资型号, a.数量, a.金额 From FL_辅料出库表 as a INNER JOIN fl_物资信息表 as b ON a.物资编号=b.物资编号 WHERE a.票据状态<2 and a.审核状态=1 and a.出库时间>='" & txt03.Value & "' and a.出库时间<='" & txt04.Value & "' and a.部门名称 LIKE '%" & Combo2.Text & "%' and b.类别编码='" & DataGrid2.Columns(1) & "' and a.物资编号='" & DataGrid2.Columns(2) & "'"
    Set Rstmpp = New ADODB.Recordset
    Rstmpp.Open TxtSql, Cw_DataEnvi.DataConnect, adOpenStatic, adLockPessimistic, adCmdText
    Set Frm明细列表.DataGrid2.DataSource = Rstmpp
    Frm明细列表.DataGrid2.Visible = True
            Frm明细列表.Show 1
End Sub

Private Sub DataGrid1_DblClick()
TxtSql = "SELECT a.采购时间,a.仓库名称, a.物资编号,b.类别编码,b.物资名称,b.物资型号, a.数量,a.金额,a.供货单位 From Fl_采购票据表 as a INNER JOIN fl_物资信息表 as b ON a.物资编号=b.物资编号 WHERE a.票据状态<2 and a.审核状态=1 and a.采购时间>='" & txt01.Value & "' and a.采购时间<='" & txt02.Value & "' and a.仓库名称 LIKE '%" & combo1.Text & "%' and b.类别编码='" & DataGrid1.Columns(1) & "' and a.物资编号='" & DataGrid1.Columns(2) & "'"
    Set Rstmpp = New ADODB.Recordset
    Rstmpp.Open TxtSql, Cw_DataEnvi.DataConnect, adOpenStatic, adLockPessimistic, adCmdText
    Set Frm明细列表.DataGrid1.DataSource = Rstmpp
    Frm明细列表.DataGrid1.Visible = True
            Frm明细列表.Show 1
End Sub

Private Sub Form_Load()
    txt01.Value = Date
    txt02.Value = Date
    txt03.Value = Date
    txt04.Value = Date
    Dim RsCk As ADODB.Recordset
    Set RsCk = New ADODB.Recordset
    
    RsCk.Open "Fl_辅料仓库表", Cw_DataEnvi.DataConnect, adOpenStatic, adLockPessimistic, adCmdTable
    combo1.Clear
    Do While Not RsCk.EOF
        combo1.AddItem RsCk!仓库名称
        RsCk.MoveNext
    Loop
    If combo1.ListCount > 0 Then combo1.ListIndex = 0
    RsCk.Close
    
    Set RsCk = New ADODB.Recordset
    RsCk.Open "select * from Bs_部门分类 order by 部门名称", Cw_DataEnvi.DataConnect, adOpenStatic, adLockPessimistic, adCmdText
    Combo2.Clear
    Do While Not RsCk.EOF
        Combo2.AddItem RsCk!部门名称
        RsCk.MoveNext
    Loop
    If Combo2.ListCount > 0 Then Combo2.ListIndex = 0
    RsCk.Close
End Sub

'纪录导出到Execl
Public Sub RecordsetToExcel(Rs As ADODB.Recordset, excel_sheet As Excel.Worksheet)
    Dim i As Long, j As Long
    Dim excel_range As Excel.Range
    Dim col_count As Long

    If Rs.RecordCount = 0 Then
        Exit Sub
    End If

    Set excel_range = excel_sheet.Cells
    col_count = Rs.Fields.Count
                
    For i = 0 To col_count - 1
        excel_sheet.Cells(1, i + 1).Value = Rs.Fields(i).Name
    Next
    excel_sheet.Range(excel_sheet.Cells(1, 1), _
                      excel_sheet.Cells(1, col_count)).Font.Bold = True
        
    excel_sheet.Range("A2").CopyFromRecordset Rs
            
End Sub

Private Sub Command2_Click()
On Error GoTo errs
    Dim Rs As ADODB.Recordset
    Dim ExcelApp As Excel.Application
    Dim ExcelBook As Excel.Workbook
    Dim ExcelSheet As Excel.Worksheet

    Set ExcelApp = New Excel.Application
    ExcelApp.Visible = False
    Set ExcelBook = ExcelApp.Workbooks.Add
    Set ExcelSheet = ExcelBook.Worksheets.Item(1)
    
    Set Rs = New ADODB.Recordset
     
    Rs.Open TxtSql, Cw_DataEnvi.DataConnect, , adLockPessimistic, adCmdText
    RecordsetToExcel Rs, ExcelSheet
    If OutTxt1.Text = "" Then
      MsgBox "请指定输出文件位置和文件名！", 16, "严重错误"
      Exit Sub
    End If
    On Error GoTo ErrSave
     ExcelBook.Close True, OutTxt1.Text
     MsgBox "输出成功！文件位于" & OutTxt1.Text
     Rs.Close

Exit Sub
errs:
    MsgBox "Select 语句错误！", 16, "严重错误"
    ExcelBook.Close False
     Exit Sub
ErrSave:
    MsgBox "输出错误！", 16, "严重错误"
End Sub

Private Sub Command3_Click()
    Cdlg.DialogTitle = "另存为Excel文件："
    Cdlg.Filter = "Excel文件|*.Xls|所有文件|*.*"
    Cdlg.ShowSave
    If Cdlg.FileName = "" Then Exit Sub
         OutTxt1.Text = Cdlg.FileName
End Sub