ACCESSで給付金等の支給事務をするときの口座情報を入力するフォームの作成！金融機関コード・支店コード・金融機関名・支店名から相互に検索できるようにする！

へっぽこヘタレシステム管理者の管理人です。

さて、仕事をしていると・・・

小規模な給付金の支給事務等で、簡単なデータベースを内製する場合が割とあります。

エクセル等でやる方法もあるのですが・・・

管理人は、データベース的なモノを作成する場合は、必ずエクセルではなく、アクセスを使うようにしています。

これは・・・エクセルの場合では、生データが直で触れるので・・・

意図せず手がキーボードに触れた場合に、データが誤入力されたりする場合があるためです。

そうならないようにフォームを作る方法もあるのですが・・・

フォームを作るのであれば、エクセルよりもアクセスの方がはるかに簡単です。

SQLを簡単に記述できるのもアクセスのメリットです。

話はそれましたが・・・

給付金の支給事業等では・・・

必ず、口座情報の入力が必要となりますが・・・

振込データを作成するには・・・

金融機関コードと支店コードが必須となりますが・・・

一般的には、コードを書く欄がありませんので・・・

フォームに金融機関名を入力すると、自動的に金融機関コードが表示されて入力されるようにする必要があります。

また、支店コードも同様ですし・・・

反対に、金融機関コードから金融機関名を逆引きできる必要もあります。

作ろうと思うと・・・・これが案外難しい・・・

結構悩みました・・・

このフォームでは・・・

金融機関CDからでも金融機関名からでも相互に入力することができます。

同様に、支店CDからでも支店名からでも相互に入力可能です。

入力のベースとなる全国の口座一覧は・・・

ネット等から入手します。

金融機CD・金融機関名・支店CD・支店名の単なる一覧表です。

同様に入力するテーブルも・・・

金融機CD・金融機関名・支店CD・支店名の単なる一覧表です。

ここからは、本命の入力フォームの作成です・・・

管理人も試行錯誤の上、作成しましたが、これが正解のなのか分かりませんが・・・

意図する動作は出来ています。

入力するメインフォームに設定するテーブルは・・・

当たり前ですが【金融機関を参照する】テーブルではなく・・・

【入力先】のテーブルをしています。

フォームの各入力項目

金融機関コード
支店コード
金融機関名
支店名

は、それぞれコンボボックスとして、金融元のテーブルからＳＱＬを駆使して表示させるようにします。

まず【金融機関コード】ですが・・・

同一の金融機関コードには、何個も支店コードが引っ付いていいますので・・・

とにかく、金融機関コードを重複を排除して表示させる必要がありますので・・・

金融機関キードのコンボボックスに設定する【集合値ソース】にSQLを仕込みます。

SELECT DISTINCT KZF_BNK.BNK_HON_CD, KZF_BNK.BNK_HON_NAME
FROM KZF_BNK
ORDER BY KZF_BNK.BNK_HON_CD;

【DISTINCT】を使い、金融機関コードと金融機関名の重複を排除して選択できるようにします。

次に、更新後処理として、SQLを走らせます。

Private Sub bnk_hon_cd_AfterUpdate()

    Set db1 = CurrentDb
    Set query1 = db1.QueryDefs("kzf_query")
    
    sql1 = " select "
    sql1 = sql1 & " distinct AA.bnk_hon_cd, AA.bnk_hon_name "
    sql1 = sql1 & " from "
    sql1 = sql1 & " KZF_BNK AA"
    sql1 = sql1 & " where "
    sql1 = sql1 & " AA.bnk_hon_cd =  '" & Me!BNK_HON_CD & "' "
    
    query1.SQL = sql1

  
    Me.BNK_HON_NAME = DLookup("bnk_hon_name", "kzf_query")
    Me.Refresh
    
End Sub

このSQLで、選択した金融機関コードから金融機関名を一意に選択するクエリを作成します。

一意に選択するには【DISTINCT】を使います。

さらに、フォームの金融機関名に【DLOOKUP】関数を使って、金融機関名を入力させます。

次に金融機関名からの金融機関コードの逆引きですが・・・

こちらも同様にコンボボックスの【集合値ソース】にSQLを仕込みます。

SELECT DISTINCT KZF_BNK.BNK_HON_NAME, KZF_BNK.BNK_HON_CD
FROM KZF_BNK
ORDER BY KZF_BNK.BNK_HON_CD;

【DISTINCT】を使い、金融機関コードと金融機関名を【DISTINCT】を使い、重複を排除して選択できるようにします。

次に、金融機関コードの更新後処理と同様に・・・

