【AccessVBA】フォームにあるコントロールの値集合ソースの値をすべて取得するには

この記事では、フォームにあるコントロールの値集合ソースの値をすべて取得する方法についてご説明します。

すぐにソースコードを見たい場合はこちらをクリック

値集合ソースとは、Accessのフォームに設置されたコンボボックスまたはリストボックスの設定値の一つです。

コンボボックス

リストボックス

Table of Contents

【動画】フォームにあるコントロールの値集合ソースの値をすべて取得する実際の動き

本題に入る前に、まずは次の動画をご覧ください。

まずはAccessのマクロが自分自身のAccessのデータベースに接続します。

接続後は、マクロがフォームにコンボボックスまたはリストボックスがあるのか確認し、ある場合は値集合ソースの値を取得して、値集合ソースの値を保持しておくテーブル「tmp_tbl」に追加しています。

値集合ソースの値をすべて取得できたら、値集合ソースの値を表示するためのフォーム「frm_data」にあるリストボックスに値集合ソースの値を表示させています。

目次へ戻る

マクロ作成の流れ

STEP.1

自分自身のデータベースを開く

自分自身のデータベースを開きます。

STEP.2

自分自身の(Accessの)データベース用インスタンスを生成する

自分自身の(Accessの)データベース用インスタンスを生成します。

STEP.3

値集合ソースの値を保存しておくテーブル「tmp_tbl」が存在しているか確認し、存在していなければテーブル「tmp_tbl」を作成する

値集合ソースの値を保存しておくテーブルが存在しているか確認し、存在していなければテーブルを作成します。
存在している場合は、値集合ソースの値を追加するために、テーブルのデータを全削除します。

STEP.4

フォームを開く

フォームを開きます。

STEP.5

フォームにある「値集合ソース」の値を取得する

フォームにある「値集合ソース」の値を取得します。
今回のサンプルでは、値集合ソースだけでなく、値集合ソースを持つコントロールが設置されたフォーム名、値集合ソースを持つコントロール名も取得します。

STEP.6

取得した値集合ソースの値(と値集合ソースを持つコントロールが設置されたフォーム名、値集合ソースを持つコントロール名)を、テーブル「tmp_tbl」に追加する

取得した値集合ソースの値(と値集合ソースを持つコントロールが設置されたフォーム名、値集合ソースを持つコントロール名)を、テーブル「tmp_tbl」に追加します。

STEP.7

フォームの数だけSTEP.4からSTEP.6を繰り返す

フォームの数だけSTEP.4からSTEP.6を繰り返します。

STEP.8

テーブル「tmp_tbl」の値を取得してリストボックスに表示させる

テーブル「tmp_tbl」の値を取得してリストボックスに表示させます。

目次へ戻る

Accessのデータベースファイルの例

今回は次のAccessのデータベースファイルを用意しました。

Accessのデータベースファイルにあるフォームは次の通りです。

frm_社員データ

frm_社員リスト：コンボボックス

frm_社員リスト：リストボックス

frm_商品：コンボボックス

frm_商品：リストボックス

frm_data

frm_dataは、値集合ソースの値を表示させるフォームです。

設置されたリストボックスに、値集合ソースの値を表示させます。
※マクロを実行した結果

値集合ソースの値はリストボックスに表示されるのですが、一つ問題があって、値集合ソースの値の文字列がかなり長い場合はリストボックスに入りきらず見切れてしまうんです。

このままでは値集合ソースの値が一部しか確認できません。

そこで、リストボックスの下にテキストボックスを用意し、値集合ソースの値の行をクリックするとテキストボックスに表示されるようにしました。

値が非常に長くても、テキストボックスにすべてが表示されるようになっています。

もしテキストボックスの横幅を超える長さのデータでも、データを改行させてすべてが見れるようになっています。

目次へ戻る

(1)コードの例：リストボックスに「値集合ソース」の値を表示

Option Compare Database

