Access (VBA)

Access VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(指定なし : 指定なし)
yes/no型フィールドをTrueにしたレコードのみを集計する方法について
投稿日時: 21/07/12 16:12:43
投稿者: だいふくもち

現在Accessを勉強中の者です。ご回答くださる皆様には大変お世話になっております。
さっそくですが、今回困っていることを質問させていただきます。
 
最終的な目的は、入金管理を行うことです。
リース契約料金の振り込みが確認できた場合、帳票形式のフォームで企業名を検索し、
抽出されたレコードの入金済フィールド(yes/no型)をチェックし、
入金日フィールド(日付型)に日付を記入することで、入金済みであることにします。
 
基本的に1件ずつ処理していきますが、契約番号や件名は違っても請求先が同じ企業に関しては、
一括で入金されているため、金額がまとめられている場合があります。
そこで、一括選択ボタンを作成し、レコードを一括で☑できるようにしています。
その際、合計額コントロールをフォーム下記に表示することで
口座に入金された金額と照らし合わせるようにしております。
 
<振込>
〇〇
振込額:45,000円
 
<フォーム>
検索ボックス:〇〇
契約番号 | 請求先 | 月額 | 入金済 | 入金日
100100 | 〇〇 | \10,000 | ☑ | 2021/7/1
200200 | 〇〇 | \20,000 | ☑ | 2021/7/1
300300 | 〇〇 | \15,000 | ☑ | 2021/7/1
合計金額:45,000円
※合計金額は入金済フィールドが☑されているもののみ集計します
 
なぜこのような設計にしているかというと、
例えば契約番号「200200」のみ入金が遅れて振込額が「\25,000」になるような月もあるため、
一括選択ボタンを押して「合計金額」を表示した際に、振込額との差を明らかにして、
入金されていないものに関しては☑を外すようにするためです。
 
ここからが困っている部分なのですが、合計金額テキストボックスに表示される金額にずれが生じます。
具体的に言うと、一括選択ボタンを使用せず手動で上から☑処理を行う際、
100100のレコードの入金済を☑し、200200のレコードを☑したときにやっと
合計金額に「\10,000」が表示されます。
入金済フィールドのさまざまなイベント(プロパティシートに載っているイベント)
で試してみたのですが、どうもずれてしまいます。
他にしかるべきイベントがあるのでしょうか?
それともそもそものやり方が何かまずいのでしょうか…?
 
Private Sub 入金済_GotFocus()
 
Dim totalFilter As String
totalFilter = totalFilter & " AND 入金済=True"
 
'フォーム上のテキストボックスの期間内に当てはまる場合、抽出する
totalFilter = totalFilter & " AND 入金月 Between #" & DateSerial(Year(Me.FROM年月), Month(Me.FROM年月), 1) & "# AND #" & DateSerial(Year(Me.TO年月), Month(Me.TO年月) + 1, 0) & "#"
 
'フォーム状のテキストボックスの値に応じて、抽出する
If Not IsNull(Me.SEARCH契約番号) Then
     totalFilter = totalFilter & " AND 契約番号 Like '*" & Me.SEARCH契約番号 & "*'"
End If
 
If Not IsNull(Me.SEARCH請求先) Then
     totalFilter = totalFilter & " AND 請求先 Like '*" & Me.SEARCH請求先 & "*'"
End If
 
If Not IsNull(Me.SEARCH契約名) Then
     totalFilter = totalFilter & " AND 契約名 Like '*" & Me.SEARCH契約名 & "*'"
End If
 
Me.合計金額 = DSum("月額", "Q_入金確認クエリ", Mid(totalFilter, 6))
 
End Sub
 
度々お手数をお掛けして申し訳ありませんが、何かしらの不備に気づかれた方が
いらっしゃいましたら教えていただけませんでしょうか…?
どうぞよろしくお願いいたします。

回答
投稿日時: 21/07/12 17:14:20
投稿者: Suzu

入金済_GotFocus ではなく、入金済_AfterUpDate ではいかがでしょうか?
 
 
それでも 希望の動作にならない場合には、
 Docmd.SaveRecord または フォームに対し Requery を行い レコードの保存を実施後
集計する様にしてみてください。
 
 
余談ですが、
クエリ 【Q_入金確認クエリ】は、このフォームのレコードソースでしょうか?
その場合、フォーム上のフィルターにて、請求先の抽出をおこなうのではなく
 
フォームのテキストボックスに入力された値をクエリの抽出条件に設定する
https://www.moug.net/tech/acopr/0040014.html
 
を参考に、クエリ側にて抽出を行った方が 集計は楽ですので、試してみてください。

投稿日時: 21/07/14 10:14:31
投稿者: だいふくもち

>Suzu様
ご回答いただき本当にありがとうございます!
迅速なアドバイスにも関わらず、返信が遅くなってしまい申し訳ありません…!
 
入金済_AfterUpDateでも同様の動作になってしまいました…
また、requeryもレコードの保存も仕様上しようできないため止む無く断念しました。
せっかく提案していただいたのに大変申し訳ありません…
 
