Microsoft Access 掲示板

今回問題にしているメインフォーム（フォームA）でフィルタリングがあるため、

サブフォームのレコードソースのクエリ、または、コントロールソースで、フォームAを参照しているということでしょうか。

もし、使いわわしたいなら、この参照はやめて別の方法を考えることになるでしょう。
下記のようないくつかの方法が考えられます。状況に応じて選択してください。

• メインフォームの「リンク親フィールド」「リンク子フィールド」の設定でフィルタリングする。

• 親フォームのVBAでFilterプロパティを設定してフィルタリングする。

• サブフォームからの参照を絶対参照ではなく相対参照にする。（メインフォームが変わっても条件が同じになる場合）
  [Parent]![コントロール名] で親フォームのコントロールを相対的に参照できます。

なかなかむずかしそうですね
EXCELでやっていたときは
COUNTIFSやCOUNTSUMをつかってやっていました
・月の所定労働時間から実際の勤務時間を引いていき、当月あと何時間勤務できるかを表示
というのもなかなか大変そうですか？

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

このサブフォームは2つのメインフォームで使用する汎用フォームとして設定しましたが、
今回問題にしているメインフォーム（フォームA）でフィルタリングがあるため、
別のメインフォーム（フォームB）を開くときに、フォームAのテキストボックスに関する
パラメータ入力ダイアログを要求されて困っています。
そのため、合計はサブフォームではなくメインフォームで計算しようと考えました。
(できない理由は理解しました)

サブフォームの使いまわしをやめるしかないでしょうか？

Sum関数は、自身のフォームのレコードソースのフィールドを対象とします。別フォームのフィールドは使用できません。サブフォームでも同様です。

サブフォーム内の =Sum([Qty]) のテキストボックスを参照するようにしてください。このテキストボックス名を SumQty として、メインフォームのテキストボックスのコントロールソースを下記のように設定します。

=[sbf_Treatment].[Forms]![SumQty]

サブフォーム内の =Sum([Qty]) を表示させたくない場合、可視を「いいえ」にしておけばいいでしょう。

そのフォームでしか使わず、かつ、特定の日付を元に計算するなら計算式一つでやってもいいんじゃないかな

週ごとの集計を表示したいとか、年間の集計を表示したいとかになると処理の効率化も考えたほうがよさそうだけども
週を識別するIDを作りたいだけならInt([日付]/7)で計算させてもいいんじゃないですかね

簡単なのは定義域集計関数を使ってQ1上で計算する方法かな？
DSum 関数
ex)

商品ごとの合計: DSum("[数量]*[単価]","T1","商品ID="&[商品ID])

アドバイスありがとうございます。
試してみます

恥ずかしながら、queryについてよく把握できていません。
調べてみます。

集計クエリ、また、集計クエリを含むクエリは更新できません。

入力できる状態で合計を表示したい場合は、DSum関数、DCount関数などで集計すれば可能です。ただし、件数が多いと重くなりがちです。

合計:フィールド1 + フィールド2 というのはカレントレコード内のみでの計算ですのですぐに反映されます。
100以上:DCount("合計","クエリ1","合計>100")というのは複数レコードを対象とする集計ですので反映させるには、閉じてから開くか、レコード保存した上で再クエリする必要があります。

アドバイスありがとうございます。
試してみます

前もって作成しておいてもいいでしょう。手入力が面倒なら、VBAか追加クエリで一気に生成することもできます。

あるいは、フォームで新規入力するときに、VBAで自動入力してもいいでしょう。

土・日曜日の日数は、年の最初の日付の曜日が取得できれば、計算で求められますね。

祝日は祝日を格納するテーブルを作成しておいて、DCount関数でカウントすればいいでしょう。
あるいは、祝日判定関数を利用してカウントしてもいいでしょう。例えば下記で公開されている関数が使えます。

AddinBox(祝日マクロ-1)

サブフォームのレコードの内容を変更する
かつ
M_鍵マスターの情報はメインフォームにもサブフォームにもない

なので、クエリで修正すればいいですね。ただし、サブフォームのレコードの状態が新規レコードの場合と既存レコードの場合の2通りが考えられるので、状態をチェックして追加クエリか更新クエリか、どちらを実行するか条件分岐が必要でしょう

Private Sub ボタン_Click()
    If Me.NewRecord Then
        CurrentDb.QueryDefs("追加クエリ").Execute
        me.Requery
    Else
        CurrentDb.QueryDefs("更新クエリ").Execute
        me.Refresh
    End If
End Sub

