库存状况.frm

引用枕善居的天宏钢构仓库管理系统 Ver 2.96

      EndProperty
      BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
         Name            =   "楷体_GB2312"
         Size            =   11.25
         Charset         =   134
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ColumnCount     =   2
      BeginProperty Column00 
         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 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
      SplitCount      =   1
      BeginProperty Split0 
         MarqueeStyle    =   1
         RecordSelectors =   0   'False
         BeginProperty Column00 
         EndProperty
         BeginProperty Column01 
         EndProperty
      EndProperty
   End
   Begin MSDataListLib.DataCombo DataCombo1 
      Height          =   330
      Left            =   1380
      TabIndex        =   1
      Top             =   180
      WhatsThisHelpID =   215
      Width           =   1995
      _ExtentX        =   3519
      _ExtentY        =   529
      _Version        =   393216
      Style           =   2
      BackColor       =   14737632
      Text            =   ""
   End
   Begin VB.Frame Frame1 
      Height          =   615
      Left            =   0
      TabIndex        =   3
      Top             =   0
      Width           =   3495
   End
End
Attribute VB_Name = "库存状况"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
'****************************************************************************
'人人为我，我为人人
'枕善居收藏整理
'发布日期：2008/05/11
'描    述：天宏钢构仓库管理系统 Ver 2.96
'网    站：http://www.Mndsoft.com/  (VB6源码博客)
'网    站：http://www.VbDnet.com/   (VB.NET源码博客,主要基于.NET2005)
'e-mail  ：[e-mail]
'e-mail  ：[e-mail]
'OICQ    ：88382850
'          如果您有新的好的代码别忘记给枕善居哦!
'****************************************************************************
Option Explicit
Dim SQL As String

Private Sub initdatagrid1()

    DataGrid1.Columns(0).width = 800
    DataGrid1.Columns(1).width = 800
    DataGrid1.Columns(2).width = 1900
    DataGrid1.Columns(3).width = 1200
    DataGrid1.Columns(4).width = 1200
    DataGrid1.Columns(5).width = 1000
    DataGrid1.Columns(6).width = 1200
    DataGrid1.Columns(7).width = 1000
    DataGrid1.Columns(8).width = 800
    DataGrid1.Columns(9).width = 1200

End Sub

Private Sub Command1_Click()
 On Error GoTo quit
    '获取仓库编号
    
    If Text1.Text <> "" Then
    'fMainForm.m_checkado.RecordSource = "select 编号, 货物名称,货物规格,计量单位 from 货物信息 where  货物信息.货物名称 like '%" + Text1.Text + "%'"
         fMainForm.m_checkado.RecordSource = "select  库存状况.编号,库存状况.货物编号,货物信息.货物名称,货物信息.货物类别,货物信息.货物规格,货物信息.计量单位,库存状况.库存数量,入库单.入库单价 as 单价,(入库单.入库单价*入库单.入库数量) as 金额,货物信息.最低限量,货物信息.最高限量,仓库.仓库名称 as 存放仓库 from 库存状况,货物信息,仓库,入库单 where 货物信息.编号=库存状况.货物编号 and 仓库.编号=库存状况.仓库编号 and 库存状况.货物编号 = 入库单.货物编号 and  货物信息.货物名称 like '%" + Text1.Text + "%'"
    fMainForm.m_checkado.Refresh
    
    End If
    
    If Text3.Text <> "" Then
         fMainForm.m_checkado.RecordSource = "select 库存状况.编号,库存状况.货物编号,货物信息.货物名称,货物信息.货物类别,货物信息.货物规格,货物信息.计量单位,库存状况.库存数量,货物信息.最低限量,货物信息.最高限量,仓库.仓库名称 as 存放仓库 from 库存状况,货物信息,仓库 where 货物信息.编号=库存状况.货物编号 and 仓库.编号=库存状况.仓库编号 and  货物信息.货物规格 like '%" + Text3.Text + "%'"
    End If
    
    Adodc3.RecordSource = fMainForm.m_checkado.RecordSource
    Set DataGrid1.DataSource = Adodc3
    Adodc1.Refresh
    
    initdatagrid1
    