Private Sub btn_exec_Click()

    Dim db          As DAO.Database     'DAOデータベース用オブジェクト用変数
    Dim tbl         As DAO.TableDef     'テーブル用変数
    Dim ctl         As Control          'Control用変数
    Dim tblExistFlg As Boolean          'テーブル存在確認フラグ
    Dim aObj        As AccessObject     'オブジェクト用変数
    Dim dbs         As Object           '自分自身の(Accessの)データベース用インスタンス用変数
    Dim sqlStr      As String           'SQL文用変数

    '「値集合ソース」の値を表示させるフォーム
    Const formName As String = "frm_data"

    '「値集合ソース」の値を表示させるリストボックス
    Const controlName As String = "ltb1"

    '「値集合ソース」の値を保存しておくテーブル
    Const tmpTbl As String = "tmp_tbl"

    '自分自身のデータベースを開く
    Set db = CurrentDb

    '自分自身の(Accessの)データベース用インスタンスを生成する
    Set dbs = Application.CurrentProject

    'テーブルの数分処理を繰り返すFor文
    For Each tbl In db.TableDefs

        If tbl.Name = tmpTbl Then

            'テーブル「tmp_tbl」が存在している場合

            '変数「tblExistFlg」にTrueを設定する
            tblExistFlg = True

            'テーブル「tmp_tbl」のデータを全件削除する
            db.Execute "delete from " & tmpTbl, dbFailOnError

        End If

    Next tbl

    If tblExistFlg = False Then

        'テーブル「tmp_tbl」が存在しない場合

        'テーブル「tmp_tbl」を作成するCREATE TABLE文を生成する
        '⇒フィールド：①値集合ソースを持つコントロールが設置されたフォーム名
        '　　　　　　　②値集合ソースを持つコントロール名
        '              ③値集合ソースの値
        
        sqlStr = "CREATE TABLE " & tmpTbl
        sqlStr = sqlStr & " ("
        sqlStr = sqlStr & "  FormName TEXT(255)"
        sqlStr = sqlStr & ", ControlName TEXT(255)"
        sqlStr = sqlStr & ", RowSourceValue TEXT(255)"
        sqlStr = sqlStr & ")"
        
        'CREATE TABLE文を実行する
        db.Execute sqlStr, dbFailOnError

    End If

    'リストボックスをクリアする
    Me.Controls(controlName).RowSource = ""

    'リストボックスの「値集合タイプ」プロパティを「テーブル/クエリ」に設定する
    Me.Controls(controlName).RowSourceType = "Table/Query"

    'リストボックスのデータ部の列幅を設定する
    Me.Controls(controlName).ColumnWidths = "2000;2000;10000"

    'フォームを取得するFor文
    For Each aObj In dbs.AllForms

        If aObj.Name <> formName Then

            '「frm_data」のフォーム以外の場合

            'フォームを開く
            DoCmd.OpenForm aObj.Name

            'フォームに設置されたコントロールの数分処理を繰り返すFor文
            For Each ctl In Forms(aObj.Name).Controls

                ' ここでctlを使用して各コントロールを操作します

                Select Case ctl.ControlType

                    Case acComboBox, acListBox

                        'コントロールがコンボボックスまたはリストボックスの場合
                        
                        '値集合ソースを持つコントロールが設置されたフォーム名、値集合ソースを持つコントロール名、値集合ソースの値を、テーブル「tmp_tbl」に追加するINSERT文を生成する
                        sqlStr = "INSERT INTO " & tmpTbl
                        sqlStr = sqlStr & " ("
                        sqlStr = sqlStr & "  FormName"
                        sqlStr = sqlStr & ", ControlName"
                        sqlStr = sqlStr & ", RowSourceValue"
                        sqlStr = sqlStr & ")"
                        sqlStr = sqlStr & " VALUES "
                        sqlStr = sqlStr & "("
                        sqlStr = sqlStr & "  '" & aObj.Name & "'"
                        sqlStr = sqlStr & ", '" & ctl.Name & "'"
                        sqlStr = sqlStr & ", '" & ctl.RowSource & "'"
                        sqlStr = sqlStr & ")"

                        'INSERT文を実行する
                        db.Execute sqlStr, dbFailOnError

                End Select

            Next ctl

            'フォームを閉じる
            DoCmd.Close acForm, aObj.Name, acSaveNo

        End If

    Next aObj

    'テーブル「tmp_tbl」から値集合ソースを持つコントロールが設置されたフォーム名、値集合ソースを持つコントロール名、値集合ソースの値を取得するSELECT文を生成する
    sqlStr = "SELECT * FROM " & tmpTbl
    
    'SELECT文を実行してRowSourceプロパティに設定する
    '⇒SELECT文を実行して取得されたデータがリストボックスに表示される
    Me.Controls(controlName).RowSource = sqlStr

    '後処理
    Set ctl = Nothing
    Set aObj = Nothing
    Set db = Nothing

