ACCESS フォームからサブフォームのデータを更新・入力したい

質問

ACCESS　フォームからサブフォームのデータを更新・入力したい

tbl_プロジェクトというテーブルにIDとプロジェクトコード、プロジェクトの基本情報を持たせ、f_プロジェクトフォームからプロジェクトコード、件名、契約日等のデータ及び、f_テーマサブフォームにテーマを入力できるようなフォームを作成しています。フォームで入力したものをそれぞれのテーブルに保存したいのですがうまくいかない部分があります。

フォームから入力すると、tbl_プロジェクトに所属する情報は更新されます。そのtbl_プロジェクトに所属するプロジェクトコードをサブフォームのtbl_テーマのプロジェクトコードにも反映したいのですがどのようにしたらよいでしょうか？
そういったことは可能でしょうか？分かりにくい説明で申し訳ないですが教えていただけると助かります。よろしくお願いします！

DBの構成
【テーブル】
tbl_プロジェクト　（1件1件のIDとプロジェクトコードを持つ）
(フィールド：P_ID、プロジェクトコード、件名、契約日など)
tbl_テーマ（プロジェクト毎に関連のあるキーワードを持つ）
（フィールド：P_ID、プロジェクトコード、テーマ）←これのプロジェクトコードにtbl_プロジェクトのデータが入るようにしたい。
tbl_顧客（プロジェクトを依頼した顧客情報を持つ）
【クエリ】
q_テーマ（プロジェクト、テーマ、顧客を抽出するクエリ）
【フォーム】
f_プロジェクト　（プロジェクトを1件ずつ表示・参照・新規作成するフォーム）
f_テーマサブフォーム　（f_プロジェクトフォームのプロジェクトコードにづきテーマを
　　　　　　　 　表示・入力するサブフォーム）

リレーションはP_IDをキーに一対多で作成しています。

回答No.6

DexMachina · ベストアンサー

No.4です。

前回の回答は、「レコードの新規登録」のみを想定していました(汗)
大変失礼致しました。

【「メインフォーム」-「サブフォームの新規レコード」間の不一致について】
(＝No.4の補足での、2個目の「◆」の件)

メインフォーム側の「レコード移動時」イベントで、『プロジェクトコード』の
更新後イベントと同様の処理を行えば、解消すると思います。
(先頭1行が「Private Sub Form_Current()」となる他は、前回のVBAと全く
　同じなので、今回は省略します)

【既存レコードでの『プロジェクトコード』の空白について】
(＝No.4の補足での、1個目の「◆」の件)

対処法としては、
　a)更新クエリを使用して、tbl_テーマの『プロジェクトコード』を一括更新
　　(→tbl_テーマに入力済みの『P_ID』の再確認が不要な場合)
　b)「メインフォーム上のプロジェクトコードを、サブフォームの全レコードに
　　一括代入」するためのコマンドボタンを、メインフォームに追加
　　(→tbl_テーマの『P_ID』が正しいかを目視確認してから代入する場合)
の、2つの考え方があります。

「a」については添付画像をご覧いただくとして、ここでは「b」について説明
します。

メインフォームに『コード転記』コマンドボタンを作成したら、そのクリック時
イベントに以下のコードを記載して下さい：

Private Sub コード転記_Click()

　　If MsgBox("サブフォームにプロジェクトコードを追記します", vbOKCancel, "確認") = vbCancel Then Exit Sub

　　'サブフォームの全レコードを上書き更新
　　With Me![サブフォームのコントロール名].Form.RecordsetClone
　　　　Do Until .EOF
　　　　　　.Edit
　　　　　　!プロジェクトコード = Me!プロジェクトコード
　　　　　　.Update
　　　　Loop
　　End With

　　'更新を反映
　　Me![サブフォームのコントロール名].Form.Refresh

　　MsgBox "追記しました", , "確認"

End Sub

なお、「a/b」のどちらを採った場合でも、サブフォームの『P_ID』の上書き
編集に合わせて『プロジェクトコード』も連動して更新させるには、別途、
以下のような『P_ID』の更新後イベントでの対応が必要です：

Private Sub P_ID_AfterUpdate()
'メインではなくサブフォームの『P_ID』テキストボックスの更新後イベント

　　If IsNull(Me!P_ID) Then
　　　　Me!プロジェクトコード = Null
　　Else
　　　　Me!プロジェクトコード = DLookup("[プロジェクトコード]", "tbl_プロジェクト", "[P_ID]=" & Me!P_ID)
　　　　'『P_ID』がテキスト型の場合は上記ではなく以下のコード
　　　　'Me!プロジェクトコード = DLookup("[プロジェクトコード]", "tbl_プロジェクト", "[P_ID]='" & Me!P_ID & "'")
　　End If

