商品で得意先を絞り込んでリストボックスに表示させるお手本データベース
図2-3-1 商品で得意先を絞り込んでリストボックスに表示
このサンプルデータベースは、受注データから複数の商品を指定して得意先を絞り込んでリストボックスに表示します。検索条件として、受注日（範囲指定可能）、都道府県、商品を指定することができます。更に、検索条件に該当する得意先の表示件数を、上位からｎ件／n%のように制約することができます。商品は、選択可能アイテムのリストボックスからダブルクリックするか、＞ボタンをクリックして複数アイテム選択することができます。全アイテムを選択するときは、＞＞ボタンをクリックします。複数のアイテムを選択したときは、ＯＲ条件にて検索します。選択されたアイテムは、右側の選択されたアイテムのリストボックスに表示されます。選択したアイテムを戻すときは、＜ボタンをクリックします。＜＜ボタンをクリックすると、全てのアイテムを戻します。
フィルタボタンをクリックすると、検索条件と一致する得意先が売上高の降順に表示されます。リストボックスの得意先をダブルクリックすると右側のテキストボックスに、得意先の詳細情報が表示されます。
サンプルデータベース（CH2-3.mdb)では、以下のノウハウを習得することができます。
◆ リストボックスから複数アイテムを選択する方法
◆ リストボックスから選択したアイテムを戻す方法
◆ 都道府県のコンボボックスに(全国)を表示する方法
◆ 検索条件と一致したレコードを上位n件／n％指定で制限する方法
◆ リストボックスから得意先をダブルクリックしたとき詳細情報を表示する方法
◆ 複数の商品アイテムをAND条件で検索する方法
● サンプルデータベースに必要なテーブルを用意するには
１ Access を起動して作業フォルダに、新規データベースCH2-3.mdbを作成します。
２ ファイルメニューから外部データの取り込み、インポートをクリックします。インポートのダイアログが表示されたら、Access のサンプルデータベースNorthwind.mdbを選択してインポートボタンをクリックします。オブジェクトのインポートダイアログが表示されたら、テーブルタブをクリックして、受注、受注明細、商品、都道府県、得意先テーブルを選択します。OKボタンをクリックしてインポートを完了させます。
図2-3-2 Northwind.mdbから受注、受注明細、商品、都道府県、得意先テーブルをインポート
３ データベースウィンドウからテーブル
をクリックしたら、商品テーブルをデザインモードで開きます。商品テーブルのデザインビューが表示されたら、最後行にSelected(Yes/No型)のフィールドを追加します。このフィールドは、リストボックスからアイテムを選択／非選択するときフラグとして使用します。商品テーブルを保存して閉じます。
図2-3-3 商品テーブルにSelectedのフィールドを追加
４ 都道府県テーブルをデザインモードで開きます。デザインビューが表示されたら、トドウフケンのフィールドを削除します。先頭行にIDフィールド（２桁のテキスト型）を追加したら、メニューから主キーのアイコン
をクリックして主キーを設定します。テーブルを保存してビューモードで開いたら、北海道から順番にIDを採番して入力します。都道府県テーブルを保存して閉じます。
表2-3-1 都道府県に北から順番にIDを採番する
|
ID |
都道府県 |
ID |
都道府県 |
ID |
都道府県 |
|
01 |
北海道 |
32 |
新潟県 |
73 |
岡山県 |
|
11 |
青森県 |
41 |
富山県 |
74 |
広島県 |
|
12 |
岩手県 |
42 |
石川県 |
75 |
山口県 |
|
13 |
宮城県 |
43 |
福井県 |
81 |
徳島県 |
|
14 |
秋田県 |
51 |
岐阜県 |
82 |
香川県 |
|
15 |
山形県 |
52 |
静岡県 |
83 |
愛媛県 |
|
16 |
福島県 |
53 |
愛知県 |
84 |
高知県 |
|
21 |
茨城県 |
54 |
三重県 |
91 |
福岡県 |
|
22 |
栃木県 |
61 |
滋賀県 |
92 |
佐賀県 |
|
23 |
群馬県 |
62 |
京都府 |
93 |
長崎県 |
|
24 |
埼玉県 |
63 |
大阪府 |
94 |
熊本県 |
|
25 |
千葉県 |
64 |
兵庫県 |
95 |
大分県 |
|
26 |
東京都 |
65 |
奈良県 |
96 |
宮崎県 |
|
27 |
神奈川県 |
66 |
和歌山県 |
97 |
鹿児島県 |
|
28 |
山梨県 |
71 |
鳥取県 |
99 |
沖縄県 |
|
31 |
長野県 |
72 |
島根県 |
|
|
５ テーブルの準備ができたら、Accessを終了させます。
● 商品で得意先を絞り込んでリストボックスに表示させるフォームを作成するには
１ Accessを起動したら作業フォルダに作成した CH2-3.mdbを開きます。
２ データベースウィンドウからフォーム
をクリックしたら、新規作成のボタンをクリックします。フォームの新規作成ダイアログが表示されたら、一覧からデザインビューを選択してOKボタンをクリックします。フォームのデザインビューが表示されたら、以下の手順に従って、図2-3-4のようなフォームを作成します。
ツールボックスからテキストボックスのアイコン
をクリックしたら、フォームの左上に受注開始日のテキストボックスを作成します。ラベルの標題を受注日に書き替えます。ツールボックスからラベルのアイコン
をクリックしたら、受注開始日の右に配置します。ラベルの標題に“～”を入力します。ツールボックスからテキストボックスのアイコンをクリックしたら、ラベルの右に受注終了日のテキストボックスを作成します。テキストボックスのラベルは不要ですから削除します。
ツールボックスからコンボボックスのアイコン
をクリックしたら、受注日の下に配置します。コンボボックスには、都道府県テーブルの都道府県名を北から順番に表示します。コンボボックスの値集合ソースは、実行時設定します。コンボボックスには、注文を受けていない都道府県は不要ですから、受注テーブルと連動させて注文のある都道府県のみ表示するようにします。コンボボックスの先頭には、(全国)を表示します。コンボボックスのラベルの標題を都道府県に書き替えます。
ツールボックスからオプショングループのアイコン
をクリックして、都道府県のコンボボックスの下に配置します。オプショングループには、上位n件と上位n％の２個のラジオボタンを作成します。ツールボックスからテキストボックスのアイコンをクリックして、オプショングループのフレーム内に数値を入力するテキストボックスを作成します。オプショングループのラベル標題を上位に書き替えます。
ツールボックスからリストボックスのアイコン
をクリックしたら、選択可能アイテムと選択されたアイテムの２個のリストボックスを作成します。これらのリストボックスには、商品テーブルのアイテム一覧を表示します。選択可能アイテムのリストボックスには、商品テーブルのSelectedフラグがFalseのアイテムが表示されるように値集合ソースを作成します。選択されたアイテムのリストボックスには、商品テーブルのSelectedフラグがTrueのアイテムが表示されるように値集合ソースを作成します。
ツールボックスから、オプショングループのアイコンをクリックしたら、リストボックスの間に検索条件（OR,AND,NOT）のトグルボタンを３個作成します。（尚、このサンプルデータベースでは、OR条件のみサポートします。）ツールボックスからコマンドボタンのアイコン
をクリックしたら、オプショングループの下に４個のコマンドボタン（＞、＞＞、＜、＜＜）を作成します。ツールボックスから、四角形のアイコン
をクリックしてこれらのコマンドボタンを囲みます。
ツールボックスから、リストボックスのアイコンをクリックして、得意先リストを表示するリストボックス作成します。
ツールボックスからテキストボックスのアイコンをクリックして、得意先の詳細情報を表示するテキストボックスを得意先リストの右側に作成します。
ツールボックスから、トグルボタンのアイコン
をクリックして、得意先リストの右上に件数を表示するトグルボタンを作成します。
ツールボックスから、コマンドボタンのアイコンをクリックしたら、フォームの右上にフィルタ実行、フィルタ解除、フォームを閉じる、ヘルプの４個のコマンドボタンを作成します。
フォーム上に作成したコントロールのプロパティは、表2-3-2のように設定します。
表2-3-2 フォームに作成したコントロールのプロパティ
|
コントロールの種類 |
プロパティ |
値 |
||||||||
|
テキストボックス１ |
名前 |
txtFromDate |
||||||||
|
既定値 |
#96/04/01# |
|||||||||
|
エラーメッセージ |
受注開始日を入力してください！ |
|||||||||
|
ラベル１ |
標題 |
～ |
||||||||
|
テキストボックス２ |
名前 |
txtToDate |
||||||||
|
既定値 |
#96/12/31# |
|||||||||
|
エラーメッセージ |
受注終了日を入力してください！ |
|||||||||
|
コンボボックス |
名前 |
cboKen |
||||||||
|
値集合タイプ |
値リスト |
|||||||||
|
値集合ソース |
実行時設定 |
|||||||||
|
連結列 |
１ |
|||||||||
|
列数 |
１ |
|||||||||
|
エラーメッセージ |
都道府県を選択してください！ |
|||||||||
|
オプショングループ１ |
名前 |
grpTop
|
||||||||
|
テキストボックス３ （オプショングループ1内に作成） |
名前 |
txtTop |
||||||||
|
リストボックス１ |
名前 |
lstAvailable |
||||||||
|
値集合タイプ |
テーブル／クエリ |
|||||||||
|
値集合ソース |
SELECT 商品.商品コード, 商品.商品名 FROM 商品 WHERE (((商品.Selected)=False)) ORDER BY 商品.商品コード; |
|||||||||
|
連結列 |
１ |
|||||||||
|
列数 |
２ |
|||||||||
|
列幅 |
0cm |
|||||||||
|
リストボックス２ |
名前 |
lstSelected |
||||||||
|
値集合タイプ |
テーブル／クエリ |
|||||||||
|
値集合ソース |
SELECT 商品.商品コード, 商品.商品名 FROM 商品 WHERE (((商品.Selected)=True)) ORDER BY 商品.商品コード; |
|||||||||
|
連結列 |
１ |
|||||||||
|
列数 |
２ |
|||||||||
|
列幅 |
0cm |
|||||||||
|
オプショングループ２ |
名前 |
grpCriteria
|
||||||||
|
コマンドボタン１ |
名前 |
cmdAddOne |
||||||||
|
コマンドボタン２ |
名前 |
cmdAddAll |
||||||||
|
コマンドボタン３ |
名前 |
cmdDeleteOne |
||||||||
|
コマンドボタン４ |
名前 |
cmdDeleteAll |
||||||||
|
四角形ボックス （ｺﾏﾝﾄﾞﾎﾞﾀﾝ1～4を囲む） |
|
|
||||||||
|
トグルボタン |
名前 |
tglHitCount |
||||||||
|
標題 |
件数 |
|||||||||
|
リストボックス３ |
名前 |
lstCustomer |
||||||||
|
値集合タイプ |
テーブル／クエリ |
|||||||||
|
値集合ソース |
実行時設定 |
|||||||||
|
連結列 |
０ |
|||||||||
|
列数 |
３ |
|||||||||
|
列幅 |
0cm;6cm;2cm |
|||||||||
|
テキストボックス４ |
名前 |
txtCustomerInfo |
||||||||
|
コマンドボタン５ |
名前 |
cmdFilter |
||||||||
|
コマンドボタン６ |
名前 |
cmdReset |
||||||||
|
コマンドボタン７ |
名前 |
cmdExit |
||||||||
|
コマンドボタン８ |
名前 |
cmdHelp |
図2-3-4 商品から得意先を絞り込んでリストボックスに表示するフォーム
３ メニューからコードのアイコン
をクリックして、フォームモジュールを表示させます。CH2-3.mdbを開いてfrmMultiSelectListBoxのフォームモジュールをコピーして貼り付けます。フォームモジュールのソースコードは、リスト2-3-1を参照してください。CH2-3.mdbから標準モジュールbasMultiSelect, basMyLibとクラスモジュールclsMultiSelectをインポートします。clsMultiSelectには、リストボックスから複数アイテムを選択するためのメソッド／プロパティが登録されています。
４ フォームをビューモードで開いたら、受注日に1996/01/01～1996/12/31を入力します。都道府県のコンボボックスからは、（全国）を選択します。選択可能アイテムから果樹100%オレンジをダブルクリックして選択したら、フィルタ実行ボタンをクリックします。得意先リストには、得意先が売上高の降順に８件表示されます。更に、得意先リストから大宮ユニオンをダブルクリックすると、右側のテキストボックスに得意先の詳細情報が表示されます。得意先リストに表示される得意先件数は、上位１０件、上位１０％のように制約することができます。この機能は、DMなどのラベル印刷に適用すると便利です。
５ 検索条件を指定したとき、検索条件と一致する得意先が表示されることを確認したらフォームをfrmMultiSelectListBoxの名称で保存して、Access を終了させます。
Sub Form_Open()イベントの処理
このイベントは、フォームが開かれたときに実行されます。このイベントでは、クラスモジュールclsMultiSelectのインスタンスを生成してモジュールレベルのオブジェクト変数mMSにオブジェクトのポインタを設定します。clsMultiSelectのRegisterControlsメソッドで、商品アイテムを複数選択するためのリストボックスとコマンドボタンのコントロールを登録します。
.RegisterControls _
lstAvailable, lstSelected, _
cmdAddOne, cmdAddAll, _
cmdDeleteOne, cmdDeleteAll
clsMultiSelectでは、RegisterControlsで登録したコントロールの各種イベント処理を集中制御します。clsMultiSelectのソースコードの解説は、紙面の都合上省略しますが興味のある方は、CH2-3.mdbを参照してください。
clsMutilSelectに必要なコントロールを登録したら、選択可能アイテム、選択されたアイテムのリストボックスの使用可能(Enabled)プロパティをFalseに設定します。また、フィルタ解除ボタンの使用可能(Enabled)プロパティもFalseに設定します。
Sub Form_Load()イベントの処理
このイベントは、フォームが開かれてデータがロードされたときに実行されます。このイベントでは、clsMultiSelectのSetDataメソッドを実行してリストボックスに表示するテーブルと主キーを登録します。SetDataメソッドで指定したテーブルと主キーは、選択可能アイテムのリストボックスから、選択されたアイテムのリストボックスにアイテムを移動するときに使用します。
mMS.SetData "商品", "PrimaryKey"
Sub cboKen_GotFocus()イベントの処理
このイベントは、都道府県のコンボボックスがフォーカスを取得したときに実行されます。このイベントでは、コンボボックスの値集合ソースを作成して設定します。値集合ソースには、注文のあった得意先の都道府県のみ表示されるように、都道府県と受注テーブルを関連付けしたクエリでレコードセットを開きます。Do Until .EOF…Loopでは、メモリ変数strValueListにセミコロン（；）区切りの都道府県名を格納します。strValueListの先頭には、（全国）を格納してコンボボックスの先頭に（全国）が表示されるようにします。
strValueList = "(全国)"
Do Until .EOF
strValueList = strValueList & ";" & !都道府県
.MoveNext
Loop
strValueListの内容：（全国）;北海道;青森県;･････
Sub cmdFilter_Click()イベントの処理
このイベントは、フィルタ実行ボタンをクリックしたときに実行されます。このイベントでは、Sub SearchbyORを呼び出して検索条件と一致する得意先をリストボックスに表示します。
ValidateControls()関数では、検索条件（受注日、都道府県等）が入力されているか調べて未入力のときは、エラーメッセージを表示します。
If Not ValidateControls() Then
Exit Sub
End If
mMS.SelectedCountでは、clsMultiSelectのSelectedCountプロパティを参照して、選択されたアイテムのリストボックスに表示されているアイテム件数を取得します。件数が０ならエラーメッセージを表示します。
intItems = mMS.SelectedCount
mMS.AvailableCountでは、clsMultiSelectのAvailableCountプロパティを参照して、選択可能アイテムのリストボックスに表示されているアイテム件数を取得します。アイテム件数が０（全アイテム選択）なら、メモリ変数varItemsにNullを設定します。アイテム件数が０以外なら、clsMultiSelectのSelectedItemsプロパティを参照して、選択されたアイテムのリストボックスに表示されているアイテムの主キーをメモリ変数varItemsに格納します。SelectedItemsプロパティの引数には、リストボックスの列のインデックスを指定します。
intItems = mMS.AvailableCount
If intItems = 0 Then ' Select All
varItems = Null
Else
varItems = mMS.SelectedItems(0) ' PrimaryKey
End If
varItemsに商品コードのリストを格納したら、Sub SearchbyORを呼び出して、検索条件と一致する得意先を検索してリストボックスに表示します。Sub SearchbyORの引数には、フォームと商品アイテムの主キーが格納されている変数を指定します。Sub SearchbyORのソースコードは、リスト2-3-2を参照してください。
フォームに配置されているコントールの使用可能(Enabled)プロパティにTrue/Falseを設定して使用可能または使用不可にします。得意先の件数を表示するトグルボタンを調べて、値がTrueならSub tglHitCount_AfterUpdateを呼び出して件数を表示します。
Sub cmdReset_Click()イベントの処理
このイベントは、フィルタ解除ボタンをクリックしたときに実行されます。このイベントでは、フォームに配置されているコントロールの使用可能(Enabled)プロパティをTrue/Falseに設定します。また、得意先リストのリストボックスに表示されている内容を初期化します。
Sub cmdExit_Click()イベントの処理
このイベントは、フォームを閉じるボタンをクリックしたときに実行されます。このイベントでは、DoCmdのCloseメソッドで現在表示されているフォームを閉じます。
Sub lstCustomer_DblClick()イベントの処理
このイベントは、得意先リストのリストボックスからアイテムをダブルクリックしたときに実行されます。このイベントでは、得意先テーブルから得意先を検索して、テキストボックスに得意先の詳細情報を表示します。OpenRecordsetメソッドで、得意先テーブルをスナップショット形式のレコードセットとして開きます。FindFirstメソッドに、検索条件として得意先コードを指定して得意先を検索します。得意先が見つかったら、テキストボックスに得意先コード、得意先名、担当者名、住所等の詳細情報をセットして表示します。
Set db = CurrentDb
Set rs = db.OpenRecordset("得意先", dbOpenSnapshot)
With rs
.FindFirst "得意先コード=" & Me.lstCustomer.Column(0)
If Not .NoMatch Then
Me.txtCustomerInfo.Value = "ID:" & !得意先コード & vbCrLf ･･･
End If
.Close
End With
Sub tglHitCount_AfterUpdate()イベントの処理
このイベントは、得意先の件数を表示するトグルボタンをクリックしたときに実行されます。このイベントでは、得意先のリストボックスの値集合ソースを元にレコードセットを開いてレコード件数を取得します。OpenRecordsetメソッドでは、得意先のリストボックスの値集合ソースを元にレコードセットを開きます。MoveLastメソッドでレコードセットの最終レコードに移動してから、RecordCount プロパティを参照してレコード件数を取得します。取得したレコード件数を、ラベルの標題(Caption)に設定して表示します。
Set rs = CurrentDb.OpenRecordset(Me.lstCustomer.RowSource)
With rs
.MoveLast
Me.lblHitCount.Caption = "得意先リスト （" & .RecordCount & " 件）"
.MoveFirst
.Close
End With
Function ValidateControls()イベントの処理
この関数は、フィルタ実行ボタンをクリックしたときに呼ばれます。このイベントでは、検索条件に必要な項目が入力されているか調べて、未入力のときはエラーメッセージを表示します。For Each ctl In Me.Section(acDetail).Controls…Nextでは、フォームの詳細に配置されている全てのコントロールに対して処理します。Select Case .ControlTypeでコントロールのタイプを調べて、acTextBox, acComboBoxならValidationTextにエラーメッセージが格納されているか調べます。エラーメッセージが格納されているとき、このフィールドは必須条件と判断してValue(値)を調べます。値が未入力ならメモリ変数strMsgにエラーメッセージを格納してMsgBoxにて表示します。この関数からは、返り値としてTrue（ｴﾗｰ無し）/False（ｴﾗｰ有り）が設定されます。
For Each ctl In Me.Section(acDetail).Controls
With ctl
Select Case .ControlType
Case acTextBox, acComboBox
If Len(Nz(.ValidationText)) <> 0 Then
If Len(Nz(.Value)) = 0 Then
strMsg = strMsg & .ValidationText & vbCrLf & vbCrLf
End If
End If
End Select
End With
Next ctl
リスト2-3-1 frmMultiSelectListBoxのフォームモジュール
|
Option Compare Database Option Explicit
Private mMS As clsMultiSelect Private Const conErrCantDisable = 2164 Private Sub cboKen_GotFocus() Dim db As DAO.Database Dim rs As DAO.Recordset Dim strSQL As String Dim strValueList As String
strSQL = "SELECT 都道府県.都道府県" _ & " FROM (得意先 INNER JOIN 都道府県" _ & " ON 得意先.都道府県 = 都道府県.都道府県)" _ & " INNER JOIN 受注 ON 得意先.得意先コード = 受注.得意先コード" _ & " WHERE ((([受注].[受注日]) Between #" _ & Me.txtFromDate & "# And #" & Me.txtToDate & "#))" _ & " GROUP BY 都道府県.都道府県, 都道府県.ID" _ & " ORDER BY 都道府県.ID;" Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot) With rs If .EOF Then strValueList = "(全国)" Else strValueList = "(全国)" .MoveFirst Do Until .EOF strValueList = strValueList & ";" & !都道府県 .MoveNext Loop End If rs.Close End With Set rs = Nothing Me.cboKen.RowSource = strValueList
End Sub Private Sub cmdFilter_Click()
Dim intI As Integer Dim intItems As Integer Dim varItems As Variant
If Not ValidateControls() Then Exit Sub End If
With Me intItems = mMS.SelectedCount If intItems = 0 Then MsgBox "アイテムを選択してください！", vbExclamation .lstAvailable.SetFocus Exit Sub End If
intItems = mMS.AvailableCount If intItems = 0 Then ' Select All varItems = Null Else varItems = mMS.SelectedItems(0) ' PrimaryKey End If
Call SearchbyOR(Me, varItems)
On Error GoTo HandleErrors ' Enable .txtFromDate.Enabled = False .txtToDate.Enabled = False
.cboKen.Enabled = False .txtTop.Enabled = False .grpTop.Enabled = False .lstAvailable.Enabled = False .lstSelected.Enabled = False
.cmdAddOne.Enabled = False .cmdAddAll.Enabled = False .cmdDeleteOne.Enabled = False .cmdDeleteAll.Enabled = False .cmdFilter.Enabled = False
' Enable .lstCustomer.Enabled = True .txtCustomerInfo.Value = vbNullString .txtCustomerInfo.Enabled = True .cmdReset.Enabled = True
If tglHitCount Then Call tglHitCount_AfterUpdate End If .cmdReset.SetFocus End With
ExitHere: Exit Sub
HandleErrors: If Err.Number = conErrCantDisable Then Dim ctl As Control Set ctl = Screen.ActiveControl Me.cmdExit.SetFocus ctl.Enabled = False Resume Next Else MsgBox Err.Number & vbCrLf & Err.Description, _ vbExclamation, "cmdFilter_Click()" Resume ExitHere End If
End Sub
Private Sub cmdReset_Click()
On Error GoTo HandleErrors With Me .txtFromDate.Enabled = True .txtToDate.Enabled = True .cboKen.Enabled = True .txtTop.Enabled = True .grpTop.Enabled = True .lstAvailable.Enabled = True .lstSelected.Enabled = True .cmdAddOne.Enabled = True .cmdAddAll.Enabled = True .cmdDeleteOne.Enabled = True .cmdDeleteAll.Enabled = True .cmdFilter.Enabled = True
.lblHitCount.Caption = "得意先リスト" .lstCustomer.Enabled = False .lstCustomer.RowSource = vbNullString .txtCustomerInfo.Enabled = False .cmdReset.Enabled = False .cmdFilter.SetFocus End With ExitHere: Exit Sub
HandleErrors: If Err.Number = conErrCantDisable Then Dim ctl As Control Set ctl = Screen.ActiveControl Me.cmdExit.SetFocus ctl.Enabled = False Resume Next Else MsgBox Err.Number & vbCrLf & Err.Description, _ vbExclamation, "cmdReset_Click()" Resume ExitHere End If End Sub Private Sub Form_Load() mMS.SetData "商品", "PrimaryKey" End Sub Private Sub Form_Open(Cancel As Integer) SetAppTitle_FS ("MultiSelect ListBox (C) " _ & Year(Date) & " by Akio Kasai") Set mMS = New clsMultiSelect With mMS .RegisterControls _ lstAvailable, lstSelected, _ cmdAddOne, cmdAddAll, _ cmdDeleteOne, cmdDeleteAll End With
With Me .lstCustomer.Enabled = False .txtCustomerInfo.Enabled = False .cmdReset.Enabled = False End With
End Sub Private Sub lstCustomer_DblClick(Cancel As Integer)
Dim db As DAO.Database Dim rs As DAO.Recordset
Set db = CurrentDb Set rs = db.OpenRecordset("得意先", dbOpenSnapshot) With rs .FindFirst "得意先コード=" & Me.lstCustomer.Column(0) If Not .NoMatch Then Me.txtCustomerInfo.Value = "ID:" & !得意先コード & vbCrLf _ & Nz(!得意先名) & vbCrLf _ & Nz(!担当者名) & vbCrLf & vbCrLf _ & "〒 " & Nz(!郵便番号) & vbCrLf _ & Nz(!都道府県) & Nz(!住所1) & vbCrLf _ & Nz(!住所2) & vbCrLf _ & "Tel: " & Nz(!電話番号) & vbCrLf _ & "Fax: " & Nz(!ファクシミリ) & vbCrLf End If .Close End With db.Close Set rs = Nothing Set db = Nothing End Sub Private Sub cmdExit_Click() DoCmd.Close End Sub Private Sub tglHitCount_AfterUpdate() Dim rs As DAO.Recordset If tglHitCount Then If Nz(Me.lstCustomer.RowSource, "") <> "" Then DoCmd.Hourglass True Set rs = CurrentDb.OpenRecordset(Me.lstCustomer.RowSource) With rs .MoveLast Me.lblHitCount.Caption = "得意先リスト （" & .RecordCount & " 件)" .MoveFirst .Close End With Set rs = Nothing DoCmd.Hourglass False End If Else Me.lblHitCount.Caption = "得意先リスト" End If End Sub Private Function ValidateControls() As Boolean Dim ctl As Control Dim strMsg As String
For Each ctl In Me.Section(acDetail).Controls With ctl Select Case .ControlType Case acTextBox, acComboBox If Len(Nz(.ValidationText)) <> 0 Then If Len(Nz(.Value)) = 0 Then strMsg = strMsg & .ValidationText & vbCrLf & vbCrLf End If End If End Select End With Next ctl If Len(strMsg) <> 0 Then MsgBox strMsg, vbExclamation ValidateControls = False Else ValidateControls = True End If End Function |
Sub SearchbyORの処理
このサブプロシージャでは、検索条件に該当する得意先を抽出するSQLを作成して、得意先のリストボックスの値集合ソースに設定します。サブプロシージャでは、以下の雛型のSQLを元に、TOPオプションとWHERE句を組み立ててSQLを完成させます。
SELECT TOP n [PERCENT]
得意先.得意先コード, 得意先.得意先名, Sum(CCur([受注明細].[単価]*[数量])) AS 売上額
FROM 商品 INNER JOIN (得意先 INNER JOIN (受注 INNER JOIN 受注明細 ON 受注.受注コード = 受注明細.受注コード) ON 得意先.得意先コード = 受注.得意先コード) ON 商品.商品コード = 受注明細.商品コード
WHERE (((受注.受注日) Between #1/1/1996#
And #12/31/1996#) AND ((得意先.都道府県)="埼玉県") AND ((受注明細.商品コード)=1 Or (受注明細.商品コード)=2 Or (受注明細.商品コード)=3 Or (受注明細.商品コード)=4))
GROUP BY 得意先.得意先コード, 得意先.得意先名
HAVING (((Sum(CCur([受注明細].[単価]*[数量])))>0))
ORDER BY Sum(CCur([受注明細].[単価]*[数量])) DESC;
図2-3-5 SearchbyORで使用する雛型用のクエリ
検索条件に上位ｎ件、ｎ％の指定があるか調べて、上位オプションの指定があるときは、SQLのSELECTにTOP n [PERCENT]オプションを追加します。
If Nz(!txtTop) <> "" Then
strTop = " TOP " & !txtTop ' TOP n
If !grpTop = 2 Then
strTop = strTop & " PERCENT " ' TOP n Percent
End If
End If
SQLのWHERE句には、受注日の範囲、都道府県、商品アイテムを指定します。受注日には、開始日／終了日で範囲を指定しますのでBETWEENを使用します。SQLで日付を指定するときは、日付の前後に（＃）を付加します。
strWhere = " WHERE ((受注.受注日) Between #" &
!txtFromDate & "# And #" & !txtToDate & "#) AND"
検索条件に都道府県の指定があるときは、WHERE句に都道府県＝“都道府県名”を追加します。SQLで文字列を指定するときは、検索文字列をダブルクオーテーション（“）または、シングルクオテーション（‘）で囲みます。都道府県に（全国）を選択したときは、都道府県のWHERE句は不要ですからスキップします。
If !cboKen <> "(全国)" Then
strWhere = strWhere &
" ((得意先.都道府県)='" & !cboKen & "') AND"
End If
For intI = LBound(varSelected) To UBound(varSelected)…Nextのループでは、配列変数varSelectedに格納されている商品コードを取得して、商品コードのWHERE句を作成します。複数の商品コードを指定したときは、OR条件にて検索します。 Left(strWhere, Len(strWhere) - 4)では、最後の” OR “を除去しています。複数の商品コードを、AND条件にて検索する方法は、後述するTipを参照してください。
strWhere = strWhere & " ("
For intI = LBound(varSelected) To UBound(varSelected)
strWhere =
strWhere & "受注明細.商品コード=" & varSelected(intI) & " OR "
Next intI
strSQL = strSQL & Left(strWhere, Len(strWhere) - 4) & ")"
GROUP BY句では、得意先コード、得意先名をグループ化します。HAVING句では、受注金額が０より大きい得意先のみ抽出するように条件を指定します。（WHERE句で指定した抽出条件が、HAVING句で指定した抽出条件より優先します。）ORDER BY句では、得意先を受注金額の降順に並べ替えます。SQLを完成させたら、コンボボックスの値集合ソースに設定して得意先リストを表示させます。
リスト2-3-2 basMultiSelectモジュールのソースコード
|
Option Compare Database Option Explicit Public Sub SearchbyOR(frm As Form, _ varSelected As Variant) Dim db As DAO.Database Dim rs As DAO.Recordset Dim strSQL As String Dim strWhere As String Dim intI As Integer Dim intItems As Integer Dim strTop As String On Error GoTo SearchbyOR_Err
DoCmd.Hourglass True
With frm If Nz(!txtTop) <> "" Then strTop = " TOP " & !txtTop ' TOP n If !grpTop = 2 Then strTop = strTop & " PERCENT " ' TOP n Percent End If End If
strSQL = "SELECT " & strTop _ & " 得意先.得意先コード, 得意先.得意先名," _ & " Sum(CCur([受注明細].[単価]*[数量])) AS 売上額" _ & " FROM 得意先 INNER JOIN (受注 INNER JOIN (受注明細" _ & " INNER JOIN 商品 ON 受注明細.商品コード = 商品.商品コード)" _ & " ON 受注.受注コード = 受注明細.受注コード)" _ & " ON 得意先.得意先コード = 受注.得意先コード"
strWhere = " WHERE ((受注.受注日) Between #" _ & !txtFromDate & "# And #" & !txtToDate & "#) AND"
If !cboKen <> "(全国)" Then strWhere = strWhere & " ((得意先.都道府県)='" & !cboKen & "') AND" End If If IsArray(varSelected) Then strWhere = strWhere & " (" For intI = LBound(varSelected) To UBound(varSelected) strWhere = strWhere & "受注明細.商品コード=" _ & varSelected(intI) & " OR " Next intI strSQL = strSQL _ & Left(strWhere, Len(strWhere) - 4) & ")" ' strip last OR Else strSQL = strSQL _ & Left(strWhere, Len(strWhere) - 4) ' strip last AND End If
strSQL = strSQL _ & " GROUP BY 得意先.得意先コード, 得意先.得意先名" _ & " HAVING (((Sum(CCur([受注明細].[単価] * [数量]))) > 0))" _ & " ORDER BY Sum(CCur([受注明細].[単価]*[数量])) DESC;"
!lstCustomer.RowSource = strSQL End With SearchbyOR_Exit: DoCmd.Hourglass False Exit Sub SearchbyOR_Err: MsgBox Err.Description, vbExclamation, Err.Number Resume SearchbyOR_Exit End Sub |
表2-3-3 clsMultiSelectのプロパティ／メソッド
|
メソッド／プロパティ |
内容 |
||||||||||||||
|
RegisterControlsメソッド |
リストボックスから複数のアイテムを選択するために必要なコントロールを登録します。引数には、２個のリストボックスと４個のコマンドボタンを指定します。
|
||||||||||||||
|
SetDataメソッド |
リストボックスに表示するテーブル名と主キーのインデックス名を登録します。
|
||||||||||||||
|
AvailableCountプロパティ |
選択可能アイテムのリストボックスに表示されているアイテム数を返します。（参照のみ） |
||||||||||||||
|
SelectedCountプロパティ |
選択されたアイテムのリストボックスに表示されているアイテム数を返します。（参照のみ） |
||||||||||||||
|
AvailableItemsプロパティ |
選択可能アイテムのリストボックスに表示されているアイテムの列データを取得します。引数として０から始まる列のインデックスを指定します。 |
||||||||||||||
|
SelectedItemsプロパティ |
選択されたアイテムのリストボックスに表示されているアイテムの列データを取得します。引数として０から始まる列のインデックスを指定します。 |
Note
|
クラスモジュールclsMultiSelectの使い方 clsMultiSelectに登録されているメソッド／プロパティを使用すると、リストボックスから複数のアイテムを選択する機能を簡単に組み込むことができます。以下に、Access のサンプルデータベースNorthwind.mdbの商品テーブルを使用して、clsMultiSelectの使い方を説明します。 １ Acccesを起動したら作業フォルダに、新規データベース MultiSelect.mdbを作成します。 ２ Northwind.mdbから商品テーブルをインポートします。 ３ CH2-3.mdbからクラスモジュール clsMultiSelectをインポートします。 ４ 商品テーブルをデザインモードで開いたら最終行に、Selected(Yes/No型)のフィールドを追加します。 ５ 新規フォームを作成したら、リストボックス２個とコマンドボタン４個作成します。左側に配置した、選択可能アイテムのリストボックスの値集合ソースには： SELECT 商品.商品コード, 商品.商品名 FROM 商品 WHERE (((商品.Selected)=False)) ORDER BY 商品.商品コード; を埋め込みます。 右側に配置した、選択されたアイテムのリストボックスの値集合ソースには： SELECT 商品.商品コード, 商品.商品名 FROM 商品 WHERE (((商品.Selected)=True)) ORDER BY 商品.商品コード; を埋め込みます。 尚、リストボックスの列数は２に設定しますが、商品コードの列幅を０cmにして非表示とします。
図2-3-6 ２個のリストボックスと４個のコマンドボタンを作成
６ フォームモジュールを表示したら、以下のコードを入力します。
リストボックスとコマンドボタンのコントロール名が、異なるときは、RegisterControlsメソッドで指定する引数を書き替えてください。 ７ フォームをビューモードで開いたら、複数アイテムを選択して確認します。 |
Tip
|
複数の商品アイテムをOR、AND、NOTの条件で検索するには 複数のアイテムをOR条件で検索するには、SQLのWHERE句に： WHERE ((受注明細.商品コード)=1 Or (受注明細.商品コード)=2 Or (受注明細.商品コード)=3 Or (受注明細.商品コード)=4)) のように商品コードの条件式を記述します。ところが、AND条件で検索するには、ORをANDに替えただけでは期待した結果が得られません。 AND条件で複数アイテムを検索するには、クロス集計クエリを作成して商品コード別に受注数を集計してワークテーブルに保存します。このとき、クロス集計クエリの列見出し（商品コード）を固定にするために、IN句で商品コードを指定します。（IN句で商品コードを指定しないときは、列見出しが変動します。） TRANSFORM Sum(受注明細.数量) AS 数量の合計 SELECT 得意先.得意先コード, 得意先.得意先名 FROM 商品 INNER JOIN (得意先 INNER JOIN (受注 INNER JOIN 受注明細 ON 受注.受注コード = 受注明細.受注コード) ON 得意先.得意先コード = 受注.得意先コード) ON 商品.商品コード = 受注明細.商品コード WHERE (((受注.受注日) Between #1/1/1996# And #12/31/1996#) AND ((得意先.都道府県)="埼玉県") AND ((受注明細.商品コード)=1 Or (受注明細.商品コード)=2 Or (受注明細.商品コード)=3 Or (受注明細.商品コード)=4)) GROUP BY 得意先.得意先コード, 得意先.得意先名 PIVOT 受注明細.商品コード
IN("0001","0002","0003","0004");
図2-3-7 クロス集計クエリで商品コード別受注数を集計する ワークテーブルを作成したら、列見出しの商品コードのフィールド（0001,0002,0003,0004）に対してAND条件を設定して検索します。例えば、商品コード１，２，３、４の全てのアイテムの注文を受けた得意先を検索するには、WHERE句に： WHERE (0001 > 0 ) AND (0002 > 0) AND (0003 > 0) AND (0004 > 0) のように条件式を記述します。 また、商品コード１の注文があって、商品コード２の注文がない得意先を検索するには、WHERE句に： WHERE (0001 > 0) AND (0002 Is Null) のような条件式を記述します。 |