End Sub

注目すべきコード①

最初に見て頂きたいのは29行目から64行目です。

    'テーブルの数分処理を繰り返すFor文
    For Each tbl In db.TableDefs

        If tbl.Name = tmpTbl Then

            'テーブル「tmp_tbl」が存在している場合

            '変数「tblExistFlg」にTrueを設定する
            tblExistFlg = True

            'テーブル「tmp_tbl」のデータを全件削除する
            db.Execute "delete from " & tmpTbl, dbFailOnError

        End If

    Next tbl

    If tblExistFlg = False Then

        'テーブル「tmp_tbl」が存在しない場合

        'テーブル「tmp_tbl」を作成するCREATE TABLE文を生成する
        '⇒フィールド：①値集合ソースを持つコントロールが設置されたフォーム名
        '　　　　　　　②値集合ソースを持つコントロール名
        '              ③値集合ソースの値
        
        sqlStr = "CREATE TABLE " & tmpTbl
        sqlStr = sqlStr & " ("
        sqlStr = sqlStr & "  FormName TEXT(255)"
        sqlStr = sqlStr & ", ControlName TEXT(255)"
        sqlStr = sqlStr & ", RowSourceValue TEXT(255)"
        sqlStr = sqlStr & ")"
        
        'CREATE TABLE文を実行する
        db.Execute sqlStr, dbFailOnError

    End If

コードの説明

以上のコードは、Accessのデータベースファイルの中に「tmp_tbl」のテーブルが存在するか確認し、存在していれば「tmp_tbl」のテーブルデータを全削除を、存在していなければ「tmp_tbl」のテーブルを作成する処理のコードです。

「tmp_tbl」のテーブルを作成する理由

値集合ソースを取得したらそのままリストボックスに表示させればいいのに、なぜわざわざ新規でテーブルを用意する必要があるのかと思われるかもしれません。

新規でテーブルを用意する理由は、データに「,」(カンマ)が含まれている場合は正常にリストボックスにデータが表示されない事象を回避するためです。

正常にリストボックスに表示されない事象とは、データに「,」(カンマ)が含まれていると、複数列のリストボックスの場合データが複数の列に分割表示されてしまう事象です。
※「,」(カンマ)だけでなく「;」(セミコロン)も区切り文字として認識される

データが分割されてしまうというのは、例えば「aaa,bbb」というデータの場合は、リストボックスの1列目に「aaa」を2列目には「bbb」と表示されてしまいます。

分割されてリストボックスに表示された状態は下の通りです。

この区切り表示されてしまう対策として、テーブルに一度追加して取得しリストボックスに表示する、という方法です。

テーブルのデータなら「,」(カンマ)が含まれていても区切られることなくそのままの値がリストボックスに表示されます。

ちなみに、リストボックスが1列の場合なら、「,」(カンマ)が含まれているデータは列に収まります。

コードの詳細

29行目のコードは、テーブルの数分処理を繰り返すFor文で、31行目で「値集合ソース」の値を保存しておくテーブル「tmp_tbl」がどうかを判定します。(テーブル「tmp_tbl」の存在確認判定)

テーブル「tmp_tbl」の場合は、36行目でテーブル存在確認フラグ「tblExistFlg」にTrueを設定し、「tmp_tbl」のデータを削除します。

テーブル「tmp_tbl」以外の場合は、何もせず45行目の判定処理に進みます。

テーブル存在確認フラグ「tblExistFlg」がFalse(テーブル「tmp_tbl」が存在しない)の場合、54行目から59目でテーブル「tmp_tbl」を作成するCREATE TABLE文を用意し、62行目でCREATE TABLE文を実行します。

注目すべきコード②

