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

前回、商品在庫画面のマスタデータ取得部分を作成しました。

この記事では、商品在庫画面の在庫表表示のプログラムを作成していきます。

VBA在庫管理システムVer02
- 商品在庫の在庫表作成

VBA在庫管理システムVer02

商品在庫の在庫表作成

VBA在庫管理システムVer01

Ver01では、M_商品を並べ替えし、在庫データを配列変数：aryIStock() に取り込んだ後、在庫表に表示するプロシージャをサブルーチンとして作成しました。

Sub ShowItemStock()

    Application.ScreenUpdating = False

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

'商品在庫表データの取り込み
Dim aryStock() As Variant
Dim i As Long, j As Long

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

'有効在庫/発注フラグを昇順で並べ替え
wsItemMaster.Range("A1").Sort key1:=wsItemMaster.Range("R1"), order1:=xlAscending, Header:=xlYes
wsItemMaster.Range("A1").Sort key1:=wsItemMaster.Range("S1"), order1:=xlAscending, Header:=xlYes

'変数の設定
ReDim aryStock(wsItemMasterRow - 2, 4) As Variant
j = 0

'変数への取り込み
For i = 2 To wsItemMasterRow
    If wsItemMaster.Cells(i, wsItemMasterColumns.M削除) = 0 Then
        aryStock(j, 0) = wsItemMaster.Cells(i, wsItemMasterColumns.M商品ID)
        aryStock(j, 1) = wsItemMaster.Cells(i, wsItemMasterColumns.M商品名称)
        aryStock(j, 2) = wsItemMaster.Cells(i, wsItemMasterColumns.M現在在庫)
        aryStock(j, 3) = wsItemMaster.Cells(i, wsItemMasterColumns.M有効在庫)
        aryStock(j, 4) = wsItemMaster.Cells(i, wsItemMasterColumns.M発注Flag)
        j = j + 1
    End If
Next

'商品在庫表リストボックスの設定
With lstStock
    .Clear
    .ColumnCount = 5
    .ColumnWidths = "50;150;80;80;60"

    .List = aryStock
End With

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

    Application.ScreenUpdating = True

End Sub

クエリの作成

在庫表の取り込み用クエリを作成していきます。
クエリの名称は、Q_在庫表とします。

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

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

＊ Q_在庫表　デザインビュー

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

Ver01では、商品在庫データを配列変数に取り込む際に、商品在庫データに以下の処理を加えました。これは、クエリで実行します。
　① 有効在庫数を昇順で並べ替え
　② 発注Flagを昇順で並べ替え
　③ 削除フラグ=0のみ取り込み

Ver01のフィルターオプションの並び替え処理を参考に、①②の処理を設定します。
デザイングリッドの [並べ替え] を昇順に設定しますが、並べ替えの実行は、右にあるフィールドからおこなわれるので、有効在庫数フィールドを作成し並べ替えを設定します。
なお、③の処理は、Q_0105_在庫数クエリで設定済みです。

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

＊ Q_在庫表　デザインビュー

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

　

在庫表の表示

在庫リストを商品在庫リストボックスに表示させるプロシージャー：ShowItemStockを修正します。

Sub ShowItemStock()

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

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

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

'SQL文の変数への取り込み
Dim strSQL As String
strSQL = "SELECT*FROM Q_在庫表"

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

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

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

'配列変数への書込み
Do Until adoRs.EOF
    aryStock(i, 0) = adoRs!商品ID
    aryStock(i, 1) = adoRs!商品名称
    aryStock(i, 2) = adoRs!現在在庫数
    aryStock(i, 3) = adoRs!有効在庫数
    aryStock(i, 4) = adoRs!発注Flag
    i = i + 1                    'カウンターを加算

    adoRs.MoveNext       'カーソルを1行下へ
Loop

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

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

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

'商品在庫表リストボックスの設定
With lstStock
    .Clear
    .ColumnCount = 5
    .ColumnWidths = "50;150;80;80;60"

    .List = aryStock
End With

End Sub

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

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

@3. SQL文の変数への取り込み
データ抽出用のSQL文を作成し、文字列変数に格納します。
レコードセットの抽出には、SELECT句を使用します。

SQL SELECT フィールド名1,フィールド名2 FROM テーブル名 WHERE フィールド名=検索条件

今回はQ_在庫表の、全てのフィールドを抽出します。

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

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

@6. 配列変数への取り込み
Do_Until_Loop構文でadoRsオブジェクト内をループしながら、変数に取り込んでいきます。
繰り返し条件は、adoRs.EOF がTrue になるまでです。EOFとは、End Of Fileの略称で、レコードセットが最後のレコードより後に達するとTrueを返します。
レコードセットのレコードが無くなったら、ループを抜けます。

＊Do_Loopステートメントについては、下記の記事で解説しています。

データの指定は、 adoRs.Fields(フィールド名)のようにFieldsコレクションを使用する方法もありますが、adoRs!フィールド名と"!"を使用すると短いコードで指定できます。

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

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

@7. リストボックスへの表示
商品在庫表リストボックスへ在庫リストを表示します。

UserForm_Initializeプロシージャの入庫リスト表示をコメントアウトしている場合は、元に戻して動作確認してみましょう。

次回は受払履歴リストの表示部分を作成していきます。
www.minizaiko.com

小さな在庫管理

在庫管理導入からシステム作成まで詳細解説！