ous">

小さな在庫管理

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

トップ > システムVer02 > 商品マスタ画面のマスタデータ取得　[VBA在庫管理システムVer02#03]

商品マスタ画面のマスタデータ取得　[VBA在庫管理システムVer02#03]

システムVer02 VBA Access

前回、データベースの作成をおこないました。ここから商品マスタ画面のプログラムを修正していきます。

この記事では、商品マスタ画面のマスタデータ取得のプログラムを作成していきます。
　
※各画面のプログラム修正の解説は、データベースとの連携部分のみで進めていきます。


VBA在庫管理システムVer02

商品マスタのマスタデータ取得

データの取得に際しては、データベース側でデータの加工（並べ替えや条件抽出）を実行させるためのクエリを作成します。このクエリを、VBA側で取り込む流れで連携させていきます。

テーブルに対し、VBA側からデータ加工用のSQL文を発行することも可能ですが、アクセスではSQL文を意識せずにクエリを作成できるので、アクセスでのクエリ作成がおすすめです。
また、データベース側でデータの加工等の処理を実行すると、速度的にも有利になります。
　

VBA在庫管理システムVer01

Ver01では、エクセルブックの商品マスタデータを配列変数 aryItemMaster() に取り込むプロシージャをサブルーチンとして作成しました。

Sub GetItemMaster()

    Application.ScreenUpdating = False

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

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

'商品名称を昇順で並べ替え
wsItemMaster.Range("A1").Sort key1:=wsItemMaster.Range("B1"), order1:=xlAscending, Header:=xlYes

'変数の設定
Dim i As Long, j As Long
ReDim aryItemMaster(wsItemMasterRow - 2, 9) As Variant
j = 0

'配列変数への書込み
For i = 2 To wsItemMasterRow
    If wsItemMaster.Cells(i, wsItemMasterColumns.M削除) = 0 Then
        aryItemMaster(j, 0) = wsItemMaster.Cells(i, wsItemMasterColumns.M商品ID)
        aryItemMaster(j, 1) = wsItemMaster.Cells(i, wsItemMasterColumns.M商品名称)
        aryItemMaster(j, 2) = wsItemMaster.Cells(i, wsItemMasterColumns.M登録日)
        aryItemMaster(j, 3) = wsItemMaster.Cells(i, wsItemMasterColumns.M更新日)
        aryItemMaster(j, 4) = wsItemMaster.Cells(i, wsItemMasterColumns.M発注先)
        aryItemMaster(j, 5) = wsItemMaster.Cells(i, wsItemMasterColumns.M発注単位)
        aryItemMaster(j, 6) = wsItemMaster.Cells(i, wsItemMasterColumns.M梱包数)
        aryItemMaster(j, 7) = wsItemMaster.Cells(i, wsItemMasterColumns.M最大在庫)
        aryItemMaster(j, 8) = wsItemMaster.Cells(i, wsItemMasterColumns.M発注点)
        aryItemMaster(j, 9) = wsItemMaster.Cells(i, wsItemMasterColumns.M棚位置)
        j = j + 1
    End If
Next

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

    Application.ScreenUpdating = True

End Sub

　

クエリの作成

商品マスタデータの取り込み用クエリを作成していきます。

Ver01では、商品マスタデータを配列変数に取り込む際に、商品マスタデータに以下の処理を加えました。
　① 商品名称を昇順で並べ替え
　② 削除フラグが立っているものは取り込まない
この処理をデータベース側で行う為に、選択クエリを作成します。

[作成]-[クエリデザイン]を選択します。

クエリのデザインビューが表示されます。デフォルトで、[クエリの種類]‐[選択クエリ]が選択されているので、確認してください。
[テーブルの表示]ダイアログボックスが表示される場合がありますが、あまり使わないので閉じておきます。

ナビゲーションウィンドウのM_商品テーブルを選択し、クエリデザインウィンドウにドラッグするとフィールドリストが表示されます。
フィールドリストのリストがすべて表示されていない場合は、フィールドリストの下方をポイントし、矢印をドラッグして調整します。

フィールドリストのフィールドを選択し、ダブルクリックすると、選択したフィールドがデザイングリッドに追加されます。
[フィールド]に選択したフィールド名が、[テーブル]に選択したフィールドがあるテーブル名が、それぞれ表示されます。

M_商品マスタ画面のマスタデータ取得に必要なフィールドを、すべて追加します。

① 商品名称を昇順で並べ替え、を設定します。
デザイングリッドの商品名称フィールドの[並べ替え]を昇順に設定します。

② 削除フラグが立っているものは取り込まない、を設定します。
削除フィールドの[抽出条件]をFlaseに設定します。また、削除フィールドは条件フィールドのため、[表示]のチェックを外して、データシートビューには非表示とします。

[結果]-[実行]をクリックすると、データシートビューに切り替わります。

表示状態を確認後、Q_M商品と名前を付けて保存してください。

これで、クエリが完成しました。
　

データの取り込み

Ver02では、アクセスDBの商品マスタデータを配列変数 aryItemMaster() に取り込むので、プロシージャ：GetItemMasterを修正します。

※アクセスとのADOを用いた操作については、下記の記事で解説しています。レコード抽出の部分にあたりますので、参考にして下さい。

Sub GetItemMaster()

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

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

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

'SQL文の変数への取り込み
Dim strSQL As String
strSQL = "SELECT*FROM Q_M商品"

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

'変数の設定
Dim i As Long
ReDim aryItemMaster(adoRs.RecordCount - 1, 9) As Variant
i = 0

'配列変数への書込み
Do Until adoRs.EOF
    aryItemMaster(i, 0) = adoRs!商品ID
    aryItemMaster(i, 1) = adoRs!商品名称
    aryItemMaster(i, 2) = adoRs!登録日
    aryItemMaster(i, 3) = adoRs!更新日
    aryItemMaster(i, 4) = adoRs!発注先
    aryItemMaster(i, 5) = adoRs!発注単位
    aryItemMaster(i, 6) = adoRs!梱包数
    aryItemMaster(i, 7) = adoRs!最大在庫
    aryItemMaster(i, 8) = adoRs!発注点
    aryItemMaster(i, 9) = adoRs!棚位置
    i = i + 1                    'カウンターを加算

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

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

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

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

End Sub

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

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

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

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

フィールド名の代わりに、"*"を使用すると、全てのフィールドが抽出されます。また、テーブル名の代わりにクエリ名を使用することもできます。

今回はQ_M商品クエリの、全てのフィールドを抽出します。

@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と切断します。
　

データの格納

商品マスタデータは、UserForm_Initialize プロシージャで、各コントロールに格納されます。

Private Sub UserForm_Initialize()

'----サイズ調整---------------------------------------------------------------
    Me.Zoom = Me.Zoom * ((画面高さ * 高さ割合) / Me.Height)
    Me.Width = Me.Width * ((画面高さ * 高さ割合) / Me.Height)
    Me.Height = Me.Height * ((画面高さ * 高さ割合) / Me.Height)
'-----------------------------------------------------------------------------

'商品マスタ情報の取得
Call GetItemMaster

'商品名称コンボボックスの設定
With cmbItemName
    .Clear
    .ColumnCount = 10
    .ColumnWidths = "0;200;0;0;0;0;0;0;0;0"

    .List = aryItemMaster()
End With

'商品リストボックスの設定
With lstItemMaster
    .Clear
    .ColumnCount = 10
    .ColumnWidths = "40;250;0;0;150;60;60;60;60;60"

    .List = aryItemMaster()
End With

End Sub

　

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

MIFDUP

コメントを書く