2022/06/08

Accessデータベースを使用した売上台帳

• １．背景
• ２．システム概要
• 　２－１．全体の流れ
• 　２－２．取引先データの登録
• 　２－３．商品データの登録
• 　２－４．売上データの登録
• ３．プログラムの流れ
• 　３－１．テーブルの構造
• 　３－２．データベースの存在確認と作成
• 　３－３．取引先データの操作
• 　３－４．商品データの操作
• 　３－５．売上データの操作
• ４．操作用シート
• ５．ワークブックモジュール
• ６．標準モジュール
• 　６－１．データベース・テーブルの準備
• 　　よりみち（Exit For で抜ける時の注意）
• 　６－２．データベースファイル作成
• 　６－３．テーブルの作成と削除
• 　６－４．フォームの起動
• 　６－５．SQL文の実行
• 　　よりみち（SQL文の戻り値）
• 　　よりみち（カーソルの種類）
• ７．売上ダイアログ
• 　７－１．コントロールの配置
• 　７－２．フォームモジュール
• 　　７－２－１．起動時準備
• 　　７－２－２．コンボボックス作成
• 　　７－２－３．リストボックス作成
• 　　７－２－４．入力部の処理
• 　　７－２－５．リストボックスの選択
• 　　７－２－６．データ処理
• 　　　７－２－６－１．登録／更新ボタン
• 　　　７－２－６－２．削除ボタン
• 　　　７－２－６－３．リストボックス再表示
• 　　　７－２－６－４．シートへの出力
• 　　　７－２－６－５．終了ボタン
• 　　　７－２－６－６．入力部のチェック
• ８．取引先ダイアログ
• 　８－１．コントロールの配置
• 　８－２．フォームモジュール
• 　　８－２－１．起動時準備
• 　　　よりみち（リストの右寄せ・左寄せ）
• 　　８－２－２．リストボックス作成
• 　　８－２－３．入力部の処理
• 　　８－２－４．リストボックスの選択
• 　　８－２－５．データ処理
• 　　　８－２－５－１．登録／更新ボタン
• 　　　８－２－５－２．削除ボタン
• 　　　　よりみち（SQL実行時のエラー種類）
• 　　　８－２－５－３．終了ボタン
• 　　８－２－６．入力部のチェック
• ９．商品ダイアログ
• 　９－１．コントロールの配置
• 　９－２．フォームモジュール
• 　　９－２－１．起動時準備
• 　　９－２－２．リストボックス作成
• 　　９－２－３．入力部の処理
• 　　９－２－４．リストボックスの選択
• 　　９－２－５．データ処理
• 　　　９－２－５－１．登録／更新ボタン
• 　　　９－２－５－２．削除ボタン
• 　　　９－２－５－３．終了ボタン
• 　　９－２－６．入力部のチェック
• １０．最後に
• サンプルファイル

１．背景

前回「ExcelからAccessデータベースを作成・操作」では、AccessのデータベースファイルをExcel側から作成・操作する方法について説明しました。今回は、そのAccessデータベースを実際に活用する事例として、簡単な「売上台帳」を作成しましたので紹介します。

以前「ExcelシートDBとSQLを使った倉庫管理システム」等でExcelシートをデータベースとしたものを紹介してきましたが、それらとプログラムの流れ自体はほぼ同じです。しかしAccessデータベースを利用した場合は以下のような特徴を活かせるため、より厳密で安心なデータ管理が可能となります。今回は、この特徴を盛り込んで作っています。
　・テーブルの列のデータ型が定められる
　・テーブルの列に対しNot NullやUnique等の制約を設定できる
　・自動連番が可能
　・SQLのDelete文が使用できる

なお、Accessデータベースを使ったものとしては、以下のものも有りますので併せて参照下さい。
「設備の稼働状態を入力し、グラフで確認」

２．システム概要

２－１．全体の流れ

今回の「サンプルファイル」を開くと、図２－１の様な作業シートになります。
シート上には３つのボタンがあり、通常は「入力」ボタンから売上情報の入力をしますが、事前に取引先データ、商品データを登録しておく必要があります。取引先データの作成・メンテには「取引先」ボタンを、商品データには「商品」ボタンをクリックします。
またシート上には各項目が並んでいますが、データを出力した際のタイトルになるようにしています。

図２－１

なおシートが開く際に、使用するデータベースファイルとテーブルの状態を調べ、正常な状態であれば図２－２の左側のようなメッセージを、データベースファイルが存在しなければ自動的に作成した後に図２－２の右側のようなメッセージを出します。
ですので、データベースファイルが無い状態でサンプルファイルを開いた際には、サンプルファイルと同じフォルダーに「データベースファイル（ファイル名：it-082.accdb）」が作成されます。

図２－２

今回は３つのダイアログを使用しますが、その関係について説明します。
売上が発生した時点で入力するデータは、「売上台帳ダイアログ（図２－３の中央）」に入力をします。

図２－３

そのダイアログ内の「取引先」と「商品」は毎回異なる事は少ない為「選択式」とし、そのリストは「別なテーブル」からデータを持ってきています。別なテーブルのデータを管理しているのが、取引先・商品のダイアログという事になります。

２－２．取引先データの登録

売上データを入力する前に、取引先データ・商品データを作成しておく必要がありますので、そちらを先に説明します。
シート上の「取引先」ボタンをクリックし、表示されるダイアログ上で取引先のデータを入力します。
まず取引先の情報として「取引先番号」「取引先名」を入力（図２－４の左側）し、下部の「登録/更新」ボタンをクリックします。するとリストに項目が登録（図２－４の右側）されます。

図２－４

今回、取引先番号は「文字列」型としていますので、アルファベット（大文字のみ）が付いていても数字だけでもＯＫです。また、取引先番号はUnique制約を付けていますので重複はNGですが、取引先名は重複可です。
またリストは取引先番号順で表示しています。但し取引先番号が文字列のため、文字コード順に並びます。そのため「10」の後に「3」が来るような、数値としては不自然な並びになることがあります。

登録先情報を変更する際は、リストボックス内の該当する項目を選択（図２－５の左側）し、上部のテキストボックス内で文字列を変更（図２－５の中央）します。その後「登録/更新」ボタンをクリックすることで情報が書き換わり（図２－５の右側）ます。

図２－５

なお図２－５では、Uniqueでは無い「取引先名」を変更しているため「既データの変更」になりましたが、取引先番号を「リストには無い番号」に変更した場合は「データの新規追加」という扱いになります。また、取引先番号を「リスト内の異なる番号」にした場合は、その異なる番号の取引先名を変更することになりますので、注意が必要です。

登録先情報を削除する際は、リストボックス内の該当する項目を選択（図２－６の左側）し、「削除」ボタンをクリックします。

図２－６

削除しようとする取引先が、売上台帳のデータに使われていない場合は、素直に「データから削除」されます。しかし売上台帳のデータに使われている場合は、図２－６の右側の様にデータに「レ点」を付け、売上台帳のコンボボックスには表示されないようにしています。これは「売上台帳のデータの一部に、取引先データを使用」しているので「削除してしまうと、データの整合性が崩れる」ため、「見掛け上、削除相当」にしています。
なお、レ点の付いた項目を図２－５の手順で変更する際、「非表示のチェックボックスを消す」ことで、データ復活させることが出来ます。

２－３．商品データの登録

基本的には「取引先」データの操作と同じですが、商品情報には「単価」が必要なため１つ入力項目が増えます。

図２－７

但し、同じ商品であっても「単価」は変化する可能性があります。商品データ側の単価は最新にしておく事はもちろん必要な事ですが、売上台帳に残す単価は「売り上げた時の単価」にする必要があります。ですので売上台帳側のデータには、商品データに結び付いた単価では無く、「売り上げた時の単価」を保存しておきます。

２－４．売上データの登録

シート上の「入力」ボタンのクリックにより表示される売上台帳には「伝票番号」「売上の日付」「取引先」「商品」「売上個数」を入力します。
「伝票番号」は「文字列」型としているため、アルファベット（大文字のみ）・数字が使用できます。「売上の日付」は、今回はテキストボックスへの手入力としてあります。「ボタンを自動生成するフォームカレンダー」等を参考にしてカレンダー入力方式とすることは可能です。
「取引先」「商品」は、図２－８のようにコンボボックス内から選択します。「取引先」のリストには取引先番号・取引先名を表示しています（選択後は、取引先名のみになります）。また「商品」のリストには商品番号・商品名・最新の単価を表示し、選択後は「単価の値は単価のラベルにデータを移動」し、入力した「売上個数」と掛け合わせて合計値としています。

図２－８

入力を全て完了したら図２－９のように、上部左端の「登録/更新」ボタンをクリックすることで「入力情報がリストへ登録」されます。

図２－９

ダイアログ下部のリストには図２－１０の様に「売り上げ日付に対して降順に表示」し、「最新のものが一番上に来る」ようにしています。日付が同じ場合は、SNo（＝売上台帳番号）の降順になります。

図２－１０

ダイアログの上部の「リスト出力」ボタンをクリックすることで、売上リストはワークシート上に「昇順（＝日付の若いデータを前）」で書き出します。書き出すデータ量は「年単位」で、ダイアログ上部の「表示年」のテキストボックスの年のデータが対象となります。なお書き出す直前に、以前のデータはクリアされます。

図２－１１

ダイアログ上部の「表示年」は、起動直後は「今年」の値が表示（＝ダイアログのリストも今年の売上データが表示）されます。その表示年のテキストボックスの値を手動で変更することで、リストデータ及びワークシートに出力されるデータを「表示年のデータ」にすることが出来ます。なお表示年のテキストボックスを変更しただけではリストは変更されませんので、横の「リスト再表示」ボタンをクリックして下さい。

図２－１２

売上台帳のデータを削除するには、図２－１３のように削除するデータを選択し、上部の「削除」ボタンをクリックします。

図２－１３

「取引先データ」や「商品データ」を削除する時には「売上台帳で使用中の場合はエラーが出てしまう為にレ点を付ける」と 説明しましたが、売上台帳のデータを削除する時には「エラーが出ない（＝他のテーブルに悪影響を与えることは無い）」ために削除可能です。（元には戻せないので、注意は必要です。）

３．プログラムの流れ

３－１．テーブルの構造

まず今回システムのテーブルは、図３－１のように「Sale」「Cust（Customerの略）」「Pdct（Productの略）」の３つに分けています。それぞれのテーブルにはPrimary Keyを設けており、メインである売上台帳のSaleテーブルの「CustNo」列～「Cust」テーブル、また「PdctNo」列～「Pdct」テーブルを参照制約（Foreign Key）で結び付けています。


図３－１

テーブルの列には、各々データ型を設定する必要があります。Accessの主なデータ型を図３－２に示しましたが、「伝票番号」「取引先番号」「商品番号」の「人間が設定した番号」については、アルファベットを頭番号とする場合もあると考え、VBAで言うとString型（SQLでSTRING型など）としました。また、数値を扱う「UnitPrice」「Uprice」「Quant」は、VBAでのLong型としました。
売上番号（SNo）は、自動で追番が振られるようにCOUNTER型とし、データ削除相当の非表示列「Del列」はTrueまたはFalseのみで良いためBIT型（VBAで言うBoolean型）としました。

図３－２

３－２．データベースの存在確認と作成

今回システムにはデータベースファイルとテーブルが必須ですので、サンプルファイルを開く際に存在するか否かのチェックを行っています。そして存在していない場合には、新たに作成します。

図３－３

データベースファイルは、定められたファイル（既定は it-082.accdb）がExcelファイルと同じフォルダーに存在しているかを調べます。
テーブルとしては「Sale」「Cust」「Pdct」の３つを今回使用しますので、その名前のテーブルが存在するかを調べます。 ３つ揃っていない場合は、一旦全てのテーブルを削除し、新たに３つのテーブルを新作します。
なお、テーブル内の列名が揃っているか、データ型が正しいか等のチェックは今回行っていません。

３－３．取引先データの操作

シート上の「取引先」ボタンをクリックして表示されるダイアログ（UserForm2）上には、データを操作するボタンとして「登録/更新」と「削除」の２つがあります。（「終了ボタン」はダイアログを終了させるだけです。）

図３－４

上部にあるテキストボックス等にデータを入力するか、又はリストボックスの項目を選択することで上部テキストボックスにそのデータが転記されるデータを手修正した後に「登録/更新」ボタンをクリックすると、Primary Keyである取引先番号（CNo）がCustテーブル内に存在するか否かを調べます。
もし存在しない場合は「データ追加（SQL文ではInsert）」となり、存在する場合は「データ修正（Update）」となります。

また、リスト内の項目を選択した上で「削除」ボタンをクリックすると、選択している取引先番号が「売上台帳（テーブルSale）」で使われているか否かを調べます。
もし取引先番号が使われている場合には、売上台帳から「取引先の情報元が消えてしまう」ことになりますので、削除できません。かと言ってそのまま消さずに残しておく訳にもいかないので、「Del列をTrue」にして「売上台帳のダイアログ上で選択できない（コンボボックスのリストから外す）」ようにしています。
一方、売上台帳上で取引先番号が使われていない場合は、テーブルCustからデータ削除しています。

３－４．商品データの操作

商品データダイアログ（UserForm3）上にも、取引先ダイアログと同様に「登録/更新」と「削除」の２つのボタンがあります。

図３－５

上部のテキストボックス等でデータを入力・修正後に「登録/更新」ボタンをクリックすると、Primary Keyである商品番号（PNo）がテーブルPdct内に存在するか否かを調べます。
もし存在しない場合は「データ追加（SQL文ではInsert）」となり、存在する場合は「データ修正（Update）」となります。

また、リスト内の項目を選択した上で「削除」ボタンをクリックすると、選択している商品番号が「売上台帳（テーブルSale）」で使われているか否かを調べます。
もし商品番号が使われている場合には、売上台帳から「商品の情報元が消えてしまう」ことになりますので、削除できません。「Del列をTrue」にして「売上台帳のダイアログ上で選択できない」ようにしています。
一方、売上台帳上で商品番号が使われていない場合は、テーブルPdctからデータ削除しています。

３－５．売上データの操作

売上データダイアログ（UserForm1）上にも、取引先ダイアログ・商品ダイアログと同様の「登録/更新」と「削除」のボタンがあります。

図３－６

上部のテキストボックス等でデータを入力・修正後に「登録/更新」ボタンをクリックすると、Primary Keyである商品番号（SNo）がテーブルSale内に存在するか否かを調べます。
もし存在しない場合は「データ追加（SQL文ではInsert）」となり、存在する場合は「データ修正（Update）」となります。

また、リスト内の項目を選択した上で「削除」ボタンをクリックすると、選択している売上番号のデータをテーブルSaleから削除します。取引先ダイアログ・商品ダイアログとは異なり、Saleテーブルからレコードを削除しても整合性は崩れませんので削除可能です。

ダイアログ下部のリストボックスには、テーブルSaleのデータ全てを表示する訳では無く、データ内の売上日付（SaleD列）が上部の「表示年」に該当するデータのみを出力するようにしています。また「リスト出力」ボタンは、「表示年で絞り込んだデータ」をリストボックスでは無く、ワークシート側へ出力させる事になります。

４．操作用シート（Sheet1）

今回は、ワークシートSheet1上に操作用ボタンを３つ配置し、また出力用に項目タイトルを置いています。

図４－１

ボタンは「入力」「取引先」「商品」とのCaptionにし、それぞれ「DataInput」「CustomerMgt」「ProductMgt」という標準モジュールに置いた「フォームを起動」するマクロを登録しています。
また、データ出力は１０項目としましたので、B2セル～K2セルにタイトルを手書きし、その下（３行目以降）にデータを書き込みます。そのデータ出力時に「表示年」も同時に出力し、その出力先をC1セルとしました。

５．ワークブックモジュール（ThisWorkbook）

ブックを開いた時に実行されるWorkbook_Openイベントが図５－１です。０２行目「Call SystemStart」で図６－２を呼び出し、データベースファイル及びテーブルの状態を調べ、存在しない場合には作成をします。

0. '==========　⇩(1)　Excelを開く際に実行　============
1. Private Sub Workbook_Open()
2. 　Call SystemStart
3. End Sub

図５－１

６．標準モジュール（Module1）

標準モジュールの宣言部で、標準モジュール内、及びシステム全体で使用する定数・変数の宣言をします。

10. '==========　⇩(2)　定数・変数宣言　============
11. Private DBpath As String 　　　　 '←データベースファイルのパス
12. Private Const DBname As String = "it-082.accdb" 　　 '←データベースファイルのファイル名
14. Public Const TBL0 As String = "Sale" 　　 '←売上データのテーブル名
15. Public Const TBL1 As String = "Cust" 　　 '←取引先データのテーブル名
16. Public Const TBL2 As String = "Pdct" 　　 '←商品データのテーブル名

図６－１

１１行目「DBpath」は、データベースファイルのパスです。値は図６－２の２５行目で代入しています。なお、実際に運用する時には「データベースファイルは、共有ファイルサーバー等」に置く事が多いと考えられますので、定数として宣言部で宣言することになると思います。
１２行目「DBname」は、データベースファイルのファイル名です。今回はサンプルファイルと同じ名前にしています。
１４～１６行目はテーブルの名前です。テーブル名を変更した時でも、各SQL文を１つ１つチェック・変更しなくても良いように定数化しています。