End Sub

また、この対応を組み込んで戴けば、

> テーマを修正するとフォーム上では10001が入力される。
> tbl_テーマには反映されない。

の件も解消すると思います。
(但し、別のレコードに移動したり、メニューから「レコード(R)→レコードの
　保存(O)」を選択する等して、レコードが保存してからでないと、テーブル
　には反映されないので、確認の際はご注意下さい)

・・・以上です。

お礼

ご報告が遅くなり、申し訳ありません。
新規作成の場合、tbl_テーマにプロジェクトコードを登録することが出来ました。
既存レコードのプロジェクトコードの更新がまだうまくいきませんが、なんとか
試してみます。

また、今後はもう少し勉強して、テーブル作成時にきちんと正規化することを
心がけたいと思います。

本当に親切に詳しくご説明いただき、ありがとうございました。また質問させて
いただくと思いますがよろしくお願いいたします。

回答No.5

30246kiku

以下でどうなりますか。

パターン０）（現状？　リンク親/子フィールドが P_ID）

「f_テーマサブフォーム」の挿入前処理で、「プロジェクトコード」を親から持ってきます。

Private Sub Form_BeforeInsert(Cancel As Integer)
　　If (IsNull(Me.Parent.プロジェクトコード)) Then
　　　　Cancel = True
　　Else
　　　　Me.プロジェクトコード = Me.Parent.プロジェクトコード
　　End If
End Sub

If の判別は、
IsNull(Me.Parent.P_ID) Or IsNull(Me.Parent.プロジェクトコード)
でも。

親が設定されていなければ編集状態にしない判別をしているだけです。
挿入前処理なので、新規への設定になります。

パターン１）

f_プロジェクトのレコードソースが、tbl_プロジェクト
f_テーマサブフォームのレコードソースが、tbl_テーマ

だった場合、

リンク親フィールド、リンク子フィールドともに、
P_ID;プロジェクトコード

親が空欄なら編集状態にさせないようにするには、子側で

Private Sub Form_BeforeInsert(Cancel As Integer)
　　If (IsNull(Me.Parent.P_ID) Or IsNull(Me.Parent.プロジェクトコード)) Then
　　　　Cancel = True
　　End If
End Sub

パターン２）

f_プロジェクトのレコードソースが、tbl_プロジェクト
f_テーマサブフォームのレコードソースは、tbl_テーマをベースに
抽出条件で P_ID = [Forms]![f_プロジェクト]![P_ID] と親フォームを参照

だった場合、（リンク親/子フィールドが空欄だった場合）

パターン０と同様に

Private Sub Form_BeforeInsert(Cancel As Integer)
　　If (IsNull(Me.Parent.P_ID) Or IsNull(Me.Parent.プロジェクトコード)) Then
　　　　Cancel = True
　　Else
　　　　Me.P_ID = Me.Parent.P_ID
　　　　Me.プロジェクトコード = Me.Parent.プロジェクトコード
　　End If
End Sub

サブフォームの再クエリは、必要なタイミングで行います。

パターン３）（パターン０，２と複合して）

既に何件か関連付けられたデータがあって、メインのプロジェクトコードを変更した時に
サブ側のプロジェクトコードも変更したい。

っていう場合？

メイン側の更新前処理でプロジェクトコードに変更があったか判別し、更新後処理で変更

Dim bChg As Boolean

Private Sub Form_BeforeUpdate(Cancel As Integer)
　　bChg = False
　　If (Not Me.NewRecord) Then
　　　　If (Nz(Me.プロジェクトコード) <> Nz(Me.プロジェクトコード.OldValue)) Then
　　　　　　bChg = True
　　　　End If
　　End If
End Sub

Private Sub Form_AfterUpdate()
　　Dim sSql As String

　　If (bChg) Then
　　　　sSql = "UPDATE tbl_テーマ SET プロジェクトコード = "
　　　　sSql = sSql & IIf(IsNull(Me.プロジェクトコード), "Null", Me.プロジェクトコード)
　　　　sSql = sSql & " WHERE P_ID = " & Me.P_ID & ";"
　　　　CurrentDb.Execute sSql
　　End If
End Sub

※プロジェクトコードは数値型とした時のものになります。

※現在tbl_テーマ内のプロジェクトコードが完全じゃない場合、以下クエリで設定します。

