商品在庫画面の受払履歴作成 [VBA在庫管理システムVer02#16]

前回、商品在庫画面の商品在庫表の表示部分を作成しました。

この記事では、受払履歴リストを作成していきます。

VBA在庫管理システムVer02

商品在庫の受払履歴作成

VBA在庫管理システムVer01

Ver01では、受払履歴データを抽出後、配列変数：aryStockDetail() に取り込み、受払履歴リストボックスに表示するプロシージャをサブルーチンとして作成しました。

また、チェックボックスが False の時は日別、True の時は詳細の受払履歴を表示させました。

Private Sub ShowItemStockDetailList()

    Application.ScreenUpdating = False

'作業ブックを開く
Workbooks.Open データ位置 & "在庫管理DATA.xlsx"
    
'オブジェクト変数の取得
Dim wsItemMaster As Worksheet, wsItemInOut As Worksheet, wsStockExtraction As Worksheet
Set wsItemMaster = Workbooks("在庫管理DATA.xlsx").Sheets("M_商品")
Set wsItemInOut = Workbooks("在庫管理DATA.xlsx").Sheets("T_入出庫")
Set wsStockExtraction = Workbooks("在庫管理DATA.xlsx").Sheets("受払抽出")

'棚卸し情報取得
Dim 検索行 As Long
検索行 = wsItemMaster.Range("A:A").Find(Val(lstStock.List(lstStock.ListIndex, 0)), lookat:=xlWhole).Row

'抽出条件の設定
wsStockExtraction.Cells(2, 1).Value = lstStock.List(lstStock.ListIndex, 0)
wsStockExtraction.Cells(2, 2).Value = ">" & wsItemMaster.Cells(検索行, wsItemMasterColumns.M棚卸日).Value

'受払抽出
wsItemInOut.Columns("A:T").AdvancedFilter _
 Action:=xlFilterCopy, _
 CriteriaRange:=wsStockExtraction.Range("A1:C2"), _
 CopyToRange:=wsStockExtraction.Range("F1:I1")

'棚卸し情報追記
With wsStockExtraction
    .Activate
    .Range("F2:I2").Insert shift:=xlDown

    .Cells(2, 6).Value = 0
    .Cells(2, 7).Value = wsItemMaster.Cells(検索行, wsItemMasterColumns.M棚卸日).Value
    .Cells(2, 8).Value = "棚卸数"
    .Cells(2, 9).Value = wsItemMaster.Cells(検索行, wsItemMasterColumns.M棚卸数).Value
End With

'入出庫区分/入出庫日を降順/昇順で並べ替え
wsStockExtraction.Range("F1").Sort key1:=wsStockExtraction.Range("H1"), order1:=xlDescending, Header:=xlYes
wsStockExtraction.Range("F1").Sort key1:=wsStockExtraction.Range("G1"), order1:=xlAscending, Header:=xlYes

'最終行の取得
Dim wsStockExtractionRow As Long
wsStockExtractionRow = wsStockExtraction.Cells(Rows.Count, 6).End(xlUp).Row

'同一日付で合計
Dim i As Long

If chkDetail.Value = False Then
    With wsStockExtraction
        For i = wsStockExtractionRow To 4 Step -1
            If .Cells(i, 7).Value = .Cells(i - 1, 7).Value And .Cells(i, 8).Value = .Cells(i - 1, 8).Value Then
                    .Cells(i - 1, 9).Value = .Cells(i, 9).Value + .Cells(i - 1, 9).Value
                    .Range(Cells(i, 6), Cells(i, 9)).Delete shift:=xlUp
            End If
        Next
    End With
End If

'受払履歴データの取り込み
Dim aryStockDetail() As Variant

'最終行の再取得
wsStockExtractionRow = wsStockExtraction.Cells(Rows.Count, 6).End(xlUp).Row

'変数の設定
ReDim aryStockDetail(wsStockExtractionRow - 2, 4) As Variant

'変数への取り込み
For i = 0 To wsStockExtractionRow - 2

    '入出庫日
    aryStockDetail(i, 0) = Format(wsStockExtraction.Cells(i + 2, 7), "yy/mm/dd")

    '入出庫区分
    aryStockDetail(i, 1) = wsStockExtraction.Cells(i + 2, 8)

    '入出庫数
    If aryStockDetail(i, 1) = "　出庫" Then
        aryStockDetail(i, 2) = wsStockExtraction.Cells(i + 2, 9) * -1
    Else
        aryStockDetail(i, 2) = wsStockExtraction.Cells(i + 2, 9)
    End If

    '在庫数
    If i = 0 Then
        aryStockDetail(i, 3) = aryStockDetail(i, 2)
    Else
        aryStockDetail(i, 3) = aryStockDetail(i - 1, 3) + aryStockDetail(i, 2)
    End If

    '発注フラグ
    If aryStockDetail(i, 3) <= wsItemMaster.Cells(検索行, wsItemMasterColumns.M発注点) Then
        aryStockDetail(i, 4) = "〇"
    Else
        aryStockDetail(i, 4) = ""
    End If

Next

'受払履歴リストボックスの設定
With lstStockDetail
    .Clear
    .ColumnCount = 5
    .ColumnWidths = "100;100;80;80;40"

    .List = aryStockDetail
End With

'作業ブックを閉じる
Workbooks("在庫管理DATA.xlsx").Close savechanges:=False

    Application.ScreenUpdating = True

End Sub

受払履歴クエリの作成

Ver02では、受払履歴データの作成をデータベース側に受け持たせるために、受払履歴データ取得用のクエリを作成していきます。

まず、入出庫履歴を出力するサブクエリを作成します。つぎに、それらのサブクエリを使用し、詳細 および日別 の受払履歴を出力するユニオンクエリを作成していきます。

棚卸数

棚卸数を出力するクエリを作成します。
クエリの名称は、Q_0201_棚卸数 とします。

クエリデザイン画面のクエリデザインウィンドウに、M_商品テーブル のフィールドリストを表示させます。

デザイングリッドでクエリを設定します。
各フィールドは、表示名称を入出庫日・区分・入出庫数と書き換えます。区分フィールドは"棚卸数"と表示させます。

＊ Q_0201_棚卸数　デザインビュー

データシートビューを表示して、集計データを確認します。
＊ Q_0201_棚卸数　データシートビュー

　

入庫数_詳細

入庫数を出力するクエリを作成します。
クエリの名称は、Q_0202_入庫数_詳細 とします。

クエリデザイン画面のクエリデザインウィンドウに、T_入庫テーブル と M_商品テーブル のフィールドリストを表示させます。

デザイングリッドでクエリを設定します。
各フィールドは、表示名称を入出庫日・区分・入出庫数と書き換えます。区分フィールドは"入庫"と表示させます。
入出庫日は棚卸日より後とするので、抽出条件を設定します。

＊ Q_0202_入庫数_詳細　デザインビュー

データシートビューを表示して、集計データを確認します。
＊ Q_0202_入庫数_詳細　データシートビュー

　

入庫数_日別

日別の入庫数を出力するクエリを作成します。
クエリの名称は、Q_0203_入庫数_日別 とします。

クエリデザイン画面のクエリデザインウィンドウに、T_入庫テーブル と M_商品テーブル のフィールドリストを表示させます。

デザイングリッドでクエリを設定します。
各フィールドは、表示名称を入出庫日・区分・入出庫数と書き換えます。区分フィールドは"入庫"と表示させます。
また、日別表示とするため、集計クエリに変更し入出庫数フィールドを合計にします。
入出庫日は棚卸日より後とするので、抽出条件を設定します。

＊ Q_0203_入庫数_日別　デザインビュー

データシートビューを表示して、集計データを確認します。
＊ Q_0203_入庫数_日別　データシートビュー

　

出庫数_詳細

出庫数を出力するクエリを作成します。
クエリの名称は、Q_0204_出庫数_詳細 とします。

クエリデザイン画面のクエリデザインウィンドウに、T_出庫テーブル と M_商品テーブル のフィールドリストを表示させます。

デザイングリッドでクエリを設定します。
各フィールドは、表示名称を入出庫日・区分・入出庫数と書き換えます。区分フィールドは"　出庫"と表示させます。
また、入出庫数はマイナス表示とするので、演算式を設定します。
入出庫日は棚卸日より後とするので、抽出条件を設定します。

＊ Q_0204_出庫数_詳細　デザインビュー

データシートビューを表示して、集計データを確認します。
＊ Q_0204_出庫数_詳細　データシートビュー

　

出庫数_日別

日別の出庫数を出力するクエリを作成します。
クエリの名称は、Q_0205_出庫数_日別 とします。

クエリデザイン画面のクエリデザインウィンドウに、T_出庫テーブル と M_商品テーブル のフィールドリストを表示させます。

デザイングリッドでクエリを設定します。
各フィールドは、表示名称を入出庫日・区分・入出庫数と書き換えます。区分フィールドは"　出庫"と表示させます。
また、日別表示とするため、集計クエリに変更し入出庫数フィールドを演算にして、マイナス表示を合計します。
入出庫日は棚卸日より後とするので、抽出条件を設定します。

＊ Q_0205_出庫数_日別　デザインビュー

データシートビューを表示して、集計データを確認します。
＊ Q_0205_出庫数_日別　データシートビュー

　

受払履歴_詳細

詳細の受払履歴を出力するユニオンクエリを作成します。
クエリの名称は、Q_受払_詳細 とします。

Q_0201_棚卸数をSQLビューで表示し、SQL文をコピーします。
＊ Q_0201_棚卸数　SQLビュー

Q_受払_詳細をSQLビューで表示し、SQL文を貼り付けます。

Q_0202_入庫数_詳細 および Q_0204_出庫数_詳細のSQL文をコピーし、Q_受払_詳細に貼り付けます。
このとき、各SQL文は UNION ALL で結合させます。

Q_受払_詳細を、入出庫日昇順で並べ替えします。
最後のSQL文にORDER BY句を追加します。セミコロンの位置に注意してください。
＊ Q_受払_詳細　SQLビュー

データシートビューを表示して、集計データを確認します。
＊ Q_受払_詳細　データシートビュー

　

受払履歴_日別

日別の受払履歴を出力するユニオンクエリを作成します。
クエリの名称は、Q_受払_日別 とします。

Q_0201_棚卸数、Q_0203_入庫数_日別 および Q_0205_出庫数_日別のSQL文をコピーし、Q_受払_日別に貼り付けます。
このとき、各SQL文は UNION ALL で結合させます。

また、Q_受払_詳細を、入出庫日昇順で並べ替えします。
最後のSQL文にORDER BY句を追加します。セミコロンの位置に注意してください。
＊ Q_受払_日別　SQLビュー

データシートビューを表示して、集計データを確認します。
＊ Q_受払_日別　データシートビュー

これで、受払履歴取得用のクエリができました。このクエリを使用すれば、いつでも最新の受払履歴が取得できるようになります。
　

受払履歴の表示

受払履歴を受払リストボックスに表示させるプロシージャー：ShowItemStockDetailListを修正します。

Private Sub ShowItemStockDetailList()

'データベース接続
Call DB接続

'''''''''' レコード抽出 ''''''''''

'レコードセットオブジェクトの作成
Dim adoRs As Object
Set adoRs = CreateObject("ADODB.Recordset")

'SQL文の変数への取り込み
Dim strSQL As String

If chkDetail.Value = False Then
    strSQL = "SELECT*FROM Q_受払_日別"
Else
    strSQL = "SELECT*FROM Q_受払_詳細"
End If

'レコードセットの取得
adoRs.Open strSQL, adoCn

'変数の設定
Dim aryStockDetail() As Variant
Dim i As Long

ReDim aryStockDetail(adoRs.RecordCount - 1, 4) As Variant
i = 0

'配列変数への書込み
Do Until adoRs.EOF
    If adoRs!商品ID = cmbItemID.Text Then
        '入出庫日
        aryStockDetail(i, 0) = Format(adoRs!入出庫日, "yy/mm/dd")
        '入出庫区分
        aryStockDetail(i, 1) = adoRs!区分
        '入出庫数
        aryStockDetail(i, 2) = adoRs!入出庫数
        '在庫数
        If i = 0 Then
            aryStockDetail(i, 3) = aryStockDetail(i, 2)
        Else
            aryStockDetail(i, 3) = aryStockDetail(i - 1, 3) + aryStockDetail(i, 2)
        End If
        '発注フラグ
        If aryStockDetail(i, 3) <= Val(texOrderPoint.Value) Then
            aryStockDetail(i, 4) = "〇"
        Else
            aryStockDetail(i, 4) = ""
        End If
        
        i = i + 1            'カウンターを加算
    End If
        
    adoRs.MoveNext       'カーソルを1行下へ
Loop

'レコードセットオブジェクトの破棄
adoRs.Close
Set adoRs = Nothing

''''''''''''''''''''''''''''''''''''''''

'データベース切断
Call DB切断

'受払履歴リストボックスの設定
With lstStockDetail
    .Clear
    .ColumnCount = 5
    .ColumnWidths = "100;100;80;80;40"

    .List = aryStockDetail
End With

End Sub

@1. データベース接続
サブプロシージャ：DB接続を実行し、アクセスDBと接続します。

@2. レコードセットオブジェクトの作成
adoRsオブジェクトを作成します。

@3. SQL文の変数への取り込み
データ抽出用のSQL文を作成し、文字列変数に格納します。
チェックボックスがFalseのときはQ_受払_日別 、TrueのときはQ_受払_詳細 の、全てのフィールドを抽出します。

@4. レコードセットの取得
adoRsオブジェクトのOpenメソッドを使用し、 SQLを実行して、抽出結果をレコードセットへ取り込みます。

@5. 配列変数の設定
データ格納用に、配列変数を宣言し、要素数を設定します。
adoRs.RecordCountで、レコードセットのレコード数を取得できます。

@6. 配列変数への書き込み
Do_Until_Loop構文でadoRsオブジェクト内をループしながら、変数に取り込んでいきます。
選択した商品IDで抽出し、データを取得します。
① 入出庫日・区分・入出庫数を、配列変数に取り込みます。
② 在庫数を棚卸数を基準に累積しながら、配列変数に取り込みます。
ただし、１行目は棚卸情報なので、累積せずに棚卸数を取り込みます。
③ ②で取得したデータを利用して、発注Flagを発行し、配列変数に取り込みます。

@7. レコードセットオブジェクトの破棄
レコードセットのデータは、配列変数に取り込みましたので、adoRsオブジェクトを破棄します。
adoRsオブジェクトのCloseメソッドを使用し、閉じます。
adoRsオブジェクトを破棄します。

@8. データベース切断
サブプロシージャ：DB切断を実行し、アクセスDBと切断します。

@9. 受払履歴リストへの表示
ColumnWidthsで表示状態を設定し、受払履歴リストボックスへ表示します。

商品在庫画面の受払履歴リストができました。
次回は発注リスト出力部分を作成していきます。
www.minizaiko.com