Access:毎月発生する同じ請求書データをレコードに追加したい

Question

請求書データを入力するフォームがあります。

メインフォームとサブフォームで構成されており、
メインに入力された内容はテーブルAに、
サブに入力された内容はテーブルBに保存されます。

入力されたレコードはそれぞれのテーブルにある
フィールド「請求NO」をキーとして紐づけています。

この請求書データの中で、毎月同じ内容で発生する請求データがあるのですが、
現状、毎月同じ内容のデータ都度入力しているので、この入力を簡略化したいと考えました。
(※請求日、請求NOは変わります)

すでに入力されているレコードをコピーし、
発行日と請求NOだけ変更しようとしたのですが、
コピーしたデータは、請求NOを変更しようとすると
サブフォームの請求内容が消えてしまい、結果、再度入力が必要になってしまいました。

また、新たにテーブルと追加クエリを作成、実行してみたのですが、
テーブルBに対して実行した際、エラー(キー違反)で出来ませんでした。
追加クエリでは追加先のテーブルのフィールド全てを設定しました。
追加用のテーブルのレコードは１件で、１件のキー違反、他は0でした。
クエリに設定するフィールドを１つずつ全て試してみたのですが、
全てキー違反となってしまい、どれが違反しているのか特定できませんでした。

ここまでが現状です。

方法としては、
①入力済のレコードを利用する
②追加クエリを利用する
③Excel等のファイルをインポートする 等、

やりようはあると思うのですが、
どれが良いか、またどうやればいいのかが分からず困っております。

拙い言葉足らずな説明かもしれませんが、お分かりになる方がいましたら
お力添えいただけますでしょうか。

どうぞよろしくお願いいたします。

・入力フォーム

・テーブル構成

※テーブルBの「印刷日」は印刷実行時に値が入る為、フォームに項目はありません。

画像１

画像２

画像３

画像４

画像５

　
画像Ａ

画像Ｂ

画像Ｃ

画像Ｄ

画像Ｅ

画像Ｆ

画像Ｇ

画像Ｈ

画像Ｉ

画像Ｊ

画像Ｋ

画像Ｌ

Answer 1

すでに入力されているレコードをコピーし、発行日と請求NOだけ変更しようとしたのですが、

どのような方法でコピーしましたか？

コピーしたデータは、請求NOを変更しようとすると
サブフォームの請求内容が消えてしまい、結果、再度入力が必要になってしまいました。

サブフォームが表示されている状態から請求Noの入力によりサブフォームが消えるという状況から判断するとコピーはされていなくて、表示しているデータの請求Noの修正を行っていることになり、該当する明細が無いので、表示が消える状況だと思われます。
この状況は、テーブルAとテーブルBの請求Noのリンクが切れている状態だと思いますので、データは確認されて下さい。

参照作成を行いたい場合、テーブルAとテーブルB両方のコピーを行います。
具体的にはコピー元の請求Noを条件にして、ID以外のコピーしたい項目を追加します。
※その際、請求Noは新しいものである必要があります。

追記

概要

コピーボタンで、表示している請求データと請求明細データをコピーを行う。
1.コピー元は画面上の請求NOを用いる。
2.追加はクエリーで行う。
2.1.コピー元条件は画面の請求NOを使用する。
2.2.コピー先の請求NOはテーブルAの最大値+1
3.コピー完了後に、画面上の請求NOにテーブルAの請求NOの最大値を設定し、コピー先を表示する。

※以下画面や項目の名称を以下と仮定しているので、実際の名称に適宜合わせて下さい。
請求データ入力画面=F請求データ入力
請求データ入力画面の請求NO=コンボ_請求NO

クエリー

コピーする項目は想定して設定しているので、請求NO以外は過不足があれば変更して下さい。
※IDは追加しないこと。

クエリー名：Q_請求データ追加

INSERT INTO テーブルA ( 請求NO, 取引先コード, 部門, 業務件名 )
SELECT (SELECT Max(請求NO) FROM テーブルA)+1 , 取引先コード, 部門, 業務件名
FROM テーブルA
WHERE 請求NO=[Forms]![F_請求データ入力]![コンボ_請求NO];