６－１．データベース・テーブルの準備

Excelを開いた時に、図５－１「Workbook_Open」から呼び出されるのが図６－２です。

20. '==========　⇩(3)　データベース・テーブルの準備　============
21. Public Sub SystemStart()
22. 　Dim chk As Variant 　　 '←ファイル・テーブルのチェック結果
23. 　Dim TBL As Variant 　　 '←テーブル名の配列変数
25. 　DBpath = ThisWorkbook.Path & "¥" 　　 '←データベースファイルのパス設定
26. 　TBL = Array(TBL0, TBL1, TBL2)
28. 　chk = isMissingDB(TBL)
30. 　Select Case chk
31. 　　Case 1
32. 　　　Call makeDB
33. 　　　Call makeTable
34. 　　　MsgBox "DB,Tableを新作しました"
35. 　　Case 2
36. 　　　Call deleteTable(TBL)
37. 　　　Call makeTable
38. 　　　MsgBox "Tableを新作しました"
39. 　　Case False
40. 　　　MsgBox "DB､TableともOK"
41. 　End Select
42. End Sub

図６－２

２５行目「DBpath = ThisWorkbook.Path & "¥"」では、データベースファイルの置いてあるパスを設定しています。パスが固定していて、そのパス先を宣言部で定数宣言する場合には、２５行目は不要になります。
２６行目「TBL = Array(TBL0, TBL1, TBL2)」では、３つのテーブル名を配列化しています。これは、各テーブルの存在を調べている図６－４「isMissingDB」、及び図６－１２「deleteTable」の独立性を高くするためです。

２８行目「chk = isMissingDB(TBL)」では、図６－４に検査するテーブル名の配列を渡し、その検査結果を変数chkで受け取ります。戻り値は以下の通りです。

状態		戻り値
DBファイル	テーブル
×	（×）	1
〇	×～△	2
〇	〇	False

図６－３

表中の〇は「正しく存在する」、×は「存在しない」、△は「存在はするが不足している」を表しています。
DBファイル・テーブルとも正常（図６－３で、両方とも〇）な状態でなければシステムは動かせませんので、３０～４１行目で状態に従ってDBファイル・テーブルを作り直します。

３０行目「Select Case chk」で検査結果を選別し、「１（＝DBファイルが無い）」の場合は３２行目「Call makeDB」で図６－７を呼出して「新たにDBファイルを作成」し、３３行目「Call makeTable」で図６－８を呼出し「テーブルを作成」します。
作成が完了したら、３４行目「MsgBox "DB,Tableを新作しました"」でコメントを出します。

「２（＝テーブルが無い、又は不足）」の場合は、３６行目「Call deleteTable(TBL)」で図６－１２を呼出し「作ろうとするテーブルを一旦削除」します。その後の３７行目「Call makeTable」で図６－８を呼出し「テーブルを作成」します。
作成が完了したら、３８行目「MsgBox "Tableを新作しました"」でコメントを出します。

「False（＝ファイルとテーブルが正常に存在）」の場合は、４０行目「MsgBox "DB､TableともOK"」でコメントを出します。

図６－２の２８行目から呼び出される「DBファイル・テーブルを調べる」のが図６－４です。引数として、調べるテーブル名の配列TBLを受け取ります。

50. '==========　⇩(4)　DBファイル・テーブルの存在チェック　============
51. Private Function isMissingDB(ByVal TBL As Variant) As Variant
52. 　Dim Cat As Object 　　 '←Catalogオブジェクト
53. 　Dim T As Object 　　 '←Tableオブジェクト
54. 　Dim i As Integer 　　 '←カウンタ変数（テーブルの数）
56. 　If Dir(DBpath & DBname) = "" Then
57. 　　isMissingDB = 1 　　 '←DBファイルが無い
58. 　　Exit Function
59. 　End If
61. 　Set Cat = CreateObject("ADOX.Catalog")
62. 　Cat.ActiveConnection = "Provider=Microsoft.Ace.OLEDB.12.0;" & _
63. 　　　　　　　　　　　　　　"Data Source=" & DBpath & DBname
65. 　For Each T In Cat.Tables
66. 　　If T.Type = "TABLE" Then
67. 　　　For i = 0 To UBound(TBL, 1)
68. 　　　　If T.Name = TBL(i) Then TBL(i) = 1
69. 　　　Next i
70. 　　End If
71. 　Next T
73. 　If WorksheetFunction.Sum(TBL) = UBound(TBL, 1) - LBound(TBL, 1) + 1 Then
74. 　　isMissingDB = False 　　 '←テーブルが揃っている
75. 　Else
76. 　　isMissingDB = 2 　　 '←テーブルが無い。又は不足
77. 　End If
79. 　Set Cat = Nothing
80. End Function

図６－４

５６～５９行目では、DBファイルが存在するか否かをチェックしています。
５６行目「If Dir(DBpath & DBname) = "" Then」では、Dir関数の引数に「DBファイルのパス＋DBファイル名」を渡しています。Dir関数はファイルが存在する時には「ファイル名」を戻してきますので、「 = ""」と比較している事から「DBファイルが存在しない」場合に、５７行目「isMissingDB = 1」で関数プロシージャの戻り値を１としています。そして５８行目「Exit Function」でプロシージャを抜け出します。

６１行目「Set Cat = CreateObject("ADOX.Catalog")」では、ADOX（ActiveX Data Objects Extensions for Data Definition Language and Security ＝ データ定義とセキュリティのために拡張したADO）のCatalogオブジェクトを生成します。
６２～６３行目「Cat.ActiveConnection = "Provider=Microsoft.Ace.OLEDB.12.0;" & "Data Source=" & DBpath & DBname」では、DBファイルに接続しています。Providerの種類等については「ExcelからAccessデータベースを作成・操作」を参照して下さい。

６５～７１行目では、引数で得た配列内のテーブル名が存在するかをつぶし込んでいます。
６５行目「For Each T In Cat.Tables」では、接続しているDBファイル内のテーブルを１つ１つ抽出しています。DBファイル内には、ユーザーが作成する「データを収めるテーブル」の他にも「システムテーブル（SYSTEM TABLE）」などが含まれています。そのため６６行目「If T.Type = "TABLE" Then」でデータテーブルのみに絞り込みます。
６７行目「For i = 0 To UBound(TBL, 1)」では、カウンタ変数ｉを引数で得た配列のテーブルの数分だけ回し、６８行目「If T.Name = TBL(i) Then TBL(i) = 1」のIf文で、テーブル名配列の１つと合致した時に「TBL(i) = 1」で「テーブル名を数値の１と置換」します。

分かり難いので図６－５で説明すると、引数として得た時には配列TBLの各要素にはテーブル名が書き込まれていますが、６５～７１行目のFor Each～Next を繰返してデータテーブルをチェックするたびに配列内の要素が数値「１」に書き換えられていき、全てのテーブルが存在すれば「配列内の全要素が１」になります。

図６－５

全テーブルのチェックが完了したら、７３行目「If WorksheetFunction.Sum(TBL) = UBound(TBL, 1) - LBound(TBL, 1) + 1 Then」の前半「WorksheetFunction.Sum(TBL)」で配列内の各要素を足していきます。存在したテーブル名は数値１に置き換わっていますし、存在しなかったテーブル名は「テーブル名のまま（＝文字列 → 数値ゼロ相当）」となります。
ですので、足し算の結果が「配列の要素数」であれば「配列内の要素は全て数値１に置換（＝テーブルは全て存在した）」され、それより少なければ「存在しないテーブルがあった」ことになります。要素数は最大インデックスと最小インデックスの差である「 UBound(TBL, 1) - LBound(TBL, 1) + 1」で表されます。

テーブルが全て存在した場合は、７４行目「isMissingDB = False」で関数プロシージャの戻り値をFalseにし、テーブルが無い又は不足していた場合は７６行目「isMissingDB = 2」で戻り値を「２」に設定します。

異なる手法として、配列内のテーブル名を数値に変換するのでは無く、別な数値型変数にヒットの都度１を加えていくことで、最終的に全てがヒットしたか否かが計算できます。

今回はテーブルが３つと少ない事が分かっているため、テーブルが見つかった後でもFor～Nextを回し続けていますが、「用が済んだらExit Forで抜けて次のTABLEに移る」という図６－６のコード（図６－４の６５～７１行目の代替）でも良いと思います。 但し配列TBL内に同じテーブル名を複数個入れられると、計算対象から漏れたテーブル名が残ってしまうことになり、常にテーブルが新しく作り直されることになるという危険がありますので注意が必要です。 　For Each T In Cat.Tables 　　If T.Type = "TABLE" Then 　　　For i = 0 To UBound(TBL, 1) 　　　　If T.Name = TBL(i) Then 　　　　　TBL(i) = 1 　　　　　Exit For 　　　　End If 　　　Next i 　　End If 　Next T 図６－６

６－２．データベースファイル作成

図６－２の３２行目から呼び出される「データベースファイルを作成」するのが図６－７です。

90. '==========　⇩(5)　データベースファイル作成　============
91. Private Sub makeDB()
92. 　Dim Cat As Object 　　 '←Catalogオブジェクト
94. 　Set Cat = CreateObject("ADOX.Catalog")
95. 　Cat.Create "Provider=Microsoft.Ace.OLEDB.12.0;" & _
96. 　　　　　　　"Data Source=" & DBpath & DBname
98. 　Set Cat = Nothing
99. End Sub

図６－７

９４行目「Set Cat = CreateObject("ADOX.Catalog")」でADOXのCatalogオブジェクトを生成し、９５～９６行目でデータベースファイルを作成します。
９５行目の「Cat.Create」のCreateメソッドの引数に「データベースファイルへの接続文字列」を設定することでデータベースファイルが作られます。今回はプロバイダとして「"Provider=Microsoft.Ace.OLEDB.12.0;"」を使用し、接続先は９６行目「"Data Source=" & DBpath & DBname」となります。

６－３．テーブルの作成と削除

図６－２の３３行目、及び３７行目から呼び出される「テーブルを作成」するのが図６－８です。テーブルは今回３つ作成するのですが、作る順番には注意が必要です。図３－１で分かるように「Saleテーブルには参照制約（Foreign Key）を設定」するため、その設定よりも前に「参照される側のテーブルにPrimary Keyを設定」する必要があります。
ですので、CustとPdctテーブルの設定を先に行い（この順番はどちらが先でもOK）、最後にSaleテーブルの設定を行います。

110. '==========　⇩(6)　テーブルの作成　============
111. Private Sub makeTable()
112. 　Dim sql As String 　　 '←SQL文
114. 　sql = "Create Table " & TBL2 & " (" & _
115. 　　　　"PNo STRING PRIMARY KEY," & _
116. 　　　　"Pname STRING," & _
117. 　　　　"Uprice LONG," & _
118. 　　　　"Del Bit" & _
119. 　　　　")"
120. 　Call SQL_exec5(sql)
122. 　sql = "Create Table " & TBL1 & " (" & _
123. 　　　"CNo STRING PRIMARY KEY," & _
124. 　　　　"Cname STRING," & _
125. 　　　　"Del Bit" & _
126. 　　　　")"
127. 　Call SQL_exec5(sql)
129. 　sql = "Create Table " & TBL0 & " (" & _
130. 　　　　"SNo COUNTER PRIMARY KEY," & _
131. 　　　　"Slip STRING," & _
132. 　　　　"SaleD DATETIME," & _
133. 　　　　"CustNo STRING," & _
134. 　　　　"PdctNo STRING," & _
135. 　　　　"UnitPrice LONG," & _
136. 　　　　"Quant LONG," & _
137. 　　　　"FOREIGN KEY(CustNo) REFERENCES " & TBL1 & " (CNo) ," & _
138. 　　　　"FOREIGN KEY(PdctNo) REFERENCES " & TBL2 & " (PNo) " & _
139. 　　　　")"
140. 　Call SQL_exec5(sql)
142. End Sub

図６－８

テーブルを作成するSQL構文は以下の通りです。制約は省略可能です。
　Create Table　テーブル名　( 列名　データ型　[制約]　,　 列名　データ型　[制約]　, ･･･)

１１４～１１９行目は、商品データを格納するPdctテーブルを作成するSQL文です。テーブル構造は図６－９です。

図６－９

１１４行目「sql = "Create Table " & TBL2 & " (" & _」で、テーブル名「Pdct（＝TBL2）」を作成します。
１１５行目「"PNo STRING PRIMARY KEY," & _」で、PNo列をString型で作成します。制約としてPrimary Key（Not Null ＋ Unique）を設定します。
１１６行目「"Pname STRING," & _」で、Pname列をString型で作成します。
１１７行目「"Uprice LONG," & _」で、Uprice列をLong型で作成します。
１１８行目「"Del Bit" & _」で、Del列をBit型（VBAでBoolean型）で作成します。
１２０行目「Call SQL_exec5(sql)」で、上記で作成したSQL文を図６－１６の「SQL文を実行するSQL_exec5関数プロシージャ」に渡して、データベース内にPdctテーブルを作成します。

１２２～１２６行目は、取引先データを格納するCustテーブルを作成するSQL文です。テーブル構造は図６－１０です。

図６－１０

１２２行目「sql = "Create Table " & TBL1 & " (" & _」で、テーブル名「Cust（＝TBL1）」を作成します。
１２３行目「"CNo STRING PRIMARY KEY," & _」で、CNo列をString型で作成します。制約としてPrimary Key（Not Null ＋ Unique）を設定します。
１２４行目「"Cname STRING," & _」で、Cname列をString型で作成します。
１２５行目「"Del Bit" & _」で、Del列をBit型（VBAでBoolean型）で作成します。
１２７行目「Call SQL_exec5(sql)」で、上記で作成したSQL文を実行してCustテーブルを作成します。

１２９～１３９行目は、売上データを格納するSaleテーブルを作成するSQL文です。テーブル構造は図６－１１です。

図６－１１

１２９行目「sql = "Create Table " & TBL0 & " (" & _」で、テーブル名「Sale（＝TBL0）」を作成します。
１３０行目「"SNo STRING PRIMARY KEY," & _」で、SNo列をCOUNTER型で作成します。制約としてPrimary Key（Not Null ＋ Unique）を設定します。COUNTER型はデータ挿入時に指定しなくても「自動的に最大値＋１」の値としてくれる型です。
１３１行目「"Slip STRING," & _」で、Slip列をString型で作成します。
１３２行目「"SaleD DATETIME," & _」で、SaleD列をDATETIME型で作成します。
１３３行目「"CustNo STRING," & _」で、CustNo列をString型で作成します。この列は参照制約（FOREIGN KEY）を付けますが、列設定時では無く、後から１３７行目で設定します。参照制約時の構文は以下になります。
　Create Table テーブル名 (列の設定,･･･,FOREIGN KEY(列名) REFERENCES 相手のテーブル名(相手の列名))

１３４行目「"PdctNo STRING," & _」で、PdctNo列をString型で作成します。この列も参照制約を１３８行目で設定します。
１３５行目「"UnitPrice LONG," & _」で、UnitPrice列をLONG型で作成します。
１３６行目「"Quant LONG," & _」で、Quant列をLONG型で作成します。
１３７行目「"FOREIGN KEY(CustNo) REFERENCES " & TBL1 & " (CNo) ," & _」で、CustNo列をTBL1（＝テーブルCust）のCNo列に対して参照制約（FOREIGN KEY）を設定します。参照制約先はPrimary Keyである必要があります。
１３８行目「"FOREIGN KEY(PdctNo) REFERENCES " & TBL2 & " (PNo) " & _」で、PdctNo列をTBL2（＝テーブルPdct）のPNo列に対して参照制約（FOREIGN KEY）を設定します。

１４０行目「Call SQL_exec5(sql)」で、上記で作成したSQL文を実行してSaleテーブルを作成します。

図６－２の３６行目から呼び出される「指定したテーブルを削除」するのが図６－１２です。引数として削除するテーブル名の配列を受け取ります。

150. '==========　⇩(7)　テーブルの削除　============
151. Private Sub deleteTable(ByVal TBL As Variant)
152. 　Dim sql As String 　　 '←SQL文
153. 　Dim cn As Object 　　 '←Connectionオブジェクト
154. 　Dim i As Integer 　　 '←カウンタ変数（削除するテーブルの数）
156. 　Set cn = CreateObject("ADODB.Connection")
157. 　cn.Provider = "Microsoft.ACE.OLEDB.12.0"
158. 　cn.Open DBpath & DBname
160. 　For i = 0 To UBound(TBL, 1)
161. 　　On Error Resume Next
162. 　　　sql = "drop Table " & TBL(i)
163. 　　　cn.Execute sql
164. 　　On Error GoTo 0
165. 　Next i
167. 　cn.Close
169. 　Set cn = Nothing
170. End Sub

図６－１２

１５６行目「Set cn = CreateObject("ADODB.Connection")」で、ADOのConnectionオブジェクトを生成します。
１５７行目「cn.Provider = "Microsoft.ACE.OLEDB.12.0"」で、ConnectionのプロバイダをACE系に設定し、１５８行目「cn.Open DBpath & DBname」でデータベースファイルに接続します。

