こちらのコミュニティには度々お世話になっております。
早速ですが、現在困っていることを相談させていただきます。
私が所属している部署向けに、契約管理システムを作成しております。
この契約管理システムは、契約詳細や金額情報を登録し閲覧できるものです。
今回困っているのが、登録した情報を編集するためのフォームで起こった問題です。
登録情報の編集フォームにはワークテーブルを使用しており、
フォームのレコードソースには「契約マスタ_ワークテーブル」が設定されています。
仕組みとしては、データの編集のために本テーブルからワークテーブルにレコードを移し、
編集後、登録ボタンを押すとワークテーブルから本テーブルにレコードを移すようになっています。
また、このフォームには「月額マスタ_ワークテーブル」をレコードソースとする
サブフォームが下記の通り埋め込まれています。
サブフォームの使い方としては、請求期間を上部のテキストボックスに入力し、青色の「ごとに登録」ボタンを押すと
サブフォームに期間分のレコードが生成される仕組みです。
また、既に同じ入金月の金額レコードが存在している場合、レコードを上書きをします。
今回起こっているエラーはこちらです。
何回か試してみるとエラーになる場合とならない場合がありました。
<エラーが発生する条件>
- 「税の処理」フィールドを「なし」から「切り上げ」および「切り捨て」に更新しようとした場合
- 既に登録されている期間と登録しようとしている期間が被り、かつ新しい期間のレコードを登録しようとしている場合
※例えば、既に4月～6月分のレコードが登録されている際に、6月～7月分のレコードを登録しようとした場合
「ごとに登録」ボタンに埋め込まれているVBAは以下の通りです。
'フォーム上のテキストボックスから期間を抽出し、期間の差から請求月数を算出
startDate = DateSerial(Me.開始年, Me.開始月, 1) '開始
endDate = DateSerial(Me!終了年, Me!終了月, 1) '終了
Dim monthNum As Long
monthNum = DateDiff("m", startDate, DateSerial(Me.終了年, Me.終了月, 1))
'フォーム上のテキストボックスから頻度を抽出し、請求頻度を選択する
Dim i As Long, s As Long
If Me.入金月頻度 = "1ヶ月" Then
s = 1
ElseIf Me.入金月頻度 = "2ヶ月" Then
s = 2
ElseIf Me.入金月頻度 = "半年" Then
s = 6
ElseIf Me.入金月頻度 = "1年" Then
s = 12
End If
'月額サブフォームに新規レコードを追加する、もしくは入金月が同じ場合上書きする
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("月額マスタ_ワークテーブル", dbOpenDynaset)
With Me.月額サブフォーム.Form.Recordset
For i = 0 To monthNum Step s '請求頻度ごとに
'レコードの登録がひとつもない場合、そのまま追加する
If .RecordCount = 0 Then
.AddNew
![契約番号] = Me.契約番号
![月額] = Me.月額
![入金月] = DateAdd("m", i, startDate)
![税率] = Me.税率
![税の処理] = Me.税の処理
.Update
'既にレコードが存在する場合
Else
'同じ入金月のレコードが登録されていない場合
rs.FindFirst "入金月 = " & "#" & DateAdd("m", i, startDate) & "#"
If rs.NoMatch = True Then
.AddNew
![契約番号] = Me.契約番号
![月額] = Me.月額
![入金月] = DateAdd("m", i, startDate)
![税率] = Me.税率
![税の処理] = Me.税の処理
.Update '★ここでデバッグが出る
'既に同じ入金月のレコードが登録されている場合
Else
rs.Edit
rs![月額] = Me.月額
rs![税率] = Me.税率
rs![税の処理] = Me.税の処理
rs.Update
End If
End If
Next
End With
rs.close
また、エラーが発生した場合にのみ、Accessファイルを閉じようとすると下記のメッセージが表示されます。
このデータベースは、マシン'〇〇〇'のユーザー'〇〇〇'が開いています。
データベースが使用可能になった時点で、再実行してください。
なぜ「税の処理」フィールドを更新した場合にのみエラーが出るか分からず、途方に暮れています…
このような場合、皆様でしたらどこに着目し修正されますでしょうか？
何かとっかかりになるようなものを教えていただけますと大変助かります…！
ここまでご覧いただきありがとうございました🙇♂️
回答ではありませんが
フォーム：契約マスタ_ワークテーブル
サブフォーム：月額マスタ_ワークテーブル
コード中に出てくる「T_月額マスタ_編集_仮」は全く無関係の別なテーブルですか？
またこのテーブルは閉じられる（
rs.Closeされる）ことなくForループの中で何度もCurrentDb.OpenRecordsetされるようですが大丈夫ですか？>hirotonさん
ご指摘いただきありがとうございます！
「T_月額マスタ_編集_仮」は「月額マスタ_ワークテーブル」の書き間違いです。大変失礼いたしました…
無知でお恥ずかしい限りなのですが、このような場合、Forループ外で
recordsetを指定するのが通常なのでしょうか？
エラーが発生しない場合、希望通りの動きができていたので特に気にしておりませんでした…
CurrentDb.OpenRecordsetをForループ外に置いて試してみます！
開発中の契約管理システムがよくわからないのですが、月次売上サマリみたいなものが職場で求められているのでしょうか？個人的には、テーブル設計が気になります。
趣味で開発するのは、いいと思いますが。仕事で使うのであれば、外注やパッケージを検討するといいと思います。
>りんごさん
ご回答いただきありがとうございます！
現在は年度別のExcelシートで管理している売上表を、
データベースで一貫して閲覧できるようにしたいというのがはじまりです。
ワークテーブルの設計が不味いということでしょうか…？汲み取れず申し訳ありません…
実はクラウドサービスや外注も検討したのですが、契約数も数百件規模ですし予算上厳しく…
また、入社してはじめて取り掛かった業務のひとつですので、できれば完成させたいという気持ちがあります…
>> 2
問題なのはOpenに対するCloseがないのほうです。このくらいの規模であれば
Forの外で宣言して終わったらrs.Closeしますが、超巨大なFor文とか、一概にループの外でというわけではありません今のコードだと、開いたテーブルが開きっぱなしのまま放っておかれるので何かしらの不具合の原因になってもおかしくありません。今回のエラーの原因かどうかはわかりませんが、同じテーブルを複数開いてそのうちどれかで編集をしようとしているので原因にもなりそうです
あとは、全体の処理として
If .RecordCount = 0 Thenのところがループの中なのに最高でも1回しか実行されない作りなのが気になりますねこのくらいの処理なら
FindFirstで全数チェックかけても大した問題じゃないのでいっそIf .RecordCount = 0 Thenの制御処理は無くてもいいと思います（実際今のコードはほぼFindFirstで全数チェックになっています）その他変更点として
これはフォームのレコードセットをVBA上で扱う場合、テーブルを直接開く以外の便利な記述方法の内の一つです。より意味の明確なコードにしてるだけで「やりたいこと」自体は同じです
>hirotonさん
で、できました…！！希望通りの処理ができました！！
先週からずっーと悩んでいていたのでとてもすっきりしました…！！
本当にありがとうございます…！！思わず目頭が熱くなりました…！！
恐らく初歩的な内容であるにも関わらず、なぜレコードセットをcloseで閉じる必要があるかを
丁寧にご説明いただきありがとうございます。
テーブルを開きっぱなしと言われると、恐ろしさが重々理解できました…
また、コードの訂正に関してもありがとうございます…！
処理が早くなるかなと思って入れたIf .RecordCount = 0 Thenでしたが、
今試してみたら全く変わらない上に可読性も落ちていましたね…ご指摘ありがとうございます！
RecordsetCloneはこのような場合に使えるのですね！
存在自体は薄々知っていましたが、どう使いこなすべきかよくわからないプロパティでしたので
今回の件で大変勉強になりました。
見返してみると、recordsetの中でもう一度recordsetを開いていたことが複雑化する原因のようでした。
これからはシンプルな作りを心がけます！この度は助けていただき本当にありがとうございました…！
心からの感謝を申し上げます…！
高速化を考えるなら
フォームの Recordset, RecorsetClone, RecordSet.Clone の違いとは？（hatena chipsさん）
データの操作・更新とともにフォームの表示も一緒にやるかどうかでかなり速度が変わります
If .RecordCount = 0 Thenを使うなら「ループ自体を2パターンにする」ですね新規契約（
.RecordCount = 0）の割合が高ければ効果的ですが、正直あまり使う形じゃないかなぁと。もう少し条件を変えて「startDate以上の[入金月]が無ければ」でレコード数チェック（Dcount）するなら実行頻度もあがるかも？もう一つは、チェックするデータがなくなったら残り全てを登録するという手法もありますが、「チェックしなくていいデータ」（チェックの終えたデータ）をどう表現するかということになるのでちょっと難易度が上がります
まぁ、今回の件だとデータ数はかなり少ないでしょうからチェック手法での差はほとんど出ないと思います。まずは冒頭の「フォームの更新が同時に起こる操作」に注目してみてください
>hirotonさん
参考資料をありがとうございます！とても分かりやすかったです！
そもそもRecordsetChoneとRecordset.Cloneの2種類があることをはじめて知りました！
千件単位のデータを処理すると、処理時間にはっきりと差が出るのですね…
これからはこの差を念頭に置いて、どの処理を使用すべきかを考えたいと思いました。
確かにForループを2つに分けた方がごちゃごちゃせず見やすいです！
そして、「実行頻度の高さから処理方法を考える」という視点を持っていなかったので、肝に銘じました…
だいたいひとつくらいしか処理するアイデアが浮かばないので
もっとこうできないかな？と発想を広げられるよう頑張ります！
>> 4
まず、テーブルがわからなかったのですが、こんな感じですか？
契約マスタ_ワークテーブル:契約番号,契約名,請求先,顧客ID,…
単独主キー 請求先と顧客の関係性は何かありますか？
月額マスタ_ワークテーブル:月額ID,契約番号,入金月,税抜価格,…
単独主キー
次に、業務がわからなかったのですが、使った分だけお支払い、上限ありのサービスをイメージすれば良いですか？
最後に、本テーブルって何でしょうか？毎回、ワークテーブルのレコード全削除、本テーブルのレコードを全コピー、登録ボタンを押すと、全置換されたりするのでしょうか。
>りんごさん
テーブル構造をうまく伝えられず申し訳ありません…
以下の画像で伝わりますでしょうか…？
月額マスタには単独主キーが存在しません。
(参考書でテーブルには必ず主キーを設定すべしと書いてあったため、
最初は月額IDフィールドを作成していたのですが、
編集したり、消したりという処理が多く、連番を管理する技術がなかったため
フィールド自体を消してしまいました…)
業務内容は、数年単位のリース契約が主です。契約期間が終わるまで、特定の月に請求するという感じです。
本テーブルという分かりづらい言葉を使ってしまい、申し訳ありません…
新規登録や編集に使うワークテーブルに対し、
データを蓄積するテーブルという意味で「本テーブル」と書きました。
例えば、編集する場合の流れだと
① 帳票形式の検索フォームで、ユーザーが編集したいレコードを選択する
② 選択したレコードの契約番号をフラグテーブルに追加し、同じ契約番号のレコードの編集を防ぐ
③ データを蓄積しているテーブル(本テーブル)から、選択したレコードの契約番号を検索する
④ 検索したレコードを空のワークテーブルに移す
⑤ ワークテーブルがレコードソースの編集フォームを開く
⑥ ユーザーが編集する
・フォーム上の登録ボタンを押した場合
⑦ 編集したレコードの契約番号を本テーブルから検索し、そのレコードを削除する
⑧ ワークテーブルから本テーブルにレコードを移す
⑨ ワークテーブルからレコードを全削除する
⑩ フラグテーブルから編集したレコードを消す
・フォーム上のキャンセルボタンを押した場合/ユーザーが画面を閉じた場合
⑦ ワークテーブルからレコードを全削除する
⑧ フラグテーブルから編集したレコードを消す
質問文中のコードが解決済みコードに書き換えられているようですが、なお不具合ありという事でしょうか？
>りんごさん
hirotonさんが書いてくださったコードを使用したことで、無事解決いたしました！
>> 11
試行錯誤するのであれば、主キーを消さずに出来る範囲で頑張る方がいいと思いますよ。絵に描いた餅になりそうで心配です。
テーブル同士に線を引く時は、データベースツールタブのリレーションシップがオススメです。参照整合性を設定できるので、リンク切れ、怪しいリンクを未然に防いでくれるでしょう。
もしかしたら、変動支払条件みたいなものがあるのかなぁ。
やった事がないので、ワークテーブル、フラグテーブルがセオリーなのかわかりません。何かあればどなたか回答をお願いします。