クエリー名：Q_請求明細追加

INSERT INTO テーブルB ( 請求NO, 内容, 数量, 単価, 備考 )
SELECT (SELECT Max(請求NO) FROM テーブルA)+1 , 内容, 数量, 単価, 備考
FROM テーブルB
WHERE 請求NO=[Forms]![F_請求データ入力]![コンボ_請求NO];

・作成タブ⇒クエリデザイン（テーブルの表示は何も選択せず閉じる）⇒右クリックでSQLビューを選択
この状態で上記のSQLを元に変更（実際のテーブル名や請求NOの画面上での名前など）したものを貼り付けて、
クエリーに名前を付けて保存してください。
※修正後にエラーが無ければ、デザインに戻すことができます。

コード

コピーボタンのクリック時イベントに割り当てられているコードを以下に差し替えし、テーブル名や請求NOの名前を実際のものに変更して下さい。
※マクロの呼出はではなくイベントプロシージャに変更して下さい。マクロ自体の変更は不要です。

On Error GoTo Err_コピー_Click
  DoCmd.SetWarnings False 'メッセージの表示　無効
  
  DoCmd.OpenQuery "Q_請求明細コピー"
  DoCmd.OpenQuery "Q_請求データコピー"
  
  DoCmd.SetWarnings True  'メッセージの表示　有効
  Me.Requery  '再表示
  Me.Recordset.FindFirst "請求NO=" & DMax("請求NO", "テーブルA")  'コピー先の請求NOへ移動
Exit_コピー_Click:
    Exit Sub
Err_コピー_Click:
    MsgBox Err.Description
    Resume Exit_コピー_Click

Comments

[th_bridge]

saziさん＞
ご回答ありがとうございます。

コピーの方法ですが、デザインツールからボタンを配置して、
コマンドボタンウィザードで動作を「レコードの複製」と指定しました。

画像を追加しました。
レコードが115件ある状態です。

画像の115番目のレコードを表示した状態で「コピー」をクリックすると、
116番目のレコードとしてコピーされます。(※画僧1、2)

コピーされた116番目のレコードの請求NOを変更してフォーカスを移動すると、
メインフォームは残りますがサブフォームの内容が消えてしまいます。(※画像3)

理想は、コピー後請求NOを変更してもサブフォームの内容が消えずに残り、
新たなレコードとして追加できるようにすることです。

これで何かお分かりになりますでしょうか？

[sazi]

コピーしているのはメインフォームのデータしか行っていないからです。
サブフォームのデータもコピーしてあげないと、データが無いのですから表示するものはありません。
メインフォームの挿入後処理イベントあたりで、サブフォームのデータもコピーする必要があります。
※但し、そのイベント処理では通常処理の場合にはコピーの処理が行われないようにフラグ制御などが必要になると思われます。

[sazi]

もし、サブフォームのデータもコピーしているのであれば、コピー先の請求Noが設定されていないか誤っています。

[sazi]

コピーボタンのイベントだけで完結しようとするなら、dit.さんも書かれているように、新しい請求NoをALL9や最大値で求めておいてコピーする。
で、画面表示後に修正という方法も考えられます。

[th_bridge]

メインフォームのコピーしかできていない状態なのですね。
私の中ではメインもサブもコピーできているものと思い違いをしていました。

仰る通りコピーボタンのイベントだけで完結させたいのですが、
"新しい請求NoをALL9や最大値で求めておく"というのはどのようにすれば良いのでしょうか。
よろしければ今しばらくお付き合いいただけますでしょうか。
よろしくお願いいたします。

[sazi]

現状は、コピーボタンのコードはどのようになっていますか？
コメントではなく質問への追記していただき、具体的なロジックでの提示が望ましいです。

[sazi]

もし、マクロならコードに変換して、変換後のコードで。
難しいようなら言って下さい。

[th_bridge]

ご回答ありがとうございます。

ボタンを作る際、コマンドボタンウィザードから埋め込みマクロを作成したので、
コードが見れるよう、同じ内容のマクロを作ってから変換しようとしたのですが、
作ったマクロを保存する際に、
'メニューコマンドの実行'マクロアクションの'コマンド'引数に無効な値が入力されています。と
エラーメッセージが出て保存できず、コード変換まで行うことができませんでした。

