Access マクロについて

質問

Accessのマクロについて教えてください。

仮テーブルに
管理コード（オートナンバー）
「あ」（Yes/No型）
「判定」（短いテキスト）
があります。

仮テーブルを元に作成した、Aフォームで「あ」チェックボックスがTrueだったら「判定」フィールドに”OK”
という文字を

上記のような処理はマクロではできないのでしょうか。
アクションカタログを見てもチェックボックスが〇〇だったら（IF）、までしか作成できないので。
フィールドに書き込むことはマクロでは難しいのでしょうか。

すみませんが、よろしくお願いします。

回答No.8

山田 太郎（@f_a_007）

自動連番モジュールの実用化に向けて（３）

　１と２で事前準備は完了。いよいよ、本番作業を行う段階ですが、その為には、私が、《テーブル構造》を正確に知る必要があります。

ID・・・・・・・長整数（主キー）
A管理番号・・・長整数
B管理番号・・・長整数
C管理番号・・・長整数
区分・・・・・・整数

　一番大事なことは、全てのテーブルの冒頭に'ID'と言う名前の主キーが存在するのかどうか？で、添付図のような命名規則を採用することを勧めておきます。これですと、[管理台帳]と[商品台帳]は、[管理台帳]![商品台帳_ID]と[商品台帳]![ID]とでリレーションしていることが一目瞭然です。

【なぜ、主キー列[ID]が必要なのか？】

理由１、リレーショナルデータベースには必須の列だから。
理由２、入力順位等を知る唯一の手掛かりだから。

　質問者は、入力データは、常に入力順で《入力フォーム》に呼び出されると思っているかも知れません。が、それは大間違いです。クエリーを開いてテーブルを並び変えると、その順番が《入力フォーム》にも反映されます。ですから、《入力フォーム》では必ず[ID]列降順で呼び出す必要があります。

【なぜ、主キー列[ID]は長整数なのか？】

　これは、《オートナンバー型》では、テーブルを復旧できないからです。《オートナンバー型》は、管理者が数字を打ち込むことを拒否します。つまり、管理者の制御を拒むのです。ここんところが、最大の問題点です。で、なぜ初心者の多くが、長整数ではなくて《オートナンバー型》を採用しているのか？それは、《採番方法》が判らないからです。で、「判らんから任せてしまえ！」という判断をしているのです。

　で、先ずは、[管理台帳入力フォーム]で[ID]を採番する方法を確立しませんか？一度、知ってしまえば、後は、それを踏襲するだけです。で、それは、超簡単。

　これから先は、質問者と同じテーブルと入力フォームを用意して、同時作業を行います。ですから、正確なテーブル名と入力フォーム名も教えて下さい。表構造は、冒頭に書いた列の他に二つ、三つあれば十分です。

　では、明日！

補足

f_a_007さん

親切に教えていただいているのに連絡できていなくてすみません。子供たちが元気でパソコンに向かう時間がないので、
明日の午前中にテーブルの詳細送ります。すみません。

回答No.7

山田 太郎（@f_a_007）

Public Function DBSelect(ByVal strQuerySQL As String, _
　　　　　　　　　　　　 Optional colDelimita As String = ";", _
　　　　　　　　　　　　 Optional rowDelimita As String = ";") As String
On Error GoTo Err_DBSelect
　　Dim R　　　　　 As Integer ' 行インデックス
　　Dim N　　　　　 As Integer ' 行総数 - 1
　　Dim rst　　　　 As ADODB.Recordset
　　Dim fld　　　　 As ADODB.Field
　　Dim strList　　 As String　' 全てのデータを区切子で連結して格納

　　Set rst = New ADODB.Recordset
　　With rst
　　　　.Open strQuerySQL, _
　　　　　　　CurrentProject.Connection, _
　　　　　　　adOpenStatic, _
　　　　　　　adLockReadOnly
　　　　If Not .BOF Then
　　　　　　N = .RecordCount - 1
　　　　　　.MoveFirst
　　　　　　For R = 0 To N
　　　　　　　　For Each fld In .Fields
　　　　　　　　　　With fld
　　　　　　　　　　　　strList = strList & .Value & colDelimita
　　　　　　　　　　End With
　　　　　　　　Next fld
　　　　　　　　strList = Mid(strList, 1, Len(strList) - 1) & rowDelimita
　　　　　　　　.MoveNext
　　　　　　Next R
　　　　Else
　　　　　　strList = ""
　　　　End If
　　End With
Exit_DBSelect:
On Error Resume Next
　　rst.Close
　　Set rst = Nothing
　　DBSelect = IIf(Len(strList) > 0, Replace(strList & "[END]", rowDelimita & "[END]", ""), "")
　　Exit Function
Err_DBSelect:
　　MsgBox "SELECT 文の実行時にエラーが発生しました。(DBSelect)" & Chr(13) & Chr(13) & _
　　　　　　"・Err.Description=" & Err.Description & Chr(13) & _
　　　　　　"・SQL Text=" & strQuerySQL, _
　　　　　　vbExclamation, " 関数エラーメッセージ"
　　Resume Exit_DBSelect
End Function

