クイックコンボボックスのお手本データベース
図2-1-1 データ件数の多いテーブルを高速検索するクイックコンボボックスのサンプルデータベース
このサンプルデータベースは、１２万件の郵便番号テーブルを町域名カナで高速検索します。通常、テーブルにコンボボックスを適用する場合、レコード件数は２～３万件が限界です。レコード件数が３万件以上のテーブルにコンボボックスを適用すると、全件表示されなかったり、最後のレコードが表示されない等の不具合が発生します。このサンプルデータベースでは、コンボボックスの値集合ソース(RowSource)プロパティを、実行時ダイナミックに書き替えてレコード件数を必要最小限に絞り込むことにより高速検索を実現しています。この方式を採用すると、１０万件以上のテーブルにコンボボックスを適用しても、検索結果が瞬時に表示されます。
サンプルデータベース(CH2-1.mdb)では、以下のノウハウを習得することができます。
◆ コンボボックスの値集合ソース(RowSource)を実行時ダイナミックに設定する方法
◆ コンボボックスに入力した文字列と部分一致するレコードを高速検索する方法
◆ コンボボックスに複数列を指定した場合、各列の値を取得する方法
● クイックコンボボックスを作成するには
１ Access を起動して作業フォルダに新規データベース CH2-1.mdbを作成します。
２ ファイルメニューから外部データの取り込み、インポートをクリックします。インポートのダイアログが表示されたら、CH2-1.mdbを選択してインポートボタンをクリックします。
オブジェクトのインポートダイアログが表示されたら、テーブルタブをクリックしてtblPostalCodeを選択します。最後に、OKボタンをクリックしてインポートを完了させます。tblPostalCodeには、約１２万件の全国の郵便番号が登録されています。サンプルデータベースでは、コンボボックスを作成してtblPostalCodeの町域名カナから逆引きで郵便番号を検索します。
図2-1-2 CH2-1.mdbからインポートしたテーブルtblPostalCodeの内容
３ tblPostalCodeをデザインモードで開いたら、メニューからインデックスのアイコン
をクリックして、インデックスのダイアログを表示させます。インデックス名に、”KanaKey”を入力して、フィールド名のコンボボックスから町域名カナをクリックします。並べ替え順序のコンボボックスから昇順をクリックします。インデックス KanaKeyのプロパティは、図2-1-3のように設定します。町域名カナにインデックスを作成することにより、検索処理を最適化することができます。
図2-1-3 tblPostalCodeにKanaKeyのインデックス作成
４ データベースウィンドウからフォーム
をクリックして、新規作成ボタンをクリックします。フォームの新規作成ダイアログが表示されたら、デザインビューを選択してOKボタンをクリックします。フォームのデザインビューが表示されたら、ツールボックスからテキストボックスのアイコン
をクリックして、有効桁数を入力するテキストボックスを作成します。有効桁数は、コンボボックスの値集合ソースを作成するとき参照します。ツールボックスからコンボボックスのアイコン
をクリックして、町域名カナのコンボボックスを作成します。コンボボックスの下に、郵便番号と住所を表示するテキストボックスを作成します。最後に、ツールボックスからラベルのアイコン
をクリックしてラベルを作成します。ラベルの標題には、クイックコンボボックス使用上の注意事項を入力します。フォームに作成した、テキストボックス、コンボボックス、ラベルのプロパティを表2-1-1のように設定します。
表2-1-1 フォームに作成したコントロールのプロパティ
|
コントロールの種類 |
プロパティ |
値 |
|
テキストボックス１ |
名前 |
txtValidLen |
|
タブストップ |
いいえ |
|
|
既定値 |
１ |
|
|
コンボボックス |
名前 |
cboTownKana |
|
値集合タイプ |
テーブル／クエリ |
|
|
値集合ソース |
実行時SQLをダイナミックに作成： SELECT tblPostalCode.町域名カナ, tblPostalCode.町域名, tblPostalCode.市区町村名, tblPostalCode.都道府県名, tblPostalCode.郵便番号 FROM tblPostalCode WHERE (((tblPostalCode.町域名カナ) Like "ｶ*")) ORDER BY tblPostalCode.町域名カナ; |
|
|
連結列 |
５ |
|
|
列数 |
５ |
|
|
列幅 |
1.614cm;3.443cm;3.173cm;1.512cm;0cm |
|
|
テキストボックス２ |
名前 |
txtAddress |
|
タブストップ |
いいえ |
|
|
編集ロック |
はい |
|
|
背景色 |
16777183 |
|
|
ラベル |
標題 |
・町域名カナのコンボボックスに町域名のカナを半角で入力すると、指定したカナで始まる全国の町域名が表示されます。 ・カナは有効桁数以上入力しないとコンボボックスには表示されません。 |
図2-1-4 フォームにクイックコンボボックスを作成した例
５ メニューからコードのアイコン
をクリックしてフォームモジュールを表示させたら、CH2-1.mdbを開いて”frmQuickComboBox”のフォームモジュールをコピーして貼り付けます。フォームモジュールのソースコードは、リスト2-1-1を参照してください。また、CH2-1.mdbのモジュールからbasMyLibをインポートしてください。
６ フォームをビューモードで開いたら、コンボボックスに半角の”ｶ”を入力してリターンキーを押します。コンボボックスの一覧に“カ”で始まる全国の町域名が表示されます。コンボボックスに入力した文字を全て削除してから、有効桁数を”4”にして、コンボボックスに”ｶｲｳﾝ”と入力すると更にレコードが絞り込まれて高速検索することができます。コンボボックスに入力した文字を全て消去すると、コンボボックスの値集合ソースが空になりレコードは表示されません。
７ クイックコンボの動作を確認したらフォームを”frmQuickComboBox”の名称で保存して、Access を終了させます。
Sub cboTownKana_Change()イベントの処理
このイベントは、コンボボックスの値が変更されたときに実行されます。このイベントでは、Sub BuildRowSourceを呼び出してコンボボックスの値集合ソース(RowSource)を生成して書き替えます。
Sub cboTownKana_AfterUpdate()イベントの処理
このイベントは、コンボボックスの値が更新されたときに実行されます。このイベントでは、コンボボックスの一覧から選択した住所をテキストボックス(txtAddress)に格納します。コンボボックスの列１には町域名カナ、列２には町域名、列３には市区町村名、列４には都道府県名、列５には郵便番号が格納されています。コンボボックスの列を参照するには、Columnプロパティを使用します。尚、Columnプロパティのインデックスは、０から始まります。
Column(4) è 郵便番号
Column(3) è 都道府県名
Column(2) è 市区町村名
Column(1) è 町域名
Sub txtValidLen_AfterUpdate()イベントの処理
このイベントは、有効桁数が更新されたときに実行されます。このイベントでは、Sub BuilRowSourceを呼んでコンボボックスの値集合ソースを生成して書き替えます。
Sub BuildRowSource()の処理
このサブプロシージャは、コンボボックスの値集合ソース(RowSource)を生成してレコードを再表示します。コンボボックスに入力した町域カナが有効桁数と等しいか大きいとき、RowSourceのSQLを生成して書き替えます。但し、インクレメンタルサーチのときは、RowSourceを更新しません。コンボボックスに入力した町域名カナが、有効桁数に満たないときは、RowSourceにNullを設定してコンボボックスの一覧にレコードが表示されないようにします。
Note
|
インクレメンタルサーチとは 例えば、“カサイ”で始まる町域名を検索するとき、最初に“カ”を入力して該当する町域名を表示させてから、“サ”を追加して“カサ”で始まる町域名に移動します。更に、“イ”を追加して“カサイ”で始まる町域名に移動します。このように検索文字列を順次追加して検索することを、インクレメンタルサーチと呼びます。 |
リスト2-1-1 frmQuickComboBoxのフォームモジュール
|
Option Compare Database Option Explicit Dim mstrPreKana As String Dim mstrNewKana As String Private Sub Form_Open(Cancel As Integer) Call SetAppTitle_FS("Quick ComboBox for Large Recordset (C) " _ & Year(Date) & " by Akio Kasai") End Sub Private Sub cboTownKana_AfterUpdate() With Me.cboTownKana Me.txtAddress = .Column(4) & " " _ & .Column(3) & " " _ & .Column(2) & " " _ & .Column(1) End With Me.txtAddress.SetFocus End Sub Private Sub cboTownKana_Change() BuildRowSource End Sub Private Sub BuildRowSource() Dim intLenPreKana As Integer Dim intLenNewKana As Integer
On Error Resume Next
With Me.cboTownKana mstrNewKana = Trim(Nz(.Text)) intLenPreKana = Len(mstrPreKana) intLenNewKana = Len(mstrNewKana) If intLenNewKana >= Me.txtValidLen Then If intLenPreKana = 0 Or _ intLenNewKana < intLenPreKana Then .RowSource = "SELECT 町域名カナ, 町域名, 市区町村名,” _ & “都道府県名, 郵便番号" _ & " FROM tblPostalCode" _ & " WHERE (((町域名カナ) Like '" & mstrNewKana & "*'))" _ & " ORDER BY 町域名カナ;" .Dropdown End If mstrPreKana = mstrNewKana Else .RowSource = vbNullString mstrPreKana = vbNullString End If End With End Sub Private Sub txtValidLen_AfterUpdate() BuildRowSource End Sub |
Tip
|
小規模なテーブルにコンボボックス／リストボックスを適用するには テーブルのデータ件数が３万件を超えるような大規模なテーブルは、クイックコンボボックスを適用すれば高速検索することができます。データ件数が２千件ぐらいの小規模なテーブルにコンボボックス／リストボックスを適用するときは、フォームのOnLoadイベントでListCountプロパティを参照することにより高速表示させることができます。コンボ／リストボックスのListCountプロパティを参照すると、Accessは、最終レコードまで事前に読み込みます。このため、コンボ／リストボックスのスクロールボタンをクリックして最終レコードに素早く移動することができます。 Private Sub Form_Load() Dim lngCount As Long lngCount = Me.MyComboOrListBox.ListCount End Sub |