UPDATE tbl_テーマ INNER JOIN tbl_プロジェクト ON tbl_テーマ.P_ID = tbl_プロジェクト.P_ID
SET tbl_テーマ.プロジェクトコード = tbl_プロジェクト.プロジェクトコード;

試される時には戻せる状態にしてから・・・

お礼

ご回答ありがとうございます！
バックアップを取ってから確認させていただきます。

回答No.4

DexMachina

※長文ですが、P_IDとプロジェクトコードが一対一でない場合は、後半
　は無視して下さい(汗)

【ご質問の内容通りの動作を行わせる方法】
リレーションシップが設定されている『P_ID』と(ほぼ)同様の動作を
『プロジェクトコード』に行わせるには、メイン側の『プロジェクトコード』
の更新後イベントで、サブ側に既定値を設定します。

<VBAの場合>
Private Sub プロジェクトコード_AfterUpdate()

　　Dim DefVal As String

　　'入力値を確認
　　If IsNull(プロジェクトコード) Then
　　　　'値を削除したときは既定値を解除
　　　　DefVal = ""
　　Else
　　　　'値を設定したときは既定値を設定
　　　　'(数値型/文字列型の双方に対応するため「"」(=Chr(34))で
　　　　'　括りましたが、数値型の場合はなくてもOK)
　　　　DefVal = Chr(34) & プロジェクトコード & Chr(34)
　　End If

　　'上で変数に記録した値を、既定値プロパティに設定
　　Me![サブフォームのコントロール名]!プロジェクトコード.DefaultValue = DefVal

End Sub

<マクロの場合>
メイン側の『プロジェクトコード』の更新後イベントに以下のマクロを設定：
アクション：　値の代入
アイテム：　Forms![メインフォーム名]![サブフォームのコントロール名]!プロジェクトコード.DefaultValue　
式：　IIF(IsNull(Forms![メインフォーム名]!プロジェクトコード), "", Chr(34) & Forms![メインフォーム名]!プロジェクトコード & Chr(34))
　※アイテム・式の一部に、自動で角括弧が追加されます。

【データベースで推奨される方法】
提示サンプルを見ると、『P_ID』と『プロジェクトコード』は一対一の
関係にあるようですが、この場合、『プロジェクトコード』は『P_ID』
からの演算で表示可能なので、サブ側には『P_ID』のみを記録
するのが一般的です。
(サブ側での入力は不要(というか『プロジェクトコード』はなくなる
　ので、不可能)になります)

具体的には
　a)【検索×・編集×】フォームで演算コントロールを追加
　b)【検索○・編集×】クエリで演算フィールドを追加
　c)【検索○・編集○(P_IDと連動)】フォーム又はクエリで、『P_ID』
　　から『プロジェクトコード』を表示させるコンボボックスを追加
等です。

以下、それぞれの概要です：

a)『f_テーマサブフォーム』のデザインビューで、ツールバーから
　テキストボックスを新たに追加するか、既にある『プロジェクト
　コード』テキストボックスを選択したら、プロパティシートの
　『データ』タブの『コントロールソース』に以下のような式を指定
　します(先頭に「=」が必要)：

　　=DLookup("[プロジェクトコード]", "tbl_プロジェクト", "[P_ID]=" & [P_ID])

b)『tbl_テーマ』を元とするクエリのデザインビューで、『フィールド:』
　欄に以下のような式を指定します(『テーブル:』欄は空白)：

　　プロジェクトコード: DLookup("[プロジェクトコード]", "tbl_プロジェクト", "[P_ID]=" & [tbl_テーマ].[P_ID])
　　※前半(「:」の左側)は、演算結果を表示させる際の名前になります。
　　　(特に指定しない場合、「式1」などが自動で入力されます)

c)以下、クエリでの例：
　1)『tbl_テーマ』を元とするクエリをデザインビューで開く
　2)『P_ID』のデータの表示が不要なら1個、表示が必要なら2個、
　　『P_ID』を表示対象として追加
　3)『フィールド:』欄の「P_ID」の1つを、「プロジェクトコード: P_ID」に
　　変更
　4)右クリックメニューなどからこのフィールドのプロパティシートを
　　開き、『ルックアップ』タブを選択
　5)『表示コントロール』に「コンボ ボックス」を指定
　6)表示項目が増えるので、以下のように設定：
　・値集合タイプ：　テーブル/クエリ
　・値集合ソース：　Select Distinct プロジェクトコード, P_ID From tbl_プロジェクト Order by プロジェクトコード;
　・連結列：　2
　・列数：　2
　・列幅：　5cm;0cm