金融機関名の更新後処理として、SQLを走らせます。

Private Sub bnk_hon_name_AfterUpdate()

    Set db1 = CurrentDb
    Set query1 = db1.QueryDefs("kzf_query")
    
    sql1 = " select "
    sql1 = sql1 & " distinct AA.bnk_hon_cd, AA.bnk_hon_name "
    sql1 = sql1 & " from "
    sql1 = sql1 & " KZF_BNK AA"
    sql1 = sql1 & " where "
    sql1 = sql1 & " AA.bnk_hon_NAME =  '" & Me!BNK_HON_NAME & "' "
    
    query1.SQL = sql1

    
    Me.BNK_HON_CD = DLookup("bnk_hon_CD", "kzf_query")
    Me.Refresh
End Sub

このSQLで、選択した金融機関名から【DISTINCT】を一意に金融機関コードを選択するクエリを作成します。

さらに、フォームの金融機関名に【DLOOKUP】関数を使って、金融機関コードを入力させます。

ブログランキングにご協力ください！

次は支店コードです・・・

支店コードのコンボボックスのSQLについては、次のとおりです。

SELECT KZF_BNK.BNK_SIT_CD, KZF_BNK.BNK_SIT_NAME
FROM KZF_BNK
WHERE (((KZF_BNK.BNK_HON_CD)=[Forms]![koza_input_2]![bnk_hon_cd]))
ORDER BY KZF_BNK.BNK_SIT_CD;

【Where】で金融機関コードを指定して、選択した金融機関コードから該当する支店一覧を選択して・・・

支店コードのコンボボックスの【集合値ソース】とします。

同様に、支店コードの更新後処理として・・・

次のSQLを走らせます。

Private Sub bnk_sit_cd_AfterUpdate()

    Set db1 = CurrentDb
    Set query1 = db1.QueryDefs("kzf_query")
    
    sql1 = " select "
    sql1 = sql1 & " AA.bnk_SIT_cd, AA.bnk_sit_name "
    sql1 = sql1 & " from "
    sql1 = sql1 & " KZF_BNK AA"
    sql1 = sql1 & " where "
    sql1 = sql1 & " AA.bnk_hon_cd =  '" & Me!BNK_HON_CD & "' "
    sql1 = sql1 & " and AA.bnk_sit_cd =  '" & Me!BNK_SIT_CD & "' "
    
    query1.SQL = sql1

    
    Me.BNK_SIT_NAME = DLookup("bnk_sit_name", "kzf_query")
    Me.Refresh
    
End Sub

入力された、金融機関コードと支店コードから、支店名をユニークに特定して・・・

【DLOOKUP】を使って、支店名に入力させます。

支店名から支店コードの逆引きに関しては・・・

こちらも同様に、支店名のコンボボックスの【集合値ソース】にSQLを仕込みます。

SELECT KZF_BNK.BNK_SIT_NAME, KZF_BNK.BNK_SIT_CD
FROM KZF_BNK
WHERE (((KZF_BNK.BNK_HON_CD)=[Forms]![koza_input_2]![bnk_hon_cd]))
ORDER BY KZF_BNK.BNK_SIT_CD;

入力された、金融機関コードから、支店名の一覧表を選択します。

同様に支店名の更新後処理として、次のSQLを走らせます。


Private Sub bnk_sit_name_AfterUpdate()

    Set db1 = CurrentDb
    Set query1 = db1.QueryDefs("kzf_query")
    
    sql1 = " select "
    sql1 = sql1 & " AA.bnk_SIT_cd, AA.bnk_sit_name "
    sql1 = sql1 & " from "
    sql1 = sql1 & " KZF_BNK AA"
    sql1 = sql1 & " where "
    sql1 = sql1 & " AA.bnk_hon_cd =  '" & Me!BNK_HON_CD & "' "
    sql1 = sql1 & " and AA.bnk_sit_name =  '" & Me!BNK_SIT_NAME & "' "
    
    query1.SQL = sql1
   
    Me.BNK_SIT_CD = DLookup("bnk_sit_cd", "kzf_query")
    Me.Refresh
    
End Sub

入力された、金融機関コードと支店名から、支店コードを特定して・・・

【DLOOKUP】で支店コードを入力させます。

仕組みとしては以上となります。

アクセスのサンプルデータベースのダウンロードはこちらから

お試しは、Access Runtimuの2013で使用できます。

編集も場合は、アクセスのフルバージョンの2010以上で使用できます。

もう少しスマートなやり方があるのかもしれませんが・・・

管理人のレベルではこれしか思い浮かびませんでした。