１６０行目「For i = 0 To UBound(TBL, 1)」で、カウンタ変数ｉを引数で得たテーブル名配列の要素数分だけ回します。
テーブルを削除するSQL構文は、「Drop Table　テーブル名　」　ですので、
１６２行目「sql = "drop Table " & TBL(i)」でテーブル名を１つずつ構文にあてはめ、１６３行目「cn.Execute sql」で削除を実行します。
但し、今回は「テーブルが不足」している場合にもこの削除プロシージャを実行しています。削除しようとするテーブルが存在しなかった場合にはエラーが発生しますので、１６１行目「On Error Resume Next」でエラーをスルーさせます。

なお、図６－８と同様に「Drop Table のSQL文を作成した後、SQL_exec5プロシージャに渡してテーブル削除」することも可能です。しかし今回のSQL_exec5関数プロシージャは、テーブルが無いのに削除実行した時のエラー等には対応させていないため、自プロシージャ内でConnection.Open → SQL実行 → Connection.Close までの処理を行い、途中に「On Error Resume Next」を挟み込むことで成立させています。

６－４．フォームの起動

ワークシート上のボタンをクリックすることで呼び出されるのが、以下のプロシージャです。ボタンは今回３つあります。
　・「入　力」ボタン　→　図６－１３の「DataInputプロシージャ」　→　UserForm1を起動
　・「取引先」ボタン　→　図６－１４の「CustomerMgtプロシージャ」　→　UserForm2を起動
　・「商　品」ボタン　→　図６－１５の「ProductMgtプロシージャ」　→　UserForm3を起動
各フォームは、モーダル状態（フォームの操作しかできない）で起動しています。

180. '==========　⇩(8)　売上台帳操作　============
181. Public Sub DataInput()
182. 　UserForm1.Show
183. End Sub

図６－１３

185. '==========　⇩(9)　取引先データ操作　============
186. Public Sub CustomerMgt()
187. 　UserForm2.Show
188. End Sub

図６－１４

190. '==========　⇩(10)　商品データ操作　============
191. Public Sub ProductMgt()
192. 　UserForm3.Show
193. End Sub

図６－１５

６－５．SQL文の実行

各ユーザーフォームから、また図６－８（テーブル作成）から呼び出されるのが、「SQL文を実行」する図６－１６です。
引数としてSQL文を受け取り、Select文のようにデータを受取るSQLの場合には引数としてデータを格納した配列を戻します。
なお、Select文以外は戻り値を必要としていませんが、Variant型の初期値であるEmptyを戻す事になります。

200. '==========　⇩(11)　SQL文の実行　============
201. Public Function SQL_exec5(sql As String) As Variant
202. 　Dim cn As Object 　　　　 '←Connectionオブジェクト
203. 　Dim rs As Object 　　　　 '←Recordsetオブジェクト
204. 　Dim i As Long 　　　　　　 '←カウンタ変数(行数)
205. 　Dim j As Integer 　　　　 '←カウンタ変数(列数)
206. 　Const adOpenStatic = 3 　 '←カーソル状態の定数値
207. 　Dim buf() As Variant 　　 '←戻り値の配列
209. 　Set cn = CreateObject("ADODB.Connection")
210. 　Set rs = CreateObject("ADODB.Recordset")
212. 　cn.Provider = "Microsoft.ACE.OLEDB.12.0"
213. 　cn.Open DBpath & DBname
215. 　rs.Open sql, cn, adOpenStatic
217. 　If rs.State = 0 Then
218. 　　Select Case Left(LTrim(sql), 1)
219. 　　　Case "i", "I"
220. 　　　　MsgBox "データ追加しました"
221. 　　　Case "u", "U"
222. 　　　　MsgBox "データ更新しました"
223. 　　　Case "d", "D"
224. 　　　　MsgBox "データ削除しました"
225. 　　End Select
227. 　Else
228. 　　ReDim buf(0 To rs.RecordCount, 1 To rs.Fields.Count)
229. 　　For j = 1 To rs.Fields.Count
230. 　　　buf(0, j) = rs.Fields(j - 1).Name
231. 　　Next j
233. 　　Do Until rs.EOF
234. 　　　i = i + 1
235. 　　　For j = 1 To rs.Fields.Count
236. 　　　　buf(i, j) = IIf(IsNull(rs.Fields(j - 1).Value), "", rs.Fields(j - 1).Value)
237. 　　　Next j
238. 　　　rs.MoveNext
239. 　　Loop
241. 　　SQL_exec5 = buf
242. 　　rs.Close
243. 　End If
245. 　cn.Close
247. 　Set rs = Nothing
248. 　Set cn = Nothing
249. End Function

図６－１６

２０９行目「Set cn = CreateObject("ADODB.Connection")」で、ADOのConnectionオブジェクトを生成します。
２１０行目「Set rs = CreateObject("ADODB.Recordset")」で、Recordsetオブジェクトを生成します。
２１２行目「cn.Provider = "Microsoft.ACE.OLEDB.12.0"」で、ConnectionのプロバイダをACE系で設定します。
２１３行目「cn.Open DBpath & DBname」で、データベースファイルに接続します。

２１５行目「rs.Open sql, cn, adOpenStatic」で、引数で得たSQL文を実行します。その際、省略可能な第三引数として図６－１７のカーソルから「静的カーソル」を指定します。
なお、２１５行目ではadOpenStaticという定数値で指定していますが、今回ADOを実行時バインディングでセットしていますので「ADOの定数は認識されない」状態です。ですので２０６行目「Const adOpenStatic = 3」とコード内で定数値を宣言する必要があります。

定数	値	内容	他ﾕｰｻﾞｰが操作したﾃﾞｰﾀの反映	RecordCountの戻り値
adOpenUnspecified	-1	カーソル種類を指定せず	(＝順方向専用ｶｰｿﾙ?)	-1
adOpenForwardOnly	0	順方向専用カーソル(既定)	開いた時点のﾚｺｰﾄﾞのみ	-1
adOpenKeyset	1	キーセットカーソル	他ﾕｰｻﾞｰによるﾚｺｰﾄﾞの更新を参照可	レコード数
adOpenDynamic	2	動的カーソル	他ﾕｰｻﾞｰの全操作を参照可	-1
adOpenStatic	3	静的カーソル	開いた時点のﾚｺｰﾄﾞのみ	レコード数

図６－１７

通常は既定である「ForwardOnly」を使い、Do～Loopで１つずつカーソルを移動するのと同時にReDimで配列を広げながら値を格納する事が多いと思います。しかし完成した配列は、Redimが列方向にしか増やせない事から「行と列が逆転」してしまうため、最後にTranspose関数で元に戻す必要が出てきます。
今回は最初から「行と列が正しい姿」の配列にデータを格納するため、rs.Open直後に「取得したレコード数を把握」できる「静的カーソル（adOpenStatic）」を使用しました。（キーセットカーソルを使用しなかった理由は「よりみち」で説明します。）

２１５行目「rs.Open sql, cn, adOpenStatic」を実行した時点で、Insert文のように取得データの無いSQL文では、Recordsetオブジェクトが閉じる（Recordset.State = 0 ）ことになります。一方、Select文のように取得データがあるSQL文は開いて（Recordset.State = 1 ）います。
ですので、２１７行目「If rs.State = 0 Then」でRecordsetオブジェクトの状態を調べ、閉じている場合は２１８～２２５行目を実行し、開ている場合は２２８～２４２行目で「取得したレコードを配列に格納し、戻り値に設定」します。

まず閉じている場合は２１８行目「Select Case Left(LTrim(sql), 1)」で、SQL文の先頭文字列を調べます。データを操作するSQL文には「Select」「Insert」「Update」「Delete」の４つがあり、それぞれの先頭文字列は異なりますので「どんな操作をするSQLが来たか分かる」ことになります。２１９行目「Case "i", "I"」は「Insert文」の場合で、２２０行目「MsgBox "データ追加しました"」というコメントを出しています。以下、「Update」「Delete」文に対応したコメントを表します。
なお、今回は図６－８から「Create Table ･･･」というSQL文もこのSQL_exec5関数プロシージャで処理を行うため、「C」という先頭文字列も流れますが、仕訳けのCase文が無いため特にコメントなどは表示されません。

次に開いている場合は、まず２２８行目「ReDim buf(0 To rs.RecordCount, 1 To rs.Fields.Count)」でデータを格納する配列の最終的なサイズを決めてしまいます。行方向は「rs.RecordCount」行、列方向は「rs.Fields.Count」列として得られます。

今回、SQL_exec5関数プロシージャが戻す配列のインデックスは、図６－１８のように「タイトルはゼロ行目」に、また「データはインデックス１から」としています。タイトル行には列名を記入しています。 図６－１８ この「タイトル＋データ」の配列にするメリットは２つあります。 １つ目は「データ配列を受取った側での処理が、全て二次元配列の処理に単純化できる」ことです。Select文で得られるレコード数は色々ですが、ゼロ行となる場合もあります。もしタイトル行が無くデータ行のみの配列とした場合、２行取得なら「２行ｘ列数」の配列、１行取得なら「１行ｘ列数」の配列となります。１行ｘ列数ならば「二次元配列」にすることは可能です。 もし取得したレコードがゼロ行の場合は「ゼロ行ｘ列数」となりますが、これは二次元配列にすることは出来ません。 そのため先頭行（ゼロ行目）にタイトル行を設け、図６－１９のようにゼロ行でも「１行ｘ列数（＝二次元配列可）」の状態にしています。 図６－１９ ２つ目は「Select * from ･･･」と、アスタリスクを使って全列のデータを取得した場合、タイトル行があれば、そのままワークシートに貼り付けても「どの列のデータかが分かる」ようになります。以前紹介した「ExcelシートDBとSQLを使った倉庫管理システム」のようなシステムにも対応可能です。

２２９～２３１行目ではタイトル行に列名を記入しています。
２２９行目「For j = 1 To rs.Fields.Count」で、カウンタ変数ｊを列数分だけ回し、２３０行目「buf(0, j) = rs.Fields(j - 1).Name」で配列の０行目に列名を１つずつ格納していきます。

タイトル行が完了したら、２３３～２３９行目データを配列内に格納していきます。
２３３行目「Do Until rs.EOF」で、Do～Loopを取得レコードの最後（EOF：End of File）が来るまで回します。
２３４行目「i = i + 1」は、配列の行位置を１つずつ移動しています。カウンタ変数ｉは２０４行目でLong型として宣言しただけですので、初期値のゼロとなっています。ですので最初に２３４行目を通過する時にはｉ=1 となり、配列のデータを入れる１行目にデータを書き込むことになります。
２３５行目「For j = 1 To rs.Fields.Count」では、カウンタ変数ｊを列数分だけ回します。
２３６行目「buf(i, j) = IIf(IsNull(rs.Fields(j - 1).Value), "", rs.Fields(j - 1).Value)」で、データを書き込みますが、その際に「Nullデータは、長さゼロの文字列に変換」してから配列に格納します。ここではIIF関数を使ってワークシート関数のような使い方をしました。
なおNullデータを嫌うのは、今回のようにリストボックスやコンボボックスのリストにデータを貼り付ける際、Null文字だとエラーが出てしまう為です。１つ１つのリスト貼付け時にNullを""（長さゼロの文字列）に変換するよりは、データ供給時点でまとめて変換した方がコードも少なくなると考えました。

１行分のデータを配列に格納したら、２３８行目「rs.MoveNext」で「カーソルを１行移動」し、次の行の処理をします。
全行のデータを配列に格納し終わったら、２４１行目「SQL_exec5 = buf」で「配列を関数プロシージャの戻り値に設定」し、２４２行目「rs.Close」でRecordsetを閉じます。

rs.Open直後に取得したレコード数が分かるのは、図６－１７で分かる通りキーセットカーソルと静的カーソルです。今回、キーセットカーソル（adOpenKeyset）では無く「静的カーソル（adOpenStatic）」を使った理由は以下になります。この２つの違いは「他のユーザーが操作したデータが反映されるか否か」です。 今回システムでは「同時に複数のユーザーがデータ操作」する可能性がありますので、例えば２１５行目「rs.Open sql, cn, adOpenStatic」と２３３行目「Do Until rs.EOF」の間で、別ユーザーが同じテーブルのデータを１行削除した時のことを考えてみます。RecordsetをOpenした時には10行のレコードだったものが、Do～Loopで処理をする時には9行になるのです。 カーソルとして「静的カーソル（adOpenStatic）」を使用していれば、取得するのは「rs.Open の時点」のデータで固定されているためにDo～Loop処理時点でも10行（言ってみれば、古新聞の状態）が存在するため、食い違いは発生しません。 しかし、もし「キーセットカーソル（adOpenKeyset）」を使用していれば、他ユーザーに削除されてしまったレコードのデータを取得しようとした時に、データにアクセスできずエラーが発生します。反映される操作は図６－２０の通りです。 定数 値 反映される他ﾕｰｻﾞｰの操作 Insert Delete Update adOpenUnspecified -1 × × × adOpenForwardOnly 0 × × × adOpenKeyset 1 × 〇 〇 adOpenDynamic 2 × × × adOpenStatic 3 × × × 図６－２０ 表からも分かるように、キーセットカーソル（adOpenKeyset）を使った場合でも、他ユーザーが行挿入してもデータは反映されずにエラーは出ません。 また動的カーソル（adOpenDynamic）は、図６－１７でも「他ユーザーの全ての操作を反映」と記しましたが、結果は全て古新聞状態でした。これは、rs.Open時にadOpenDynamicを指定しても、（ロック指定が既定のadLockReadOnlyの時には）adOpenDynamic → adOpenStaticに強制的に変更されてしまうのが原因のようです。 カーソル種類を選ぶ時「最新状態の方がより良いだろう」と考えがちですが、このような不具合も考えられますので注意が必要です。 なお今回のカーソル（静的カーソル）では、古新聞のデータリストとなり、例えば「別ユーザーが削除済みのデータが表示されている」のような状態も考えられます。その（既に削除済みの）データを再び削除処理をすると、Where句で抽出されずにDelete文を実行することになり、結果として「削除しましたコメントは出るが、実際には（既に削除されてるので）削除されない」ことになります。結果的には異常は無い形になっています。 また、SQL文を実行するのに「cn.Execute sql」というコードが使えますが、Executeメソッドのカーソルは前方スクロールカーソル(adOpenForwardOnly相当)固定です。取得したレコード行数を取得するカーソルはExcecuteメソッドには指定できないため、今回は「Recordset.Open」を使用しました。

７．売上ダイアログ（UserForm1）

７－１．コントロールの配置

売上データを操作するダイアログがUserForm1で、そのフォーム上のコントロール類の配置は図７－１の様にしました。

図７－１

操作を実行するCommandButtonをフォーム上部に５つ並べます。また表示するデータの年を入力するTextBox4も上部に配置しています。
ダイアログ下部にデータ一覧を表示するListBox1を配置し、中央部にユーザーが入力するTextBox、ComboBoxを配置します。
Label１～３は、ユーザーが入力するものではありませんが、リストの選択や、単価・合計値の計算結果を表示するため、ユーザー入力部と同じ段に配置しました。

７－２．フォームモジュール（UserForm1）

７－２－１．起動時準備

フォームが呼び出された際に最初に実行されるのが図７－２のInitializeイベントです。ここではコントロール類の静的なプロパティをセットします。

260. '==========　⇩(12)　フォーム起動時　============
261. Private Sub UserForm_Initialize()
263. 　With Me.ComboBox1
264. 　　.ColumnCount = 2
265. 　　.ColumnWidths = "40;50"
266. 　　.MatchRequired = True
267. 　　.TextAlign = fmTextAlignLeft
268. 　　.TextColumn = 2
269. 　End With
271. 　With Me.ComboBox2
272. 　　.ColumnCount = 3
273. 　　.ColumnWidths = "40;50;20"
274. 　　.MatchRequired = True
275. 　　.TextAlign = fmTextAlignLeft
276. 　　.TextColumn = 2
277. 　End With
279. 　Me.Label1.TextAlign = fmTextAlignRight
280. 　Me.Label2.TextAlign = fmTextAlignRight
281. 　Me.Label3.TextAlign = fmTextAlignRight
283. 　With Me.ListBox1
284. 　　.ColumnCount = 10
285. 　　.ColumnWidths = "25;50;65;40;75;40;110;50;30;30"
286. 　　.TextAlign = fmTextAlignLeft
287. 　End With
289. 　Me.TextBox1.IMEMode = fmIMEModeOff
290. 　Me.TextBox2.IMEMode = fmIMEModeDisable
291. 　Me.TextBox3.IMEMode = fmIMEModeDisable
292. 　Me.TextBox4.IMEMode = fmIMEModeDisable
294. End Sub

図７－２

２６３～２６９行目は、取引先を選択するComboBox1の設定です。取引先は「取引先番号」と「取引先名」の２つのデータで出来ていますので、２６４行目「.ColumnCount = 2」で２列表示とし、２６５行目「.ColumnWidths = "40;50"」で、表示列の幅を設定しています。なおコンボボックスを複数列表示にした時に、横スクロールバーが出ないようにするには「先入先出の入出庫管理システム」を参照願います。