次に見て頂きたいのは67行目から73行目です。

    'リストボックスをクリアする
    Me.Controls(controlName).RowSource = ""

    'リストボックスの「値集合タイプ」プロパティを「テーブル/クエリ」に設定する
    Me.Controls(controlName).RowSourceType = "Table/Query"

    'リストボックスのデータ部の列幅を設定する
    Me.Controls(controlName).ColumnWidths = "2000;2000;10000"

コードの説明

以上のコードは、リストボックスのクリア、リストボックスの「値集合タイプ」プロパティを「テーブル/クエリ」に設定、リストボックスのデータ部の列幅の設定を行う処理のコードです。

コードの詳細

67行目のコードでは、リストボックスのRowSourceプロパティにブランクを設定しています。

リストボックスのRowSourceプロパティにブランクを設定することでリストボックスをクリアすることができます。

70行目のコードでは、RowSourceTypeプロパティに”Table/Query”を設定します。

“Table/Query”を設定することで、テーブルまたはクエリを実行し取得した結果をリストボックスに設定することができます。

なお、RowSourceTypeプロパティはリストボックスの「値集合タイプ」の設定と同じです。

73行目のコードは、リストボックスのデータ部の列幅を設定します。

注目すべきコード③

次に見て頂きたいのは75行目から122行目です。

    'フォームを取得するFor文
    For Each aObj In dbs.AllForms

        If aObj.Name <> formName Then

            '「frm_data」のフォーム以外の場合

            'フォームを開く
            DoCmd.OpenForm aObj.Name

            'フォームに設置されたコントロールの数分処理を繰り返すFor文
            For Each ctl In Forms(aObj.Name).Controls

                ' ここでctlを使用して各コントロールを操作します

                Select Case ctl.ControlType

                    Case acComboBox, acListBox

                        'コントロールがコンボボックスまたはリストボックスの場合
                        
                        '値集合ソースを持つコントロールが設置されたフォーム名、値集合ソースを持つコントロール名、値集合ソースの値を、テーブル「tmp_tbl」に追加するINSERT文を生成する
                        sqlStr = "INSERT INTO " & tmpTbl
                        sqlStr = sqlStr & " ("
                        sqlStr = sqlStr & "  FormName"
                        sqlStr = sqlStr & ", ControlName"
                        sqlStr = sqlStr & ", RowSourceValue"
                        sqlStr = sqlStr & ")"
                        sqlStr = sqlStr & " VALUES "
                        sqlStr = sqlStr & "("
                        sqlStr = sqlStr & "  '" & aObj.Name & "'"
                        sqlStr = sqlStr & ", '" & ctl.Name & "'"
                        sqlStr = sqlStr & ", '" & ctl.RowSource & "'"
                        sqlStr = sqlStr & ")"

                        'INSERT文を実行する
                        db.Execute sqlStr, dbFailOnError

                End Select

            Next ctl

            'フォームを閉じる
            DoCmd.Close acForm, aObj.Name, acSaveNo

        End If

    Next aObj

コードの説明

以上のコードは、フォームにあるコンボボックスまたはリストボックスの「値集合ソース」をテーブルtmp_tblに追加する処理のコードです。

コードの詳細

76行目のコードはフォームの数だけ処理を繰り返すFor文です。

78行目のコードは、Accessのデータベース内に「値集合ソース」の値を表示させるリストボックスがあるフォーム「frm_data」が存在するかIF文で判定します。

フォーム「frm_data」以外の場合は、83行目のコードでフォーム「frm_data」以外のフォームを開きます。

フォームを開いたら、86行目でフォームにあるコントロールを取得します。

コントロールの取得はフォーム上にあるすべてを取得し、取得したコントロールがコンボボックスまたはリストボックスかを92行目で判定します。

コンボボックスまたはリストボックスの場合は「値集合ソース」をctl.RowSourceから取得し(RowSourceプロパティは「値集合ソース」を返す(107行目))、97行目から108行目で生成するINSERT文に「値集合ソース」を設定して111行目でINSERT文を実行します。

119行目のコードでは、開いたフォームを閉じます。(コントロールすべてを取得し終わったので)

注目すべきコード④

次に見て頂きたいのは125行目から129行目です。

    'テーブル「tmp_tbl」から値集合ソースを持つコントロールが設置されたフォーム名、値集合ソースを持つコントロール名、値集合ソースの値を取得するSELECT文を生成する
    sqlStr = "SELECT * FROM " & tmpTbl
    
    'SELECT文を実行してRowSourceプロパティに設定する
    '⇒SELECT文を実行して取得されたデータがリストボックスに表示される
    Me.Controls(controlName).RowSource = sqlStr

