ous">

小さな在庫管理

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

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

前回、商品入庫画面の入庫リストを作成しました。

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

VBA在庫管理システムVer02

商品入庫の入庫入力ボタン

VBA在庫管理システムVer01

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

Sub 登録()

'異常値の回避
If cmbItemName.Text = "" Or texInputDay.Value = "" Or texInputQuantity = "" Or cmbInputDetail.Text = "" Then
    MsgBox " 未入力項目があります。 ", vbExclamation, "確認"
    Exit Sub
End If

        Application.ScreenUpdating = False

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

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

'登録情報書き込み
With wsInOut
    .Cells(wsInOutRow + 1, wsInOutColumns.T入出庫ID) = wsInOutRow
    .Cells(wsInOutRow + 1, wsInOutColumns.T入出庫日) = Format(texInputDay.Value, "yyyy/mm/dd")
    .Cells(wsInOutRow + 1, wsInOutColumns.T商品ID) = cmbItemID.Text
    .Cells(wsInOutRow + 1, wsInOutColumns.T商品名称) = cmbItemName.Text
    .Cells(wsInOutRow + 1, wsInOutColumns.T入出庫数) = texInputQuantity.Value
    .Cells(wsInOutRow + 1, wsInOutColumns.T入出庫内訳) = cmbInputDetail.Text
    .Cells(wsInOutRow + 1, wsInOutColumns.T入出庫区分) = "入庫"
    .Cells(wsInOutRow + 1, wsInOutColumns.T削除) = 0
End With

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

        Application.ScreenUpdating = True


'商品別リストの表示
Call ShowInputItemScheduleList

End Sub

 

登録

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

Sub 登録()

'異常値の回避
If cmbItemName.Text = "" Or texInputDay.Value = "" Or texInputQuantity = "" Or cmbInputDetail.Text = "" Then
    MsgBox " 未入力項目があります。 ", vbExclamation, "確認"
    Exit Sub
End If

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

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

    .MoveLast                  '最終レコードに移動
    
    .AddNew                    '新規レコードに追加
      !入庫ID = adoRs.RecordCount
      !入庫日 = texInputDay.Value
      !商品ID = cmbItemID.Text
      !商品名称 = cmbItemName.Text
      !入庫数 = texInputQuantity.Value
      !入庫内訳 = cmbInputDetail.Text
      !削除 = False
    
    .Update                      'レコードの更新

End With

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

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

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

'商品別リストの表示
Call ShowInputItemScheduleList

End Sub

@1. 異常値の回避(Ver01同コード)
入力データが不足している場合、コメントを表示して処理を終了させます。

@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同コード)
ShowInputItemScheduleListプロシージャを実行し、登録情報を含む商品別リストを再表示させます。

 

商品入庫の修正ボタン

VBA在庫管理システムVer01

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

Sub 修正()

'異常値の回避
If InOutID = 0 Then
    MsgBox "商品選択がありません。", vbExclamation, "確認"
    Exit Sub
End If

        Application.ScreenUpdating = False

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

'修正行の取得
Dim 修正行 As Long
修正行 = wsInOut.Columns("A:A").Find(InOutID, lookat:=xlWhole).Row

'修正情報書き込み
With wsInOut
    .Cells(修正行, wsInOutColumns.T入出庫日) = Format(texCorrectInputDay.Value, "yyyy/mm/dd")
    .Cells(修正行, wsInOutColumns.T入出庫数) = texCorrectInputQuantity.Value
    .Cells(修正行, wsInOutColumns.T入出庫内訳) = cmbCorrectInputDetail.Text
End With

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

        Application.ScreenUpdating = True

'入庫リストの表示
Call ShowInputItemScheduleList

'リスト再選択
Dim i As Long
For i = 0 To lstInput.ListCount - 1
    If lstInput.List(i, 0) = InOutID Then
        lstInput.ListIndex = i
        Exit For
    End If
Next

End Sub

@1.データ操作用変数
変数:InOutIDは、入庫履歴を一意に確定させるために、入庫IDを格納する変数で、宣言セクションでPrivateで宣言されています。

変数には、入庫リストの選択ボタンのクリック時のプロシージャ:btnListSelect_Click内で入庫IDが取り込まれます。

 

修正

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

Sub 修正()

'異常値の回避
If InOutID = 0 Then
    MsgBox "商品選択がありません。", vbExclamation, "確認"
    Exit Sub
End If

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

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

'SQL文の変数への取り込み
Dim strSQL As String
strSQL = "UPDATE T_入庫 " & _
"SET 入庫日 = #" & texCorrectInputDay.Value & "# , " & _
"入庫数 = " & texCorrectInputQuantity.Value & " , " & _
"入庫内訳 = '" & cmbCorrectInputDetail.Value & "' " & _
"WHERE 入庫ID = " & InOutID

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

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

'入庫リストの表示
Call ShowInputItemScheduleList

'リスト再選択
Dim i As Long
For i = 0 To lstInput.ListCount - 1
    If lstInput.List(i, 0) = InOutID Then
        lstInput.ListIndex = i
        Exit For
    End If
Next

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

@5. 入庫リストの再表示(Ver01同コード)
商品別リストを再表示したあと、入庫IDをキーに、修正ボックスに表示されているデータを選択します。

 

商品入庫の削除ボタン

VBA在庫管理システムVer01

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

Sub 削除()

'異常値の回避
If InOutID = 0 Then
    MsgBox "商品選択がありません。", vbExclamation, "確認"
    Exit Sub
End If

        Application.ScreenUpdating = False

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

'削除行の取得
Dim 削除行 As Long
削除行 = wsInOut.Columns("A:A").Find(InOutID, lookat:=xlWhole).Row

'削除情報書き込み
With wsInOut
    .Cells(削除行, wsInOutColumns.T削除) = 1
End With

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

        Application.ScreenUpdating = True

'入庫リストの表示
Call ShowInputItemScheduleList

'修正ボックスの表示解除
btnListRiset_Click

End Sub

@1.データ操作用変数
変数:InOutIDは、入庫履歴を一意に確定させるために、入庫IDを格納する変数で、宣言セクションでPrivateで宣言されています。

変数には、入庫リストの選択ボタンのクリック時のプロシージャ:btnListSelect_Click内で入庫IDが取り込まれます。

 

削除

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

Sub 削除()

'異常値の回避
If InOutID = 0 Then
    MsgBox "商品選択がありません。", vbExclamation, "確認"
    Exit Sub
End If

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

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

'SQL文の変数への取り込み
Dim strSQL As String
strSQL = "UPDATE T_入庫 " & _
"SET 削除 = True " & _
"WHERE 入庫ID = " & InOutID

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

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

'入庫リストの表示
Call ShowInputItemScheduleList

'修正ボックスの表示解除
btnListRiset_Click

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

@5. 入庫リストの再表示(Ver01同コード)
商品別リストを再表示したあと、修正ボックスをリセットします。

 

実行ボタンの設定

入庫入力・修正・削除の各実行ボタンクリック時の動作を確認しましょう。

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

 

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

現在、編集中