補足

f_a_007さん

ありがとうございます。
今(2)の課題完了しました。
テーブルの情報が更新されるようになって感動しております。

回答No.6

山田 太郎（@f_a_007）

自動連番モジュールの実用化に向けて（２）

【課題１】CnnExecute()の標準モジュールへの追加

　この関数も、1996年にADOが登場した際に書いた関数です。当時は、開発オフィスを購入すると、詳細なADO関数の作成要領等の解説書も一緒に付いていました。その後、そういうのは廃止されて今日に至っています。ですから、今の開発者がADOについて学ぶのは至難。パラパラとめくれば知ることが出来たことも、容易には知ることは不能。まあ、そういう意味では、これまた年代ものの関数です。

　確かに、マイクロソフトは、その後、DoCmd.RunSQL などをサポートしています。が、それは使えません。なぜなら、SQLを発行した際にエラーが出た場合の処理をするには不適だからです。エラー対策がなされていないアプリケーションは、ユーザーを混乱させる迷惑アプリ。ですから、どうあっても、DoCmd.RunSQL に相当する関数を用意する必要があります。

　では、今日は、次の二つの関数の追加をお願いします。

Public Sub ErrMessage(ByVal CnnErrors As ADODB.Error, ByVal strSQL As String)
　　MsgBox "ADOエラーが発生しましたので処理をキャンセルします。" & Chr$(13) & Chr$(13) & _
　　　　　 "・Err.Description=" & CnnErrors.Description & Chr$(13) & _
　　　　　 "・Err.Number=" & CnnErrors.Number & Chr$(13) & _
　　　　　 "・SQL State=" & CnnErrors.SQLState & Chr$(13) & _
　　　　　 "・SQL Text=" & strSQL, _
　　　　　 vbExclamation, " ADO関数エラーメッセージ"
End Sub

Public Function CnnExecute(ByVal strSQL As String) As Boolean
On Error GoTo Err_CnnExecute
　　 Dim isOK As Boolean
　　 Dim cnn　As ADODB.Connection

　　 isOK = True
　　 Set cnn = CurrentProject.Connection
　　 With cnn
　　　　 .Errors.Clear
　　　　 .BeginTrans
　　　　 .Execute strSQL
　　　　 .CommitTrans
　　 End With
Exit_CnnExecute:
On Error Resume Next
　　 cnn.Close
　　 Set cnn = Nothing
　　 CnnExecute = isOK
　　 Exit Function
Err_CnnExecute:
　　 isOK = False
　　 If cnn.Errors.Count > 0 Then
　　　　 ErrMessage cnn.Errors(0), strSQL
　　　　 cnn.RollbackTrans
　　 Else
　　　　 MsgBox "プログラムエラーが発生しました。" & _
　　　　　　　　"システム管理者に報告して下さい。(CnnExecute)", _
　　　　　　　　vbExclamation, " 関数エラーメッセージ"
　　 End If
　　 Resume Exit_CnnExecute
End Function

　ここまでで、お気付きでしょうが、標準モジュールに登録する関数ってのは、Access がサポートしている関数と同じ役目を担うもの。一人ひとりの開発者は、そのコードについて無知であっても何ら構いません。マイクロソフトが提供する Len() のコードなんて誰も知らないでしょう。それと一緒で、CnnExecute()の中身なんて開発者は知る必要はないんです。ただ、CnnExecute()の製作者である質問者の責任は、重大です。ですから、どんなに面倒ではあっても、CnnExecute()をエラートラップなしの14行で書いてはいけません。共通ライブラリとも言うべき関数には、エラートラップは不可欠です。僅かに14行で書けるCnnExecute()が、かくも複雑なのはそういうことです。

【課題２】CnnExecute()で遊ぶ

