前回に引き続き、商品マスタ画面を例に、正規化したテーブルと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. レコード存在判定
カウンターｉで、レコードの有無を判定させます。
※商品名称選択時の商品マスタ画面
商品在庫情報がない場合は、”設定なし"が表示されます。
※商品名称選択時の商品マスタ画面
商品在庫情報がある場合は、在庫関連データが表示されます。
商品マスタ情報の登録
商品情報 実行ボタン
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