２６６行目「.MatchRequired = True」は、リストにある値と同じ値のみを許可する設定をしています。２６８行目で２列目を表示列にしているため、手入力の場合は「取引先名」のみが入力可能となります。もし、それ以外を入力すると、コンボボックスを抜け出る時に「プロパティの値が無効です」と怒られます。なお、一旦コンボボックスに入り「何も選択せずに抜け出す」と「リスト内には、""（＝無選択）は無い」ために、やはり「プロパティの値が無効です」と怒られます。
２６７行目「.TextAlign = fmTextAlignLeft」は、各列の値を左寄せで表示します。
２６８行目「.TextColumn = 2」は、選択後は「２列目の取引先名」を表示するようにしています。

２７１～２７７行目は、商品を選択するComboBox2の設定です。商品は「商品番号」「商品名」「単価」の３つのデータで出来ていますので、２７２行目「.ColumnCount = 3」で３列表示とし、２７３行目「.ColumnWidths = "40;50;20"」で、表示列の幅を設定しています。
２７４行目「.MatchRequired = True」は、リストにある値と同じ値のみを許可する設定をし、２７５行目「.TextAlign = fmTextAlignLeft」は、各列の値を左寄せで表示します。
２７６行目「.TextColumn = 2」は、選択後は「２列目の商品名」を表示するようにしています。

２７９行目「Me.Label1.TextAlign = fmTextAlignRight」は、SNoを表示するLabel1を右寄せにしています。理由はSNoが「数値」だからです。２８０行目（単価のLabel2）、２８１行目（合計値のLabel3）も数値のため右寄せにします。

２８３～２８７行目は、売上データ一覧を表示するListBox1の設定です。
リストボックスに表示するデータは、「SNo」「伝票番号」「売上日付」「取引先番号」「取引先名」「商品番号」「商品名」「単価」「個数」「合計値」の１０項目ですので、２８４行目「.ColumnCount = 10」で１０列表示とし、その列幅を２８５行目「.ColumnWidths = "25;50;65;40;75;40;110;50;30;30"」で設定します。この列幅はTry & Error で調整しました。
また、２８６行目「.TextAlign = fmTextAlignLeft」でリストは左寄せにしています。本当でしたら「文字列は左寄せ」「数値は右寄せ」にしたいのですが、各列毎の設定は出来ないため左側に統一しました。

２８９行目「Me.TextBox1.IMEMode = fmIMEModeOff」は、伝票番号を入力するTextBox1に「fmIMEModeOff」を設定し、既定は半角入力にしています。既定ですので、「漢字キーを押せば、全角入力が可能」となります。
２９０～２９２行目は「売上日付」「売上個数」「表示年」のTextBox２～４のIME設定です。この３つは全角は有り得ないため「fmIMEModeDisable」と「半角のみ」としています。ですので漢字キーを押しても全角モードにはなりません。

Initializeイベントで静的なプロパティをセットした後、ダイアログが表示された直後に実行されるのが図７－３のActivateイベントです。ここではコントロールの動的なプロパティ等をセットします。

300. '==========　⇩(13)　フォーム表示時　============
301. Private Sub UserForm_Activate()
303. 　Me.TextBox4.Value = Year(Date)
305. 　Me.Label1.Caption = ""
306. 　Me.Label2.Caption = "0"
307. 　Me.Label3.Caption = "0"
309. 　Call makeCombo1
310. 　Call makeCombo2
311. 　Call makeListBox
313. End Sub

図７－３

３０３行目「Me.TextBox4.Value = Year(Date)」は、表示するリスト、及び出力するリストの「表示年」のTextBox4に、起動直後は「今年」を表示しています。フォーム起動後、手入力で表示年を変更することが可能です。
３０５行目「Me.Label1.Caption = ""」は、SNo（売上番号）のLabel1を起動直後は空にしています。このLabel1は、リストボックスの項目を選択することで、その行のSNoが転記されることになります。
３０６行目「Me.Label2.Caption = "0"」は、単価のLable2です。初期値はゼロ（円）としています。
３０７行目「Me.Label3.Caption = "0"」は、合計金額のLable3です。初期値はゼロ（円）としています。

３０９行目「Call makeCombo1」では、図７－４を呼出し、取引先コンボボックスのリストを作成しています。
３１０行目「Call makeCombo2」では、図７－５を呼出し、商品コンボボックスのリストを作成しています。
３１１行目「Call makeListBox」では、図７－６を呼出し、売上データをリストボックスに表示します。その際、表示年はTextBox4の値で絞り込みを行っています。

７－２－２．コンボボックス作成

図７－３の３０９行目から呼び出されるのが「取引先コンボボックスを作成」する図７－４です。

320. '==========　⇩(14)　取引先コンボボックスの作成　============
321. Sub makeCombo1()
322. 　Dim sql As String 　　 '←SQL文
323. 　Dim i As Long 　　　　 '←カウンタ変数(行数)
324. 　Dim buf As Variant 　 '←取引先データの配列
326. 　Me.ComboBox1.Clear
328. 　sql = "SELECT CNo,Cname FROM " & TBL1 & " where Del = false order by CNo"
329. 　buf = SQL_exec5(sql)
331. 　For i = 1 To UBound(buf, 1)
332. 　　Me.ComboBox1.AddItem ""
333. 　　Me.ComboBox1.List(i - 1, 0) = buf(i, 1)
334. 　　Me.ComboBox1.List(i - 1, 1) = buf(i, 2)
335. 　Next i
336. End Sub

図７－４

３２６行目「Me.ComboBox1.Clear」で、コンボボックスのリストをクリアしています。但し今回システムでは「フォームを閉じる時にUnLoadを使用」しているため、リストを二重に作成することは有りませんが、プログラムを改造された時も考え念の為クリアさせています。

３２８行目「sql = "SELECT CNo,Cname FROM " & TBL1 & " where Del = false order by CNo"」は、取引先データを取得するSQL文です。データのあるTBL1（＝Custテーブル）から２つの列（CNo列、Cname列）のデータを取り出します。但し、削除相当のデータ（Del列がTrue）は除き、表示する順序はCNo（取引先番号）の順番にしています。
作成したSQL文を引数にして、３２９行目「buf = SQL_exec5(sql)」で図６－１６を呼出し、データが格納された戻り値を受け取り、変数bufに代入します。

３３１～３３５行目では、コンボボックスのリスト上に取引先データを書き込みます。
３３１行目「For i = 1 To UBound(buf, 1)」で取引先データの行数分だけカウンタ変数ｉを回します。
３３２行目「Me.ComboBox1.AddItem ""」で、まず空の行を追加します。その空行の１列目（インデックスはゼロ）に３３３行目「Me.ComboBox1.List(i - 1, 0) = buf(i, 1)」で、取引先番号のデータを書込み、３３４行目「Me.ComboBox1.List(i - 1, 1) = buf(i, 2)」で、２列目（インデックスは１）に取引先名のデータを書き込みます。

図７－３の３１０行目から呼び出されるのが「商品コンボボックスを作成」する図７－５です。

340. '==========　⇩(15)　商品コンボボックスの作成　============
341. Sub makeCombo2()
342. 　Dim sql As String 　　 '←SQL文
343. 　Dim i As Long 　　　　 '←カウンタ変数(行数)
344. 　Dim buf As Variant 　 '←商品データの配列
346. 　Me.ComboBox2.Clear
348. 　sql = "SELECT PNo,Pname,Uprice FROM " & TBL2 & " where Del = false order by PNo"
349. 　buf = SQL_exec5(sql)
351. 　For i = 1 To UBound(buf, 1)
352. 　　Me.ComboBox2.AddItem ""
353. 　　Me.ComboBox2.List(i - 1, 0) = buf(i, 1)
354. 　　Me.ComboBox2.List(i - 1, 1) = buf(i, 2)
355. 　　Me.ComboBox2.List(i - 1, 2) = buf(i, 3)
356. 　Next i
357. End Sub

図７－５

３４６行目「Me.ComboBox2.Clear」で、コンボボックスのリストをクリアしています。
３４８行目「sql = "SELECT PNo,Pname,Uprice FROM " & TBL2 & " where Del = false order by PNo"」は、商品データを取得するSQL文です。データのあるTBL2（＝Pdctテーブル）から３つの列（PNo列、Pname列、Uprice列）のデータを取り出します。但し、削除相当のデータ（Del列がTrue）は除き、表示する順序はPNo（商品番号）の順番にしています。
作成したSQL文を引数にして、３４９行目「buf = SQL_exec5(sql)」で図６－１６を呼出し、データが格納された戻り値を受け取り、変数bufに代入します。

３５１～３５６行目では、コンボボックスのリスト上に商品データを書き込みます。
３５１行目「For i = 1 To UBound(buf, 1)」で商品データの行数分だけカウンタ変数ｉを回します。
３５２行目「Me.ComboBox2.AddItem ""」で、まず空の行を追加します。その空行の１列目（インデックスはゼロ）に３５３行目「Me.ComboBox2.List(i - 1, 0) = buf(i, 1)」で、商品番号のデータを書込み、３５４行目「Me.ComboBox2.List(i - 1, 1) = buf(i, 2)」で２列目（インデックスは１）に商品名、３５５行目「Me.ComboBox2.List(i - 1, 2) = buf(i, 3)」で３列目（インデックスは２）に単価のデータを書き込みます。

７－２－３．リストボックス作成

図７－３の３１１行目、図７－１６の５１０行目、図７－１７の５３８行目、及び図７－１８の５４７行目から呼び出されるのが「リストボックスに売り上げデータを表示」させる図７－６です。

360. '==========　⇩(16)　リストボックス作成　============
361. Private Sub makeListBox()
362. 　Dim i As Long 　　　　 '←カウンタ変数(行数)
363. 　Dim buf As Variant 　 '←売上データの配列
365. 　Me.ListBox1.Clear
367. 　buf = SQL_exec5(SQLword("Desc")) 　 '←降順（最新データが一番上）でデータ取得するSQL文
369. 　For i = 1 To UBound(buf, 1)
370. 　　Me.ListBox1.AddItem ""
371. 　　Me.ListBox1.List(i - 1, 0) = buf(i, 1)
372. 　　Me.ListBox1.List(i - 1, 1) = buf(i, 2)
373. 　　Me.ListBox1.List(i - 1, 2) = buf(i, 3)
374. 　　Me.ListBox1.List(i - 1, 3) = buf(i, 4)
375. 　　Me.ListBox1.List(i - 1, 4) = buf(i, 5)
376. 　　Me.ListBox1.List(i - 1, 5) = buf(i, 6)
377. 　　Me.ListBox1.List(i - 1, 6) = buf(i, 7)
378. 　　Me.ListBox1.List(i - 1, 7) = Format(buf(i, 8), "#,##0")
379. 　　Me.ListBox1.List(i - 1, 8) = buf(i, 9)
380. 　　Me.ListBox1.List(i - 1, 9) = Format(buf(i, 10), "#,##0")
381. 　Next i
383. End Sub

図７－６

３６５行目「Me.ListBox1.Clear」は、リストボックスのデータを一旦クリアしています。取引先コンボボックスと商品コンボボックスの際には「Clearは念の為」だったのですが、ここでは「フォーム起動時」の他に、起動後の売上データの「登録／更新」、データの「削除」、表示年を切り替えた後の「リスト再表示」ボタンをクリックした時にも呼び出されます。ですので元の表示データをクリアしておかないと、ダブって表示されてしまいます。

３６７行目「buf = SQL_exec5(SQLword("Desc"))」ではSQL文を図７－７から呼出し、それを引数にしてSQL文を実行し、戻り値である売上データ配列を変数bufに代入します。
SQL文を呼出し式にしたのは、今回「取得する列、絞り込み条件は一緒」なのに「並べ方のみが異なる」SQL文が必要になったため、コードの効率化＋修正容易化を目的に独立させました。
なおSQLwordに渡す引数は「Asc（昇順）」「Desc（降順）」のどちらかで、「""（長さゼロの文字列）」を渡すと、既定の昇順になります。

３６９～３８１行目では、売上データ配列をリスト化しています。「データ列数＝配列の列数」としており、配列の列順序通りにListBox1へ並べれば良いように、図７－７のSQL文を作っています
３６９行目「For i = 1 To UBound(buf, 1)」で、カウンタ変数ｉを配列の行数分だけ回しています。
３７０行目「Me.ListBox1.AddItem ""」では、リストボックスに空の行を作成します。
３７１行目「Me.ListBox1.List(i - 1, 0) = buf(i, 1)」では、作った空の行の１列目（インデックスはゼロ）に配列の１列目のデータ（売上番号）を書き込みます。３７２行目以降も２列目・３列目・・・と書き込みます。
なお８列目（インデックス＝７）は「単価」、１０列目（インデックス＝９）は「合計」で両方とも金額のため、「Format(buf(i, 8), "#,##0")」と「３桁ごとにカンマ」を入れた表示に変換しています。

図７－６の３６７行目、図７－１９の５６０行目から呼び出される「売上データ」を抽出するSQL文を作るのが図７－７です。引数として「並び順を指定するString」を受け取ります。

390. '==========　⇩(17)　売上データ抽出のSQL文　============
391. Private Function SQLword(ORD As String) As String
393. 　SQLword = "SELECT S.SNo,S.Slip,S.SaleD,S.CustNo,C.Cname,S.PdctNo,P.Pname," & _
394. 　　　　　　"S.UnitPrice,S.Quant,S.UnitPrice*S.Quant " & _
395. 　　　　　　" FROM " & TBL0 & " S ," & TBL1 & " C ," & TBL2 & " P " & _
396. 　　　　　　" where S.CustNo = C.CNo and S.PdctNo = P.PNo " & _
397. 　　　　　　" and year(S.SaleD) =" & Me.TextBox4.Value & _
398. 　　　　　　" order by S.SaleD " & ORD & " , S.SNo " & ORD
400. End Function

図７－７

今回のSQL文は、３つのテーブルからデータを取り出しています。３９３～３９４行目「SQLword = "SELECT S.SNo,S.Slip,S.SaleD,S.CustNo,C.Cname,S.PdctNo,P.Pname," & "S.UnitPrice,S.Quant,S.UnitPrice*S.Quant " & _ 」では、取り出す１０列分の列名を列記しています。しかし、どの列名がどのテーブルのものかを明記する必要があります。
ですので３９５行目「" FROM " & TBL0 & " S ," & TBL1 & " C ," & TBL2 & " P " & _」では、「テーブル名」の後ろにスペースを空けて別名（エイリアス：S、C、P ）を設定し、各列名の前に付けています。例えば「S.SNo」は、TBL0（＝Saleテーブル）のSNo列という意味になります。

３９６行目「" where S.CustNo = C.CNo and S.PdctNo = P.PNo " & _」は、３つのテーブルの結合の仕方を定めています。Saleテーブル（TBL0）のCustNoとCustテーブル（TBL1）のCNoを結びつけ、またSaleテーブル（TBL0）のPdctNoとPdctテーブル（TBL2）のPNoを結びつけて「見掛け上１つのテーブル」にしています。
また３９７行目「" and year(S.SaleD) =" & Me.TextBox4.Value & _」では、売上日付をTextBox4（表示年）のものに絞り込みをしています。

３９８行目「" order by S.SaleD " & ORD & " , S.SNo " & ORD」では、売上日付・売上番号を「引数で得た順序（Ascまたは""の時には昇順、Descの時には降順）」で並べ替えをしています。

７－２－４．入力部の処理

伝票番号を入力するTextBox1に、値を入力した時に発生するChangeイベントが図７－８です。
４０７行目「Me.TextBox1.Value = UCase(Me.TextBox1.Value)」で、入力した文字を「大文字」に変換しています。

405. '==========　⇩(18)　伝票番号を入力　============
406. Private Sub TextBox1_Change()
407. 　Me.TextBox1.Value = UCase(Me.TextBox1.Value)
408. End Sub

図７－８

売上日付を入力するTextBox2に、値を入力した時に発生するKeyPressイベントが図７－９です。
日付は「2022/12/24」や「2022-12-24」のように、数字及び「-」「/」しか使いません。ですので４１２行目「If Not Chr(KeyAscii) Like "[0-9/-]" Then KeyAscii = 0」で、数字及び「-」「/」以外は無視をしています。
Likeに続くカギカッコの中に、通過させる文字列を記載し、Notで反転することでそれ以外を無視させています。

410. '==========　⇩(19)　売上日付を入力　============
411. Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
412. 　If Not Chr(KeyAscii) Like "[0-9/-]" Then KeyAscii = 0
413. End Sub

図７－９

売上個数を入力するTextBox3に、値を入力した時に発生するKeyPressイベントが図７－１０です。
個数は数値のみなので、４１７行目「If Not Chr(KeyAscii) Like "[0-9]" Then KeyAscii = 0」で数値以外を無視します。
なお４１７行目の代わりに「If KeyAscii < Asc("0") Or KeyAscii > Asc("9") Then KeyAscii = 0」としてもOKです。

415. '==========　⇩(20)　売上個数を入力　============
416. Private Sub TextBox3_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
417. 　If Not Chr(KeyAscii) Like "[0-9]" Then KeyAscii = 0
418. End Sub

図７－１０