コードの説明

以上のコードは、テーブル「tmp_tbl」からデータを取得してリストボックスに表示させる処理のコードです。

コードの詳細

125行目のコードでは、テーブル「tmp_tbl」からデータを取得するSELECT文を生成します。(ここでは全フィールドで全件取得)

129行目のコードでは、125行目のSELECT文をRowSourceプロパティに設定しています。

SELECT文をRowSourceプロパティに設定することでSELECT文が取得したデータがリストボックスに表示されます。

目次へ戻る

(2)コードの例：リストボックスに「値集合ソース」の値を表示

Private Sub ltb1_Click()

    'リストボックスの名前を取得
    Const controlName As String = "ltb1"
    
    'リストボックスの1列目の値を、フォーム名を出力するコンボボックスに出力する
    Me.Controls("txb_frmNm").Value = Me.Controls(controlName).Column(0, Me.Controls(controlName).ListIndex)
        
    'リストボックスの2列目の値を、コントロール名を出力するコンボボックスに出力する
    Me.Controls("cbx_ctrlNM").Value = Me.Controls(controlName).Column(1, Me.Controls(controlName).ListIndex)
        
    'リストボックスの3列目の値を、値集合ソースの値を出力するコンボボックスに出力する
    Me.Controls("txb_valSetsrc").Value = Me.Controls(controlName).Column(2, Me.Controls(controlName).ListIndex)
    
End Sub

注目すべきコード①

最初に見て頂きたいのは7行目から13行目です。

    'リストボックスの1列目の値を、フォーム名を出力するコンボボックスに出力する
    Me.Controls("txb_frmNm").Value = Me.Controls(controlName).Column(0, Me.Controls(controlName).ListIndex)
        
    'リストボックスの2列目の値を、コントロール名を出力するコンボボックスに出力する
    Me.Controls("cbx_ctrlNM").Value = Me.Controls(controlName).Column(1, Me.Controls(controlName).ListIndex)
        
    'リストボックスの3列目の値を、値集合ソースの値を出力するコンボボックスに出力する
    Me.Controls("txb_valSetsrc").Value = Me.Controls(controlName).Column(2, Me.Controls(controlName).ListIndex)

コードの説明

以上のコードは、リストボックスで選択された行の値をテキストボックスに出力する処理のコードです。

テキストボックスのValueプロパティに値を設定することで、テキストボックスに値を表示させることができます。

リストボックスのColumnプロパティの1つ目の引数は列位置、2つ目の引数は行位置指定することで、指定した位置の値が返されます。

下の画像はリストボックスの1行目が選択されていますが、1行目の1列目、2列目、3列目の値がリストボックスの下にあるそれぞれのテキストボックスに出力されます。

目次へ戻る

動作確認

フォームにあるコントロールの値集合ソースの値をリストボックスに表示させた結果は「Accessのデータベースファイルの例」を、リストボックスで選択された行の値をテキストボックスに出力した結果は「(2)コードの例：リストボックスに「値集合ソース」の値を表示」の「注目すべきコード①」をご覧ください。

目次へ戻る

【注意】参照設定が必要です

一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。

参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。

microsoft office 15.0 access database engine object library(ACEDAO.DLL)

なぜ必要かというと、Excelのマクロのコードの18行目の「DAO.DBEngine」というオブジェクトが「ACEDAO.DLL」というファイルを参照するからです。

    Dim db          As DAO.Database     'DAOデータベース用オブジェクト用変数
    Dim tbl         As DAO.TableDef     'テーブル用変数

この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。

ここでは「ACEDAO.DLL」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「DAO.DBEngine」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。

目次へ戻る

最後に

この記事では、フォームにあるコントロールの値集合ソースの値をすべて取得する方法についてご説明しました。

Accessのフォームにあるコントロールの値集合ソースの値を取得したいときは本記事を参考に試してみてくださいね。

プログラミングのスキルを習得するなら

プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。

プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。

→　TechAcademyの「1週間無料体験」はこちら

目次へ戻る