access 自動反映（？）について

質問

テーブルA　　　name code
one 1
two 2

テーブルB　　　Bname Bcode

上記のようなテーブルが有り、テーブルBを使用したフォームを作成しています。
Bname へ入力する値は、テーブルA name にある値と一致します。
フォームで Bname を入力すると、一致したテーブルA name の code を Bcode　へ自動反映したいのですが、やり方が解りません。。

どのようにすれば反映させることが出来るでしょうか？

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

回答No.6

piroin654 · ベストアンサー

No5の説明をしておきます。

(1)　Q追加用差分
これは不一致クエリで、テーブルAのnameと
テーブルBのBnameを比較して、テーブルB
のBnameにテーブルAのnameにないものが
あれば抽出するクエリです。

(2)　Q追加
これは、追加クエリでテーブルBのBnameと
テーブルAのnameが同じでないものがあれば
テーブルAにテーブルBの該当するレコードを
追加します。

(3)　Q更新用差分
これは不一致クエリで、テーブルAのNameとテーブルBの
Bnameが同じもので、テーブルAのcode
とテーブルBのBcodeが同じでないものが
あれば抽出するクエリです。

すみません。No5のQ更新用差分は以下に
してください。

SELECT テーブルB.Bname, テーブルB.Bcode
FROM テーブルA RIGHT JOIN テーブルB ON (テーブルA.code = テーブルB.Bcode) AND (テーブルA.name = テーブルB.Bname)
WHERE (((テーブルA.code) Is Null));

(4)　Q更新
これは、テーブルAのNameとテーブルBの
Bnameが同じもので、テーブルAのcode
とテーブルBのBcodeが同じでないものが
あればテーブルAに追加するクエリです。

すみません。No5の更新クエリは以下に
してください。

UPDATE テーブルB LEFT JOIN テーブルA ON テーブルB.Bname = テーブルA.name SET テーブルA.code = [テーブルB]![Bcode];

(5)　Private Sub コマンド4_Click()
　　の主なところについて。

If rs1.RecordCount > 0 Then
DoCmd.OpenQuery ("Q追加")
End If
If rs2.RecordCount > 0 Then
DoCmd.OpenQuery ("Q更新")
End If

If rs1.RecordCount > 0 Then　は
Q追加用差分でデータが抽出されるなら
という意味。
DoCmd.OpenQuery ("Q追加")　は
Q追加を実行する、という意味。
If rs2.RecordCount > 0 Then　は
Q更新用差分でデータが抽出されるなら
という意味。
DoCmd.OpenQuery ("Q更新")　は
Q更新を実行する、という意味。

Private Sub Form_Close()　も
中身は同じです。

以上です。またまた変更点があり申し訳ないです。
このようになかなか便利にしようとすると、
はずしてしまうことが結構あります。
説明しながら途中で「あれ？」と
気が付いたものがほとんどです。

回答No.5

piroin654

No4のクエリに訂正箇所等がありました。
また、追加、更新などを見直しました。
以下のように設定してみてください。
(1)から(4)までは作成するクエリの内容です。
追加、更新をそれぞれ独立してするか、しないか
にしました。

確認してみてください。

(1)
Q追加用差分

SELECT テーブルB.Bname, テーブルB.Bcode
FROM テーブルA RIGHT JOIN テーブルB ON テーブルA.name = テーブルB.Bname
WHERE (((テーブルA.name) Is Null));

(2)
Q追加

INSERT INTO テーブルA ( name, code )
SELECT テーブルB.Bname, テーブルB.Bcode
FROM テーブルA RIGHT JOIN テーブルB ON テーブルA.name = テーブルB.Bname
WHERE (((Exists (SELECT * FROM テーブルB WHERE テーブルB.Bname = テーブルA.name))=False));

(3)
Q更新用差分

SELECT テーブルB.Bname, テーブルB.Bcode
FROM テーブルA RIGHT JOIN テーブルB ON (テーブルA.code = テーブルB.Bcode) AND (テーブルA.name = テーブルB.Bname)
WHERE (((テーブルA.name) Is Null) AND ((テーブルA.code) Is Null));

(4)
Q更新

UPDATE テーブルB LEFT JOIN テーブルA ON テーブルB.Bname = テーブルA.name SET テーブルA.name = [テーブルB]![Bname], テーブルA.code = [テーブルB]![Bcode];

(5)
ボタンクリックで追加、更新する場合。

Private Sub コマンド4_Click()
Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset

