商品在庫画面の在庫更新作成 [VBA在庫管理システムVer02#13]

前回、商品出庫画面の動作設定をおこないました。ここから商品在庫画面のプログラムを作成していきます。

この記事では、商品在庫画面の在庫更新用のクエリを作成していきます。

VBA在庫管理システムVer02

在庫更新の準備作業

在庫数の取り扱いについて

商品在庫表には、現在在庫数と有効在庫数の２つの在庫数を表示させ、在庫数を監視する指標とします。

現在在庫数とは、棚卸数を基準に、前日までの出庫数(出庫済み数)を減算し、当日までの入庫数(入庫済み数)を加算した在庫数で、当日の使用可能在庫数となります。

有効在庫数とは、現在在庫数から当日以降の出庫数(出庫予定数)を減算した未来の在庫数です。有効在庫数はマイナス表示になる場合もあります。

発注Flagは、有効在庫数に発注残数:翌日以降の入庫数(入庫予定数)を加算した在庫数が、発注点を下回った時に発行します。

現在在庫数・有効在庫数・発注Flagは、在庫データから下記の数量を取得すると、それぞれ計算で求められます。

棚卸日、棚卸数
出庫済み数、出庫予定数
入庫済み数、入庫予定数
発注点

このシステムでは、棚卸日・棚卸数・発注点はマスタデータとして登録されているので、出庫済み数・出庫予定数・入庫済み数・入庫予定数を算出すれば、現在在庫数・有効在庫数・発注Flagが求まります。

また、在庫の推移などは、下記の記事で解説していますので参考にしてみてください。

商品マスタの在庫更新

VBA在庫管理システムVer01

商品マスタの在庫データを更新するプロシージャをサブルーチンとして、標準モジュール 在庫更新に作成しました。実行速度が遅いのが、難点でした。

Sub GetItemStockUpdate()

    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 Buf() As Variant
Dim i As Long

'最終行の取得
Dim wsItemMasterRow As Long
wsItemMasterRow = wsItemMaster.Cells(Rows.Count, 1).End(xlUp).Row

'変数の設定
ReDim Buf(wsItemMasterRow - 1, 6) As Variant

With wsStockExtraction
    For i = 0 To wsItemMasterRow - 2
    
        '抽出条件の設定
        .Cells(2, 1).Value = wsItemMaster.Cells(i + 2, 1).Value
        .Cells(2, 2).Value = ">" & wsItemMaster.Cells(i + 2, 11).Value
        
        '受払抽出
        wsItemInOut.Columns("A:H").AdvancedFilter _
         Action:=xlFilterCopy, _
         CriteriaRange:=.Range("A1:C2"), _
         CopyToRange:=.Range("F1:I1")
        
        '出庫済み数
        Buf(i, 0) = WorksheetFunction.SumIfs(.Range("I:I"), .Range("H:H"), "　出庫", .Range("G:G"), "<" & Date)
        
         '出庫予定数
        Buf(i, 1) = WorksheetFunction.SumIfs(.Range("I:I"), .Range("H:H"), "　出庫", .Range("G:G"), ">=" & Date)
    
        '入庫済み数
        Buf(i, 2) = WorksheetFunction.SumIfs(.Range("I:I"), .Range("H:H"), "入庫", .Range("G:G"), "<=" & Date)
    
         '出庫予定数
        Buf(i, 3) = WorksheetFunction.SumIfs(.Range("I:I"), .Range("H:H"), "入庫", .Range("G:G"), ">" & Date)
    
         '現在在庫
        Buf(i, 4) = wsItemMaster.Cells(i + 2, 12) - Buf(i, 0) + Buf(i, 2)
        
         '有効在庫
        Buf(i, 5) = Buf(i, 4) - Buf(i, 1)
        
         '発注Flag
         If Buf(i, 5) + Buf(i, 3) <= wsItemMaster.Cells(i + 2, 9) Then
            Buf(i, 6) = "〇"
        Else
            Buf(i, 6) = ""
        End If
     
    Next
End With

'製品マスタへの書き込み
wsItemMaster.Range("M2").Resize(UBound(Buf, 1) + 1, UBound(Buf, 2) + 1).Value = Buf

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

    Application.ScreenUpdating = True

End Sub

在庫更新クエリの作成

Ver.02では、在庫更新をデータベース側に受け持たせるために、在庫更新用のクエリを作成していきます。

Ver.01の在庫更新を参考に、入出庫数を算出するサブクエリを作成します。さらに、それらのサブクエリを使用し、在庫数を出力する選択クエリを作成していきます。

出庫済み数

出庫済み数を算出するクエリを作成します。
期間条件は、Ver01を参考にして、棚卸日より後 かつ 今日より前とします。
また、クエリの名称は、Q_0101_出庫済み数 とします。

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

デザイングリッドに、M_商品テーブルの商品ID、T_出庫テーブルの出庫数・出庫日・削除を追加していきます。

[表示/非表示]-[集計] をクリックし集計クエリに変更します。
デザイングリッドに [集計] 行 が挿入されます。

デザイングリッドでクエリを設定します。
出庫数フィールドは、表示名称を出庫済みとするため、 "出庫済み：出庫数" と書き換えます。

＊ Q_0101_出庫済み数　デザインビュー

データシートビューを表示して、集計データを確認します。
＊ Q_0101_出庫済み数　データシートビュー

　

出庫予定数

出庫予定数を算出するクエリを作成します。
期間条件は、Ver01を参考にして、今日以降とします。
また、クエリの名称は、Q_0102_出庫予定数 とします。

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

デザイングリッドでクエリを設定します。
出庫数フィールドは、表示名称を出庫予定とするため、 "出庫予定：出庫数" と書き換えます。

＊ Q_0102_出庫予定数　デザインビュー

データシートビューを表示して、集計データを確認します。
＊ Q_0102_出庫予定数　データシートビュー

　

入庫済み数

入庫済み数を算出するクエリを作成します。
期間条件は、Ver01を参考にして、棚卸日より後 かつ 今日以前とします。
また、クエリの名称は、Q_0103_入庫済み数 とします。

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

デザイングリッドでクエリを設定します。
入庫数フィールドは、表示名称を入庫済みとするため、 "入庫済み：入庫数" と書き換えます。

＊ Q_0103_入庫済み数　デザインビュー

データシートビューを表示して、集計データを確認します。
＊ Q_0103_入庫済み数　データシートビュー

　

入庫予定数

入庫予定数を算出するクエリを作成します。
期間条件は、Ver01を参考にして、今日より後とします。
また、クエリの名称は、Q_0104_入庫予定数 とします。

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

デザイングリッドでクエリを設定します。
入庫数フィールドは、表示名称を入庫予定とするため、 "入庫予定：入庫数" と書き換えます。

＊ Q_0104_入庫予定数　デザインビュー

データシートビューを表示して、集計データを確認します。
＊ Q_0104_入庫予定数　データシートビュー

　

在庫数

ここまでに作成したサブクエリを使用し、在庫数を出力する選択クエリを作成していきます。
クエリの名称は、Q_0105_在庫数 とします。

クエリデザイン画面のクエリデザインウィンドウに、M_商品テーブル および 4つのサブクエリのフィールドリストを表示させます。
商品マスタに登録されている全ての商品を表示させるため、各フィールドリストの結合方法を変更します。結合線をダブルクリックし、結合プロパティを表示し、2：M_商品の全レコードと… を選択します。結合線は、→ の矢印表示となります。

デザイングリッドでクエリを設定します。

＊ Q_0105_在庫数　デザインビュー

データシートビューを表示して、集計データを確認します。
＊ Q_0105_在庫数　データシートビュー

データにブランクの部分があります。これは、Null値といい、データが無い状態です。このままでは、演算に使用できないので、Null → 0 に変換するため、該当フィールド を集計フィールド に変更していきます。[フィールド]に演算式を書き込みます。

出庫済み →
出庫済み数: IIf(IsNull([Q_0101_出庫済み数]![出庫済み]),0,[Q_0101_出庫済み数]![出庫済み])
出庫予定 →
出庫予定数: IIf(IsNull([Q_0102_出庫予定数]![出庫予定]),0,[Q_0102_出庫予定数]![出庫予定])
入庫済み →
入庫済み数: IIf(IsNull([Q_0103_入庫済み数]![入庫済み]),0,[Q_0103_入庫済み数]![入庫済み])
入庫予定 →
入庫予定数: IIf(IsNull([Q_0104_入庫予定数]![入庫予定]),0,[Q_0104_入庫予定数]![入庫予定])

データシートビューを表示して、集計データを確認します。
＊ Q_0105_在庫数　データシートビュー

在庫数のフィールドを追加していきます。
デザイングリッドでクエリを設定します。

追加した3つのフィールドは、集計フィールドなので、[フィールド]に演算式を書き込みます。

現在在庫数 →
現在在庫数: [棚卸数]-[出庫済み数]+[入庫済み数]
有効在庫数 →
有効在庫数: [棚卸数]-[出庫済み数]+[入庫済み数]-[出庫予定数]
発注Flag →
発注Flag: IIf([有効在庫数]+[入庫予定数]<=[M_商品]![発注点],"〇"," ")

＊ Q_0105_在庫数　デザインビュー

データシートビューを表示して、集計データを確認します。
＊ Q_0105_在庫数　データシートビュー

これで、在庫更新用のクエリができました。このクエリを使用すれば、いつでも最新の在庫情報が取得できるようになります。
　

在庫更新フォームの削除

VBA在庫管理システムVer01

Ver01では、メニュー画面から直接在庫画面を開くのではなく、メニュー画面 → 在庫更新選択画面 → 在庫画面の順に画面遷移を作成しました。

Private Sub btnItemStock_Click()

frmItemStockSelect.show
Unload Me

End Sub

メニュー画面の商品在庫ボタン

Ver02では、在庫更新をデータベース側に受け持たせたため、速度の遅いサブルーチンは実行しません。そこで、画面遷移の迂回は不要になるので、プロシージャを変更します。

Private Sub btnItemStock_Click()

frmItemStock.Show
Unload Me

End Sub

@1.在庫画面の表示
メニュー画面から直接在庫画面を開く様に画面遷移を変更します。
　　
　

次回は、商品在庫画面のマスタデータ取得のプログラムを作成していきます。
www.minizaiko.com