【Access】できるならいいけど…もっとスマートにできないか

Accessのフォームとは通常、レコードソースを指定し、ある程度きれいなかたちに整えてデータを表示させるものです。

ですがマスターテーブルをそのまま表示させると、ユーザーはテキストボックスからデータを容易に変更できます。猫がキーボードに乗って歩きまわると「あｔ；あｓちあがｋｂ；あｌｊｈ」などとテーブルに書き込まれてしまいます笑。マスターデータはマスターというだけありデータベースの根幹を成すものですので、こんな事態になったら取り返しがつかなくなります。脳を直接いじらせるようなものです。

テキストボックスを編集不可にすればいいのではないか。ではユーザーが編集したくなったらどうするのか…。

ということで、ある程度Accessを使い慣れてくると、フォームにマスターテーブルを使用するのはマズい、とわかってきます。

そこで対策として、ワークテーブルを用意し、任意のSQLを発行して書き込み、フォームのレコードソースに指定する、という方法を採ります。

ADOなら、

'ワークテーブルを削除。

strSQL = "DELETE * FROM tbl_WT_表示用

cnc.Execute strSQL

'「tbl_WT_表示用」にデータを入力。

strSQL = "SELECT 猫ID, 猫氏名, 猫カラー, …, 猫尻尾の毛並み FROM tbl_MT_猫"

rs1.Open strSQL, , , , adCmdText

rs3.Open "tbl_WT_表示用", , , , adCmdTable

Do Until rs1.EOF

    rs3.AddNew

        rs3!猫ID = rs1!猫ID

        rs3!猫氏名 = rs1!猫氏名

        rs3!猫カラー = rs1!猫カラー

        …

        rs3!猫尻尾の毛並み = rs1!猫尻尾の毛並み

    rs3.Update

    rs1.MoveNext

    DoEvents

Loop

'フォームのレコードソースにセット。

DoCmd.OpenForm "frm_猫フォーム"

strSQL = "SELECT * FROM tbl_WT_表示用"

Forms("frm_猫フォーム").RecordSource = strSQL

Forms("frm_猫フォーム).Requery

とできますが、この書き方ですとrs3.AddNew以下の行数がテキストボックスの数だけ増えていきます。

せっかくなので、

    rs3.AddNew

        fieldNo = 0

        Do Until fieldNo > rs1.Fields.Count - 1

            fieldName = rs1.Fields(fieldNo).Name

            rs3(fieldName).Value = rs1(fieldName).Value

            fieldNo = fieldNo + 1

        Loop

    rs3.Update

とすれば仮に1,000個増えてもコードを追加せずに済みます。各テーブルのフィールド名を一致させる必要がありますが。

これでフォームから編集できるのはワークテーブルのデータということになります。いわばクローンの脳です。いじりまくっておかしくなっても「再表示」などのボタンで再び同じコードを呼び出せば元通りです。脳の例えは倫理的によくありませんでしたが笑。

しかしフォームが増えるとワークテーブルの数も増えるので、これはこれで問題があります。テーブルがずらーっと並ぶと目がチカチカしてきます。

だったらワークテーブルを使わず直接書き込めばいい、ということで次。非連結のテキストボックスが並んだフォームに用意し、VBAでデータを入力します。

strSQL = "SELECT 猫ID, 猫氏名, 猫カラー, …, 猫尻尾の毛並み FROM tbl_MT_猫"

rs1.Open strSQL, , , , adCmdText

For Each ctl In Forms("frm_猫フォーム").Controls

    If ctl.Name Like "テキスト*" Then

        fieldName = Replace(ctl.Name, "テキスト", "")

        Forms("frm_猫フォーム").Controls(ctl.Name).Value = rs1(fieldName).Value

    End If

Next

Forms("frm_猫フォーム").Requery

なんとこれだけで済んでしまう！　テキストボックスが10,000個あってもOK！　しかもワークテーブルいらずです。

非連結のテキストボックスはマスターテーブルと紐付いていないので、たとえ配偶者がキーボードの上を歩きまわったとしても「再表示」で問題なしです。ユーザーは好きなように編集して、「更新」ボタンを押す。あとはエラーチェックですね。

Access覚え立てのうちは「できればいい」、しかし学んでいくうちにもっと効率のいい方法を発見できます。これはとりもなおさず自分のためでもあったりします。久々に見る長ーいソースコードはウンザリしますからね。

はじめてブログでまともな記事を書いた気がします笑。