商品のコンボボックスを選択した時に発生するのが図７－１１です。この商品リストの３列目の商品単価の処理を行ってます。
４２２行目「Me.Label2.Caption = Me.ComboBox2.List(Me.ComboBox2.ListIndex, 2)」で選択した項目の単価の値をLabel2の単価欄へ書き込みます。
次に、４２３行目「Me.Label3.Caption = Val(Me.Label2.Caption) * Val(Me.TextBox3.Value)」で、そのLable2の単価とTextBox3の個数を掛け合わせて、合計金額をLabel3の合計欄に書き込みます。もちろん個数が未入力（＝ゼロ個）の場合は合計値もゼロとなります。
４２４行目「Me.Label3.Caption = Format(Me.Label3.Caption, "#,##0")」は、Label3の金額を３桁毎にカンマを付けています。４２３と４２４行目は、もちろん１つの行にすることが出来ます。

420. '==========　⇩(21)　商品コンボボックスを選択　============
421. Private Sub ComboBox2_Click()
422. 　Me.Label2.Caption = Me.ComboBox2.List(Me.ComboBox2.ListIndex, 2)
423. 　Me.Label3.Caption = Val(Me.Label2.Caption) * Val(Me.TextBox3.Value)
424. 　Me.Label3.Caption = Format(Me.Label3.Caption, "#,##0")
425. End Sub

図７－１１

個数を入力した時に発生するのが図７－１２です。商品単価とこの個数の値を使って合計値処理をします。
４３２行目「Me.Label3.Caption = Val(Me.Label2.Caption) * Val(Me.TextBox3.Value)」で、Lable2の単価と個数を掛け合わせて、合計金額をLabel3の合計欄に書き込みます。
４３３行目「Me.Label3.Caption = Format(Me.Label3.Caption, "#,##0")」は、Label3の金額を３桁毎にカンマを付けています。
Label2の単価とTextBox3の個数の掛け算がLabel3の合計金額になる訳ですが、単価と個数のどちらが先に値が入るのかは分かりませんので、図７－１１と図７－１２の両方に掛け算の式を入れています。

430. '==========　⇩(22)　個数を入力　============
431. Private Sub TextBox3_Change()
432. 　Me.Label3.Caption = Val(Me.Label2.Caption) * Val(Me.TextBox3.Value)
433. 　Me.Label3.Caption = Format(Me.Label3.Caption, "#,##0")
434. End Sub

図７－１２

表示年を入力するTextBox4の文字種制限をするのが図７－１３です。
４４２行目「If Not Chr(KeyAscii) Like "[0-9]" Then KeyAscii = 0」では、数値（0～9）以外は無視をしています。
代わりに「If KeyAscii < Asc("0") Or KeyAscii > Asc("9") Then KeyAscii = 0」としてもOKです。

440. '==========　⇩(23)　表示年を入力　============
441. Private Sub TextBox4_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
442. 　If Not Chr(KeyAscii) Like "[0-9]" Then KeyAscii = 0
443. End Sub

図７－１３

７－２－５．リストボックスの選択

売上データのリストボックスを選択した時、選択したデータを入力部に転記します。その処理を行うのが図７－１４です。

450. '==========　⇩(24)　リストボックスの選択　============
451. Private Sub ListBox1_Click()
453. 　On Error Resume Next
454. 　　With Me.ListBox1
455. 　　　Me.Label1.Caption = .List(.ListIndex, 0)
456. 　　　Me.TextBox1.Value = .List(.ListIndex, 1)
457. 　　　Me.TextBox2.Value = .List(.ListIndex, 2)
458. 　　　Me.ComboBox1.ListIndex = -1
459. 　　　Me.ComboBox1.Value = .List(.ListIndex, 3)
460. 　　　Me.ComboBox2.ListIndex = -1
461. 　　　Me.ComboBox2.Value = .List(.ListIndex, 5)
462. 　　　Me.Label2.Caption = .List(.ListIndex, 7)
463. 　　　Me.TextBox3.Value = .List(.ListIndex, 8)
464. 　　　Me.Label3.Caption = .List(.ListIndex, 9)
465. 　　End With
466. 　On Error GoTo 0
468. End Sub

図７－１４

４５４行目「With Me.ListBox1」以降でListBox1の値を各コントロールへ転記処理を行います。転記するのは、図７－１５のように全部で８項目です。

送る側		受け取る側
項目	列Index
売上番号	0	Label1
伝票番号	1	TextBox1
売上日付	2	TextBox2
取引先番号	3	ComboBox1
商品番号	5	ComboBox2
単価	7	Label2
個数	8	TextBox3
合計金額	9	Label3

図７－１５

４５５～４５７行目では、リストの先頭側からまず「売上番号」「伝票番号」「売上日付」を「Label1」「TextBox1」「TextBox2」に値転記します。

次に、取引先関係は「取引先番号」と「取引先名」がありますが、受け取るComboBox1側には「コンボボックスの先頭列の取引先番号」を渡します。これはInitializeイベント等で「BoundColumn」の設定をしていない為、既定値の１（＝先頭列がコンボボックスの値）となっている為です。
例えばBoundColumn値を２と設定した場合は、取引先名を転記することになります。しかし、今回「取引先名には何も制約を設定していない（図６－１０）」ために、名前が重複していた場合に先にある方を選択してしまうことになります。
ですので「BoundColumn値にはPrimary Keyに設定した列を指定」する必要があります（今回は先頭列だった為省略した）。

また、コンボボックスの転記する前の４５８行目で「Me.ComboBox1.ListIndex = -1」と、一旦「非選択状態」にしています。これは「削除相当（Del列＝True）の取引先データを選択」した場合、４５９行目「Me.ComboBox1.Value = .List(.ListIndex, 3)」が「（Del列＝Trueのレコードは、リスト非表示のため）コンボボックスのリスト内には、相当する取引先番号が無い」ためにエラーとなります。エラー自体は４５３行目「On Error Resume Next」でスルーされるのですが、４５９行目が実行されない為に「前回選択した取引先データが表示されたまま」となってしまいます。
この矛盾を解消するために、一旦４５８行目で「非選択」にしています。ですので、もし削除相当の項目を選択した時には、コンボボックスは「何も選択されていない状態」となります。

４６０～４６１行目の商品番号をComboBox2に転記するのも、ComboBox1と同様です。

４６２～４６４行目は、「単価」「個数」「合計金額」を「Label2」「TextBox3」「Label3」に値転記します。

７－２－６．データ処理

７－２－６－１．登録／更新ボタン

「登録／更新」ボタン（CommandButton1）をクリックした時に実行されるのが図７－１６です。

470. '==========　⇩(25)　登録／更新ボタン　============
471. Private Sub CommandButton1_Click()
472. 　Dim sql As String 　　　　　　 '←SQL文
473. 　Dim Ans As VbMsgBoxResult 　 '←MsgBoxでの返答
475. 　If isMissingInput Then Exit Sub 　　　 '←入力部の入力有無を確認
477. 　If Me.Label1.Caption = "" Then 　　　 '←SNoが無い場合
479. 　　sql = "Insert into " & TBL0 & " (Slip,SaleD,CustNo,PdctNo,UnitPrice,Quant)" & _
480. 　　　　　" values('" & Me.TextBox1.Value & "','" & _
481. 　　　　　Me.TextBox2.Value & "','" & _
482. 　　　　　Me.ComboBox1.List(Me.ComboBox1.ListIndex, 0) & "','" & _
483. 　　　　　Me.ComboBox2.List(Me.ComboBox2.ListIndex, 0) & "'," & _
484. 　　　　　Val(Me.Label2.Caption) & "," & _
485. 　　　　　Val(Me.TextBox3.Value) & ")"
487. 　Else 　 '←SNoが有る場合
488. 　　Ans = MsgBox("データを上書き(Yes)しますか？　追加(No)しますか？", vbYesNo)
490. 　　If Ans = vbYes Then 　　　 '←｢上書き｣をﾕｰｻﾞｰが指定した場合
491. 　　　sql = "Update " & TBL0 & _
492. 　　　　　　" set Slip= '" & Me.TextBox1.Value & "'," & _
493. 　　　　　　" SaleD='" & Me.TextBox2.Value & "'," & _
494. 　　　　　　" CustNo='" & Me.ComboBox1.List(Me.ComboBox1.ListIndex, 0) & "'," & _
495. 　　　　　　" PdctNo='" & Me.ComboBox2.List(Me.ComboBox2.ListIndex, 0) & "'," & _
496. 　　　　　　" UnitPrice=" & Val(Me.Label2.Caption) & "," & _
497. 　　　　　　" Quant=" & Val(Me.TextBox3.Value) & _
498. 　　　　　　" where SNo=" & Val(Me.Label1.Caption)
500. 　　Else 　　　 '←｢追加｣をﾕｰｻﾞｰが指定した場合
501. 　　　Me.Label1.Caption = "" 　　　 '←SNoを消して
502. 　　　Call CommandButton1_Click 　　　 '←データ新規追加として再帰呼び出し
503. 　　　Exit Sub
504. 　　End If
506. 　End If
508. 　Call SQL_exec5(sql)
510. 　Call makeListBox
511. End Sub

図７－１６

４７５行目「If isMissingInput Then Exit Sub」では、図７－２４を呼び出し「入力部全てに、正しい値が入力されているか」をチェックし、正しくなければTrueを返してきます。正しくない場合は「Exit Sub」で処理を中止します。

正しく入力されている場合、SNo（売上番号：Label1）が入力されているか否かで処理を分岐させます。SNoが無ければ「ダイアログを起動した後、ユーザーが全ての項目を手入力した」ことになります。また、SNoが有れば「リストを選択し、項目の内容を変更してから登録／更新ボタンをクリック」した、と考えられます。
ですので、まず４７７行目「If Me.Label1.Caption = "" Then」が成立する場合は、４７９～４８５行目のSQL文を作成します。
作成するSQL文はInsert文で、４７９行目「sql = "Insert into " & TBL0 & " (Slip,SaleD,CustNo,PdctNo,UnitPrice,Quant)" & _」で、TBL0（＝Saleテーブル）に６列分（Slip,SaleD,CustNo,PdctNo,UnitPrice,Quant）のデータを挿入します。
データとしては、
Slip列には４８０行目「" values('" & Me.TextBox1.Value & "','" & _」でTextBox1の伝票番号を、
SaleD列には４８１行目「Me.TextBox2.Value & "','" & _」でTextBox2の売上日付を、
CustNo列には４８２行目「Me.ComboBox1.List(Me.ComboBox1.ListIndex, 0) & "','" & _」でComboBox1の取引先番号を、
PdctNo列には４８３行目「e.ComboBox2.List(Me.ComboBox2.ListIndex, 0) & "'," & _」でComboBox2の商品番号を、
UnitPrice列には４８４行目「Val(Me.Label2.Caption) & "," & _」でLabel2の単価を、
Quant列には４８５行目「Val(Me.TextBox3.Value) & ")"」でTextBox3の個数を挿入します。

なおSaleテーブルは全７列です。４７９～４８５行目のInsert文では６列しかデータを入れていませんが、残り１列「SNo」はデータ型をCOUNTERとしていますので「自動的に採番」を振ってくれますのでInsert文では対象外としています。

SNoが有る場合（４８７行目「Else」以降）は、４８８行目「Ans = MsgBox("データを上書き(Yes)しますか？　追加(No)しますか？", vbYesNo)」でメッセージボックスを表示させ、ユーザーに「上書き」か「新規としてデータ追加」するかを選択させます。
MsgBoxの回答が、４９０行目「If Ans = vbYes Then」で上書きの場合は、４９１～４９８行目でUpdate文を作成します。
４９１行目「sql = "Update " & TBL0 & _」ではTBL0（Saleテーブル）に対して更新をします。
４９２行目「" set Slip= '" & Me.TextBox1.Value & "'," & _」で、Slip列をTextBox1（伝票番号）の値にします。
４９３行目「" SaleD='" & Me.TextBox2.Value & "'," & _」で、SaleD列をTextBox2（売上日付）の値にします。
４９４行目「" CustNo='" & Me.ComboBox1.List(Me.ComboBox1.ListIndex, 0) & "'," & _」で、CustNo列をComboBox1で選択中のゼロ列目（取引先番号）の値にします。
４９５行目「" PdctNo='" & Me.ComboBox2.List(Me.ComboBox2.ListIndex, 0) & "'," & _」で、PdctNo列をComboBox2で選択中のゼロ列目（商品番号）の値にします。
４９６行目「" UnitPrice=" & Val(Me.Label2.Caption) & "," & _」で、UnitPrice列をLabel2（単価）の文字列を数値に変換した値にします。
４９７行目「" Quant=" & Val(Me.TextBox3.Value) & _」で、Quant列をTextBox3（個数）の文字列を数値に変換した値にします。
４９８行目「" where SNo=" & Val(Me.Label1.Caption)」では、SNoがLabel1（売上番号）のものだけを変更します。SNoはSaleテーブルではPrimary Keyですので、テーブル内で１行のみが書き換わることになります。

なお、合計値はSaleテーブルの中では保持せず「単価×個数」で、都度計算をしています。

４８８行目のメッセージボックスで、ユーザーが「いいえ（＝新規としてデータ追加）」を選んだ（５００行目「Else」）場合は、５０１行目「Me.Label1.Caption = ""」でSNo（売上番号）を削除してから、５０２行目「Call CommandButton1_Click」で「このプロシージャを再帰呼び出し」します。呼び出された側では、「SNoが無い」ことからそのまま「新規追加」として４７９～４８５行目のInsert文が実行されます。
５０３行目「Exit Sub」では、再帰呼び出しでInsert文を実行した後このプロシージャを抜け出し、処理を終了します。

SQL文が作成されたのち、５０８行目「Call SQL_exec5(sql)」で図６－１６を呼出し、SQL文を実行します。
なお図６－１６はFunctionプロシージャですが、今回実行するInsert文とUpdate文では「Select文のように受け取るレコードが無い」ために、「buf = SQL_exec5(sql)」とする必要はありません。

テーブルへの追加・変更が完了した後、５１０行目「Call makeListBox」で図７－６を呼出し、リストボックスを更新します。

７－２－６－２．削除ボタン

削除ボタンをクリックした時に呼び出されるのが図７－１７です。

520. '==========　⇩(26)　削除ボタン　============
521. Private Sub CommandButton2_Click()
522. 　Dim sql As String 　　　　　 '←SQL文
523. 　Dim Ans As VbMsgBoxResult 　 '←MsgBoxの回答
525. 　If Me.ListBox1.ListIndex = -1 Then
526. 　　MsgBox "リストから削除する項目を選択して下さい。"
527. 　　Exit Sub
528. 　End If
530. 　Ans = MsgBox("選択項目を削除して良いですか？", vbYesNo)
531. 　If Ans = vbNo Then Exit Sub
533. 　sql = "Delete from " & TBL0 & _
534. 　　　　" where SNo= " & Me.ListBox1.List(Me.ListBox1.ListIndex, 0)
536. 　Call SQL_exec5(sql)
538. 　Call makeListBox
539. End Sub

図７－１７

項目を削除するには、その削除対象が明確である必要があります。今回は「リスト上で選択した項目」を削除対象としています。５２５～５２８行目では、その削除対象が選択されているかを確認しています。
５２５行目「If Me.ListBox1.ListIndex = -1 Then」でリストボックスが選択されていない（ListIndex = -1 ）場合は、５２６行目「MsgBox "リストから削除する項目を選択して下さい。"」でメッセージを出し、５２７行目「Exit Sub」で処理を中止します。
リストボックスが選択されている場合は、５３０行目「Ans = MsgBox("選択項目を削除して良いですか？", vbYesNo)」でユーザーへ再確認を行い、「いいえ（＝削除しない）」を選んだ場合は５３１行目「If Ans = vbNo Then Exit Sub」で処理を中止します。

削除OKの場合は５３３～５３４行目でDelete文を作成します。
５３３行目「sql = "Delete from " & TBL0 & _」では、TBL0（Saleテーブル）を対象にレコードを削除します。
５３４行目「" where SNo= " & Me.ListBox1.List(Me.ListBox1.ListIndex, 0)」では削除するレコードを、SNo列が「リストボックスで選択している項目の先頭列の値（売上番号）のもの」に絞り込んでいます。SNoはSaleテーブルではPrimary Keyになっていますので、実際は１レコードに絞られます。
なお、リストボックスで選択した項目のSNoは、図７－１４の４５５行目でLabel1に転記されていますので、５３４行目を「" where SNo=" & Val(Me.Label1.Caption)」としてもOKです。

５３６行目「Call SQL_exec5(sql)」は、作成したSQL文を図６－１６に送付し、SQL文を実行（＝レコード削除）しています。
削除が完了したら、リストボックスに反映するため、５３８行目「Call makeListBox」で図７－６を呼出し、リストを更新します。

７－２－６－３．リストボックス再表示

「リスト再表示」ボタンをクリックした時に呼び出されるのが図７－１８です。主に表示年（TextBox4）を変更した後にリスト更新するのに使われると考えています。
５４７行目「Call makeListBox」で図７－６を呼出して、リストボックスを更新します。

545. '==========　⇩(27)　リストボックス再表示　============
546. Private Sub CommandButton3_Click()
547. 　Call makeListBox
548. End Sub