Set db = CurrentDb
Set rs1 = db.OpenRecordset("Q追加用差分")
Set rs2 = db.OpenRecordset("Q更新用差分")

If rs1.RecordCount > 0 Then
DoCmd.OpenQuery ("Q追加")
End If
If rs2.RecordCount > 0 Then
DoCmd.OpenQuery ("Q更新")
End If

rs1.Close: Set rs1 = Nothing
rs2.Close: Set rs2 = Nothing
db.Close: Set db = Nothing
End Sub

(6)
フォームを閉じるときに追加、更新をする場合。

Private Sub Form_Close()
Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset

Set db = CurrentDb
Set rs1 = db.OpenRecordset("Q追加用差分")
Set rs2 = db.OpenRecordset("Q更新用差分")

If rs1.RecordCount > 0 Then
DoCmd.OpenQuery ("Q追加")
End If
If rs2.RecordCount > 0 Then
DoCmd.OpenQuery ("Q更新")
End If

rs1.Close: Set rs1 = Nothing
rs2.Close: Set rs2 = Nothing
db.Close: Set db = Nothing
End Sub

回答No.4

piroin654

回答に抜けていたものがあるので、No2とNo3を
合わせて訂正しておきます。
フォームでテーブルAのnameにはないBnameに新しい
データを追加した場合に対応するためにはNo2やNo3
でいいのですが、既存のデータの更新も含めるならば
以下にします。既存のデータの更新だけならば
反対に、
DoCmd.OpenQuery ("Q追加")
ではなく、
DoCmd.OpenQuery ("Q更新")
にします。

(1)
新しいクエリをSQLビューにして以下のSQL文を
貼り付け保存します。

SELECT テーブルB.Bname, テーブルB.Bcode
FROM テーブルA RIGHT JOIN テーブルB ON テーブルA.name = テーブルB.Bname
WHERE (((テーブルA.name) Is Null));

名前を

Q差分

とします。

(2)-1
新しいクエリをSQLビューにして以下のSQL文を
貼り付け保存します。

INSERT INTO テーブルA ( name, code )
SELECT テーブルB.Bname, テーブルB.Bcode
FROM テーブルA RIGHT JOIN テーブルB ON テーブルA.name = テーブルB.Bname
WHERE (((Exists (SELECT * FROM テーブルB WHERE テーブルB.Bname = テーブルA.code))

=False));

名前を

Q追加

とします。

(2)-2
新しいクエリをSQLビューにして以下のSQL文を
貼り付け保存します。

UPDATE テーブルB LEFT JOIN テーブルA ON テーブルB.Bname = テーブルA.name SET テーブルA.name = [テーブルB]![Bname], テーブルA.code = [テーブルB]![Bcode];

名前を

Q更新

とします。

(3)
フォームの新規作成から、「基になるテーブルまたはクエリの選択」
でテーブルBを指定します。「オートフォーム　表形式」を選んで
「OK」とします。

(4)
出来たフォームを適当な名前で保存し、フォームをデザインビューで
開きます。マウスを詳細の上部に当て、マウスの矢印が十字になったら、
クリックして2～3cm下に下げます。残りのラベルもすべて選択して
下に下げます。

(5)
DoCmd.OpenQuery ("Q更新")
をコード中に追加しています。

フォームの空いたスペースにボタンを一つ設定します。
そのボタンのクリック時のイベントに以下を設定
します。

ここでは、DAOを使っているのでコード表の
「ツール」から「参照設定」を選択し、
Mcrosoft DAO xx Object Library
にチェックを入れ、「OK」としてください。
xxは3.6のような数字です。

もし、もう一度参照設定を開いて、
Microsoft ActiveX Data Objects xx Library
よりも下になっていたらそれよりも上に↑ボタンで
引き上げて、「OK」としてください。
この場合のxxは2.1のような数字です。

Private Sub コマンド0_Click()
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Q差分")

If rs.RecordCount > 0 Then
DoCmd.OpenQuery ("Q追加")
DoCmd.OpenQuery ("Q更新")
End If
rs.Close: Set rs = Nothing
db.Close: Set db = Nothing
End Sub

コマンド0 はボタンの名前ですが、
実際に設定した場合は違う名前に
なっているかもしれません。

(6)
No3の訂正。
DoCmd.OpenQuery ("Q更新")
をコード中に追加しています。

あるいは、フォーム閉じるときに、

Private Sub Form_Close()
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Q差分")