・・・以上です。
長文、失礼致しました(汗)

補足

なかなか試すことが出来ず、お礼と報告が遅くなりすみませんでした。
ご指摘いただいたとおり、テーブルを正しい構成にしてCの案を試そうと思っていますが、
取り急ぎ、VBAだけで対処できればと思って試行錯誤しています。

ご回答いただいたVBAを試したところ、サブフォームのプロジェクトコードに関わらず、
メインフォームに入力したプロジェクトコードが表示されてしまいます。そしてやはり
テーブルには反映できません。やはりテーブルを修正しないと思う動作はしないでしょうか？？

◆メインに10001と入力すると何も入っていないサブフォームのフィールドには反映される。
また、テーマを修正するとフォーム上では10001が入力される。tbl_テーマには反映されない。

メインフォーム
| [P_ID] [プロジェクトコード]　[プロジェクト名]
| 1　　　　　　　　10001　　　　　　　　新型テレビ
|_______________________________________________
|サブフォーム
|[テーマ] [p_ID] [プロジェクトコード]
|[テーマ1] 　 1 　　　　　　　　　　
|[テーマ2] 　 1 　　　　　　　　　　　　　
|何も入っていないレコード　　　　　　　　　10001
________________________________________________

◆メインに10001と入力してから他のプロジェクトコードに移動すると既に入力されている既存のコードは
表示されているが、何も入っていないレコードには10001と表示されている。テーマを修正すると10001が
表示される。
※プロジェクトコードが既存で入っているのは初期データはエクセルに入力後に読み込んだからです。
メインフォーム
| [P_ID] [プロジェクトコード]　[プロジェクト名]
| 1　　　　　　　　20001　　　　　　　　エアコン
|_______________________________________________
|サブフォーム
|[テーマ] [p_ID] [プロジェクトコード]
|[テーマ1] 　 1 　　　　　　　　　　　　　20001 　　　　　　　　　　
|[テーマ2] 　 1 　　　　　　　　　　　　　20001 　　　　　　　　　　
|何も入っていないレコード　　　　　　　　　10001
________________________________________________

回答No.3

layy

別途更新後項目をメインに用意しサブの更新クエリ、再クエリを実行させたら出来るのではないかと思いますがまだ検証はしてません。この手の仕掛けはフォーム閉じた際メインサブ同時更新してくれない仕様だった認識です。ネットで、同時更新のボタン作りたいとかいう情報がグーグルでありましたが、今回のにあてはまるかはなんとも。

参考意見ですみません。

お礼

お礼が遅くなりましたが、ありがとうございます。
まだうまく動作しないので記載いただいた方法も試させていただきます。

回答No.2

layy

すみません。前回回答は無視して下さい。ほかの用途と混同してました。対応になってません。

メインのフォームで入力した内容はメインのレコードソースに更新されるが、同時にサブフォームのレコードソースへも反映させたいようなことでしょうか？。

補足

はい。そういうことです。メインフォームの中に開かれているサブフォームのプロジェクトコードに
自動的に反映したいのです。

テーブルのリレーションで繋がっているP_IDはメイン、サブともにテーブルに自動的に反映されるのですが、
プロジェクトコードはどうしても反映されません。P_IDは主キーに設定しています。

フォームのイメージとしては↓のような形です。宜しくお願いいたします。

|メインフォーム
| [P_ID] [プロジェクトコード]　[プロジェクト名]
| 1　　　　　　　　10001　　　　　　　　新型テレビ
|_______________________________________________
|サブフォーム
|[テーマ] [p_ID] [プロジェクトコード]
|[テーマ1] 　 1 　　　　　　　　　　10001
|[テーマ2] 　 1 　　　　　　　　　　10001
|[テーマ3] 　 1 　　　　　　　　　　10001
________________________________________________

回答No.1

layy

「f_プロジェクトフォームからプロジェクトコードを入力したとき、」
に
tbl_テーマのプロジェクトコードに【値の代入】をし、その後で再クエリ、かと思われます。

補足

tbl_テーマのプロジェクトコードに【値の代入】とは、f_テーマサブフォームのプロジェクトコード部分に
設定するということでよいのでしょうか？

テーブルのフィールドに【値の代入】を設定する方法があるのでしょうか？わかっていなくてすみません～～。

また、【値の代入】、再クエリを設定するのはどのイベントが適切なのでしょうか？色々と試していますが
なかなかうまくいきません。宜しくお願いします。