試しにエラーとなる箇所を消して保存、変換してみましたが、
そうすると変換完了とはなるのですが、何もコードが表示されませんでした。

情報として埋め込みマクロの内容の画像を追加しましたが、
これから何かお分かりになりますでしょうか。

[dit.]

コントロールウィザードがONになっている状態でボタンを作成し、レコードの操作→レコードの複製で作った時の埋め込みマクロですね。やはりこれだとメインフォームのみのコピーです。
追加クエリなどを利用してマクロに組み込んでいった方がいいですかね…。
こんな記事もありましたのでうまくできるか試してます。
https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1269578332

[sazi]

追記しました。
もし、リレーションシップで請求NOに対して明細との整合性が設定されているようだと、
コピーボタン押下しても、明細データが作られない可能性があります。
その場合は修正が必要になります。

[th_bridge]

dit.さん＞
どうもありがとうございます。
リンク先、参考にさせていただきます。

saziさん＞
とてもご丁寧にありがとうございます。
書いていただいた内容を元に一度試してみたいと思います。
お忙しい中、ありがとうございます。

[sazi]

マクロでの修正も考えましたが、手っ取り早く、VBAとしています。

[th_bridge]

ご無沙汰しております。

その後、書いていただいたVBAを元にやってみたのですが、
コピーボタンを押した際に、
「コンパイルエラー　行ラベルが定義されていません」とエラーになってしまいました。
細かく丁寧に書いていただいたのに申し訳ないです。

記述内容は以下となりますが、不具合の原因の目処はつきますでしょうか。

それぞれ実際の名前は以下のようになっています。

テーブルA＝請求書テーブルB
テーブルB＝請求内容テーブル

請求データ入力画面＝請求データ入力B
請求データ入力画面の請求NO＝請求NO

Q_請求データ追加＝Q_メイン追加
Q_請求明細追加＝Q_サブ追加

コピーボタン⇒コピー(標題、名前とも)

《クエリ》
・Q_メイン追加
INSERT INTO 請求書テーブルB ( 請求NO, 取引先コード, 部門, 業務件名 )
SELECT (SELECT Max(請求NO) FROM 請求書テーブルB)+1, 取引先コード, 部門, 業務件名
FROM 請求書テーブルB
WHERE 請求NO=[Forms]![請求データ入力B]![請求NO];

・Q_サブ追加
INSERT INTO 請求内容テーブル ( 請求NO, 内容, 数量, 単価, 備考 )
SELECT (SELECT Max(請求NO) FROM 請求書テーブルB)+1, 内容, 数量, 単価, 備考
FROM 請求内容テーブル
WHERE 請求NO=[Forms]![請求データ入力B]![請求NO];

《コード》
Private Sub コピー_Click()

On Error GoTo Err_コピー_Click
DoCmd.SetWarnings False 'メッセージの表示　無効

DoCmd.OpenQuery "Q_サブ追加"
DoCmd.OpenQuery "Q_メイン追加"

End Sub

また、追加画像のようなリレーションを張っています。
「取引先テーブルB」というのは、取引先のデータを入れているテーブルです。
請求データを入力する際、「取引先名」にこのテーブルを元にして
取引先名をリスト選択をするようになっており、
選択した取引先名を元に「取引先コード」に自動的にコードが入る仕様になっています。

このリレーションや取引先テーブルも上手くできない原因に関わっているでしょうか？

[sazi]

エラーについては、
On Error GoTo Err_コピー_Click
の部分で、GoTo以降のラベルが無いからです。エラーロジックなので、取り敢えずその行は削除しても構いません。
それから、クエリー実行以降のロジックが記述されていませんけど？

[th_bridge]

クエリ実行以降のロジックを表示が折りたたまれていて見えていなかったのを見落としていました。
失礼しました。
追記してもう一度試してみます。

[th_bridge]

見落とし失礼いたしました。

コピーボタンクリック時のコードを以下のように記述しました。

On Error GoTo Err_コピー_Click
　DoCmd.SetWarnings False 'メッセージの表示　無効