　先ずは、添付図と同じことをやって下さい。Access でアプリを開発を試みる者は、SELECT文、INSET文、UPADATE文、DELETE文に無知であっては、何も出来ません。なんたって、Access は《SQL データベース》ですから・・・。なお、遊ぶに際しては、DBSelect()も必要。これは、後程送ります。

　では、後は、明日の心です。

　遠回りしていますが、後３、４回で自動連番システムが完成しますので頑張って下さい。こちらは、あのコードを書くことなく実現する方法を今から考えます。

回答No.5

山田 太郎（@f_a_007）

自動連番モジュールの実用化に向けて（１）

課題１、DBLookup()の標準モジュールへの登録。

《なぜ、Access 標準の DLookup()を利用しないのか？》

理由１、 DLookup()には、SQL文を書けない。

　DLookup()がサポートしているのは、

＞Select [列名] FROM [テーブル名] WHERE [条件]

という構文のみ。具体的には、次のような形でしか用いることができない。

　DLookup([列名],[テーブル名],[条件])

　つまりは、全く役に立たない代物だってことです。ですから、ちょっと複雑な SQL文の実行結果を取得するには、独自の DBLookup()関数の用意は必要不可欠です。

理由２、DLookup()は、遅い。

　これは、テストして見れば分ることですが、 DLookup()のADODBバージョンは、DLookup()の３倍速で動きます。そういうことで、自動連番モジュールの実用化に向けての一回目の作業は、

１、DLookup()の標準モジュールへ登録する。
２、DLookup()が動作することを確認する。

の二つです。《自動連番モジュールの実用化》は、このようにステップ　バイ　ステップで進めます。

【DLookup()の標準モジュールへ登録要領】

　これは、以下のモジュールを標準モジュールにコピペするだけです。この関数は、1996年にADOが登場した際に書いた関数です。その後、多くのアプリでの使用実績があります。安心して利用されて下さい。

Public Function DBLookup(ByVal strQuerySQL As String, Optional ReturnValue As Variant = "") As Variant
On Error GoTo Err_DBLookup
　　Dim DataValue
　　Dim rst As ADODB.Recordset

　　Set rst = New ADODB.Recordset
　　With rst
　　　　.Open strQuerySQL, _
　　　　　　　CurrentProject.Connection, _
　　　　　　　adOpenStatic, _
　　　　　　　adLockReadOnly
　　　　If Not .BOF Then
　　　　　　.MoveFirst
　　　　　　DataValue = .Fields(0)
　　　　End If
　　End With
Exit_DBLookup:
On Error Resume Next
　　rst.Close
　　Set rst = Nothing
　　DBLookup = IIf(Len(DataValue & ""), DataValue, ReturnValue)
　　Exit Function
Err_DBLookup:
　　MsgBox "SELECT 文の実行時にエラーが発生しました。(DBLookup)" & Chr$(13) & Chr$(13) & _
　　　　　 "・Err.Description=" & Err.Description & Chr$(13) & _
　　　　　 "・SQL Text=" & strQuerySQL, _
　　　　　 vbExclamation, " 関数エラーメッセージ"
　　Resume Exit_DBLookup
End Function

【DLookup()の標準モジュールの動作環境の設定】

　これは、簡単。添付図の一番下のが示すように[ツール]-[参照設定]で《Microsoft ActiveX Data Obeject 2.1 Library》にレ点を付けるだけ。オブジェクト宣言すれば、これは不要です。が、今回は、これで行きます。

【DLookup()を実際に使ってみる】

　自動連番モジュールの実用化に向けて（１）の最後の作業は、[イミディエイトウインドウ]で実際に使ってみること。添付図のような、簡単なテストをしてみて下さい。

　では、全ての作業が終わったら、連絡をください。そうしたら、自動連番モジュールの実用化に向けて（２）に進みます。

補足

f_a_007さん

おはようございます。
細かく教えていただき、ありがとうございます。
(1)完了しました。

回答No.4

山田 太郎（@f_a_007）

＞DBLookupにエラーが出ました。DBLookupをLookup?に変えると・・・

　ギャーッ、Access　の初心者も初心者。超超初心者じゃーないですか？それじゃー、全く、話は通じませんよ。だが、僅か後数回の応答で万事100%解決です。事情が、よーく分かりましたから。これ、返信は、不要。今から、術後の経過を看て貰うために病院に行きます。詳しくは、そのから。

　　　　　　　　　　　　　　　　【返信無用】

回答No.3

山田 太郎（@f_a_007）