集計フィールドで使える関数には制限があります。
「その数値にランダムで5～10を加算した数値」はどのように取得しているのですか。
Rnd関数を使っているのなら、集計フィールドでは使えません。
他にもDate関数とかNow関数のようにその時々で異なる値を返す関数は使用不可です。
「ランダムな数値」ということはその時々で異なる値を返すことになるので集計フィールドでは無理だと思います。

ちょっと調べてみたのですが、集計フィールドというので、フォームAにランダムで加算する、とういうのは運用的にはだめなのでしょうか？

テーブル名　T_週のレコードは前もって数年分つくるもいうことですか？

「週ID」フィールドも勤務時間管理テーブルに持たせます
といのは、どのように入力するのですか？
初心者で理解力がなくてすみません

hatena様
いつもお世話になっております。
早速のご返答ありがとうございます。
該当一年間で土・日曜日の日数や祝日日数をレポートに表示させる為にはどうすればいいでしょうか。
よろしくお願いします。

早速のご返事、誠にありがとうございます。
参考にさせていただきます。

テーブル イベントにデータ マクロを追加する

比較的新しい機能で、テーブルに直接機能を追加することはできますね。hirotonは使ったことありません。情報の少ない仕組みはそれだけで生産性が落ちるので

フォームを介しての実装はテンプレみたいなものなのでしょう。目的自体は単純なモノのように見えるので「フィールドA→フィールドB」で実装できているものがあるなら「不具合」はないと思いますよ

レポートで６ヶ月分のカレンダー表示 - hatena chips

上記の テーブル「T_月」に 0 から 11 までの数値を入力すれば12ヶ月分のデータが出力されます。
それを元にレポートを作成すればいいでしょう。
あとは、上記のリンク先の方法を理解して応用してください。

ページ関係の制御はメインレポート側で行いますので、サブフォームにはページという概念はないです。
レポートヘッダーかグループヘッダーを使ってください。

週に2回（月曜日を起算日とする）

ということなら、まずは週マスターテーブルを作成すると管理が楽になりそうです。

例えば下記のような感じです。

テーブル名　T_週　

フォームはメイン/サブフォーム形式にして、
メインフォームは上記のテーブルをレコードソースにして、サブフォームは勤務時間管理用のテーブルをレコードソースにします。「週ID」フィールドも勤務時間管理テーブルに持たせます。

サブフォームのヘッダーかフッターにテキストボックスを配置してコントロールソースに下記の式を設定すれば、
勤務時間計や勤務時間が8時間以上の回数を表示できます。

=Sum([退勤時間]-[出勤時間]) * 24

=Sum(IIf((([退勤時間]-[出勤時間]) * 24)>=8, 1, 0))

テーブルを開いてそこで直接更新しても、テーブルやテーブルのフィールドではイベントが発生しないので、自動でフィールドBに計算結果を代入することはできません。

フィールドAを更新したときに自動でフィールドBも更新するには、テーブルと連結した入力フォームを作成する必要があります。
フォーム上では、フィールドと連結したテキストボックスで入力することになります。
テキストボックスの更新後処理イベントを使うことになります。

以上の説明で理解いただけたでしょうか。
不明な点があれば、具体的に質問してください。

フィールドA→テキストボックス→フィールドBとするのですか？
フィールドA→フィールドBではなにか不具合がありますか？

どちらでもお好みでいいと思います。

単価×個数 というような単純な式ならクエリにする場合が多いですね。
フォームや他のレポートでも使いまわせますので。

複雑な式で、そのレポートのみでしか使用しない場合はレポート上のテキストボックスのコントロールソースに式を設定する場合もあります。デザインビューですぐに確認できますし、修正もそこで完了します。

しかし、たいていは入力フォームでも計算結果を表示させる場合が多いので、共通のクエリにしてクエリで式を設定する方が多いですね。

目的次第だと思います。

入力した直後に結果を格納したいのなら、テーブルと連結した入力フォームを作成して、フィールドAと連結したテキストボックスの更新後処理のイベントでフィールドBに計算結果を格納することになります。

レポートを選択すれば幅は表示されます。ただし、変更はデザインビューにする必要があります。

レイアウトで数値指定で幅を設定できましたっけ？
プロパティ見てたけど見つけられませんでした

いつも迅速、ご丁寧なご返事をありがとうございます。
参考にして頑張りたいと思います。

用紙の幅－左右の余白 が印刷範囲の幅というように計算するか、
プレビューのさせて、はみ出たら狭くする、足らなければ広くするというようにレポート幅を調整することになりますね。

例えば、A4縦なら用紙幅210mm 左右余白が各15mmなら 210-(15*2)＝180mm が印刷範囲の幅になります。

