食品成分表（八訂）の食品成分の検索フォームの作成、Microsoft Access備忘録（2）

Access
2022.03.01
この記事は約14分で読めます。

はじめに

　最初に、食品成分表（八訂）の本表、アミノ酸成分表、脂肪酸成分表、炭水化物成分表の４つの成分表をMicrosoft Accessに読み込ませるために、各表の栄養素の文字列で記入されている部分を数値化しました。

Cellsプロパティー、InStr関数、Mid関数、Val関数、VarType関数について、VBAの備忘録（１）
Cellsプロパティー、Mid関数、Instr関数、Val関数、VarType関数、食品成分表内の文字列を数値に変換するプログラムについて簡単な紹介をした記事です。
yanai-ke.com
2022.02.05

　次に、各表のテーブル定義書を作成し、それを元にAccessに読み込み、各表のテーブル定義書にしたがった各表のデータを貼り付けることにより、Accessに食品成分表の４つのテーブルを作成しました。

食品成分表（八訂）のテーブル定義書を作成し、Microsoft Acsessに読み込んでみよう！VBA備忘録（２）
食品成分表（八訂）のテーブル定義書の作成からAcessにデータを取り込むまで、DB(ACCDB)生成／テーブル定義取得ツールのダウンロードと使い方、食品成分表（八訂）のテーブル定義書の作成、作成したテーブル定義書をアクセスに読み込み、読み込んだ食品成分テーブルに実データの貼り込みなどついて紹介した記事です。
yanai-ke.com
2022.02.11

　そして、作成した４つの食品成分表（八訂）の間でリレーションを設定し、各表間で関連付けをし、簡単なテーブル（食品群テーブルマスター）を作成し、このテーブルと各食品成分表（八訂）の食品群コードとの間でリレーションを設定し、食品群コードを食品群名で表示できるようにしました。

食品成分表（八訂）間のリレーションの設定、Microsoft Access備忘録（１）
リレーショナルデータベース管理システム（RDMS）、リレーショナルデータベースの特徴、主キーについて、リレーションの設定、リレーションの確認などついて紹介した記事です。
yanai-ke.com
2022.02.22

　今回は、作成した食品成分表（八訂）から成分を、食品番号、食品名、エネルギー、タンパク質、脂質、炭水化物、食塩相当量などから検索して、検索結果を表示するフォームを作成します。

　なお、私のパソコン環境やソフトのバージョンは以下の通りです。

パソコンOS　：　Windows10 Pro
Windowsバージョン　：　21H1
Microsoft 365 Accessのバージョン　64ビット版：　2201

検索フォームを作成するためのクエリーデータの作成

　前回までに作成した食品成分表（八訂）を利用して、その中からあなたが好きな成分などを入れたクエリーを作成します。

　ここでは、本表のデータからエネルギー（KJ）などを除いたほぼ全てのデータと食品群名を入れたクエリーを作成してみます。

　前回までに作成した食品成分表（八訂）のテーブルのAccessを起動し、①上部メニューの「作成」⇒「クエリーウィザード」をクリックすると、新しいクエリーウィザードのウインドウが立ち上がりますので、②「選択クエリウィザード」を選択し「OK」をクリック、➂選択クエリウィザード画面が立ち上がりますので、テーブルから食品成分表（八訂、本表）を選択し、選択可能なフィールドから「>>」により全てのフィールドを選択フィールド内に入れ、入れたフィールドから必要のないエネルギ（KJ）などを戻し、「次へ」をクリック、➃食品群マスターテーブルを選択し、食品名フィールドを選択して「>」により、食品群コード（FoodGrCD)の次に挿入し「完了」をクリックすると、「食品成分表（八訂、本表）テーブル名クエリ」が作成されますので、ここではこのクエリー名を「QFoodcomptable」とします。

QFoodcomptableから分割フォームの作成

　作成したクエリー「QFoodcomptable」を選択した状態で上部メニューの「作成」⇒「その他のフォーム」⇒「分割フォーム」をクリックすると、上に「QFoodcomptable」の全フィールドが入った単票形式フォーム、下に表の形で表示（表形式）される、上下２段に分かれた分割フォームが作成さえます。

　このフォームは、上段の単票形式と下段の表形式が連動しており、下段の表形式のデータの左の好きなレコードを選択すれば、そのレコードの成分が上段に表示されるようになっています。