ウーン！プログラムレスで一斉に連番を打つ唯一の方法は、先の回答以外にはないと思いますよ。で、入力時に判定して連番をカウントして記録するのは、ただ１行のコードを書くだけ。だが、この致命的な欠陥は、次のようです。

１回目の入力・・・連番をカウント　190001
２回目の入力・・・連番をカウント　190002
３回目の入力・・・連番をカウント　190003

＞あっ、間違った！

で、２回目の入力を修正。で、２番目の連番は、190002でなくて0に。結果、

190001
190003

という連番が発生します。これをプログラムなしで防ぐには、一日の適当な時期に、先の一斉連番を打つコードを走らせること。で、多分、あのコードを少し書き換えれば、質問者にも理解可能。ただ、それでも大きな疑問がありますね。それは、B、Cの連番は一体どうなっているのだということ。

　だとしても、王道は、プログラムレス連番方式を完成させるべきではないでしょうか？あれって、本当の意味でプログラミングとは言えませんよ。あそこに書いたのは、《連番マクロ》です。ですから、全く、何の修正もなく動かなきゃ―いけないもの。当方では、完全に動いていますから、動かない筈はないんです。ですから、なぜエラーがでるのか？そこんところを知るのが先だと思いますよ。

＞本マクロは、質問者の環境に応じて書き換えて下さい！

を撤回して、私の方で書き換えますから、再挑戦しませんか？それが、いいですよ。それと、BとCとは、どうなっているんですか？

返事を待ちます。

補足

そうですよね。もう一度前回の方法でトライしてみます。
ありがとうございます。

ど素人ですが、お付き合いください。申し訳ありません。

前回頂いたコードを私のAccessファイルで実行すると
DBLookupにエラーが出ました。DBLookupをLookup?に変えると
コード先頭のPrivate Subが黄色に変わりました。
黄色に変わるということは何か問題あるのかなと思い、中断しました。
私のAccessファイルでは多少ファイル名が違うだけなんですが…

コードの挿入場所はフォームのプロパティから閉じるとき→コードビルダーで作成しました。

もともとこのAccessの原点は
3つほどのExcelファイルでした。
一つは受注があったときに客先や対象になる機械の情報を管理する　受付管理台帳
二つ目は国際規格の検査を受注順にトラッキングしていく国際検査台帳
三つ目は国内規格の検査を受注順にトラッキングしていく国内検査台帳です。

この三つを一つのファイルで管理したい、となったことがきっかけです。

回答No.2

山田 太郎（@f_a_007）

Q、フィールドに書き込むことはマクロでは難しいのでしょうか？
A、難しいとか易しいとかではなくて邪道。そして、無用。

なぜなら、

[あ]フィールド=[判定]フィールド

だから。同じ値なら、[判定]フィールドは、自在に生成できます。よって、テーブルに同じデータを重複して保存するのは、ルール違反ですよ。

　自在に生成の意味については、添付図を眺めて考えて下さい。なお、マクロでテーブルのデータを操作するのは原則禁止ですよ。

補足

f_a_007さん

いつも親切に教えていただきありがとうございます。
前回教えていただいた採番の方法ですが、私の力量ではエラーが出て
うまく使いこなせない為、もう少しアナログ的なやり方に
しようかと思いまして（何かあったときにすぐに自分で修正できるよう）

上の例だと「あ」のチェックボックスがTrueの時OKという処理ですが、
実際実現したいのは「あ」のチェックボックスにチェック(True)を入れて、フォームを閉じると、フォームの判定テキストボックスに1が追加されるという処理にしたいです。

フォームを開いて閉じる度に数字が１づつカウントされていき、採番されるような仕組みにしたいです。フォームを開くときは常に新しいレコードが追加される状態です。

現時点でマクロ単体だと動くのですが、フォームを閉じるときに実行するとエラーが出て(2950/2448)実行できません。

この方法も邪道でしょうか。
せっかくアドバイス頂いたのにできなくてすみません。

回答No.1

panacon

できますよ。マクロを作るときに全てのマクロを表示するをONにして、表示されるマクロメニューの中から値の代入を選択します。値の代入では、代入されるテキストボックス名「判定」と代入する値「”OK"」を入れます。このマクロに名前をつけて保存後に、「あ」のフィールドの更新後処理にこのマクロを当てれば動きます。

補足

回答ありがとうございます。
値の代入について教えてください。

アイテムは代入する先のフィールド名でしょうか、

この場合
アイテム　「あ」それとも（"[あ]", "仮テーブル”）でしょうか
式　OK

よろしくお願い致します。