ous">

小さな在庫管理

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

正規化したテーブルとVBAの連携例 2/2 [VBA在庫管理システムVer02#06]

前回に引き続き、商品マスタ画面を例に、正規化したテーブルとVBAシステムの連携方法を解説していきます。

今回は、VBAの動作設定からデータベースへの書き込みまでを解説していきます。


VBA在庫管理システムVer02 改

商品マスタ画面の動作設定

マスタ情報の展開

クエリの作成

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

商品名称コンボでの名称検索

商品名称コンボボックスの動作設定を作成します。

Private Sub cmbItemName_Change()

'名称修正の設定
texNameUpdate.Text = cmbItemName.Text

'異常値の回避
If cmbItemName.ListIndex < 0 Then
    texItemID.Value = ""
    Exit Sub
End If

'------------------------------------------------------------------------------------
' 製品情報の表示
'------------------------------------------------------------------------------------

'詳細情報の表示
With cmbItemName
    texItemID.Value = .List(.ListIndex, lstItemMasterColumns.L商品ID)
    cmbSupplier.Text = .List(.ListIndex, lstItemMasterColumns.L発注先)
    texOrderUnit.Value = .List(.ListIndex, lstItemMasterColumns.L発注単位)
    texPackage.Value = .List(.ListIndex, lstItemMasterColumns.L梱包数)
End With

'商品リストの選択
lstItemMaster.ListIndex = cmbItemName.ListIndex

'------------------------------------------------------------------------------------
' 在庫情報の取得
'------------------------------------------------------------------------------------

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

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

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

'SQL文の変数への取り込み
Dim strSQL As String
strSQL = "SELECT*FROM Q_M商品在庫 WHERE 商品ID = " & texItemID.Value

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

'変数の設定
Dim i As Long
Dim aryItemStockMaster(0, 7) As Variant
i = 0

If adoRs.RecordCount > 0 Then
    
    '配列変数への書込み
    Do Until adoRs.EOF
        aryItemStockMaster(i, 0) = adoRs!商品在庫ID
        aryItemStockMaster(i, 1) = adoRs!商品ID
        aryItemStockMaster(i, 2) = adoRs!登録日
        aryItemStockMaster(i, 3) = adoRs!棚卸日
        aryItemStockMaster(i, 4) = adoRs!棚卸数
        aryItemStockMaster(i, 5) = adoRs!最大在庫
        aryItemStockMaster(i, 6) = adoRs!発注点
        aryItemStockMaster(i, 7) = adoRs!棚位置
        i = i + 1                    'カウンターを加算
        
        adoRs.MoveNext       'カーソルを1行下へ
    Loop
    
End If

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

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

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

'------------------------------------------------------------------------------------
' 在庫情報の表示
'------------------------------------------------------------------------------------

'詳細情報の表示
If i = 0 Then
    texItemStockID.Value = "設定なし"
    texInventoryDay = ""
    texInventoryValue = ""
    texMaxStock = ""
    texOrderPoint = ""
    texStockPosition = ""
ElseIf i = 1 Then
    texItemStockID.Value = aryItemStockMaster(0, 0)
    texInventoryDay = aryItemStockMaster(0, 3)
    texInventoryValue = aryItemStockMaster(0, 4)
    texMaxStock = aryItemStockMaster(0, 5)
    texOrderPoint = aryItemStockMaster(0, 6)
    texStockPosition = aryItemStockMaster(0, 7)
End If

End Sub

@1.レコード抽出条件
Q_M商品在庫からのレコードセット取得時のSQL分は、WHERE句を使用し、商品IDで抽出します。

@2.レコード存在判定
カウンターiで、レコードの有無を判定させます。


※商品名称選択時の商品マスタ画面

商品在庫情報がない場合は、”設定なし"が表示されます。

※商品名称選択時の商品マスタ画面

商品在庫情報がある場合は、在庫関連データが表示されます。


商品マスタ情報の登録

商品情報 実行ボタン
Private Sub btnExecute_Item_Click()

If optInput_Item.Value = True Then

    Call 登録_商品

ElseIf optUpdate_Item.Value = True Then

    Call 修正_商品

ElseIf optDelete_Item.Value = True Then

    Call 削除_商品

End If

End Sub

商品情報の実行ボタンは、オプションボタンの状態に合わせ、サブルーチンが実行されます。
そこで、登録/修正/削除の各サブプロシージャを作成していきます。

登録
Sub 登録_商品()

'異常値の回避
If texItemID.Value <> "" Then
    MsgBox "商品名称が重複しています。", vbExclamation, "確認"
    Exit Sub
End If

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

'''''''''' レコード登録 ''''''''''
 
'レコードセットオブジェクトの作成
Dim adoRs As Object
Set adoRs = CreateObject("ADODB.Recordset")
    
'商品データの登録
With adoRs
        
    .Open "M_商品", adoCn, adOpenKeyset, adLockOptimistic

    .MoveLast                  '最終レコードに移動
    
    .AddNew                    '新規レコードに追加
      !商品ID = adoRs.RecordCount
      !商品名称 = cmbItemName.Text
      !登録日 = texExcuteDay_Item.Value
      !発注先ID = cmbSupplier.Value
      !発注単位 = texOrderUnit.Value
      !梱包数 = texPackage.Value
      !削除 = False
    
    .Update                      'レコードの更新

End With

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

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

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

'変数の取得
Dim 再表示名称 As String
再表示名称 = cmbItemName.Text

'詳細情報のリセット
Call Reset

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

'商品情報の再表示
cmbItemName.Text = 再表示名称

End Sub

@1.発注先IDの登録
発注先コンボボックスには、発注先IDと発注先名称の2つのフィールドが登録され、第2フィールドの発注先名称が表示設定されています。
コンボボックスのTextメソッドは表示設定されている第2フィールドが選択されますが、Valueメソッドは第1フィールドが選択されます。
そこで、商品マスタへの発注先IDの登録時はValueメソッドを使用します。

修正
Sub 修正_商品()

'異常値の回避
If texItemID.Value = "" Then
    MsgBox "商品登録がありません。", vbExclamation, "確認"
    Exit Sub
End If

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

'''''''''' レコード更新 ''''''''''

'SQL文の変数への取り込み
Dim strSQL As String
strSQL = "UPDATE M_商品 " & _
"SET 商品名称 = '" & texNameUpdate.Value & "' , " & _
"更新日 = #" & texExcuteDay_Item.Value & "# , " & _
"発注先ID = " & cmbSupplier.Value & " , " & _
"発注単位 = " & texOrderUnit.Value & " , " & _
"梱包数 = " & texPackage.Value & " " & _
"WHERE 商品ID = " & texItemID.Value

'SQLの実行
adoCn.Execute strSQL
    
''''''''''''''''''''''''''''''''''''''''

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

'変数の取得
Dim 再表示名称 As String
再表示名称 = texNameUpdate.Value

'詳細情報のリセット
Call Reset

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

'商品情報の再表示
cmbItemName.Text = 再表示名称

End Sub

@1.発注先IDの修正
商品マスタへの発注先IDの修正は、登録時と同様に、Valueメソッドを使用します。

削除
Sub 削除_商品()

'異常値の回避
If texItemID.Value = "" Then
    MsgBox "商品登録がありません。", vbExclamation, "確認"
    Exit Sub
End If

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

'''''''''' レコード削除 ''''''''''

'SQL文の変数への取り込み
Dim strSQL As String
strSQL = "UPDATE M_商品 " & _
"SET 更新日 = #" & texExcuteDay_Item.Value & "# , " & _
"削除 = True " & _
"WHERE 商品ID = " & texItemID.Value

'SQLの実行
adoCn.Execute strSQL
    
''''''''''''''''''''''''''''''''''''''''

'商品在庫情報の削除
If texItemStockID.Value <> "設定なし" Then
    
    '''''''''' レコード削除 ''''''''''
    
    'SQL文の変数への取り込み
    strSQL = "UPDATE M_商品在庫 " & _
    "SET 登録日 = #" & texExcuteDay_Item.Value & "# , " & _
    "削除 = True " & _
    "WHERE 商品在庫ID = " & texItemStockID.Value
    
    'SQLの実行
    adoCn.Execute strSQL
        
    ''''''''''''''''''''''''''''''''''''''''
End If

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

'詳細情報のリセット
Call Reset

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

End Sub

@1.在庫情報削除の追加処理
製品マスタ情報の付帯情報の、製品在庫マスタ情報に登録がある時は、同時に削除します。
 

商品在庫マスタ情報の登録

商品在庫情報 実行ボタン
Private Sub btnExecute_Stock_Click()

If optInput_Stock.Value = True Then

    Call 登録_在庫

ElseIf optUpdate_Stock.Value = True Then

    Call 修正_在庫

ElseIf optDelete_Stock.Value = True Then

    Call 削除_在庫

End If

End Sub

商品在庫情報の実行ボタンは、オプションボタンの状態に合わせ、サブルーチンが実行されます。
そこで、登録/修正/削除の各サブプロシージャを作成していきます。

登録
Sub 登録_在庫()

'異常値の回避
If texItemStockID.Value <> "設定なし" Then
    MsgBox "商品在庫IDが重複しています。", vbExclamation, "確認"
    Exit Sub
End If

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

'''''''''' レコード登録 ''''''''''
 
'レコードセットオブジェクトの作成
Dim adoRs As Object
Set adoRs = CreateObject("ADODB.Recordset")
    
'商品データの登録
With adoRs
        
    .Open "M_商品在庫", adoCn, adOpenKeyset, adLockOptimistic

    .MoveLast                  '最終レコードに移動
    
    .AddNew                    '新規レコードに追加
      !商品在庫ID = adoRs.RecordCount
      !商品ID = texItemID.Value
      !登録日 = texExcuteDay_Stock.Value
      !棚卸日 = texInventoryDay.Value
      !棚卸数 = texInventoryValue.Value
      !最大在庫 = texMaxStock.Value
      !発注点 = texOrderPoint.Value
      !棚位置 = texStockPosition.Value
      !削除 = False
    
    .Update                      'レコードの更新

End With

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

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

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

'変数の取得
Dim 再表示名称 As String
再表示名称 = cmbItemName.Text

'詳細情報のリセット
Call Reset

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

'商品情報の再表示
cmbItemName.Text = 再表示名称

End Sub
修正
Sub 修正_在庫()

'異常値の回避
If texItemStockID.Value = "設定なし" Then
    MsgBox "在庫登録がありません。", vbExclamation, "確認"
    Exit Sub
End If

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

'''''''''' レコード更新 ''''''''''

'SQL文の変数への取り込み
Dim strSQL As String
strSQL = "UPDATE M_商品在庫 " & _
"SET 棚卸日 = #" & texInventoryDay.Value & "# , " & _
"棚卸数 = " & texInventoryValue.Value & " , " & _
"最大在庫 = " & texMaxStock.Value & " , " & _
"発注点 = " & texOrderPoint.Value & " , " & _
"棚位置 = '" & texStockPosition.Value & "' " & _
"WHERE 商品在庫ID = " & texItemStockID.Value

'SQLの実行
adoCn.Execute strSQL
    
''''''''''''''''''''''''''''''''''''''''

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

'変数の取得
Dim 再表示名称 As String
再表示名称 = texNameUpdate.Value

'詳細情報のリセット
Call Reset

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

'商品情報の再表示
cmbItemName.Text = 再表示名称

End Sub
削除
Sub 削除_在庫()

'異常値の回避
If texItemStockID.Value = "設定なし" Then
    MsgBox "在庫登録がありません。", vbExclamation, "確認"
    Exit Sub
End If

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

'''''''''' レコード削除 ''''''''''

'SQL文の変数への取り込み
Dim strSQL As String
strSQL = "UPDATE M_商品在庫 " & _
"SET 登録日 = #" & texExcuteDay_Stock.Value & "# , " & _
"削除 = True " & _
"WHERE 商品在庫ID = " & texItemStockID.Value

'SQLの実行
adoCn.Execute strSQL
    
''''''''''''''''''''''''''''''''''''''''

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

'変数の取得
Dim 再表示名称 As String
再表示名称 = texNameUpdate.Value

'詳細情報のリセット
Call Reset

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

'商品情報の再表示
cmbItemName.Text = 再表示名称

End Sub

 
商品マスタと在庫マスタを分離することで、全ての商品が登録可能となり、在庫商品のみ在庫管理することができます。
商品マスタや在庫マスタの格納情報を運用に合わせて設定すると、商品管理や在庫管理を充実させることができます。


これで、正規化したテーブルとVBAシステムの連携についての解説は終了します。
バックアップしたデータベースとプログラムブックを元に戻しましょう。


次回から、商品入力画面を作成していきます。
まずは、マスタデータの取得のプログラムを作成していきます。
www.minizaiko.com