Accsess2003にて２回目のデータ表示がおかしい

質問

助けてください!!
テーブルに会員データ以外に支部テーブル、部会テーブルがあります。
部会テーブルには、支部コード,部会コード,部会名があります。
会員テーブルには、支部コード,部会コードはありますが、それぞれの名称はありません。画面表示には、各名称を表示するようにしています。
１回目の読込は、通常に表示しますが、２回目に別会員を読み込むとき
どうしても１回目の支部コードの部会コードを読込んでしまいます。
例えば各テーブルが下記の時、１回目支部テーブル01 部会テーブル01 の場合 「ははは」を表示します。
２回目　支部テーブル02 部会テーブル02 の場合 「ひひひ」を表示します。正解は「みみみ」です。
　　　　支部テーブル　01 あああ
02 いいい
　　　　部会テーブル 01 01 ははは
01 02 ひひひ
　　　　　　　　　　　02 01 ままま
02 02 みみみ

　簡単なプログラムですが悩んでいます。是非是非、アドバイスお願い致しますm(__)m　　　　　　　

Public Function 会員データ読み込み(会員番号 As String) As Integer
Dim データベース As Database, レコード As Recordset
Dim SQLステートメント

会員データ読み込み = 0 'return値設定。

SQLステートメント = "select * from 会員情報テーブル where 会員番号='" & 会員番号 & "'"
Set データベース = CurrentDb
Set レコード = データベース.OpenRecordset(SQLステートメント)

If レコード.EOF = True Then '指定されたコードがない。
:
End If

With レコード
Me!会員名 = !会員名
Me!フリガナ = !フリガナ
Me!事業所名 = !事業所名
Me!業種1 = !業種1
Me!支部 = !支部
Me!部会 = !部会
Me!隠し支部 = Me!支部.Column(1)
Me!隠し部会 = Me!部会.Column(2)
Me!会員種別 = !会員種別
: 中略
退会FLG = !退会フラグ

SQLステートメント = "select 部会テーブル.支部コード, 部会テーブル.部会コード, * FROM 部会テーブル WHERE (((部会テーブル.支部コード)='" & Me!支部 & "') AND ((部会テーブル.部会コード)='" & Me!部会 & "'));"
Me!隠し部会 = Me!部会.Column(2)
End With

レコード.Close
データベース.Close
Set データベース = Nothing
End Function

回答No.5

noname#60992 · ベストアンサー

>lngBukaiCD = RST!部会コード
>lngShibuCD = RST!支部コード

RSTではなかったですね。
lngBukaiCD = レコード!部会コード
lngShibuCD = レコード!支部コード
の誤りです。　（ついついいつもの癖が、、、、）
失礼しました。

お礼

16Augustさん。アドバイスありがとうございますm(__)m
JOIN を付けてSQL文を作成し、ちゃんと表示することが出来ました。
本当に助かりましたm(__)m

実は、このプログラムは Access95で誰かが作ったものを
Access2003にコンバートして、不具合になったものです。
なので、最初から作ったわけではなかったので？？だらけでした。
初歩的な質問にも、きちんと答えていただきまして
本当にありがとうございました。

回答No.4

noname#60992

再度No1です。　
No3を行っても正しい抽出はされないかもしれません。
よくよく見てみると、なんだかよくわからないプログラムですね。　データベースのリレーションを使おうとしているのかな？

ＪＯＩＮを１回も使っていないのに、２回のＳＱＬで３つのテーブルからデータを取ってくることに無理があるような気がします。
プログラムの流れとしては

1. １回のＳＱＬで行うなら、
アクセスでクエリを作ることができるなら、必要な情報がすべて表示されるクエリを作成しそのクエリをＳＱＬビューで見てみると、
ご希望のＳＱＬ文が得られると思います。

2. ３回に分けて行うなら、
(1)会員ＩＤから必要な情報を収集する。
(2)部会テーブルから(1)で得られた部会コードより部会名を取得する。
(3)支部テーブルから(1)で得られた支部コードより支部名を取得する。
といった順番になります。

(1)のＳＱＬ
"SELECT * FROM 会員情報テーブル WHERE 会員コード = '" & 会員番号 & "'"
この後データを取得する際に部会コード及び支部コードを変数に格納しておけば、(2)(3)のＳＱＬが作りやすくなります。
lngBukaiCD = RST!部会コード
lngShibuCD = RST!支部コード
もちろん、フォーム上のテキストボックスなどに格納してもよいですが、フォームにリフレッシュなどをかける必要があるかもしれません。

(2)のＳＱＬ
"SELECT 部会名 FROM 部会テーブル WHERE 部会コード = '" & lngBukaiCD & "'"

(3)のＳＱＬ
"SELECT 支部名 FROM 支部テーブル WHERE 支部コード = '" & lngShibuCD & "'"

どれも
a.レコードセットを定義する。　（SQLステートメント = "SELECT .........")
b.レコードセットを開く。　（Set レコード = データベース.OpenRecordset(SQLステートメント)）　
c.情報を収集する。　(me.xxx = レコード!xxx)　
d.レコードセットを閉じる。 (レコード.close)
の順番で行うのが一般的であると思います。
３つレコードセットを作ってもかまいませんが、個人的にはむやみにレコードセットを増やすのはわかりにくくなるので好みません。