　上段の単票形式のフォームは、QFoodcomptableの全てのフィールドが上から順番に並んで２列に配置されており、表示されるフィールド名、その値などの入力欄の大きさはAccess側が勝手に作成したものとなっています。

　表形式の下にあるレコードの操作により食品成分表（八訂）の成分を順に送ったり「▶」、戻しらり「◀、最終成分（2478成分）を表示させたり「▶｜」、最初の成分にもどしたり「｜◀」することができます。

単票形式のフォームの見映えを整える

　上述で作成したフォーム名を「F_QFoodcomptable」とします。

フィールド名を分かり易い和名に変更

　フィールド名がテーブルを作成した段階の名前では分かりずらいので、和名に変更します。

　「F_QFoodcomptable」を選択して右クリックして表示されるメニューで「デザインビュー（D）」をクリックし、以前に作成したテーブル定義書を見ながらフィールド名を全て和名に変更します。

　変更の仕方は、デザインビューでフィールド名の上でクリックして、クリックした部分で元の英名⇒和名に変更ができます。

フィールド幅の調整

　「F_QFoodcomptable」を選択して右クリックして表示されるメニューで「レイアウトビュー（Y）」をクリックしフィールド幅とフィールドの値幅を次のように設定します。

　フィールド名の幅　：　2.6cm
　フィールの値　：　2.0cm　

　レイアウトビューで下図の赤で囲んだ部分（フィール名幅：2.6㎝）、フィールドの値幅（青で囲んだ部分：2.0㎝）に変更すると、その下のフィールドも全て変更されます。

フィールドを見易くするために移動して調整

　上述の図で空いている部分で右クリックして表示されるメニューで「デザインビュー（D)」をクリックし、一番上の行に索引番号、食品群コード、食品群名、食品番号を配置し、食品群名の値幅を6.0㎝とします。

　その下の行に、食品名を配置し、食品名の値幅を20㎝、高さ0.77㎝とします。

　食品成分を４列に配置し、最後の備考_本表のフィールド値幅を約1５cm、高さ0.7cmとします。

　以上のようにして作成した単票形式のフォームのフォームビューを以下に示します。

ヘッダーを広げ中に検索用のコンボボックスやテキストボックスの配置

ヘッダーを広げタイトルを変更

　「F_QFoodcomptable」を選択して右クリックして表示されるメニューで「デザインビュー（Y）」をクリックし、プロパティーシートから「フォームヘッダー」を選択し、高さを4.5㎝幅に設定しタイトルを「QFoodcomptable」⇒「食品成分フォーム」に変更すると共に、その左のファームの「ロゴマーク」を削除します。

検索用のコンボボックスやテキストボックスを配置

　上部メニューの「フォームデザイン」をクリックし、テキストボックスを選択して、配置していきます。

　始めに、コンボボックスを左上に配置し、その際の値集合を食品群マスターにし、高さ0.6cm、列数を２、列幅を1cm;4cm、幅を5cmとします。

　次に、テキストボックスを２つをその横に配置します。

　一つは、ラベルが食品番号、もう一つがラベルが食品名とし、それぞれのラベル、テキストボックスの高さを0.6cm、商品番号の値の幅３cm、食品名の値の幅を６cmとします。

　次に、エネルギー、たんぱく質、脂質、炭水化物、塩分相当量、予備のテキストボックスを配置します。

　ここでは、エネルギーを例にとって紹介します。

　テキストボックスを、コンボボックスの下に配置し、そのラベルは「エネルギー(kcal)」が入る幅とし、値幅は、1.5cm、その後にラベル「～」を入れ、その後にラベルなしのテキストボックス（幅1.5cm）を配置します。

　高さは、全て0.6cmとします。

　作成したエネルギーの検索用のテキストボックスを全て選択して、コピーして５つ貼り付けます。

　配置は、上の行にエネルギー、たんぱく質、脂質とし。その下の行に炭水化物、塩分相当量、予備を配置します。

　ラベルが分かりずらいので、背景を黒くし、文字を白にします。

　このようにして作成した画面が下図です。

コマンドボタンなどの配置

　上部メニューの「フォームデザイン」をクリックし、ボタンを２つ配置します。

