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

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

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

VBA在庫管理システムVer02

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

VBA在庫管理システムVer01

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

出庫予定リストの表示

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

ub ShowOutputAllScheduleList()

        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 aryOutputDetail() As Variant
    Dim i As Long
    
    ReDim aryOutputDetail(wsInOutExtractRow - 2, 5) As Variant
    For i = 2 To wsInOutExtractRow
        aryOutputDetail(i - 2, 0) = wsInOutExtract.Cells(i, 7)
        aryOutputDetail(i - 2, 1) = Format(wsInOutExtract.Cells(i, 8), "yy/mm/dd")
        aryOutputDetail(i - 2, 2) = wsInOutExtract.Cells(i, 9)
        aryOutputDetail(i - 2, 3) = wsInOutExtract.Cells(i, 10)
        aryOutputDetail(i - 2, 4) = wsInOutExtract.Cells(i, 11)
        aryOutputDetail(i - 2, 5) = wsInOutExtract.Cells(i, 12)
    Next
        
    '出庫リストの表示
    With lstOutput
        .Clear
        .ColumnCount = 6
        .ColumnWidths = "0;100;80;380;100;90"
    
        .List = aryOutputDetail()
    End With

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

End If

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

        Application.ScreenUpdating = True

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

End Sub

End If

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

        Application.ScreenUpdating = True

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

End Sub

商品別リストの表示

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

Sub ShowOutputItemScheduleList()

'異常値の回避
If cmbItemID.Text = "" Then
    optOutputAllScheduleList.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 aryOutputDetail() As Variant
    Dim i As Long
    
    ReDim aryOutputDetail(wsInOutExtractRow - 2, 5) As Variant
    For i = 2 To wsInOutExtractRow
        aryOutputDetail(i - 2, 0) = wsInOutExtract.Cells(i, 7)
        aryOutputDetail(i - 2, 1) = Format(wsInOutExtract.Cells(i, 8), "yy/mm/dd")
        aryOutputDetail(i - 2, 2) = wsInOutExtract.Cells(i, 9)
        aryOutputDetail(i - 2, 3) = wsInOutExtract.Cells(i, 10)
        aryOutputDetail(i - 2, 4) = wsInOutExtract.Cells(i, 11)
        aryOutputDetail(i - 2, 5) = wsInOutExtract.Cells(i, 12)
    Next
        
    '出庫リストの表示
    With lstOutput
        .Clear
        .ColumnCount = 6
        .ColumnWidths = "0;100;80;380;100;90"
    
        .List = aryOutputDetail()
    End With

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

End If

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

        Application.ScreenUpdating = True

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

End Sub

クエリの作成

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

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

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

よって、Ver02では、検索条件を下記の通り設定します。
　①② はリスト取得時のVBA_SQL条件
　③ は不要
　④ はクエリで実行

また、Ver01では、商品出庫データを配列変数に取り込む際に、商品出庫データに以下の処理を加えました。
　⑤ 出庫日を昇順で並べ替え

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

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

　

出庫予定リストの表示

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

Sub ShowOutputAllScheduleList()

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

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

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

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

    '出庫リストの表示
    lstOutput.Clear

End If

'オプションボタン設定
optOutputAllScheduleList.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 ShowOutputItemScheduleList()

'異常値の回避
If cmbItemID.Text = "" Then
    optOutputAllScheduleList.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 aryOutputDetail() As Variant
    Dim i As Long
        
    ReDim aryOutputDetail(adoRs.RecordCount - 1, 5) As Variant
    i = 0
    
    '配列変数への書込み
    Do Until adoRs.EOF
        aryOutputDetail(i, 0) = adoRs!出庫ID
        aryOutputDetail(i, 1) = adoRs!出庫日
        aryOutputDetail(i, 2) = adoRs!商品ID
        aryOutputDetail(i, 3) = adoRs!商品名称
        aryOutputDetail(i, 4) = adoRs!出庫数
        aryOutputDetail(i, 5) = adoRs!出庫内訳
        i = i + 1                    'カウンターを加算
    
        adoRs.MoveNext       'カーソルを1行下へ
    Loop
    
    'レコードセットオブジェクトの破棄
    adoRs.Close
    Set adoRs = Nothing
    
    ''''''''''''''''''''''''''''''''''''''''
    
    'データベース切断
    Call DB切断

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

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

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

    '出庫リストの表示
    lstOutput.Clear

End If

'オプションボタン設定
optOutputItemScheduleList.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