商品マスタ画面の実行ボタン作成　[VBA在庫管理システムVer02#04]

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

この記事では、商品マスタ画面の実行ボタン（登録・修正・削除）のプログラムを、Ver01のプログラムコードと比較しながら、作成していきます。

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

VBA在庫管理システムVer02

VBA在庫管理システムVer02

商品マスタの登録ボタン

VBA在庫管理システムVer01

Ver01では、エクセルブックに商品情報を登録するプロシージャをサブルーチンとして作成しました。

Sub 登録()

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

        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

'登録情報書き込み
With wsItemMaster
    .Cells(wsItemMasterRow + 1, wsItemMasterColumns.M商品ID) = wsItemMasterRow
    .Cells(wsItemMasterRow + 1, wsItemMasterColumns.M商品名称) = cmbItemName.Text
    .Cells(wsItemMasterRow + 1, wsItemMasterColumns.M登録日) = texExcuteDay.Value
    .Cells(wsItemMasterRow + 1, wsItemMasterColumns.M発注先) = texSupplier.Value
    .Cells(wsItemMasterRow + 1, wsItemMasterColumns.M発注単位) = texOrderUnit.Value
    .Cells(wsItemMasterRow + 1, wsItemMasterColumns.M梱包数) = texPackage.Value
    .Cells(wsItemMasterRow + 1, wsItemMasterColumns.M最大在庫) = texMaxStock.Value
    .Cells(wsItemMasterRow + 1, wsItemMasterColumns.M発注点) = texOrderPoint.Value
    .Cells(wsItemMasterRow + 1, wsItemMasterColumns.M棚位置) = texStockPosition.Value
    .Cells(wsItemMasterRow + 1, wsItemMasterColumns.M棚卸日) = texExcuteDay.Value
    .Cells(wsItemMasterRow + 1, wsItemMasterColumns.M棚卸数) = 0
    .Cells(wsItemMasterRow + 1, wsItemMasterColumns.M削除) = 0
End With

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

        Application.ScreenUpdating = True

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

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

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

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

End Sub

登録

Ver02では、アクセスDBに商品情報を書き込むので、プロシージャ：登録を変更します。

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.Value
      !発注先 = texSupplier.Value
      !発注単位 = texOrderUnit.Value
      !梱包数 = texPackage.Value
      !最大在庫 = texMaxStock.Value
      !発注点 = texOrderPoint.Value
      !棚位置 = texStockPosition.Value
      !棚卸日 = texExcuteDay.Value
      !棚卸数 = 0
      !削除 = 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. 異常値の回避（Ver01同コード）
商品IDが表示されているものは、既に登録されていますのでマスタデータが重複しないように、コメントを表示して処理を終了させます。

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

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

@4. 商品データの登録
adoRsオブジェクトのOpenメソッドを使用し、テーブルのデータをレコードセットに取り込みます。
レコードの追加処理を可能にするため、パラメータのadOpenKeyset (キーセットカーソルを使用)と、adLockOptimistic(Update時のレコード単位のロック)を指定します。

@5. レコードの追加
MoveLastで新規レコードに移動し、AddNewで各フィールドにデータを書き込んでいきます。

@6. レコードの保存
Updateでデータを書き込んだ新規レコードが、保存されます。

@7. レコードセットオブジェクトの破棄
adoRsオブジェクトのCloseメソッドを使用し、閉じます。
adoRsオブジェクトを破棄して終了です。

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

@9. 商品情報の再表示（Ver01同コード）
一旦、変数＝再表示名称に商品名称を取り込み、Resetプロシージャーを呼び出し、詳細情報をリセットします。
UserForm_Initializeプロシージャーを呼び出し、商品マスタ情報を再取得した後、商品名称コンボに変数＝再表示名称を書き込み、詳細情報を表示させます。
　

商品マスタの修正ボタン

VBA在庫管理システムVer01

Ver01では、エクセルブックの商品情報を修正するプロシージャをサブルーチンとして作成しました。

Sub 修正()

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

        Application.ScreenUpdating = False

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

'修正行の取得
Dim 修正行 As Long
修正行 = wsItemMaster.Columns("A:A").Find(texItemID.Value, LookAt:=xlWhole).Row

'修正情報書き込み
With wsItemMaster
    .Cells(修正行, wsItemMasterColumns.M商品名称) = texNameUpdate.Value
    .Cells(修正行, wsItemMasterColumns.M更新日) = texExcuteDay.Value
    .Cells(修正行, wsItemMasterColumns.M発注先) = texSupplier.Value
    .Cells(修正行, wsItemMasterColumns.M発注単位) = texOrderUnit.Value
    .Cells(修正行, wsItemMasterColumns.M梱包数) = texPackage.Value
    .Cells(修正行, wsItemMasterColumns.M最大在庫) = texMaxStock.Value
    .Cells(修正行, wsItemMasterColumns.M発注点) = texOrderPoint.Value
    .Cells(修正行, wsItemMasterColumns.M棚位置) = texStockPosition.Value
End With

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

        Application.ScreenUpdating = True

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

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

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

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

End Sub

修正

Ver02では、アクセスDBの商品情報を修正するので、プロシージャ：修正を変更します。

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.Value & "# , " & _
"発注先 = '" & texSupplier.Value & "' , " & _
"発注単位 = " & texOrderUnit.Value & " , " & _
"梱包数 = " & texPackage.Value & " , " & _
"最大在庫 = " & texMaxStock.Value & " , " & _
"発注点 = " & texOrderPoint.Value & " , " & _
"棚位置 = '" & texStockPosition.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. 異常値の回避（Ver01同コード）
商品IDが表示されていないものは、修正元データがありませんので、コメントを表示して処理を終了させます。

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

@3. レコード更新
レコードの更新はSQLの実行で行いますので、レコードセットは必要ありません。

@4. SQL文の変数への取り込み
データ更新用のSQL文を作成し、文字列変数に格納します。SQL文が長くなるときは、改行を入れながらコードを書きます。
レコードの更新には、UPDATEを使用します。

SQL UPDATE テーブル名 SET フィールド名1=データ1,フィールド名2=データ2 WHERE フィールド名=検索条件

書き込み時のデータはフィールドの型にあわせて、次の設定が必要です。
　　日付型： # 日付 #
　　文字列： ' 文字列 ' (シングルコーテーション)
　　数値型：　数値　 (そのまま)
　Yes/No型：　True / False

@5. SQLの実行
adoCnオブジェクトのExecuteメソッドで、データベースにSQLを実行します。

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

@7. 商品情報の再表示（Ver01同コード）
一旦、変数＝再表示名称に商品名称を取り込み、Resetプロシージャーを呼び出し、詳細情報をリセットします。
UserForm_Initializeプロシージャーを呼び出し、商品マスタ情報を再取得した後、商品名称コンボに変数＝再表示名称を書き込み、詳細情報を表示させます。
　

商品マスタの削除ボタン

VBA在庫管理システムVer01

Ver01では、エクセルブックの商品情報を削除するプロシージャをサブルーチンとして作成しました。

Sub 削除()

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

        Application.ScreenUpdating = False

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

'削除行の取得
Dim 削除行 As Long
削除行 = wsItemMaster.Columns("A:A").Find(texItemID.Value, LookAt:=xlWhole).Row

'削除情報書き込み
With wsItemMaster
    .Cells(削除行, wsItemMasterColumns.M更新日) = texExcuteDay.Value
    .Cells(削除行, wsItemMasterColumns.M削除) = 1
End With

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

        Application.ScreenUpdating = True

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

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

End Sub

削除

Ver02では、アクセスDBの商品情報を削除するので、プロシージャ：削除を変更します。

Sub 削除()

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

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

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

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

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

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

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

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

End Sub

@1. 異常値の回避（Ver01同コード）
商品IDが表示されていないものは、削除元データがありませんので、コメントを表示して処理を終了させます。

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

@3. レコード削除
レコードの削除はSQLの実行で行いますので、レコードセットは必要ありません。

@4. SQL文の変数への取り込み
データ削除用のSQL文を作成し、文字列変数に格納します。SQL文が長くなるときは、改行を入れながらコードを書きます。
レコードの論理削除には、UPDATEを使用します。

SQL UPDATE テーブル名 SET フィールド名1=データ1,フィールド名2=データ2 WHERE フィールド名=検索条件

@5. SQLの実行
adoCnオブジェクトのExecuteメソッドで、データベースにSQLを実行します。

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

@7. 商品情報の再表示（Ver01同コード）
再表示する情報はありませんので、詳細情報のリセットと商品マスタ情報の再取得のみ実行します。
　

実行ボタンの設定

実行ボタンクリック時の動作を確認します。
オプションボタンの状態に合わせ、サブルーチンが実行されます。

Private Sub btnExecute_Click()

If optInput.Value = True Then

    Call 登録

ElseIf optUpdate.Value = True Then

    Call 修正

ElseIf optDelete.Value = True Then

    Call 削除

End If

End Sub

　
これで、商品マスタ画面の動作設定が終了しました。

次回から、商品マスタ画面を例に、正規化したテーブルとVBAシステムの連携方法について、解説していきます。
www.minizaiko.com

小さな在庫管理

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

商品マスタ画面の実行ボタン作成　[VBA在庫管理システムVer02#04]

VBA在庫管理システムVer02

商品マスタの登録ボタン

VBA在庫管理システムVer01

登録

商品マスタの修正ボタン

VBA在庫管理システムVer01

修正

商品マスタの削除ボタン

VBA在庫管理システムVer01

削除

実行ボタンの設定