前回、商品在庫画面のマスタデータ取得部分を作成しました。
この記事では、商品在庫画面の在庫表表示のプログラムを作成していきます。
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句を使用します。
今回は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