　この際に「フォームデザイン」のコントロールの「コントロールウィザードの使用（W）」を切っておいて下さい。

　２つのボタンは、タイトル「食品フォーム」と書かれた横に配置し、一つのラベルは、「食品成分の検索」とし、もう一つは「クリア」とします。

　フォントの大きさは14ポイント、太さを中太とし、幅は適当に文字が表示される幅とします。

　次に、フォームデザイン」のコントロールの「コントロールウィザードの使用（W）」をONにして、ボタンを選択してクリアボタンの横でクリックすると「コマンドボタンウィザード」が立ち上がりますので、種類から「レコードの移動」、ボタンの動作から「先頭のレコードに移動」を選択して「次へ」をクリック、次のコマンドボタンウィザードで「ピクチャー」、「先頭へ」を選択して「次へ」をクリック、コマンド名に「先頭へ」を記入して、「完了」をクリックします。

　上述のことを、次に示すレコード関係の操作について行います。

  • 次のレコードに移動
  • 前のレコードに移動
  • 最後のレコードに移動
  • レコードの複写
  • アレコードの追加
  • レコードの削除

　コマンドボタンの順番は、あなた好みで決めて良いですが、ここでは、次のような順番としました。

　コマンドボタンの配置は「食品成分の検索」⇒「クリア」⇒「レコードの複写」⇒「レコードの追加」⇒「レコードの削除」⇒「先頭のレコードに移動」⇒「前のレコードに移動」⇒「次のレコードに移動」⇒「最後のレコードに移動」の順に配置しました。

　以上のように配置したヘッダー部分の画面を下図に示します。

食品成分の検索、クリアのコマンドボタンのVBAの作成

　複数条件での検索については、次の記事を参照して作成しています。

複数条件の抽出フォームの設計 その1
フォーム上に複数のフィールドに対する条件を入力するテキストボックスをそれぞれ配置して、その条件で抽出したい。条件未入力の...
hatenachips.blog.fc2.com

　そして、検索フォームの各フィールド名を次のようにします。

  • 食品群コード　：　cmb食品群コード
  • 食品番号　：　txt食品番号
  • 食品名　：　txt食品名
  • エネルギー　：　minエネルギー　、maxエネルギー
  • タンパク質　：　minたんぱく質　、maxたんぱく質
  • 脂　質　：　min脂質　、max脂質
  • 炭水化物　：　min炭水化物　、max炭水化物
  • 食塩相当量　：　min食塩相当量　、max食塩相当量
  • 予備　：　min_予備　、max予備　
    この予備は、他に何か検索したい項目があれば利用できるように残したもので、今回は利用しません。
  • minエネルギー　、maxエネルギー、minたんぱく質　、maxたんぱく質、min脂質　、max脂質、min炭水化物　、max炭水化物、min食塩相当量　、max食塩相当量、min_予備　、max予備のプロパティーシートの書式を「数値」にしておく。

　以上の設定で、「食品成分の検索」と「クリア」のコマンドボタンクリック時の動作として、次のVBAを割り当てます。

　割り当て方は、フォームの「デザインビュー」において、「食品成分の検索」のコマンドボタンを右クリックして、表示されるメニューで「イベントビルド（E）」クリックするとVBE（Visual Basicエディター）が立ち上がりますので、その中に次のコードを記入してください。

