ACCESSでデーフル作成入力フォームで入力しながら、クエリのデータを参照したい

質問

ACCESSで売上明細テーブルを作成し、入力用のフォームを作成しました。他に顧客マスタテーブルがあります。売上明細には顧客CDフィールドがありますが、顧客名等はマスターに存在するため設けてません。しかし売上明細入力時に顧客CDを入力した時点で顧客名の確認がしたいのです。そこで入力フォームの顧客CDの隣に非連結でテキストボックスを作り、そこに顧客名を表示したいのです。売上明細には顧客名のフィールドは追加したくありません。ただ参照したいだけです。顧客マスタと売上明細で顧客CDをキーにクエリを作成し、明細に対応する顧客情報は顧客情報検索というクエリで作成済みです。入力フォーム画面の非連結のフィールドのプロパティのコントロールソースに
=DLookup("[顧客名]","顧客情報検索","[顧客CD] =" _ & Forms![売上明細入力Form]![顧客CD])
と表記しましたがエラーになります。
最初はコントロールソースに直接顧客情報検索クエリの顧客名フィールドを表記し、フォーカス取得時にクエリを開くマクロを実行していました。でもやはり表示されませんでした。
顧客名の設定をどのように設定すればいいのですか？
コントロールソースでの設定だけでは無理なのでしょうか？
よろしくお願いします。

回答No.4

DexMachina · ベストアンサー

ご質問の件では、アンダーバー「_」は無用です。
(VBAの編集画面上であれば、一群の文字列の改行表示のために使用することもありますが、
　コントロールソースへの直接記述には使用できません)
従って、以下のようにすれば、とりあえず今現在起きているエラーは解消するかと思います：

現在：
=DLookup("[顧客名]","顧客情報検索","[顧客CD] =" _ & Forms![売上明細入力Form]![顧客CD])

修正後：
=DLookup("[顧客名]","顧客情報検索","[顧客CD] =" & Forms![売上明細入力Form]![顧客CD])
または：
=DLookup("[顧客名]","顧客情報検索","[顧客CD] =" & [顧客CD])

*「顧客情報検索」がフォームのレコードソースで、もしも「顧客マスタ」に相当するテーブルがあるの
　でしたら、ここの式にはそちらを使用した方がよいはずです。

なお、もしも「顧客CD」のデータが数値型ではなくテキスト型などの場合は、
=DLookup("[顧客名]","顧客情報検索","[顧客CD] ='" & [顧客CD] & "'")
のように、Where条件式の右辺がシングルクォーテーションで括られるようにする必要があります。

※演算コントロール(=コントロールソースが関数のコントロール)で誤作動というのは、私には経験が
　　ありません。
　　但し、VBAでは、「Me」で、コードを記述しているフォームそのものを参照することができますが、
　　コントロールソースなど、プロパティシートに直接記述する関数では「Me」は使用できない、
　　といった制限はあります。

　　－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

なお、演算コントロールでは、検索や並べ替えができないといった制約があります。
同じDLookupを使うのであれば、フォームではなくクエリで演算フィールドを作成してコントロール
ソースとして使用した方が、検索・並べ替えが行えるので使い勝手はよいと思います。
(「売上明細には顧客名のフィールドは追加したくありません」とのことですが、テーブルではなく
　クエリのみへのフィールド追加なので、問題ないのではないかと)

演算フィールドは、クエリのデザインビューの「フィールド:」欄に、
顧客: DLookup("[顧客名]","顧客マスタ","[顧客CD]=[顧客マスタ]![顧客CD]")
といったように入力します(「テーブル:」欄は空白のまま)。

*上記の式は、「顧客情報検索」がクエリで、「顧客CD」フィールドが「顧客マスタ」テーブルの
　「顧客CD」フィールドに拠っている、という前提で作成しました。

お礼

毎度的確な回答をありがとうございます！DexMachinaさんを師匠と呼びたいくらいです！VBAで_（アンダーバー）は改行するときに使用するといったことはEXCELのVBAで実際使っていました。ACCESSって別物のような感があって、これはACCESSのHELPで参照したものをそのまま使ってしまいました。少し考えるとわかりそうなのに・・・単純なことですみません。それからクエリで演算フィールドを作成してみます。本当に細かなところまで説明とてもありがたいです！

回答No.5

noname#140971

お詫びと訂正:

あたかもレコードセットでは関数を利用できないかの回答をキャンセルします。
これは、よくよく考えれば、正しくないです。
１０年前のごくごく一部のバージョンのバグが根拠でしかないのですから。
クエリに関数を仕組むとフリーズするというのも今や昔のことかも知れません。
「VBAで書かないと何が起きてもおかしくない」－これは、もはや謬論かも知れません。
訂正しておきます。

お礼

わざわざ訂正までしていただいて恐縮します！重ねて感謝いたします!!

回答No.3

noname#140971

補足：

コントロールソースに関数を使うとバージョンでは誤動作することが確認されています。
私は、そういうことで、敢えて、VBA で記述しています。

回答No.2

noname#140971

<得意先マスター>
ID___顧客名
1____鈴木 一郎
2____中村 主水

<売上明細>
ID___伝票番号___得意先マスター_ID
1____101________1
2____102________2

こういうテーブル構造を仮定します。
当然にフォーム＜売上明細＞には非連結の[得意先名]を配置して確認。
この場合は、質問者が予定しているように DLookup関数を使います。
が、それは VBA にてです。

Private Sub Form_Current()
　　Me.顧客名 = DLookup("顧客名", "得意先マスター", "ID=" & Me.得意先マスター_ID)
End Sub

Private Sub 得意先マスター_ID_AfterUpdate(Cancel As Integer)
　　Me.顧客名 = DLookup("顧客名", "得意先マスター", "ID=" & Me.得意先マスター_ID)
End Sub

コントロールソースには含まれていないので、これは当然のこと。
また、Form_Current()と得意先マスター_ID_AfterUpdate()の両方に[顧客名]更新のコードを書くのも当然。

さて、問題は、ここで使っている DLookup関数が実にトロイ。
１クッションを置いて表示が更新されるので実に始末に悪いです。
そこで、ここは代行関数を自作するということになります。
次の DBLookup関数を利用すると DLookup関数の難点も解消されると思います。

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

　　 Set rst = New ADODB.Recordset
　　 strQuerySQL = "SELECT " & strField & " FROM " & strTable
　　 If Len(strWhere) > 0 Then
　　　　 strQuerySQL = strQuerySQL & " WHERE " & strWhere
　　 End If
　　 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 = Nz(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

お礼

ご丁寧な回答ありがとうございます。VBAをこれから学習したいと思っていて、参考書を読み始めているところです！なので今はできる限りマクロやクエリーのみで処理しようとしていました。でもこれからやっていく上でとても参考になりました！本当にありがとうございました！

回答No.1

CHRONOS_0

いろんな方法がありますがDLookupは一番まずい方法ですね

一番簡単なのはフォームのソースを両テーブルを結合したクエリにすることです
これで顧客マスタの全フィールドを確認することができます
ただし、顧客マスタのフィールドには編集ロックを書けておかないと
このフォームからいじれてしまいます

他には顧客CDをコンボから選ぶようにする方法です
顧客CD以外の値はこんぼのColumnプロパティーで求められます

お礼

回答ありがとうございます！Dlookupはまずいのですか・・・でもこの方法だと本当に簡単ですね！編集ロックをかけるといいのですよね！ありがとうございます！