quit:
End Sub

Private Sub Command2_Click()
On Error GoTo quit
MsgBox "正在导出数据，请稍候", vbOKOnly + 32, "导出数据生成EXCEL"
Call ExcelDoForVB
quit:
End Sub
Private Sub ExcelDoForVB1()

Dim i As Integer, j As Integer
    Dim myexcel As New Excel.Application
    Dim mybook As New Excel.Workbook
    Dim mysheet As New Excel.Worksheet
    Set mybook = myexcel.Workbooks.Add '添加一个新的BOOK
    Set mysheet = mybook.Worksheets.Add '添加一个新的SHEET
     For i = 1 To Adodc1.Recordset.RecordCount
     For j = 1 To Adodc1.Recordset.Fields.count
     mysheet.Cells(i, j) = Adodc1.Recordset.Fields.Item(j - 1).Value
     If (i * j) Mod 500 = 0 Then
     DoEvents
     End If
     Next j
     Adodc1.Recordset.MoveNext
   
     Next i
     myexcel.Visible = True
End Sub
Private Sub ExcelDoForVB()
Dim i As Integer, j As Integer
    Dim myexcel As New Excel.Application
    Dim mybook As New Excel.Workbook
    Dim mysheet As New Excel.Worksheet
    Set mybook = myexcel.Workbooks.Add '添加一个新的BOOK
    Set mysheet = mybook.Worksheets.Add '添加一个新的SHEET
    
     myexcel.Visible = True
     
    '快速显示excel表格
     mysheet.Cells.CopyFromRecordset Adodc1.Recordset
   
' 用EXCEL报表显示
    Selection.EntireRow.Insert
    Selection.EntireRow.Insert
    Selection.AutoFill Destination:=Range("A1:I1"), Type:=xlFillDefault
    Range("A1:I1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Selection.Merge
    Range("A1:L1").Select
    ActiveCell.FormulaR1C1 = "仓库库存信息详单"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "编号"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "货物编号"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "货物名称"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "货物类别"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "货物规格"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "计量单位"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "库存数量"
    Range("H2").Select
    ActiveCell.FormulaR1C1 = "单价"
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "金额"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "最低限量"
    Range("K2").Select
    ActiveCell.FormulaR1C1 = "最高限量"
    Range("L2").Select
    ActiveCell.FormulaR1C1 = "存放仓库"
    
    Range("A3").Select
End Sub
Private Sub DataCombo1_Change()
    限定仓库.Value = 0
End Sub

Private Sub Form_Load()

    Adodc1.ConnectionString = DataConnectString
    SQL = Adodc1.RecordSource
    Set DataGrid1.DataSource = Adodc1
    Adodc1.Refresh
    Adodc1.Visible = False
    
    Adodc2.ConnectionString = DataConnectString
    Set DataCombo1.RowSource = Adodc2
    DataCombo1.ListField = "仓库名称"
    Adodc2.Refresh
    Adodc2.Visible = False
    
    Adodc3.ConnectionString = DataConnectString
     Set DataGrid1.DataSource = Adodc1
    Adodc1.Refresh
    Adodc1.Visible = False
    
    initdatagrid1
    
End Sub

Private Sub update()

    If 限定仓库.Value = 1 And DataCombo1.Text = "" Then MsgBox "请选择仓库!": Exit Sub
    
    Dim 仓库编号 As String
    
    On Error Resume Next
    '获取仓库编号
    fMainForm.m_checkado.RecordSource = "select 编号 from 仓库 where 仓库名称='" + DataCombo1.Text + "'"
    fMainForm.m_checkado.Refresh
    
    仓库编号 = fMainForm.m_checkado.Recordset.Fields("编号").Value
    
    If 限定仓库.Value = 1 And DataCombo1.Text <> "" Then
        Adodc1.RecordSource = SQL + " and 库存状况.仓库编号=" + 仓库编号
    Else
        Adodc1.RecordSource = SQL
    End If
    Adodc1.Refresh
    
    initdatagrid1
    
End Sub

Private Sub 限定仓库_Click()
    update
End Sub