図７－１８

７－２－６－４．シートへの出力

「リスト出力」ボタンをクリックした時に呼び出されるのが図７－１９です。

550. '==========　⇩(28)　シートへの出力　============
551. Private Sub CommandButton4_Click()
552. 　Dim buf As Variant 　 '←売上データ配列
553. 　Dim r As Range 　　　 '←シート上に既に出力されているデータ
555. 　Set r = Sheet1.Range("B2").CurrentRegion
556. 　Sheet1.Range(Range("B2:K2"), Cells(r.Row + r.Rows.Count - 1, 2)).Offset(1, 0).ClearContents
558. 　Sheet1.Range("C1") = Me.TextBox4.Value
560. 　buf = SQL_exec5(SQLword("Asc")) 　 '←昇順のデータ
561. 　Set r = Sheet1.Range("B3").Resize(UBound(buf, 1) + 1, UBound(buf, 2))
562. 　r = buf
563. 　r.Rows(1).Delete
565. End Sub

図７－１９

５５５行目「Set r = Sheet1.Range("B2").CurrentRegion」は、タイトルを含め「シート上に出力されているデータ範囲」を調べています。図７－２０のように、セル範囲「B2」は、売上番号である「No」が元々記入されている場所です。それに繋がっている範囲（CurrentRegion）が変数rに設定されます。


図７－２０

５５６行目「Sheet1.Range(Range("B2:K2"), Cells(r.Row + r.Rows.Count - 1, 2)).Offset(1, 0).ClearContents」は、タイトルを除いた「データの範囲のみをクリア」しています。
まず「Sheet1.Range(Range("B2:K2"), Cells(r.Row + r.Rows.Count - 1, 2))」は、行位置を表している「Range("B2:K2")」がタイトル行で、列位置を表している「Cells(r.Row + r.Rows.Count - 1, 2)」が一番したのセル位置ですので、それを外枠とする範囲（図７－２１）となります。

図７－２１

その範囲を「.Offset(1, 0)」で、図７－２２のように１行下に移動させ、その範囲をクリア（.ClearContents）します。
クリアする範囲の一番下は「もともと空行」ですが、数式が長くなるので許容させています。

図７－２２

５５８行目「Sheet1.Range("C1") = Me.TextBox4.Value」は、C1セルにTextBox4（表示年）の値を書き込んでいます。
５６０行目「buf = SQL_exec5(SQLword("Asc"))」は、図７－７のSQLwordを呼出し、SQL文を取得します。引数に「Asc」を指定していますので、「昇順のデータ配列」が戻され、変数bufに代入します。
５６１行目「Set r = Sheet1.Range("B3").Resize(UBound(buf, 1) + 1, UBound(buf, 2))」では、タイトル行のすぐ下（B3セル）を基準にして、配列bufのサイズに合うようにデータ貼り付け範囲を設定します。行方向を「＋１」しているのは、データ配列bufのゼロ行目には、タイトルが含まれている為です。
５６２行目「r = buf」で、データ配列を５６１行目で定めたセル範囲r に貼り付けます。
５６３行目「r.Rows(1).Delete」で、データ貼付け範囲の１行目（タイトル行）を削除します。「r.Rows(1)」ですので、行削除では無く、範囲内の行削除で「データが上に持ち上がる」ことになります。

７－２－６－５．終了ボタン

「終了」ボタンをクリックした時に呼び出されるのが図７－２３です。５７２行目「Unload Me」で、フォームを閉じます。

570. '==========　⇩(29)　終了ボタン　============
571. Private Sub CommandButton5_Click()
572. 　Unload Me
573. End Sub

図７－２３

７－２－６－６．入力部のチェック

図７－１６の４７５行目から呼び出される「入力が正しく行われているか」をチェックするのが図７－２４です。

580. '==========　⇩(30)　入力部のチェック　============
581. Private Function isMissingInput() As Boolean
582. 　If Me.ComboBox1.ListIndex = -1 Or Me.ComboBox2.ListIndex = -1 Or _
583. 　　　　Trim(Me.TextBox1.Value) = "" Or Trim(Me.TextBox3.Value) = "" Or _
584. 　　　　Not IsDate(Me.TextBox2.Value) Then
585. 　　isMissingInput = True
586. 　　MsgBox "伝票、日付、取引先、商品名、個数のどれかが無効です"
587. 　End If
588. End Function

図７－２４

５８２～５８４行目のIF文で、２ヶ所のコンボボックス＋３ヶ所のテキストボックスの内容をチェックしています。
５８２行目「If Me.ComboBox1.ListIndex = -1 Or Me.ComboBox2.ListIndex = -1 Or _」では、取引先用コンボボックス（ComboBox1）、商品用コンボボックス（ComboBox2）が選択されているか（選択されていなければListIndex = -1）をチェックします。
５８３行目「Trim(Me.TextBox1.Value) = "" Or Trim(Me.TextBox3.Value) = "" Or _」では、伝票番号（TextBox1）、個数（TextBox3）が空で無いかをチェックしています。スペースだけ入れられても困るので、Trim関数でスペース削除しています。
５８４行目「Not IsDate(Me.TextBox2.Value) Then」では、売上日付（TextBox2）の値が日付になっているかをチェックします。

If文の５つの入力部の「どれか１ヶ所でもNG」だった場合は、５８５行目「isMissingInput = True」でこの関数の戻り値をTrue（＝エラー有り）にし、５８６行目「MsgBox "伝票、日付、取引先、商品名、個数のどれかが無効です"」でコメントを出しています。
５ヶ所の内のどこが違っているかをコメントする事も可能ですが、今回は一括チェックとしました。
なお、エラーが無い場合の戻り値は、Boolean型の初期値のFalseとなります。

８．取引先ダイアログ（UserForm2）

８－１．コントロールの配置

取引先データを操作するダイアログがUserForm2になり、そのフォーム上のコントロール類の配置は図８－１の様にしました。

図８－１

操作を実行するCommandButtonをフォーム下部に３つ並べます。また中央部にデータ一覧を表示するListBox1を配置します。
ダイアログ上部に入力の為のTextBoxを２つと、CheckBoxを配置します。

８－２．フォームモジュール（UserForm2）

フォームモジュール先頭で、モジュール内で使用するフラグ変数を宣言します。

590. '==========　⇩(31)　宣言部　============
591. Dim EventOff As Boolean

図８－２

このフォームのリストボックスを選択した時には、その値を入力部であるテキストボックス等にリストの値を転記します。しかし、そのテキストボックスには「変更があった時にはリストボックスを操作する」コードが記されているため、おかしな動きをすることになってしまいます。
そこで５９１行目「Dim EventOff As Boolean」で宣言するフラグ変数「EventOff」を使って、一時的にテキストボックスのコードが実行されないようにしています。

８－２－１．起動時準備

フォーム起動時には、図８－３のInitializeイベントが発生します。ここではコントロール類の静的な設定をします。

600. '==========　⇩(32)　フォーム起動時　============
601. Private Sub UserForm_Initialize()
602. 　Me.TextBox1.IMEMode = fmIMEModeOff
604. 　Me.ListBox1.ColumnCount = 3
605. 　Me.ListBox1.ColumnWidths = "50;120;15"
606. 　Me.ListBox1.TextAlign = fmTextAlignLeft
608. End Sub

図８－３

６０２行目「Me.TextBox1.IMEMode = fmIMEModeOff」は、取引先番号欄（TextBox1）を編集する際には、一旦IMEをOffにしています。全角を番号に使う事はほとんど無いと考えたためです。

６０４行目「Me.ListBox1.ColumnCount = 3」は、取引先一覧を表示するリストボックスを３列（取引先番号、取引先名、非表示有無）の指定にしています。
６０５行目「Me.ListBox1.ColumnWidths = "50;120;15"」では、各列の列幅を設定しています。
６０６行目「Me.ListBox1.TextAlign = fmTextAlignLeft」では、リストの各列を「左寄せ」で表示させます。

なお数値等を自然に見せるため、リストを「右寄せ」にする場合もあると思います。その際には６０６行目を「Me.ListBox1.TextAlign = fmTextAlignRight」と設定するのですが、注意する点があります。 右寄せをすると、全ての列の値が右寄せになり、且つ最も右側の列の値はリストボックスの右端に貼り付くように表示されます。表示行数が少ない場合は問題ないのですが、行数が増えて縦移動用のスクロールバーが表示されると、図８－４の中央のように「縦スクロールバーで際右列の値が隠れてしまう」現象が起きます。今回の様に１文字（レ点）表示の列だった場合は、全く表示している意味がありません。 図８－４ そこでリストを右寄せ表示する場合は、ダミー列（今回ならば４列目）を作り「縦スクロールバーが現れても、隠れるのはダミー列」という状態にすると、情報が欠落せずに済みます。

ダイアログが表示された後に実行されるのが図８－５のActivateイベントです。
６１２行目「Call makeListBox」で図８－６を呼出し、取引先一覧をリストボックスに表示させます。

610. '==========　⇩(33)　フォーム表示時　============
611. Private Sub UserForm_Activate()
612. 　Call makeListBox
613. End Sub

図８－５

８－２－２．リストボックス作成

図８－５の６１２行目、及び図８－１０の７１１行目、図８－１１の７５０行目から呼び出される「リストボックス上に取引先一覧を作成」するのが図８－６です。

620. '==========　⇩(34)　リストボックス作成　============
621. Private Sub makeListBox()
622. 　Dim sql As String 　 '←SQL文
623. 　Dim i As Long 　　　 '←カウンタ変数(行数)
624. 　Dim buf As Variant 　 '←取引先データ配列
626. 　Me.ListBox1.Clear
628. 　sql = "SELECT CNo,Cname,IIF(Del=True,'レ','') FROM " & TBL1 & _
629. 　　　　" order by CNo"
631. 　buf = SQL_exec5(sql)
633. 　For i = 1 To UBound(buf, 1)
634. 　　Me.ListBox1.AddItem ""
635. 　　Me.ListBox1.List(i - 1, 0) = buf(i, 1)
636. 　　Me.ListBox1.List(i - 1, 1) = buf(i, 2)
637. 　　Me.ListBox1.List(i - 1, 2) = buf(i, 3)
638. 　Next i
640. End Sub

図８－６

６２６行目「Me.ListBox1.Clear」では、一旦リストボックスのデータをクリアします。
６２８～６２９行目「sql = "SELECT CNo,Cname,IIF(Del=True,'レ','') FROM " & TBL1 & " order by CNo"」では、取引先データをTBL1（Custテーブル）から取得するSQL文を作成します。取り出す列は全３列の「CNo」「Cname」「Del」ですが、Del列はBoolean型なので「True（非表示）の時はレ点、Falseの時は空文字」に変換しています。
なお、VBAでは文字列を囲む時は「"（ダブルクォーテーション）」を使いますが、SQL文では「'（シングルクォーテーション）」を使います。
また並び順は「order by CNo」と正逆を指定していない為「既定のAsc」を指定した事となり、取引先番号の昇順で取り出します。

６３１行目「buf = SQL_exec5(sql)」で、図６－１６を呼出してSQL文を実行し、戻り値として取引先データの配列をbufで受け取ります。
６３３行目「For i = 1 To UBound(buf, 1)」では、受け取った配列の行数分だけカウンタ変数ｉを回します。
６３４行目「Me.ListBox1.AddItem ""」でリストボックスに新たな行を作成し、６３５行目「Me.ListBox1.List(i - 1, 0) = buf(i, 1)」でリストボックスの１列目（インデックス＝０）に配列の１列目データを書き込みます。以下同様に２列目・３列目のデータをリストボックスの２列目・３列目に書き込みます。

８－２－３．入力部の処理

取引先番号欄に入力を行った時には図８－７が呼び出されます。

650. '==========　⇩(35)　取引先番号　============
651. Private Sub TextBox1_Change()
652. 　If EventOff = True Then Exit Sub
654. 　TextBox1.Value = Trim(UCase(TextBox1.Value))
655. 　Me.ListBox1.ListIndex = -1
657. End Sub

図８－７

この取引先番号欄に値が入るのは、図８－８のように「ユーザーが手入力」した場合、「リストボックスの項目を選択した結果、既存の取引先番号が転記」された場合、「その転記された取引先番号をユーザーが手直し」した場合の３つが考えられます。

	入力の方法	入力値の意味	処理
1	ユーザーが手入力	新たな番号	ｱﾙﾌｧﾍﾞｯﾄ等を大文字揃え
2	リストボックスを選択	既存の番号	ﾘｽﾄは選択状態
3	転記された番号を修正	新番号に修正	新番号の為､ﾘｽﾄ選択を解除

図８－８

６５２行目「If EventOff = True Then Exit Sub」では、フラグ変数EventOffがTrueの時には以下を実行せずに抜け出します。これは、図８－８のNo.2に相当します。
６５４行目「TextBox1.Value = Trim(UCase(TextBox1.Value))」では、Ucase関数で「大文字揃え」にしています。これは、図８－８のNo.1、及びNo.3に相当します。加えてTrim関数で両端のスペースを削除しています。
スペース削除処理は、登録直前の図８－１４（isMissingInputプロシージャ）で行っても良いのですが「真ん中にスペースが入る取引先番号はあり得ない」だろうとの判断から、入力の都度チェックが入るTextBoxのChangeイベントで処理を行うことにしました。他方、TextBox2の取引先名の方は「スペースを真ん中に含んだ名前もあり得る」と思い、図８－１４側でTrim処理をしています。
６５５行目「Me.ListBox1.ListIndex = -1」では、リストボックスを非選択状態にしています。これは、図８－８のNo.3に相当します。

８－２－４．リストボックスの選択

リストボックスの項目をユーザーが選択した場合には、図８－９が呼び出されます。

660. '==========　⇩(36)　リストボックスの選択　============
661. Private Sub ListBox1_Click()
662. 　EventOff = True
663. 　　With Me.ListBox1
664. 　　　Me.TextBox1.Value = .List(.ListIndex, 0)
665. 　　　Me.TextBox2.Value = .List(.ListIndex, 1)
666. 　　　Me.CheckBox1.Value = IIf(.List(.ListIndex, 2) = "レ", True, False)
667. 　　End With
668. 　EventOff = False
669. End Sub

図８－９

６６３行目「With Me.ListBox1」では、以下のコードをListBox1ベースで実行していきます。
６６４行目「Me.TextBox1.Value = .List(.ListIndex, 0)」で、リストボックスの選択している行の１列目（インデックス＝０）の値をTextBox1（取引先番号）へ書き込みます。
６６５行目「Me.TextBox2.Value = .List(.ListIndex, 1)」で、２列目（インデックス＝１）の値をTextBox2（取引先名）へ書き込みます。
６６６行目「Me.CheckBox1.Value = IIf(.List(.ListIndex, 2) = "レ", True, False)」で、３列目（インデックス＝２）の値を確認し、もしレ点が付いていればCheckBox1のValueプロパティをTrueにします。逆にレ点が付いていない（＝値が長さゼロの文字列）時はFalseにします。
このCheckBoxの値は、CustテーブルのDel列の値と同じになります。
（Del列がTrue ＝ 非表示 ＝ リスト上でレ点有り ＝ CheckBoxでレ点有り ＝ CheckBoxのValue値がTrue）

なお６６４行目を実行すると、TextBox1のChangeイベント（図８－７）が呼び出され、その中の６５５行目「Me.ListBox1.ListIndex = -1」でリストボックスが非選択状態になってしまいます。ですので６６２行目「EventOff = True」でフラグ変数EventOffをTrueにしています。

８－２－５．データ処理

８－２－５－１．登録／更新ボタン

「登録／更新」ボタンをクリックした時に呼び出されるのが、図８－１０です。

680. '==========　⇩(37)　登録／更新ボタン　============
681. Private Sub CommandButton1_Click()
682. 　Dim sql As String 　　 '←SQL文
683. 　Dim buf As Variant 　　 '←Select文の戻り値の配列
684. 　Dim Ans As VbMsgBoxResult 　　 '←MsgBoxのユーザーの回答
686. 　If isMissingInput Then Exit Sub 　　 '←入力部の入力有無確認
688. 　sql = "SELECT count(*) FROM " & TBL1 & _
689. 　　　　" where CNo = '" & Me.TextBox1.Value & "'"
690. 　buf = SQL_exec5(sql)
692. 　If buf(1, 1) = 0 Then 　　 '←Custテーブルには同じ取引先番号が無い
693. 　　sql = "Insert into " & TBL1 & " (Cno,Cname,del)" & _
694. 　　　　　" values('" & Me.TextBox1.Value & "','" & _
695. 　　　　　Me.TextBox2.Value & "'," & _
696. 　　　　　Me.CheckBox1.Value & ")"
698. 　Else 　　 '←Custテーブル内に同じ取引先番号が有る
699. 　　If vbNo = MsgBox("取引先場号が既に存在します。上書きしますか？", vbYesNo) _
700. 　　　　Then Exit Sub 　　 '←ユーザーが上書きを拒否
702. 　　sql = "Update " & TBL1 & _
703. 　　　　　" set Cname='" & Me.TextBox2.Value & "'," & _
704. 　　　　　"del = " & Me.CheckBox1.Value & _
705. 　　　　　" where CNo= '" & Me.TextBox1.Value & "'"
707. 　End If
709. 　Call SQL_exec5(sql)
711. 　Call makeListBox
712. End Sub