また、A4の左右に2ページ分印刷するときは、A5で作成して、それを印刷設定で1枚に2ページ印刷するようにするのでしょうか？印刷時に列数を2にする方法だと、レイアウトをつくるときにどこまで幅をとると2列になるかの把握がしずらくなりますよね？

項目のレイアウトによって、印刷設定で2ページ印刷にするか、列数２にするか、レポート幅を用紙幅の2倍にするか、を選択します。

そこで、クエリに代入コーナーを設け、2021/3と入力したら、」
の意味が、
クエリのデザインビューで、フィールド欄に、[]で囲んだ式を入力しており、プレビューするときにダイアログが開きそこに日付を入力するとその日付で抽出されるということでしょうか。
この場合、フィールド欄に入力した式をパラメーターといい、パラメーターを含むクエリのことをパラメータクエリといいます。
基本的な用語ですので覚えておきましょう。

現状は、商品一覧クエリに[日付指定【例：2021/3】]というパラメーターが設定してあり、商品一覧レポート(メインレポート)のレコードソースになっている。
この商品一覧クエリからさらに集計クエリを作成して、商品別集計クエリ（サブレポート）のレコードソースにしている。

この場合、それぞれがパラメータークエリになりますので、2回入力を要求されます。

上記のような状況だと理解しました。

対処法ですが、パラメーターの式にはフォームのテキストボックスを参照する式にすることができます。そうすると、フォームを開いてテキストボックスに日付を入力してからレポートを開けば、パラメーターダイアログか開くことはないです。

設定する式は、下記のような書式になります。

Forms!フォーム名!テキストボックス名

テキストボックスなら設定で日付選択カレンダーを表示させたりすることが出来ますし、そのフォームにレポートを開くコマンドボタンを配置しておけば、より使いやすいものになります。

朱色さん
ありがとうございます。

大変申し訳ないのですが、私の不勉強故、バックエンドデータベース上のテーブルを参照する場合に「SELECT~ FROM~ 」や「INSERT INTO~ VALUE~」を用いたりすることと、朱色さんが今回説明してくださっているリンクテーブルへの変更云々との違いがよくわかりません。

今回提示している、UserLogに関するVBA以外は変更していませんが、情報の閲覧や編集はできているので、大きな不便は感じていないのですが…。

hirotonさん

申し訳ありません、言葉足らずでした。
RemoveDate = Format(RemoveDate, "yyyy/mm/dd hh:mm")　を抜けば、という
考えで勝手に先走ってました…。申し訳ありません。

日付が内部では数値で云々の箇所は、昔に、自作カレンダーフォームの移動関数
のあたりでhatena氏から聞き及んでおりました…！

ご尽力を無駄にしてしまうような形となってしまい申し訳ありません。

それともエラー回避のために念のためつけておくのが安牌でしょうか…。

RemoveDateをDim RemoveDate As Dateと宣言している（date型で宣言している）ので「#」は必須です（ないと困ったことになります）

Dim d As Date
d = date
Debug.print d & ""

こういう処理（date型を文字列にして出力する）と

2021/04/08

日付（＋時刻）っぽい文字に変わります

問題のコードも

strSQL = strSQL & "where 日時<#" & RemoveDate & "#"

が実際に処理されるときはRemoveDateが日付っぽい文字に変わるのでそれをSQL文上での日付データとして扱うためには「#」で囲む必要があります。これを怠ると、例えば

where 日時<2021/4/8

のようなSQL文になるので、「2021/4/8」=「2021÷4÷8」=「63.15625」

where 日時<63.15625

が実行されます。やばいですね

ちなみにdate型というのはコンピュータの内部では数値データです。
Date Data Type (Visual Basic)
つまり、上のSQLは

where 日時<#1900/03/03 3:45:00#

と同じです。SQL構文として問題のない文なので動きます。でも、これでは意図した通りの結果は得られませんよね？ちょーやばいです。エラーを出してくれたほうがマシです

hirotonさん
ありがとうございます。

元のプログラム文で、日数指定をしてから、「～～以前のデータを削除します」みたいなMSGBOX
を出していたので、formatで文字列に変えてました。

ってことは、strSQL内のWHERE条件も　"where 日時<" & RemoveDate &　で
わざわざ＃＃囲みしなくても良いんでしょうか…。
それともエラー回避のために念のためつけておくのが安牌でしょうか…。

