商品入庫画面の入庫リスト作成 [VBA在庫管理システムVer02#08]

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

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

VBA在庫管理システムVer02

商品入庫の入庫リスト作成

VBA在庫管理システムVer01

Ver01では、フィルターオプション機能を利用し、入庫データを配列変数aryInputDetail() に取り込んだ後、入庫リストに表示するプロシージャをサブルーチンとして作成しました。

入庫予定リストの表示

当日以降の入庫予定リストを入庫リストボックスに表示させるプロシージャーをサブルーチンとして作成しました。

Sub ShowInputAllScheduleList()

        Application.ScreenUpdating = False

'作業ブックを開く
Workbooks.Open データ位置 & "在庫管理DATA.xlsx"

'オブジェクト変数の取得
Dim wsInOut As Worksheet, wsInOutExtract As Worksheet
Set wsInOut = Workbooks("在庫管理DATA.xlsx").Sheets("T_入出庫")
Set wsInOutExtract = Workbooks("在庫管理DATA.xlsx").Sheets("入出庫抽出")

'検索条件の入力
With wsInOutExtract
    .Cells(2, 2).Value = ">=" & Date
    .Cells(2, 3).Value = "入庫"
    .Cells(2, 4).Value = 0
End With

'フィルターオプションの実行
wsInOut.Columns("A:H").AdvancedFilter Action:=xlFilterCopy, _
 CriteriaRange:=wsInOutExtract.Range("B1:D2"), _
 CopyToRange:=wsInOutExtract.Range("G1:L1")

'抽出結果の最終行取得
Dim wsInOutExtractRow As Long
wsInOutExtractRow = wsInOutExtract.Cells(Rows.Count, 7).End(xlUp).Row

If wsInOutExtractRow > 1 Then

    '入出庫日を昇順で並べ替え
    wsInOutExtract.Range("G1").Sort key1:=wsInOutExtract.Range("H1"), order1:=xlAscending, Header:=xlYes
    
    '配列変数への書込み
    Dim aryInputDetail() As Variant
    Dim i As Long
    
    ReDim aryInputDetail(wsInOutExtractRow - 2, 5) As Variant
    For i = 2 To wsInOutExtractRow
        aryInputDetail(i - 2, 0) = wsInOutExtract.Cells(i, 7)
        aryInputDetail(i - 2, 1) = Format(wsInOutExtract.Cells(i, 8), "yy/mm/dd")
        aryInputDetail(i - 2, 2) = wsInOutExtract.Cells(i, 9)
        aryInputDetail(i - 2, 3) = wsInOutExtract.Cells(i, 10)
        aryInputDetail(i - 2, 4) = wsInOutExtract.Cells(i, 11)
        aryInputDetail(i - 2, 5) = wsInOutExtract.Cells(i, 12)
    Next
        
    '入庫リストの表示
    With lstInput
        .Clear
        .ColumnCount = 6
        .ColumnWidths = "0;100;80;380;100;90"
    
        .List = aryInputDetail()
    End With

ElseIf wsInOutExtractRow = 1 Then               '入庫予定無し時の回避
    
    '入庫リストの表示
    lstInput.Clear

End If

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

        Application.ScreenUpdating = True

'オプションボタン設定
optInputAllScheduleList.Value = True

End Sub

商品別リストの表示

選択された商品の棚卸日以降の入庫リストを入庫リストボックスに表示させるプロシージャーをサブルーチンとして作成しました。

Sub ShowInputItemScheduleList()

'異常値の回避
If cmbItemID.Text = "" Then
    optInputAllScheduleList.Value = True
    Exit Sub
End If

        Application.ScreenUpdating = False

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

'棚卸し情報取得
Dim 検索行 As Long
検索行 = wsItemMaster.Range("A:A").Find(cmbItemID.Text, lookat:=xlWhole).Row
Dim 棚卸日 As Date
棚卸日 = wsItemMaster.Cells(検索行, 11).Value

'検索条件の入力
With wsInOutExtract
    .Cells(2, 1).Value = cmbItemID.Text
    .Cells(2, 2).Value = ">" & 棚卸日
    .Cells(2, 3).Value = "入庫"
    .Cells(2, 4).Value = 0
End With

'フィルターオプションの実行
wsInOut.Columns("A:H").AdvancedFilter Action:=xlFilterCopy, _
 CriteriaRange:=wsInOutExtract.Range("A1:D2"), _
 CopyToRange:=wsInOutExtract.Range("G1:L1")

'抽出結果の最終行取得
Dim wsInOutExtractRow As Long
wsInOutExtractRow = wsInOutExtract.Cells(Rows.Count, 7).End(xlUp).Row

If wsInOutExtractRow > 1 Then

    '入出庫日を昇順で並べ替え
    wsInOutExtract.Range("G1").Sort key1:=wsInOutExtract.Range("H1"), order1:=xlAscending, Header:=xlYes
    
    '配列変数への書込み
    Dim aryInputDetail() As Variant
    Dim i As Long
    
    ReDim aryInputDetail(wsInOutExtractRow - 2, 5) As Variant
    For i = 2 To wsInOutExtractRow
        aryInputDetail(i - 2, 0) = wsInOutExtract.Cells(i, 7)
        aryInputDetail(i - 2, 1) = Format(wsInOutExtract.Cells(i, 8), "yy/mm/dd")
        aryInputDetail(i - 2, 2) = wsInOutExtract.Cells(i, 9)
        aryInputDetail(i - 2, 3) = wsInOutExtract.Cells(i, 10)
        aryInputDetail(i - 2, 4) = wsInOutExtract.Cells(i, 11)
        aryInputDetail(i - 2, 5) = wsInOutExtract.Cells(i, 12)
    Next
        
    '入庫リストの表示
    With lstInput
        .Clear
        .ColumnCount = 6
        .ColumnWidths = "0;100;80;380;100;90"
    
        .List = aryInputDetail()
    End With

ElseIf wsInOutExtractRow = 1 Then               '入庫予定無し時の回避
    
    '入庫リストの表示
    lstInput.Clear

End If

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

        Application.ScreenUpdating = True

'オプションボタン設定
optInputItemScheduleList.Value = True

End Sub

クエリの作成

入庫リストデータの取り込み用クエリを作成していきます。

Ver01では、フィルターオプションの検索条件として、４つの項目を指定しています。
　① 商品ID
　② 入出庫日
　③ 入出庫区分
　④ 削除

商品入庫画面では入庫予定リストと商品別リストの２つのリストを表示させる為、各リスト表示と検索条件との関係がクエリ作成のポイントとなります。
　① 各リスト毎に検索条件設定
　② 各リスト毎に検索条件設定
　③ Ver02ではテーブル分割したので不要
　④ 両リスト共に削除フラグ=0設定

両リストで共通する項目はクエリで実行させるため、Ver02では検索条件を下記の通り設定します。
　①② はリスト取得時のVBA_SQL条件
　③ は不要
　④ はクエリで実行

また、Ver01では、商品入庫データを配列変数に取り込む際に、商品入庫データに以下の処理を加えました。これも、クエリで実行します。
　⑤ 入庫日を昇順で並べ替え

Ver01のフィルターオプションの抽出条件を参考に、④⑤の処理を含め、選択クエリを作成します。

デザイングリッドの入庫日フィールドの [並べ替え] を昇順に設定し、⑤を実行します。
削除フィールドの [抽出条件] をFalseに設定し、④を実行します。
また、削除フィールドは条件フィールドのため、[表示] のチェックを外して、データシートビューには非表示とします。

　

入庫予定リストの表示

当日以降の入庫予定リストを入庫リストボックスに表示させるプロシージャー：ShowInputAllScheduleListを修正します。

Sub ShowInputAllScheduleList()

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

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

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

'SQL文の変数への取り込み
Dim strSQL As String
strSQL = "SELECT*FROM Q_T入庫 WHERE 入庫日 >= #" & Date & "# "

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


If adoRs.RecordCount >= 1 Then

    '変数の設定
    Dim aryInputDetail() As Variant
    Dim i As Long
        
    ReDim aryInputDetail(adoRs.RecordCount - 1, 5) As Variant
    i = 0
    
    '配列変数への書込み
    Do Until adoRs.EOF
        aryInputDetail(i, 0) = adoRs!入庫ID
        aryInputDetail(i, 1) = adoRs!入庫日
        aryInputDetail(i, 2) = adoRs!商品ID
        aryInputDetail(i, 3) = adoRs!商品名称
        aryInputDetail(i, 4) = adoRs!入庫数
        aryInputDetail(i, 5) = adoRs!入庫内訳
        i = i + 1                    'カウンターを加算
    
        adoRs.MoveNext       'カーソルを1行下へ
    Loop
    
    'レコードセットオブジェクトの破棄
    adoRs.Close
    Set adoRs = Nothing
    
    ''''''''''''''''''''''''''''''''''''''''
    
    'データベース切断
    Call DB切断

    '入庫リストの表示
    With lstInput
        .Clear
        .ColumnCount = 6
        .ColumnWidths = "0;100;80;380;100;90"
    
        .List = aryInputDetail()
    End With

ElseIf adoRs.RecordCount = 0 Then    '入庫予定無し時の回避

    'レコードセットオブジェクトの破棄
    adoRs.Close
    Set adoRs = Nothing
    
    ''''''''''''''''''''''''''''''''''''''''
    
    'データベース切断
    Call DB切断

    '入庫リストの表示
    lstInput.Clear

End If

'オプションボタン設定
optInputAllScheduleList.Value = True

End Sub

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

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

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

SQL

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

今回はQ_T入庫クエリの、全てのフィールドを抽出します。

また、当日以降のデータを抽出するため、WHERE句で条件設定します。
検索条件のデータはフィールドの型にあわせて、次の設定が必要です。
　 　日付型 ： # 日付 #
　　 文字列 ： ' 文字列 ' (シングルコーテーション)
　 　数値型 ：　数値 　　(そのまま)
　Yes/No型 ：　True / False

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

@5. 入庫リストへの表示
adoRs.RecordCountで、レコードセットのレコード数を取得できます。
抽出結果のレコードが有るときは、抽出結果を変数に取り込み、リストへ表示します。
抽出結果のレコードが無いときは、リストはクリアして表示なしとします。
どちらの場合でも、adoRsオブジェクトの破棄とデータベース切断を実行します。

@6. オプションボタンの設定
入庫予定リストのオプションボタンを選択状態にします。
　

商品別リストの表示

選択された商品の棚卸日より後の入庫リストを入庫リストボックスに表示させるプロシージャーをサブルーチンとして作成します。

Sub ShowInputItemScheduleList()

'異常値の回避
If cmbItemID.Text = "" Then
    optInputAllScheduleList.Value = True
    Exit Sub
End If

'棚卸し情報取得
Dim 棚卸日 As Date
棚卸日 = cmbItemName.List(cmbItemName.ListIndex, 10)

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

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

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

'SQL文の変数への取り込み
Dim strSQL As String
strSQL = "SELECT*FROM Q_T入庫 WHERE 商品ID = " & cmbItemID.Value & " AND 入庫日 > #" & 棚卸日 & "# "

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

If adoRs.RecordCount >= 1 Then

    '変数の設定
    Dim aryInputDetail() As Variant
    Dim i As Long
        
    ReDim aryInputDetail(adoRs.RecordCount - 1, 5) As Variant
    i = 0
    
    '配列変数への書込み
    Do Until adoRs.EOF
        aryInputDetail(i, 0) = adoRs!入庫ID
        aryInputDetail(i, 1) = adoRs!入庫日
        aryInputDetail(i, 2) = adoRs!商品ID
        aryInputDetail(i, 3) = adoRs!商品名称
        aryInputDetail(i, 4) = adoRs!入庫数
        aryInputDetail(i, 5) = adoRs!入庫内訳
        i = i + 1                    'カウンターを加算
    
        adoRs.MoveNext       'カーソルを1行下へ
    Loop
    
    'レコードセットオブジェクトの破棄
    adoRs.Close
    Set adoRs = Nothing
    
    ''''''''''''''''''''''''''''''''''''''''
    
    'データベース切断
    Call DB切断

    '入庫リストの表示
    With lstInput
        .Clear
        .ColumnCount = 6
        .ColumnWidths = "0;100;80;380;100;90"
    
        .List = aryInputDetail()
    End With

ElseIf adoRs.RecordCount = 0 Then    '入庫予定無し時の回避

    'レコードセットオブジェクトの破棄
    adoRs.Close
    Set adoRs = Nothing
    
    ''''''''''''''''''''''''''''''''''''''''
    
    'データベース切断
    Call DB切断

    '入庫リストの表示
    lstInput.Clear

End If

'オプションボタン設定
optInputItemScheduleList.Value = True

End Sub

@1. 異常値の回避
商品選択が無いときは、入庫予定リストを表示し、プロシージャを抜けます。

@2. 棚卸情報の取得
商品名称コンボボックスの棚卸情報から、棚卸日を取得します。

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

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

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

SQL

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

今回はQ_T入庫クエリの、全てのフィールドを抽出します。

また、製品ID 及び 棚卸日より後のデータを抽出するため、WHERE句でAND条件設定します。
検索条件のデータはフィールドの型にあわせて、次の設定が必要です。
　 　日付型 ： # 日付 #
　　 文字列 ： ' 文字列 ' (シングルコーテーション)
　 　数値型 ：　数値 　　(そのまま)
　Yes/No型 ：　True / False

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

@7. 入庫リストへの表示
adoRs.RecordCountで、レコードセットのレコード数を取得できます。
抽出結果のレコードが有るときは、抽出結果を変数に取り込み、リストへ表示します。
抽出結果のレコードが無いときは、リストはクリアして表示なしとします。
どちらの場合でも、adoRsオブジェクトの破棄とデータベース切断を実行します。

@8. オプションボタンの設定
商品別リストのオプションボタンを選択状態にします。
　

入庫リストの表示設定

入庫リストボックスには、初期値として入庫予定リストを表示し、商品選択時 および オプションボタン選択時に商品別リストを表示します。
サブルーチンの実行位置は５箇所となりますので、動作確認してみましょう。
また、UserForm_Initializeプロシージャの入庫リスト表示をコメントアウトしている場合は、元に戻しましょう。

次回は商品入庫画面の実行ボタン（登録・修正・削除）のプログラムを作成します。
www.minizaiko.com