If rs.RecordCount > 0 Then
DoCmd.OpenQuery ("Q追加")
DoCmd.OpenQuery ("Q更新")
End If
rs.Close: Set rs = Nothing
db.Close: Set db = Nothing
End Sub

でもいいかもしれません。

以上です。

お礼

すごく丁寧に書いていただき、ありがとうございます。

のぞみ以上の事ができました！

すごいですね

回答No.3

piroin654

No2です。操作は随時いつでも
ボタンを押すことにしています。

あるいは、フォーム閉じるときに、

Private Sub Form_Close()
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Q差分")

If rs.RecordCount > 0 Then
DoCmd.OpenQuery ("Q追加")
End If
rs.Close: Set rs = Nothing
db.Close: Set db = Nothing
End Sub

でもいいかもしれません。

回答No.2

piroin654

一応、こちらで設定した方法を説明してみます。

(1)
新しいクエリをSQLビューにして以下のSQL文を
貼り付け保存します。

SELECT テーブルB.Bname, テーブルB.Bcode
FROM テーブルA RIGHT JOIN テーブルB ON テーブルA.name = テーブルB.Bname
WHERE (((テーブルA.name) Is Null));

名前を

Q差分

とします。

(2)
新しいクエリをSQLビューにして以下のSQL文を
貼り付け保存します。

INSERT INTO テーブルA ( name, code )
SELECT テーブルB.Bname, テーブルB.Bcode
FROM テーブルA RIGHT JOIN テーブルB ON テーブルA.name = テーブルB.Bname
WHERE (((Exists (SELECT * FROM テーブルB WHERE テーブルB.Bname = テーブルA.code))

=False));

名前を

Q追加

とします。

(3)
フォームの新規作成から、「基になるテーブルまたはクエリの選択」
でテーブルBを指定します。「オートフォーム　表形式」を選んで
「OK」とします。

(4)
出来たフォームを適当な名前で保存し、フォームをデザインビューで
開きます。マウスを詳細の上部に当て、マウスの矢印が十字になったら、
クリックして2～3cm下に下げます。残りのラベルもすべて選択して
下に下げます。

(5)
フォームの空いたスペースにボタンを一つ設定します。
そのボタンのクリック時のイベントに以下を設定
します。

ここでは、DAOを使っているのでコード表の
「ツール」から「参照設定」を選択し、
Mcrosoft DAO xx Object Library
にチェックを入れ、「OK」としてください。
xxは3.6のような数字です。

もし、もう一度参照設定を開いて、
Microsoft ActiveX Data Objects xx Library
よりも下になっていたらそれよりも上に↑ボタンで
引き上げて、「OK」としてください。
この場合のxxは2.1のような数字です。

Private Sub コマンド0_Click()
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Q差分")

If rs.RecordCount > 0 Then
DoCmd.OpenQuery ("Q追加")
End If
rs.Close: Set rs = Nothing
db.Close: Set db = Nothing
End Sub

コマンド0 はボタンの名前ですが、
実際に設定した場合は違う名前に
なっているかもしれません。

以上です。

回答No.1

DexMachina

Bcodeのテキストボックスの「更新後」イベントで、DLookup関数を使用するのが
簡単かと思います。

【マクロの場合】
アクション：　値の代入
　　　アイテム：　Forms![フォーム名]![コントロール名]
　　　式：　DLookup("code", "テーブルA", "[name]='" & Forms![フォーム名]![Bname] & "'")
　※Access2007以降では「すべてのアクションを表示」をオンにする必要あり。

【VBAの場合】
　Bcode = DLookup("code", "テーブルA", "[name]='" & Bname & "'")

・・・なのですが、「Bname」に記録されるのが、常に「テーブルAからの検索結果」で
取得可能なのでしたら、テーブルBにはBcodeのみを記録するのが、Accessなどの
リレーショナルデータベースでは通常の方法となります。

というのは、Bnameに記録されるはずだったデータは、「Bcodeと、テーブルAを
値集合ソースとするコンボボックス」を使用すれば表示可能だからです。

【コンボボックスの設定(フォームの場合)】
※フォームのレコードソースが「テーブルB」の場合。

<書式タブ>
　列数：2

<データタブ>
　コントロールソース： Bcode
　値集合ソース：　テーブルA
　値集合タイプ：　テーブル/クエリ
　連結列：　2
　　※テーブルAの2番目のフィールド(=code)と同じ値が入る、という意味。

・・・以上、併せて参考まで。

お礼

返事が遅れて申し訳ありません。

丁寧に解答していただいて、本当にありがとうございます。

望み通り、出来ました！