図８－１０

６８６行目「If isMissingInput Then Exit Sub」は図８－１４を呼出し、「入力部（TextBox1とTextBox2）に正しく入力されているか」を確認します。戻り値として「入力OK＝False、NG＝True」が返ってきますので、NGだった場合は「Exit Sub」で処理を中止します。

６８８～６８９行目「sql = "SELECT count(*) FROM " & TBL1 & " where CNo = '" & Me.TextBox1.Value & "'"」は、TextBox1に入力された取引先番号が、TBL1（Custテーブル）内にいくつあるかを求めるSQL文です。
CustテーブルのCNo列はPrimary Keyですので、その取引先番号があったら「１」、無ければ「ゼロ」が得られるはずです。
６９０行目「buf = SQL_exec5(sql)」で、上記のSQL文を実行し、戻り値の配列を変数bufに代入します。

bufは配列です。今回のSQL文の取得する列は「count(*)」で、値は１レコード（1 または 0）ですので、求める値はbuf(1, 1)に入っている事になります。
６９２行目「If buf(1, 1) = 0 Then」でその値を調べ、ゼロだった場合（同じ取引先番号が無い）には６９３～６９６行目を実行、１だった場合（同じ取引先番号が有る）には６９９～７０５行目を実行します。

６９３～６９６行目は、同じ取引先番号が無い場合にレコードを追加するInsert文です。
６９３行目「sql = "Insert into " & TBL1 & " (CNo,Cname,del)" & _」では、TBL1（Custテーブル）のCNo列、Cname列、Del列の順番に値を追加します。値は、以下のvalues()のカッコ内に、指定した列の順番通りに指定していきます。
６９４行目「" values('" & Me.TextBox1.Value & "','" & _」は、CNo列にTextBox1（取引先番号）の値を入れます。
６９５行目「Me.TextBox2.Value & "'," & _」は、Cname列にTextBox2（取引先名）の値を入れます。
６９６行目「Me.CheckBox1.Value & ")"」は、Del列にCheckBox（非表示）の値を入れます。
なおDel列のデータ型はBIT型（VBAだとBoolean型）なので、既定値はFalseとなります。もしレ点が無い（＝False）ことが分かっていれば、あえて値をInsertしなくても値は「False」となります。今回はCheckBoxでユーザー指定できるようにしたため、Del列にも値を指定しています。

同じ取引先番号が有る場合は、６９９～７００行目「If vbNo = MsgBox("取引先場号が既に存在します。上書きしますか？", vbYesNo) Then Exit Sub」で、上書きするか否かのメッセージボックスを表示し、その回答が「いいえ（上書きしない）」だった場合に、処理を中止しています。
「はい（上書きする）」を選択した場合は、７０２～７０５行目で「TextBox1の取引先番号のレコード」を更新するUpdate文を作成します。
７０２行目「sql = "Update " & TBL1 & _」で、TBL1（Custテーブル）に対してデータの更新をします。
７０３行目「" set Cname='" & Me.TextBox2.Value & "'," & _」は、Cname列の値をTextBox2の値に書き換えます。
７０４行目「"del = " & Me.CheckBox1.Value & _」は、Del列の値をCheckBox1のValue値（True 又は False）に書き換えます。
７０５行目「" where CNo= '" & Me.TextBox1.Value & "'"」は更新の対象を、CNo列がTextBox1の値のレコードに絞っています。CNo列はPrimary Keyですので、書き換えられるのは１レコードのみになります。

SQL文が完成しましたので、７０９行目「Call SQL_exec5(sql)」で図６－１６を呼出し、SQL文を実行します。
テーブルを書き換えた後は、７１１行目「Call makeListBox」で図８－６を呼び出し、リストボックスを更新します。

８－２－５－２．削除ボタン

「削除」ボタンをクリックした時に呼び出されるのが図８－１１です。

720. '==========　⇩(38)　削除ボタン　============
721. Private Sub CommandButton2_Click()
722. 　Dim sql As String 　　　 '←SQL文
723. 　Dim buf As Variant 　　 '←Select文の戻り値の配列
724. 　Dim Ans As VbMsgBoxResult 　　 '←MsgBoxのユーザーの回答
726. 　If Me.ListBox1.ListIndex = -1 Then
727. 　　MsgBox "リストから削除する項目を選択して下さい。"
728. 　　Exit Sub
729. 　End If
731. 　Ans = MsgBox("選択項目を削除して良いですか？", vbYesNo)
732. 　If Ans = vbNo Then Exit Sub 　　 '←ユーザーが削除を拒否
734. 　sql = "SELECT count(*) FROM " & TBL0 & _
735. 　　　　" where CustNo = '" & Me.ListBox1.List(Me.ListBox1.ListIndex, 0) & "'"
736. 　buf = SQL_exec5(sql)
738. 　If buf(1, 1) = 0 Then 　　 '←売上台帳に使用されていない場合
739. 　　sql = "Delete from " & TBL1 & _
740. 　　　　　" where CNo= '" & Me.ListBox1.List(Me.ListBox1.ListIndex, 0) & "'"
741. 　Else 　　 '←売上台帳に使用されている場合
742. 　　sql = "Update " & TBL1 & _
743. 　　　　　" set Del= true " & _
744. 　　　　　" where CNo= '" & Me.ListBox1.List(Me.ListBox1.ListIndex, 0) & "'"
745. 　　MsgBox "データが他のテーブルで使用されている為、非表示扱いにします。"
746. 　End If
748. 　Call SQL_exec5(sql)
750. 　Call makeListBox
751. End Sub

図８－１１

項目を削除するには、その削除対象が明確である必要であり、今回は「リスト上で選択した項目」を削除対象としています。
７２６～７２９行目では、その削除対象が選択されているかを確認しています。
７２６行目「If Me.ListBox1.ListIndex = -1 Then」でリストボックスが選択されていない（ListIndex = -1 ）場合は、７２７行目「MsgBox "リストから削除する項目を選択して下さい。"」でメッセージを出し、７２８行目「Exit Sub」で処理を中止します。

リストボックスが選択されている場合は、７３１行目「Ans = MsgBox("選択項目を削除して良いですか？", vbYesNo)」でユーザーへ再確認を行い、「いいえ（＝削除しない）」を選んだ場合は７３２行目「If Ans = vbNo Then Exit Sub」で処理を中止します。

削除OKの場合、まず「削除しようとしている項目が、他のテーブル（Saleテーブル：TBL0）で使われていないか」を調べる必要があります。無理に削除しようとすると図８－１２のようなエラーが発生します。

図８－１２

参照制約で引っ掛かった時のエラー番号は図８－１２の通り「-2147467259」です。このエラー番号を使い「構わずにDeleteを実行し、参照制約違反のエラー番号が出たら改めてUpdateする」という手法を当初考えていました。しかし調べてみると、このエラー番号は参照制約違反「専用」のものでは無さそうでした。 ですので、図６－１６（SQL_exec5関数プロシージャ）の中ではエラー番号を取得し呼出し元に戻すことはしませんでした。 データベースに接続出来なかったり、制約を違反したりした際のエラー番号を上手く使えれば、もう少しカッコ良い方法でSQLが実行できそうなのですが・・・　エラー種類については、もう少し調べる必要がありそうです。

７３４～７３５行目「sql = "SELECT count(*) FROM " & TBL0 & " where CustNo = '" & Me.ListBox1.List(Me.ListBox1.ListIndex, 0) & "'"」は、削除しようとしている取引先番号が、TBL0（Saleテーブル）のCustNo列の中に何レコード使われているかを取得するSQL文です。
SQL文は７３６行目「buf = SQL_exec5(sql)」で実行され、そのレコード数は変数bufに代入されます。

７３８行目「If buf(1, 1) = 0 Then」は、「使用しているレコード数」を調べた結果ゼロ（＝使用されていない）の場合は「レコードを削除するSQL文（Delete文）」を作成します。それ以外（＝使用されている）の場合は、削除する訳にはいかないので「選択リストには表示しない」状態にするため「Del列をTrueに変更するSQL文（Update文）」を作成します。

まずDelete文は、７３９行目「sql = "Delete from " & TBL1 & _」で TBL1（Custテーブル）に対して削除を行い、７４０行目「" where CNo= '" & Me.ListBox1.List(Me.ListBox1.ListIndex, 0) & "'"」で、その削除対象のCNoを「リスト選択している先頭列の取引先番号」に絞り込みます。CustテーブルではCNoはPrimary Keyですので、削除される対象は１レコードとなります。

次にUpdate文は、７４２行目「sql = "Update " & TBL1 & _」で TBL1（Custテーブル）に対して変更を行い、７４３行目「" set Del= true " & _」でDel列をTrueに設定します。その変更の対象は７４４行目「" where CNo= '" & Me.ListBox1.List(Me.ListBox1.ListIndex, 0) & "'"」でCNo列がリストボックスで選択している取引先番号となります。
なお、ユーザーにしてみれば「削除を指定したのに、システム側が勝手にデータ変更」するのですから、ユーザーに対して７４５行目「MsgBox "データが他のテーブルで使用されている為、非表示扱いにします。"」で、理由と対応内容について伝えます。

そして７４８行目「Call SQL_exec5(sql)」で図６－１６を呼び出し、上記で作成したSQL文を実行します。
テーブル内容が変更されたので、７５０行目「Call makeListBox」で図８－６を呼出し、リストボックスを更新します。

８－２－５－３．終了ボタン

「終了」ボタンをクリックした時には図８－１３が呼び出され、７６２行目「Unload Me」でフォームが閉じられます。

760. '==========　⇩(39)　終了ボタン　============
761. Private Sub CommandButton3_Click()
762. 　Unload Me
763. End Sub

図８－１３

８－２－６．入力部のチェック

図８－１０の６８６行目から呼び出される「入力部のチェック」をするのが図８－１４です。入力ミスがある場合はTrueを、OKな場合はFalseを戻します。

770. '==========　⇩(40)　入力部のチェック　============
771. Private Function isMissingInput()
772. 　Me.TextBox2.Value = Trim(Me.TextBox2.Value)
774. 　If Me.TextBox1.Value = "" Or Me.TextBox2.Value = "" Then
775. 　　isMissingInput = True
776. 　　MsgBox "取引先No 又は 取引先名が空です"
777. 　End If
778. End Function

図８－１４

今回の場合、入力部は「取引先番号」「取引先名」「非表示有無」の３ヶ所で、必要な処理・確認は図８－１５の様になります。

ﾁｪｯｸ項目	大文字化	→		両端ｽﾍﾟｰｽ削除	→		値の有無
取引先番号	図８－７		図８－７			(７７４行目)
取引先名	文字種自由		(７７２行目)			(７７４行目)
非表示有無	－		－			(True or False)

図８－１５

「取引先番号（TextBox1）」は、値が入力された時に図８－７のTextBox1_Changeが呼び出されるため、その中で「両端スペースの削除」「大文字化」が行われています。残りの「値が入力されているか否か」をここで確認する必要があります。
また「取引先名」は、ここまで何のチェックもしていないので、「両端スペースの削除」「値が入力されているか否か」を確認します。
また「非表示有無」はTrueかFalse（既定値）のどちらかしかありませんので、チェック対象外となります。

７７２行目「Me.TextBox2.Value = Trim(Me.TextBox2.Value)」では、入力した取引先名の両端のスペースを削除し、TextBox2に再記入します。
７７４行目「If Me.TextBox1.Value = "" Or Me.TextBox2.Value = "" Then」では、取引先番号（TextBox1）と取引先名（TextBox2）が空か否かを確認しています。

不足（実質的な文字列が入力されていない）があった場合には、７７５行目「isMissingInput = True」で、この関数プロシージャの戻り値をTrueにし、７７６行目「MsgBox "取引先No 又は 取引先名が空です"」で、どの部分でエラーが出ているかをコメントします。

９．商品ダイアログ（UserForm3）

９－１．コントロールの配置

商品ダイアログは、取引先ダイアログとほぼ同じです。異なる点は対象テーブルが異なることと入力項目数が多いこと位です。
商品データを操作するダイアログがUserForm3になり、そのフォーム上のコントロール類の配置は図９－１の様にしました。

図９－１

操作を実行するCommandButtonをフォーム下部に３つ並べます。また中央部にデータ一覧を表示するListBox1を配置します。
ダイアログ上部に入力の為のTextBoxを３つと、CheckBoxを配置します。

９－２．フォームモジュール（UserForm3）

今回システムでは、リストボックス選択時に入力用テキストボックス等に値を転記しますが、その時にテキストボックス側のChangeイベントをスルーさせるためのフラグ変数を宣言します（図９－２の７８１行目）。

780. '==========　⇩(41)　宣言部　============
781. Dim EventOff As Boolean

図９－２

９－２－１．起動時準備

フォーム起動時には、図９－３のInitializeイベントが発生します。ここではコントロールの静的な設定をします。

790. '==========　⇩(42)　フォーム起動時　============
791. Private Sub UserForm_Initialize()
792. 　Me.TextBox1.IMEMode = fmIMEModeOff
793. 　Me.TextBox3.IMEMode = fmIMEModeDisable
795. 　Me.ListBox1.ColumnCount = 4
796. 　Me.ListBox1.ColumnWidths = "50;80;30;15"
797. 　Me.ListBox1.TextAlign = fmTextAlignLeft
798. End Sub

図９－３

７９２行目「Me.TextBox1.IMEMode = fmIMEModeOff」は、商品番号欄を編集する際に一旦IMEをOffにしています。全角を番号に使う事はほとんど無いと考えたためです。
７９３行目「Me.TextBox3.IMEMode = fmIMEModeDisable」は、単価欄は数値ですので半角のみになるようにIMEを切っています。

７９５行目「Me.ListBox1.ColumnCount = 4」は、商品一覧を表示するリストボックスを４列（商品番号、商品名、単価、非表示有無）の指定にしています。
７９６行目「Me.ListBox1.ColumnWidths = "50;80;30;15"」では、各列の列幅を設定しています。
７９７行目「Me.ListBox1.TextAlign = fmTextAlignLeft」では、リストの各列を「左寄せ」で表示させます。

ダイアログが表示された後に呼び出されるのが図９－４のActivateイベントです。
８０２行目「Call makeListBox」で図９－５を呼出し、商品一覧をリストボックスに表示させます。

800. '==========　⇩(43)　フォーム表示時　============
801. Private Sub UserForm_Activate()
802. 　Call makeListBox
803. End Sub

図９－４

９－２－２．リストボックス作成

図９－４の８０２行目、及び図９－９の９１１行目、図９－１０の９５０行目から呼び出される「リストボックス上に商品一覧を作成」するのが図９－５です。

810. '==========　⇩(44)　リストボックス作成　============
811. Private Sub makeListBox()
812. 　Dim sql As String 　 '←SQL文
813. 　Dim i As Long 　　　 '←カウンタ変数(行数)
814. 　Dim buf As Variant 　 '←商品データ配列
816. 　Me.ListBox1.Clear
818. 　sql = "SELECT PNo,Pname,Uprice,IIF(Del=True,'レ','') FROM " & TBL2 & _
819. 　　　　" order by PNo"
820. 　buf = SQL_exec5(sql)
822. 　For i = 1 To UBound(buf, 1)
823. 　　Me.ListBox1.AddItem ""
824. 　　Me.ListBox1.List(i - 1, 0) = buf(i, 1)
825. 　　Me.ListBox1.List(i - 1, 1) = buf(i, 2)
826. 　　Me.ListBox1.List(i - 1, 2) = buf(i, 3)
827. 　　Me.ListBox1.List(i - 1, 3) = buf(i, 4)
828. 　Next i
830. End Sub

図９－５

８１６行目「Me.ListBox1.Clear」では、一旦リストボックスのデータをクリアします。
８１８～８１９行目「sql = "SELECT PNo,Pname,Uprice,IIF(Del=True,'レ','') FROM " & TBL2 & " order by PNo"」では、商品データをTBL2（Pdctテーブル）から取得するSQL文を作成します。取り出す列は全４列の「PNo」「Pname」「Uprice」「Del」ですが、Del列はBoolean型なので「True（非表示）の時はレ点、Falseの時は空文字」に変換しています。
また並び順は「order by PNo」と正逆を指定していない為「既定のAsc」を指定した事となり、商品番号の昇順で取り出します。

８２０行目「buf = SQL_exec5(sql)」で、図６－１６を呼出してSQL文を実行し、戻り値として商品データの配列をbufで受け取ります。
８２２行目「For i = 1 To UBound(buf, 1)」では、受け取った配列の行数分だけカウンタ変数ｉを回します。
８２３行目「Me.ListBox1.AddItem ""」でリストボックスに新たな行を作成し、８２４行目「Me.ListBox1.List(i - 1, 0) = buf(i, 1)」でリストボックスの１列目（インデックス＝０）に配列の１列目データを書き込みます。以下同様に２列目・３列目・４列目のデータをリストボックスの２列目・３列目・４列目に書き込みます。

９－２－３．入力部の処理

商品番号欄に入力を行った時には図９－６が呼び出されます。