　DoCmd.OpenQuery "Q_サブ追加"
　DoCmd.OpenQuery "Q_メイン追加"

　DoCmd.SetWarnings True 'メッセージの表示　有効
　Me.請求NO = DMax("請求NO", "請求書テーブルB") 'コピー先の請求NOを設定
　Me.Requery '再表示
Exit_コピー_Click:
　Exit Sub
Err_コピー_Click:
　MsgBox Err.Description
　Resume Exit_コピー_Click

フォームを開き、コピーボタンをクリックしたところ、
以下のようになりました。(画像Ａ～Ｆ)

Ａ.請求NO「17123101」が表示されている状態でコピーボタンをクリックする。
Ｂ.請求NOが「17123102」(※①の請求NOに+1した値)に変わり、メッセージが表示される。
Ｃ.OKをクリックするとメッセージが消え、請求明細も消える。
Ｄ.フォーカスを移動しようとするとメッセージが表示される。
Ｅ.請求NOを変更してフォーカスを移動すると、請求明細が表示される。
Ｆ.一度フォームを閉じて再度開くと、請求明細が空欄のレコード(請求NO17123102)と、請求明細があるレコードができている。また、コピー元であったレコード(請求NO17123101)が消えている。

希望としては、コピー元のレコードが消えることなく、
コピー元の請求NOに、+1した請求NOでレコードが作成され、
その請求NOを任意の値に変更して保存、となりますが、どのようにすればよいでしょうか？

[sazi]

先ずは、キー重複になる部分を解決しましょう。
フォームを開いた状態で、コピーボタンではなく、Q_サブ追加とQ_メイン追加をそれぞれダブルクリックして下さい。
追加して良いかどうかの確認メッセージが表示されるので、OKとします。
このタイミングで、キーが重複する場合にはエラーとなり追加できません。
多分エラーにはならないかと思います。

[sazi]

おそらくは、フォームが追加されたデータを表示している状態にはなっていないので、更新動作を行った後に追加を行うような動きをしていると思われます。
請求NO（コンボボックス）のイベントでは何か処理を行っていますか？

[sazi]

多分現状の設定に影響ないであろうコードです。
以下に書き換えてみてください。
On Error GoTo Err_コピー_Click
DoCmd.SetWarnings False 'メッセージの表示　無効

DoCmd.OpenQuery "Q_サブ追加"
DoCmd.OpenQuery "Q_メイン追加"

DoCmd.SetWarnings True 'メッセージの表示　有効
Me.Requery '再表示
Me.Recordset.FindFirst "請求NO=" & DMax("請求NO", "請求書テーブルB")
Me.Requery '再表示
Exit_コピー_Click:
Exit Sub
Err_コピー_Click:
MsgBox Err.Description
Resume Exit_コピー_Click

[th_bridge]

フォームを開き、Q_サブ追加とQ_メイン追加をそれぞれダブルクリックしました。

データを表示していない場合は確認メッセージでOKをしてもエラーにならないのですが、データを表示した場合に、Q_メイン追加は問題ないのですが、Q_サブ追加でエラーが発生しました。(画像Ｇ)

請求NO(コンボボックス)ではイベント処理は行っていないです。(画像Ｈ)

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

[sazi]

以前のエラーで明細データに不要なデータ（請求書テーブルBの請求NOの最大値＋１のデータ）が追加されていると思われます。
そのデータは削除して下さい。

[th_bridge]

確認したのですが、最大値(17123101 コピー元)+1の請求NOのデータはありませんでした。
他に何か理由として考えられることはありますでしょうか？

※以前コピーを実行した際にできた、17123102と17123103はフォームの削除ボタンで削除してあります。削除後の状態で再度17123101の請求データを表示させた状態でQ_サブ追加をダブルクリックしてエラーとなりました。

[sazi]

クエリー：Q_請求明細追加の内容を確認して下さい。
IDが追加する項目になっていないでしょうか。

[th_bridge]

Q_請求明細追加＝Q_サブ追加のSQLです。