ちなみにですけど、テーブルをリンクテーブルにしたらだめなんですかね？
そうすればVBA変えなくていいしクエリとか使ってるならそれもそのままでOKになるし･･･フォルダ構成が変わったとかの時も対応できるし、バッチリだとおもうんですが。
あとACCSESSはデータを消しても容量が減らないので、捨てるデータはたとえば月単位でDB作って書き込み、月が替わったらDB切り替えするとか、そもそも隠す内容でない＆検索するとか再利用しないならCSVに吐き出していくとかでも良い気がします。

なおリンクテーブルにするなら参考↓
ACCESSのVBAでリンクテーブルを更新する

難しい質問ですねぇ
実行されるクエリが、という意味ならDB.Execute strSQLの行でのstrSQLの中身は問題ないように思います

VBAコードの良し悪しという意味なら突っ込みどころも出てきます

とりあえず、完全にNGなので指摘しますが

RemoveDate = Format(RemoveDate, "yyyy/mm/dd hh:mm")

RemoveDateはDate型の変数です。Format()は文字列を返します。Date型の変数に文字列を代入しようとしているので暗黙の型変換が発生します。この行は結局

RemoveDate = RemoveDate

と変わりません。完全に無駄な処理です

他の記述については、好みの問題まで発展しそうな話ので触れないことにしておきます

hiroton さん
お早いご返事誠に痛み入ります。
実際に置換してみたところ、うまくいきました・・・！
ありがとうございます！
VBAでSQL文を書いてると、ゴチャついてきちゃいますね。
追加で代入していく方法も今後用いて、着実にミスの無いようにしていきたいです。

また、早速参考にして、UserLogの古い履歴をイイカンジに消す CleanLog　にも頑張って適用してみました。下記。

Public Sub CleanLog(Day As Integer)
'上記で登録したユーザーAccessデータを、いい感じの古さの物から削除

  Dim RemoveDate As Date: RemoveDate = DateAdd("d", -Day, Now)
  '今の日にちから、●●日前　を特定する関数
    
  RemoveDate = Format(RemoveDate, "yyyy/mm/dd hh:mm")
  '特定した日付を、xxxx/xx/xx xx:xx の形に整える

  Dim DB As DAO.Database: Set DB = CurrentDb
  Dim strSQL As String
  
  strSQL = strSQL & "delete from T_UserLog "
  strSQL = strSQL & "IN '' [MS Access; PWD=PASSWORD; DATABASE=C:\略\MnfctMng_BEDB.accdb;] "
  strSQL = strSQL & "where 日時<#" & RemoveDate & "#"

  DB.Execute strSQL
  '上記の日時からのデータをすべて削除する

End sub

これによって、BEDBの方の古いログも消すことができましたが、これで問題ないでしょうか…。
ログなので別にミスって全消去になってもそこまで問題はないのですが、DELETEを扱うので
すこし心配になってしまって。
ご教示の程宜しくお願い致します。

早速のご返事誠にありがとうございます。

試してみたいと思います。

コードの記述はちょっと気に掛けてあげると問題にも気づきやすくなります

問題のあるコードそのまま書き換え

Dim strSQL As String
strSQL = strSQL & "insert into T_UserLog (場所,行為,ユーザー名,マシン名)"
strSQL = strSQL & " values ('" & PN & "','" & AN & "','" & WSH.UserName & "','" & WSH.ComputerName & "')"
strSQL = strSQL & " T_UserLog IN '' [MS Access; DATABASE=C:\略\MnfctMng_BEDB.accdb;PWD=PASSWORD;];"""
CurrentDb.Execute strSQL

INSERT INTO ステートメント

外部ファイルに接続する場合の記述は

複数のレコードを追加するクエリは、次のとおりです。

INSERT INTO target [(field1[, field2[, ...]])] [IN externaldatabase]
SELECT [source.]field1[, field2[, ...]
FROM tableexpression

1 つのレコードを追加するクエリは、次のとおりです。

INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])

VALUESを使った記述の場合「外部ファイルに接続する方法」の記述がありませんね・・・

まぁ、Microsoftの公式資料なんてそんなもんです。きっと似たような記述をすればなんとかなるはず

INSERT INTO target [(field1[, field2[, ...]])] [IN externaldatabase]
VALUES (value1[, value2[, ...])

Dim strSQL As String
strSQL = strSQL & "insert into T_UserLog (場所,行為,ユーザー名,マシン名)"
strSQL = strSQL & " IN '' [MS Access;DATABASE=C:\略\MnfctMng_BEDB.accdb;PWD=PASSWORD;]"
strSQL = strSQL & " values ('" & PN & "','" & AN & "','" & WSH.UserName & "','" & WSH.ComputerName & "')"
CurrentDb.Execute strSQL

お試しを