Option Compare Database
Option Explicit
Private Sub cmdFoodkensaku_Click()  '食品成分の検索ボタン
Dim strFilter As String
    If Not IsNull(Me.cmb食品群コード) Then
            strFilter = "FoodGriCD = '" & Me.cmb食品群コード & "'"
    End If
    If Not IsNull(Me.txt食品番号) Then
            strFilter = "FoodcompNO = '" & Me.txt食品番号 & "'"
    End If
    If Not IsNull(Me.cmb食品群コード) And Not IsNull(Me.txt食品名) Then
            strFilter = strFilter & " AND " & "Foodname Like '*" & Me.txt食品名 & "*'"
    Elseif IsNull (Me.cmb食品群コード) And Not IsNull(Me.txt食品名) Then
            strFilter = strFilter & "Foodname Like '*" & Me.txt食品名 & "*'"
    End If
    If Not IsNull(Me.minエネルギー) And Not IsNull(Me.maxエネルギー) Then
            strFilter = strFilter & " AND " & "ENERC_KCAL Between " & Me.minエネルギー & " AND " & Me.maxエネルギー
    End If
    If Not IsNull(Me.minタンパク質) And Not IsNull(Me.maxタンパク質) Then
            strFilter = strFilter & " AND " & "PROTen_g Between " & Me.minタンパク質 & " AND " & Me.maxタンパク質
    End If
    If Not IsNull(Me.min脂質) And Not IsNull(Me.max脂質) Then
            strFilter = strFilter & " AND " & "FATe_g Between " & Me.min脂質 & " AND " & Me.max脂質
    End If
    If Not IsNull(Me.min炭水化物) And Not IsNull(Me.max炭水化物) Then
            strFilter = strFilter & " AND " & "CHOCDF_g Between " & Me.min炭水化物 & " AND " & Me.max炭水化物
    End If
    If Not IsNull(Me.min食塩相当量) And Not IsNull(Me.max食塩相当量) Then
            strFilter = strFilter & " AND " & "NACL_EQ_g Between " & Me.min食塩相当量 & " AND " & Me.max食塩相当量
    End If
    If Not IsNull(Me.min予備) And Not IsNull(Me.max予備) Then
            strFilter = strFilter & " AND " & "???? Between " & Me.min予備 & " AND " & Me.max予備
    End If
    If strFilter = "" Then
        Me.FilterOn = False
    Else
        Me.Filter = strFilter
        Me.FilterOn = True
    End If
End Sub
Private Sub cmdClear_Click()  'クリアボタン
    Me.Filter = ""
    Me.FilterOn = False
    Me.cmb食品群コード = Null
    Me.txt食品番号 = Null
    Me.txt食品名 = Null
    Me.minエネルギー = Null
    Me.maxエネルギー = Null
    Me.minタンパク質 = Null
    Me.maxタンパク質 = Null
    Me.min脂質 = Null
    Me.max脂質 = Null
    Me.min炭水化物 = Null
    Me.max炭水化物 = Null
    Me.min食塩相当量 = Null
    Me.max食塩相当量 = Null
    Me.min予備 = Null
    Me.max予備 = Null
End Sub

　この食品成分表の検索フォームをフォームビューにして、色々試して見て下さい。

　次の検索が可能です。

【単独検索】

  • 食品群コードによる検索
    食品群コード（01～18）をコンボボックスから選択して検索すると、その食品群を表示
  • 食品番号による検索
    食品番号をテキストボックスに入れて検索すると、その食品番号の食品成分を表示
  • 食品名による検索
    食品名の一部の文字を入れると、その文字を含む食品成分を表示

【複数検索】

  • 食品群コード＋エネルギ―などの栄養素（５成分）で検索
    食品群コード、その他のエネルギーなどの栄養素（最小値から最大値を入れる）記入して検索すると、全ての条件を満たした食品成分を表示
  • 食品名＋エネルギ―などの栄養素（５成分）で検索
    食品名（食品に含まれる一部の文字を記入）、その他のエネルギーなどの栄養素（最小値から最大値を入れる）記入して検索すると、全ての条件を満たした食品成分を表示
  • 食品群コード＋食品名＋エネルギ―などの栄養素（５成分）で検索
    食品群コード、食品名（食品に含まれる一部の文字を記入）、その他のエネルギーなどの栄養素（最小値から最大値を入れる）記入して検索すると、全ての条件を満たした食品成分を表示

おわりに

　 如何だったでしょうか？

　検索フォームを作成するためのクエリーデータの作成、QFoodcomptableから分割フォームの作成、単票形式のフォームの見映えを整える、フィールド名を分かり易い和名に変更、フィールド幅の調整、フィールドを見易くするために移動して調整、ヘッダーを広げ中に検索用のコンボボックスやテキストボックスの配置、ヘッダーを広げタイトルを変更、検索用のコンボボックスやテキストボックスを配置、コマンドボタンなどの配置、食品成分の検索、クリアのコマンドボタンのVBAの作成などついて紹介してきました。

　次は、食品成分表（本表）を利用して食事の献立を作成し、その献立の各種栄養素の栄養価計算をしてみたいと思います。

　お楽しみに・・・。

以上です。

コメント

タイトルとURLをコピーしました