INSERT INTO 請求書テーブルB ( 請求NO, 取引先コード, 部門, 業務件名 )
SELECT (SELECT Max(請求NO) FROM 請求書テーブルB)+1, 取引先コード, 部門, 業務件名
FROM 請求書テーブルB
WHERE 請求NO=[Forms]![請求データ入力B]![請求NO];

IDは追加項目に入っていないです。
ご面倒かけてすみません。

[sazi]

内容がQ_メイン追加の内容になっています。
この通りなら、同じ処理を２回処理することになりますが、キー重複は発生しませんね。

[th_bridge]

失礼しました。それぞれ以下となります。

【メイン】
INSERT INTO 請求書テーブルB ( 請求NO, 取引先コード, 部門, 業務件名 )
SELECT (SELECT Max(請求NO) FROM 請求書テーブルB)+1 AS 式1, 請求書テーブルB.取引先コード, 請求書テーブルB.部門, 請求書テーブルB.業務件名
FROM 請求書テーブルB
WHERE (((請求書テーブルB.[請求NO])=[Forms]![請求データ入力B]![請求NO]));

【サブ】
INSERT INTO 請求内容テーブル ( 請求NO, 内容, 数量, 単価, 備考 )
SELECT (SELECT Max(請求NO) FROM 請求書テーブルB)+1 AS 式1, 請求内容テーブル.内容, 請求内容テーブル.数量, 請求内容テーブル.単価, 請求内容テーブル.備考
FROM 請求内容テーブル
WHERE (((請求内容テーブル.[請求NO])=[Forms]![請求データ入力B]![請求NO]));

[sazi]

請求内容テーブルのキーはIDなので、IDを指定しなければ自動で設定され【サブ】ではエラーになりようが無いはずなのですが。
請求内容テーブルに手入力で請求NOのみを入力し、追加は行えますか？

[th_bridge]

請求NOを手入力しようとしたところ、IDになぜか50台の値が割り振られ
保存できませんでした。
これがエラーの原因でしょうか？

[sazi]

そうですね。バックアップを取り、MDBの修復を行って下さい。

[th_bridge]

承知しました。
修復をした後で教えていただいた内容をもう一度試してみます。

[sazi]

ところで、最適化/修復についてはオプションの「閉じるときに最適化する」にチェックは入っていますか？
自動で行っていない場合は、MDBが肥大化していきます。
削除してもサイズは変わらず、最適化/修復によってのみ圧縮されます。
MDBのサイズは２Gまでなので、それを超えると破損し修復できなくなります。

[sazi]

オートナンバーのリセットはデータが空の状態でしか行われないようです。
単純なのはID項目を削除後、再度追加してあげることです。（ただし、IDは振り直されるので、そのIDが他の情報とリンクしていないことが前提です。）
一応リンク貼っておきます。
https://support.microsoft.com/ja-jp/help/812718/how-to-reset-an-autonumber-field-value-in-access

[th_bridge]

リンクありがとうございます。
MDBの修復ですが、ツールバーのアイコンで実行した後、
新規レコードを作成すると、(IDの最大値+1)でIDが振られるようになりました。
これで最適化/修復と、オートナンバーのリセットができたものと考えて大丈夫でしょうか？

またご指摘いただいた最適化の自動ですが、オプションよりチェックを入れました。

[th_bridge]

上記の状態で、コピーボタンクリック時のコードに

On Error GoTo Err_コピー_Click
DoCmd.SetWarnings False 'メッセージの表示　無効

DoCmd.OpenQuery "Q_サブ追加"
DoCmd.OpenQuery "Q_メイン追加"

DoCmd.SetWarnings True 'メッセージの表示　有効
Me.Requery '再表示
Me.Recordset.FindFirst "請求NO=" & DMax("請求NO", "請求書テーブルB")
Me.Requery '再表示
Exit_コピー_Click:
Exit Sub
Err_コピー_Click:
MsgBox Err.Description
Resume Exit_コピー_Click

こちらを記述して実行したところ、
１番目のレコードに表示が切り替わりました。
その後、最終レコードを見てみると、
(コピー元の請求NO+1)の値を持ったコピーデータができているのですが、
明細内容は空欄の状態となっていました。
この時、コピー元のデータは消えずに残っている状態でした。

[sazi]