そこで、そもそもの設計を見返し、入金済フィールドが☑されたものを集計するのではなく
フォーム上に表示している全レコードの金額を表示するだけでも
作業上問題がなかったため、シンプルにそのようにいたしました。
 
また、Suzu様がご提案してくださった「クエリの抽出条件に値を設定する」というやり方に変え、
フォーム上のテキストボックスに入力された値を、クエリの抽出条件として設定してみました。
ごちゃごちゃとしていたモジュールがすっきりしたので大変良かったです!
 
入金月:

Between [Forms]![F_入金確認]![FROM年月] And [Forms]![F_入金確認]![TO年月]

契約番号(請求先、契約名も同様):
Like IIf([Forms]![F_入金確認]![SEARCH契約番号] Is Null,"*","*" & [Forms]![F_入金確認]![SEARCH契約番号] & "*")

しかし、ひとつ分からない場所がありまして…
 
備考フィールドはレコードに入力されているものと入力されてないものがあります。
下記の条件で抽出すると、備考フィールドがNull値のレコードを拾わず、
何かしら文字が入力されたレコードしか抽出してくれません…
Like IIf([Forms]![F_入金確認]![SEARCH備考] Is Null,"*","*" & [Forms]![F_入金確認]![SEARCH備考] & "*")

そこで、クエリの「抽出条件」の「または」の部分にNull値を抽出する条件を加えてみたのですが、
うまく抽出できず困っております…
Like IIf([Forms]![F_入金確認]![SEARCH備考] Is Null,"","*" & [Forms]![F_入金確認]![SEARCH備考] & "*")
⇒ダメでした
Like IIf([Forms]![F_入金確認]![SEARCH備考] Is Null,Is Null,"*" & [Forms]![F_入金確認]![SEARCH備考] & "*")
⇒ダメでした
 
本題から逸れてしまい申し訳ないのですが、もしご存じでしたら
フォーム上の備考検索用テキストボックスが空白の場合、備考フィールドのNull値も抽出する方法を
ご教授いただけませんでしょうか…?

回答
投稿日時: 21/07/14 16:56:46
投稿者: Suzu

引用:
入金済_AfterUpDateでも同様の動作になってしまいました…
また、requeryもレコードの保存も仕様上しようできないため止む無く断念しました。
せっかく提案していただいたのに大変申し訳ありません…
 
そこで、そもそもの設計を見返し、入金済フィールドが☑されたものを集計するのではなく
フォーム上に表示している全レコードの金額を表示するだけでも
作業上問題がなかったため、シンプルにそのようにいたしました。

 
AfterUpdateでも一緒でしたか。失礼しました。
 
しようできない ⇒ 使用できない だとして、
その理由はなんでしょうか?
以前のご質問にあった、サブフォームのレコードは、登録ボタン を押したときのみ登録
との兼ね合いでしょうか?
 
それを、ワークテーブルを使用し実現しているのであれば、レコード保存を行っても良いはずなのですが・・
 
別の方法で解決できたのであれば良かったです。
 
 
 
Like IIf([Forms]![F_入金確認]![SEARCH契約番号] Is Null,"*","*" & [Forms]![F_入金確認]![SEARCH契約番号] & "*")

 
SQLであれば、
WHERE IIf(IsNull([Forms]![F_入金確認]![SEARCH契約番号]),True, [契約番号] LIKE "*" & [Forms]![F_入金確認]![SEARCH契約番号] & "*") = True
でどうでしょうか。

投稿日時: 21/07/19 10:31:52
投稿者: だいふくもち

>Suzu様
ご返信いただき本当にありがとうございます!
「しようできない」⇒「使用できない」ですね…失礼いたしました。
 
requeryができない理由としましては、
このフォームのレコードソースにはクエリを設定しているのですが、
このクエリの入金済フィールドの抽出条件に、フォーム上のコンボボックスの値を設定しており、
コンボボックスで「未入金」を選択すると、入金済フィールドが「False」のレコードを抽出、
コンボボックスで「入金済」を選択すると、入金済フィールドが「True」のレコードを抽出いたします。
このコンボボックスの規定値は「未入金」のため、
フォームを開くと入金済フィールドが「False」のレコードのみ表示されます。
そのため途中で再クエリいたしますと、入金済フィールドを「True」にしたレコードが
画面から消えてしまいますので、再クエリを使用するのは難しいと判断した次第です。
 
また、ワークテーブルを実装していないからか、
教えていただいたDocmd.SaveRecordもエラーが出てしまいました…
皆様の推奨で、別のサブフォームを持つ登録画面ではワークテーブルを持たせたのですが、
入金管理フォームには特にサブフォームもないので、
特に必要ないかなーと思い敢えて作らなかったのですが…
シンプルなフォームでもワークテーブルは合った方が後々便利なのでしょうね…反省しました。
 
そして、SQLのご提案ありがとうございます!おかげさまで無事解決いたしました!