840. '==========　⇩(45)　商品番号の入力時　============
841. Private Sub TextBox1_Change()
842. 　If EventOff = True Then Exit Sub
844. 　TextBox1.Value = Trim(UCase(TextBox1.Value))
845. 　Me.ListBox1.ListIndex = -1
846. End Sub

図９－６

８４２行目「If EventOff = True Then Exit Sub」では、フラグ変数EventOffがTrueの時には以下を実行せずに抜け出します。フラグ変数EventOffがTrueになるのは、ユーザーがリストボックスを選択した結果として、商品番号がTextBoxに転記される場合ですので、その時には何も処理をしないことになります。
８４４行目「TextBox1.Value = Trim(UCase(TextBox1.Value))」では、「大文字揃え（Ucase関数）」＋「両端のスペースを削除（Trim関数）」の処理をしています。これは、ユーザーが手入力した時に、商品番号として「文字間にスペースを入れさせない」ためです。
８４５行目「Me.ListBox1.ListIndex = -1」では、リストボックスを非選択状態にします。最初から手入力をした時はもちろん、リストを選択した後で商品番号を変更するという行為は「新たな商品番号を入力しようとしている」ので、リストの選択状態を外しています。

単価の入力欄であるTextBox3内でキー入力した時に呼び出されるのが図９－７です。

850. '==========　⇩(46)　単価の入力時　============
851. Private Sub TextBox3_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
852. 　If Not Chr(KeyAscii) Like "[0-9]" Then KeyAscii = 0
853. End Sub

図９－７

単価は「数値のみ」なので、８５２行目「If Not Chr(KeyAscii) Like "[0-9]" Then KeyAscii = 0」で数字以外を無視します。

なお、このTextBox3に「コピペでアルファベットや全角文字を貼り付ける」と、KeyPressイベントを通過しないために入力が出来てしまいます。そしてそのまま「登録/更新」をクリックされると、Uprice列はLong型なので「実行時エラーが発生」しマクロが停止してしまいます。
これを避けるには、TextBox3_Changeイベントで入力された値を解析し、数値のみにするなどの処理が必要になります。今回は性善説に立ってKeyPressイベントのみの設定としましたので御了承下さい。

９－２－４．リストボックスの選択

リストボックスの項目をユーザーが選択した場合には、図９－８が呼び出されます。

860. '==========　⇩(47)　リストボックスの選択　============
861. Private Sub ListBox1_Click()
862. 　EventOff = True
863. 　　With Me.ListBox1
864. 　　　Me.TextBox1.Value = .List(.ListIndex, 0)
865. 　　　Me.TextBox2.Value = .List(.ListIndex, 1)
866. 　　　Me.TextBox3.Value = .List(.ListIndex, 2)
867. 　　　Me.CheckBox1.Value = IIf(.List(.ListIndex, 3) = "レ", True, False)
868. 　　End With
869. 　EventOff = False
870. End Sub

図９－８

８６３行目「With Me.ListBox1」では、以下のコードをListBox1ベースで実行していきます。
８６４行目「Me.TextBox1.Value = .List(.ListIndex, 0)」で、リストボックスの選択している行の１列目（インデックス＝０）の値をTextBox1（商品番号）へ書き込みます。
８６５行目「Me.TextBox2.Value = .List(.ListIndex, 1)」で、２列目の値をTextBox2（商品名）へ書込み、
８６６行目「Me.TextBox3.Value = .List(.ListIndex, 2)」で、３列目の値をTextBox3（単価）へ書き込みます。
８６７行目「Me.CheckBox1.Value = IIf(.List(.ListIndex, 3) = "レ", True, False)」で、４列目の値を確認し、もしレ点が付いていればCheckBox1のValue値をTrueにします。逆にレ点が付いていない（＝値が長さゼロの文字列）時はFalseにします。
このCheckBoxの値は、PdctテーブルのDel列の値と同じになります。

なお８６４行目を実行すると、TextBox1のChangeイベント（図９－６）が呼び出され、その中の８４５行目「Me.ListBox1.ListIndex = -1」でリストボックスが非選択状態になってしまいます。ですので８６２行目「EventOff = True」でフラグ変数EventOffをTrueにしています。

９－２－５．データ処理

９－２－５－１．登録／更新ボタン

「登録／更新」ボタンをクリックした時に呼び出されるのが、図９－９です。

880. '==========　⇩(48)　登録／更新ボタン　============
881. Private Sub CommandButton1_Click()
882. 　Dim sql As String 　　 '←SQL文
883. 　Dim buf As Variant 　　 '←Select文の戻り値の配列
884. 　Dim Ans As VbMsgBoxResult 　　 '←MsgBoxのユーザーの回答
886. 　If isMissingInput Then Exit Sub
888. 　sql = "SELECT count(*) FROM " & TBL2 & _
889. 　　　　" where PNo = '" & Me.TextBox1.Value & "'"
890. 　buf = SQL_exec5(sql)
892. 　If buf(1, 1) = 0 Then
893. 　　sql = "Insert into " & TBL2 & " (Pno,Pname,Uprice,del)" & _
894. 　　　　　" values('" & Me.TextBox1.Value & "','" & _
895. 　　　　　Me.TextBox2.Value & "'," & _
896. 　　　　　Me.TextBox3.Value & "," & _
897. 　　　　　Me.CheckBox1.Value & ")"
898. 　Else
899. 　　If vbNo = MsgBox("商品番号が既に存在します。上書きしますか？", vbYesNo) _
900. 　　　　　　　Then Exit Sub
902. 　　sql = "Update " & TBL2 & _
903. 　　　　　" set Pname='" & Me.TextBox2.Value & "'," & _
904. 　　　　　" Uprice = " & Me.TextBox3.Value & "," & _
905. 　　　　　" del = " & Me.CheckBox1.Value & _
906. 　　　　　" where PNo= '" & Me.TextBox1.Value & "'"
907. 　End If
909. 　Call SQL_exec5(sql)
911. 　Call makeListBox
912. End Sub

図９－９

８８６行目「If isMissingInput Then Exit Sub」は図９－１２を呼出し、「入力部（TextBox１～３）に正しく入力されているか」を確認します。戻り値として「入力OK＝False、NG＝True」が返ってきますので、NGだった場合は「Exit Sub」で処理を中止します。

８８８～８８９行目「sql = "SELECT count(*) FROM " & TBL2 & " where PNo = '" & Me.TextBox1.Value & "'"」は、TextBox1に入力された商品番号が、TBL2（Pdctテーブル）内にいくつあるかを求めるSQL文です。
PdctテーブルのPNo列はPrimary Keyですので、その商品番号があったら「１」、無ければ「ゼロ」が得られるはずです。
８９０行目「buf = SQL_exec5(sql)」で、上記のSQL文を実行し、戻り値の配列を変数bufに代入します。

bufは配列で、今回のSQL文の取得する列は「count(*)」で、値は１レコード（1 または 0）ですので、求める値はbuf(1, 1)に入っています。
８９２行目「If buf(1, 1) = 0 Then」でその値を調べ、ゼロだった場合（同じ商品番号が無い）には８９３～８９７行目を実行し、１だった場合（同じ商品番号が有る）には８９９～９０６行目を実行します。

８９３～８９７行目は、同じ商品番号が無い場合にレコードを追加するInsert文です。
８９３行目「sql = "Insert into " & TBL2 & " (PNo,Pname,Uprice,del)" & _」では、TBL2（Pdctテーブル）のPNo列、Pname列、Uprice列、Del列の順番に値を追加します。値は、以下のvalues()のカッコ内に、指定した列の順番通りに指定していきます。
８９４行目「" values('" & Me.TextBox1.Value & "','" & _」は、PNo列にTextBox1（商品番号）の値を入れます。
８９５行目「Me.TextBox2.Value & "'," & _」は、Pname列にTextBox2（商品名）の値を入れます。
８９６行目「Me.TextBox3.Value & "'," & _」は、Uprice列にTextBox3（単価）の値を入れます。
８９７行目「Me.CheckBox1.Value & ")"」は、Del列にCheckBox（非表示）の値を入れます。

同じ商品番号が有る場合は、８９９～９００行目「If vbNo = MsgBox("商品場号が既に存在します。上書きしますか？", vbYesNo) Then Exit Sub」で、上書きするか否かのメッセージボックスを表示し、その回答が「いいえ（上書きしない）」だった場合に、処理を中止しています。
「はい（上書きする）」を選択した場合は、９０２～９０６行目で「TextBox1の商品番号のレコード」を更新するUpdate文を作成します。
９０２行目「sql = "Update " & TBL2 & _」で、TBL2（Pdctテーブル）に対してデータの更新をします。
９０３行目「" set Pname='" & Me.TextBox2.Value & "'," & _」は、Pname列の値をTextBox2（商品名）の値に書換えます。
９０４行目「" Uprice = " & Me.TextBox3.Value & "," & _」は、Uprice列の値をTextBox3の値（単価）に書換えます。
９０５行目「"del = " & Me.CheckBox1.Value & _」は、Del列の値をCheckBox1（非表示）のValue値（True 又は False）に書換えます。
９０６行目「" where PNo= '" & Me.TextBox1.Value & "'"」は更新の対象が、PNo列がTextBox1（商品番号）の値のレコードに絞っています。PNo列はPrimary Keyですので、書き換えられるのは１レコードのみになります。

SQL文が完成しましたので、９０９行目「Call SQL_exec5(sql)」で図６－１６を呼出し、SQL文を実行します。
テーブルを書き換えた後は、９１１行目「Call makeListBox」で図９－５を呼び出し、リストボックスを更新します。

９－２－５－２．削除ボタン

「削除」ボタンをクリックした時に呼び出されるのが図９－１０です。

920. '==========　⇩(49)　削除ボタン　============
921. Private Sub CommandButton2_Click()
922. 　Dim sql As String 　　　 '←SQL文
923. 　Dim buf As Variant 　　 '←Select文の戻り値の配列
924. 　Dim Ans As VbMsgBoxResult 　　 '←MsgBoxのユーザーの回答
926. 　If Me.ListBox1.ListIndex = -1 Then
927. 　　MsgBox "リストから削除する項目を選択して下さい。"
928. 　　Exit Sub
929. 　End If
931. 　Ans = MsgBox("選択項目を削除して良いですか？", vbYesNo)
932. 　If Ans = vbNo Then Exit Sub
934. 　sql = "SELECT count(*) FROM " & TBL0 & _
935. 　　　　" where PdctNo = '" & Me.ListBox1.List(Me.ListBox1.ListIndex, 0) & "'"
936. 　buf = SQL_exec5(sql)
938. 　If buf(1, 1) = 0 Then 　　 '←売上台帳に使用されていない場合
939. 　　sql = "Delete from " & TBL2 & _
940. 　　　　　" where PNo= '" & Me.ListBox1.List(Me.ListBox1.ListIndex, 0) & "'"
941. 　Else 　　 '←売上台帳に使用されている場合
942. 　　sql = "Update " & TBL2 & _
943. 　　　　　" set Del= true " & _
944. 　　　　　" where PNo= '" & Me.ListBox1.List(Me.ListBox1.ListIndex, 0) & "'"
945. 　　MsgBox "データが他のテーブルで使用されている為、非表示扱いにします。"
946. 　End If
948. 　Call SQL_exec5(sql)
950. 　Call makeListBox
951. End Sub

図９－１０

項目を削除するには、その削除対象が明確である必要であり、今回は「リスト上で選択した項目」を削除対象としています。
９２６～９２９行目では、その削除対象が選択されているかを確認しています。
９２６行目「If Me.ListBox1.ListIndex = -1 Then」でリストボックスが選択されていない（ListIndex = -1 ）場合は、９２７行目「MsgBox "リストから削除する項目を選択して下さい。"」でメッセージを出し、９２８行目「Exit Sub」で処理を中止します。

リストボックスが選択されている場合は、９３１行目「Ans = MsgBox("選択項目を削除して良いですか？", vbYesNo)」でユーザーへ再確認を行い、「いいえ（＝削除しない）」を選んだ場合は９３２行目「If Ans = vbNo Then Exit Sub」で処理を中止します。

削除OKの場合、まず「削除しようとしている項目が、他のテーブル（Saleテーブル：TBL0）で使われていないか」を調べる必要があります。無理に削除しようとすると実行時エラーが発生します。
９３４～９３５行目「sql = "SELECT count(*) FROM " & TBL0 & " where PdctNo = '" & Me.ListBox1.List(Me.ListBox1.ListIndex, 0) & "'"」は、削除しようとしている商品番号が、TBL0（Saleテーブル）のPdctNo列の中に何レコード使われているかを取得するSQL文です。
SQL文は９３６行目「buf = SQL_exec5(sql)」で実行され、そのレコード数は変数bufに代入されます。

９３８行目「If buf(1, 1) = 0 Then」では「使用しているレコード数」を調べ、ゼロ（＝使用されていない）の場合は「レコードを削除するSQL文（Delete文）」を作成します。それ以外（＝使用されている）の場合は、削除する訳にはいかないので「選択リストには表示しない」状態にするため「Del列をTrueに変更するSQL文（Update文）」を作成します。

まずDelete文は、９３９行目「sql = "Delete from " & TBL2 & _」で TBL2（Pdctテーブル）に対して削除を行い、９４０行目「" where PNo= '" & Me.ListBox1.List(Me.ListBox1.ListIndex, 0) & "'"」で、その対象をPNoがリスト選択している先頭列の商品番号に絞り込みます。PdctテーブルではPNoはPrimary Keyですので、削除される対象は１レコードとなります。

次にUpdate文は、９４２行目「sql = "Update " & TBL2 & _」で TBL2（Pdctテーブル）に対して変更を行い、９４３行目「" set Del= true " & _」でDel列をTrueに設定します。その変更の対象は９４４行目「" where PNo= '" & Me.ListBox1.List(Me.ListBox1.ListIndex, 0) & "'"」でPNo列がリストボックスで選択している商品番号となります。
なおユーザーにしてみれば「削除を指定したのに、システム側が勝手にデータ変更」するのですから、ユーザーに対して９４５行目「MsgBox "データが他のテーブルで使用されている為、非表示扱いにします。"」で、理由と対応内容について伝えます。

そして９４８行目「Call SQL_exec5(sql)」で、図６－１６を呼び出し、上記で作成したSQL文を実行します。
テーブル内容が変更されたので、９５０行目「Call makeListBox」で図９－５を呼出し、リストボックスを更新します。

９－２－５－３．終了ボタン

「終了」ボタンをクリックした時には図９－１１が呼び出され、９６２行目「Unload Me」でフォームが閉じられます。

960. '==========　⇩(50)　終了ボタン　============
961. Private Sub CommandButton3_Click()
962. 　Unload Me
963. End Sub

図９－１１

９－２－６．入力部のチェック

図９－９の８８６行目から呼び出される「入力部のチェック」をするのが図９－１２です。入力ミスがある場合はTrueを、OKな場合はFalseを戻します。

970. '==========　⇩(51)　入力部のチェック　============
971. Private Function isMissingInput()
972. 　Me.TextBox2.Value = Trim(Me.TextBox2.Value)
974. 　If Me.TextBox1.Value = "" Or Me.TextBox2.Value = "" Or Me.TextBox3.Value = "" Then
975. 　　isMissingInput = True
976. 　　MsgBox "商品No、商品名、または単価が空です"
977. 　End If
978. End Function

図９－１２

９７２行目「Me.TextBox2.Value = Trim(Me.TextBox2.Value)」では、入力した商品名の両端のスペースを削除し、TextBox2に再記入します。
９７４行目「If Me.TextBox1.Value = "" Or Me.TextBox2.Value = "" Or Me.TextBox3.Value = "" Then」では、商品番号（TextBox1）、商品名（TextBox2）、単価（TextBox3）が空か否かを確認しています

不足（実質的な文字列が入力されていない）があった場合には、９７５行目「isMissingInput = True」で、この関数プロシージャの戻り値をTrueにし、９７６行目「MsgBox "商品先No、商品名、または単価が空です"」で、どの部分でエラーが出ているかをコメントします。

１０．最後に

Deleteが使用できれば相当楽になりそうだ、というのがプログラムを作る前の予想だったのですが、テーブル同士は参照テーブル等で結びつけられているため「簡単にはDelete出来ない」というのを改めて感じました。メインのテーブル（今回ならばSaleテーブル）ならば素直にDelete出来ますが、「よりみち」でも書きましたが、やはりエラー種類によって処理を分岐することを考えないと複雑なデータベースに対応するのは難しいのでは、という印象です。
データベースについて、まだまだ知識・経験が不足しているのを痛感しています。


Accessデータベースを使用した売上台帳（it-082.xlsm）
Accessサンプルデータ（it-082.accdb）

このit-082.xlsmを開くと、ファイルを保存した場所にit-082.accdbというデータベースファイルが自動的に作成されます。 また添付のit-082.accdbを使用すると、説明文の中に出てくるサンプルデータが使用できます。

セキュリティ向上を目的として「インターネット経由でダウンロードしたOfficeファイル（Excel等）のマクロは、既定でブロック」されるようにOfficeアプリケーションの既定動作が変更になりました。（2022年4月より切替開始） 解除の方法については「ダウンロードファイルのブロック解除方法」を参照下さい。