オートナンバーについては、本来の動作になっていますから、大丈夫だと思います。
１番目のレコードへ表示が切り替わる点については、コードでの２個めの、Me.Requery をコメントにしてみて下さい。
明細については、データは追加されていて、表示がされない状態ですか？

[th_bridge]

前回はコピーデータの請求NOを修正してフォーカスを移動すると
空欄だった明細内容が表示されたのですが、
今回は修正→フォーカス移動をしても空欄のままになっています。
請求明細が表示されるようにするにはどうすればよいでしょうか？

[sazi]

かなりスレが長くなっちゃいましたね。
実際のMDBを見た方が早いと思うので、テーブルデータを削除したMDBを送付することは可能ですか？
可能であれば、コメントにメールアドレス（捨てアドで可）を書き込むなどで、行いたいのですが。
（確認出来たらコメントから消す）
取り敢えずは、意思表示のコメントだけ下さい。

[sazi]

セキュリティなどもあるでしょうから、無理にという事ではありません。
手元で、画面を作成して動作検証していますが、コピーボタンでコピーされた新たな請求番号に画面が移りサブフォームもコピーされたものが表示され、意図通りの動作をしています。
※サブフォームはリンクフィールドは親子共に「請求NO」を設定
メインフォームの請求NOはコンボボックスのようですから、当該の請求NOへの移動も兼ねているように思えますが、その処理との兼ね合いで意図通りに動かないのかもしれません。
もう一つは、MDBが破損しかかっていて、意図通りでない動作をしているのかもしれません。
空のMDBを作成し、全オブジェクトをインポートし、そちらで確認してみてはどうでしょうか。
現物が無い状態での推測ではこれぐらいです。

Answer 2

vbaが使えるのであれば、こちらが参考になるかと思います。
https://oshiete.goo.ne.jp/qa/5876811.html?pg=1
7番の回答がほぼやりたいことと同じだと思います。（私も以前見積書で実装しました）

テーブル名は実際のものに置き換える必要がありますし、要件に応じて変更しなければならない部分もあります。
テーブルAの請求NOが主キーで手入力のようなので、処理の最中に仮NO（一時的に999999など）を設定してあげるようにした方がいいかもしれませんね。（オートナンバーのIDを主キーにしたいところですが）

必ずバックアップを取り、テスト用のファイルでお試しください。

Comments

[sazi]

リンク先は解決していないようですけど、大丈夫ですか？

[dit.]

「変更しなければならない部分もあります」が、おおむねNo7の回答でコピーできることをAccess2010で先ほど改めて確認しました。
テーブル名やフィールド名、ボタン名などを数か所修正することになります。
主キーの問題などこのまま使うことはできないですし、VBAを利用するかどうかも不明のため簡素な回答となっています。

[sazi]

了解しました。コメントでエラーが出ている等がありましたので、気になっていました。

[dit.]

ご指摘ありがとうございました。私が実装したのもかなり前のため、内容を再確認するいい機会になりました。
こちらだと解決しているようですね。この手の質問サイトは同じ内容のものがいくつかあってわかりづらいです…。http://qa.itmedia.co.jp/qa5876811.html
結局フィールド名の修正ミス？だったようです

[th_bridge]

dit.さん＞
ご回答ありがとうございます。
saziさん、リンク先の内容のご確認、ありがとうございます。

一度、改めて挙げてくださったリンク先の内容を見てみます。
どうもありがとうございます。

[dit.]

やりたいことと合致しそうなものなので紹介しましたが、th_bridgeさんがVBAを全く使ったことが無ければ少し難しい内容かもしれません。
th_bridgeさんが作っているデータベース用に書き換えないといけない部分が何箇所かあります。
今回改めて実装テストしたので直すべき部分は把握しましたが、うまく伝えられる自信があまりありません。

まずリンク先（後からコメントに入れたURLの方がいいかも）を「こういうこともできるんだ」程度に読んでいただければと思います。

[th_bridge]

VBAについてはここで教えていただいたことを書いたことはあるのですが、正直難しいと考えています。
後のコメント内のURLを今後の参考にさせていただこうと思います。
丁寧にご対応くださいまして、ありがとうございました。