私は主にＡＤＯを使いますが（ほかのやり方をあまり知りません）、
Ｎｏ２の方がおっしゃっているようにファンクションを作っておくと非常にプログラムがわかりやすくなると思います。

まず、クエリ（ＳＱＬ）をもう少し研究されることをお勧めします。

回答No.3

noname#60992

No1です。
Me!隠し部会 = Me!部会.Column(2)
は
Set レコード = データベース.OpenRecordset(SQLステートメント)
の後ですね。　すみません。

回答No.2

noname#140971

既に、正答がなされているので・・・。
全体の構想に関する老婆心ながらのアドバイスです。

フォーム毎にプログラムしていたのでは、幾ら、時間があっても足りませんよ。
そんなのは、専用の関数に任すのが一番です。

isOK=DisplayRecord(Me, SQL文)

このような僅か１行で、質問者のやろうとしていることは実現できる筈です。
なぜなら、DisplayRecord関数は、フォームとSQL文を引数としているからです。
しかも、自分で表示するフォームコントロールを探しています。
ファームのフィールド名が、＜"field_" ＋ 列名＞で命名すれば、こういう事も不可能ではありません。

まあ、OKWave の規則違反ギリギリの回答かと思いますが、余りにもシコシコとコードを書いているようなので。

' -----------------------------------------------------------------------------------------
'　フォームに読み込んだ列情報を表示します。
'
'　【要件】 ファームのフィールド名が、＜"field_" ＋ 列名＞であること。
' -----------------------------------------------------------------------------------------
Public Function DisplayRecord(ByVal frm As Form, _
　　　　　　　　　　　　　　　ByVal strQuerySQL As String) As Boolean

On Error GoTo Err_DisplayRecord
　　Dim isOK As Boolean
　　Dim I　　As Integer
　　Dim N　　As Integer
　　Dim rst　As ADODB.Recordset
　　Dim fld　As ADODB.Field

　　isOK = True
　　Set rst = New ADODB.Recordset
　　rst.Open strQuerySQL, _
　　　　　　 CurrentProject.Connection, _
　　　　　　 adOpenStatic, _
　　　　　　 adLockReadOnly
　　If Not rst.BOF Then
　　　　' =================
　　　　'　Begin With: frm
　　　　' -----------------
　　　　With frm
　　　　　　N = .Controls.Count - 1
　　　　　　For Each fld In rst.Fields
　　　　　　　　For I = 0 To N
　　　　　　　　　　If Mid$(.Controls(I).Name, 7) = fld.Name Then
　　　　　　　　　　　　.Controls(I).Value = fld.Value
　　　　　　　　　　　　Exit For
　　　　　　　　　　End If
　　　　　　　　Next I
　　　　　　Next fld
　　　　End With
　　　　' ---------------
　　　　'　End With: frm
　　　　' ===============
　　Else
　　　　MsgBox " フォームに表示する情報はありません。(DisplayRecord)", vbInformation, " お知らせ"
　　End If
Exit_DisplayRecord:
On Error Resume Next
　　rst.Close
　　Set rst = Nothing
　　DisplayRecord = isOK
　　Exit Function
Err_DisplayRecord:
　　isOK = False
　　MsgBox "実行時エラーが発生しました。(DisplayRecord)" & Chr$(13) & Chr$(13) & _
　　　　　　"・Err.Description=" & Err.Description & Chr$(13) & _
　　　　　　"・SQL Text=" & strQuerySQL, _
　　　　　　vbExclamation, " 関数エラーメッセージ"
　　Resume Exit_DisplayRecord
End Function

回答No.1

noname#60992

ざっと見たところ、
SQLステートメント = "select 部会テーブル.支部コード, 部会テーブル.部会コード, * FROM 部会テーブル WHERE (((部会テーブル.支部コード)='" & Me!支部 & "') AND ((部会テーブル.部会コード)='" & Me!部会 & "'));"
Me!隠し部会 = Me!部会.Column(2)
sqlは作ったようですが、レコードセットは更新していないようです。

レコード.Close
SQLステートメント = "select 部会テーブル.支部コード, 部会テーブル.部会コード, * FROM 部会テーブル WHERE (((部会テーブル.支部コード)='" & Me!支部 & "') AND ((部会テーブル.部会コード)='" & Me!部会 & "'));"
Me!隠し部会 = Me!部会.Column(2)
Set レコード = データベース.OpenRecordset(SQLステートメント)
のようにすればよいのでは、

今後のためアドバイスを、
・パフォーマンスなどの理由でわざと２回にしているのでなければ、ＪＯＩＮを使って一度のＳＱＬにて抽出することをお勧めします。
・フィールド名、テーブル名、変数名などはできる限りアルファベットに統一しておいたほうがよいと思われます。

補足

回答ありがとうございます。
早速、16Augustさんのコマンドを付加して実行しましたが
ダメでした(T_T)
素人の考えで申しわけございませんが、どの部分に追加すれば良いのでしょうか？
実際に追加した部分は、End With の前です。