アクセス2007 フォームフィルターをレポートにも

質問

アクセス2007　フォームフィルターをレポートにも

フォームＡ---「家」データ
　　　　　　　「家ＩＤ」　佐藤さん、斎藤さん、住所、電話等
フォームＢ---「家族内」データ
　　　　　　　各家の家族構成　お父さんの誕生日、お母さんの誕生日などなど「家ＩＤ」有り

フォームＢでフィルターをかけ、「家ＩＤ」を元にフォームＡで該当するデータを表示させています。

----------------------------------------------------
　　Set rs = Forms.フォームＢ.RecordsetClone
　　Do Until rs.EOF
　　　　フィルタ = フィルタ & "," & rs!家ＩＤ
rs.MoveNext
Loop
rs.Close
　　フィルタ = "家ＩＤ IN (" & Mid$(フィルタ, 2) & ")"

　　DoCmd.OpenForm "フォームＡ", WhereCondition:=フィルタ,
----------------------------------------------------
※フィルタは255文字を超える場合有り

このフォームＡを元に　レポートＣ（はがき宛先）で
DoCmd.OpenReport "レポートＣ", acViewPreview, WhereCondition:=フィルタ（または、forms.フォームＡ.Filter）
とすると「フィルターが長い」とエラーになります。

OpenFormでのWhereCondition:=は長くても受け付けますが、OpenReportの方は受付ないみたいです。

リレーションの知識不足のまま、進めてきたシッペ返しが今きているのですが、
こんな場合どうしたらフォームの検索条件をレポートに反映できるでしょうか？

回答No.6

DexMachina · ベストアンサー

フォームやレポートのレコードソースには、テーブルやクエリの他に
SQL文を直接指定してやることもできます。
一例：
http://okwave.jp/qa/q5784557.html

ですので、フォームAのフィルタをDoCmd.OpenReportのWhere
Conditionに指定する代わりに、レポートのレコードソースを
切り替えてしまうのが、現状からの大幅な改修は行わずに
目的の結果を得られる解決策ではないかと思います。
(No.5のお礼欄の対応をされた後では、ちょっと遅かったかも
　しれませんが(汗)：
　但し、『フォームAのクローンをつくって「選択」をTrue』という
　操作で、もしもテーブルは元のフォームAと同じままだとすると、
　　・クローンではなくフォームAそのもので処理してもいいか、
　　・フォームだけでなくテーブルも別に用意しないとまずいか、
　のような気がします(汗))

以下、上記内容での改修案になります：

【現状】
DoCmd.OpenReport "レポートＣ", acViewPreview, WhereCondition:=フィルタ

【改修】
'フィルタは使用せずにレポートを開く
DoCmd.OpenReport "レポートＣ", acViewPreview
'レポートのレコードソースを差し替え
'(元のレコードソースが「クエリZ」だった場合)
Reports!レポートＣ.RecordSource = "Select * From クエリZ Where " & フィルタ & ";"

上記の例は、元のコードが「フォームAで現在フィルタが
適用されているか」を判定していないので、それを踏襲
した形としました。
通常は、フィルタを解除(フォームのFilterOnプロパティを
Falseに設定、或いはツールバー等で「フィルタの解除」
を指定)しても、フォームのFilterプロパティには、直前の
条件が記録されたままになりますので、フォームAの
フィルタを解除した状態で、上記コードでレポートを開くと、
レポート側はフォームAと異なる結果を表示してしまいます。
これを避けるためには、フィルタ解除時に「Filter=""」の
代入を行うか、上記のコードを以下のようにして下さい：

【再改修】
Dim strRs As String 　　'変数を宣言
DoCmd.OpenReport "レポートＣ", acViewPreview
'フォームAのフィルタ適用状況に応じてレコードソースを選択
Select Case Forms!フォームA.FilterOn
　　Case True　　'フォームAでフィルタ適用済
　　　　strRs = "Select * From クエリZ Where " & フィルタ & ";"
　　Case Else　　'フォームAでフィルタ解除済
　　　　strRs = "クエリZ"
End Select
'選択したレコードソースに差し替え
Reports!レポートＣ.RecordSource = strRs

・・・以上です。

お礼

お礼が大変遅くなり申し訳ありません。

「レコードソースの差し替え」了解しました。
一度チャレンジしたのですが、コードの書き方がわからず挫折してました。

クローンを使うのはスピードが全然違う為でした。
フォームでループさせるとスクロールもしてしまいます。
クローンだと一瞬で終わりますので。

ほぼ完成に近づいている状態で、レコードソースの差し替えについては今後勉強して使わせて頂きます。

ご回答、ありがとうございました。

回答No.5

NOBNNN

#1 です。

一部、私の側でのミスがりましたので訂正いたします。

Access の使用では　SQL　の長さは　64,000文字、また　vBA で記述できる文字列型については

文字列型 (String)
　 文字列には、可変長文字列と固定長文字列の 2 種類があります。
　 可変長文字列には、約 2GB (2^31) までの文字を格納することができます。
　 固定長文字列には、1 ～ 約 2GB (2^31) の文字を格納することができます。

となっています。

255文字としたのは　デバッグウインドウで見るとそのように見えるので、勘違いしていました。

詳細は　http://www.accessclub.jp/bbs2/0040/beginter12916.html

いずれにせよ　長いＳＱＬ文は間違いの元になります。　できるだけ短くなるように工夫しましょう。

ＳＱＬの勉強については　Google　で「ＳＱＬ入門」でさがせばたくさんあります。

できるだけ　ＶＢＡでループ処理などは行わずＳＱＬで解決できるように心がけてください。
できない場合はアクションクエリで　別テーブルを作成し、そのデータを入力データにすれば
かなり効率的にできます。

これで　解決でしたら　ベストアンサーボタン　クローズしてください。

以上

お礼

長らくＳＱＬを必要としない「ファイルメーカー」を使っていたので、勉強しなおし中です。

ほぼ完成、というところでレポートでひっかかってしまいました。

クエリは全部がデザインで組んでおり、いま更きつい所があります。

そこで、
テーブルＡに「選択」フィールドを追加
フォームＡ、レポートＣに「選択」を追加

レポートＣを作る時　一旦全レコードの「選択」をfalseにUPDATE
抽出表示されたフォームＡのクローンをつくって「選択」をTrue

レポートＣを開く時、Filterに「選択」=True、

データベースをまじめにやってる方には、ひっくり返るような回りくどさでしょうが、目的は達成しました。

ご親切なご回答、ありがとうございました。

回答No.4

NOBNNN

#3　のつづき

　もし、CreateQueryDef　を使わないで行う場合は　フォームＢから抽出条件のみ　フォームＡにパラメータとして
　渡してあげてください。　グローバル変数（モジュール内で定義）　でできます。

　例：
　　　モジュールを作成（Module1)

　　　 Type T_抽出条件
お父さんの誕生日 As String
お母さんの誕生日 As String
子供さん1の誕生日 As String
子供さん2の誕生日 As String
子供さん3の誕生日 As String
End Type

Public FM_A抽出パラメータ As T_抽出条件

フォームＢからパラメータを設定

　　例：
　　 Privte Sub BB()
With FM_A抽出パラメータ
.お父さんの誕生日 = "1961/08/10"
.お母さんの誕生日 = "1964/05/10"
.子供さん1の誕生日 = "1990/05/10"
.子供さん2の誕生日 = "1993/02/15"
.子供さん3の誕生日 = "1996/09/24"
End With

End Sub

フォームＡでパラメータを受け取り　クエリをレコードソースに指定。

　　例：

　 Privte Sub aa()

　 　 DIM Str_Where as string

　 　 With FM_A抽出パラメータ

　 　 Str_Where = Str_Where & " お父さんの誕生日 = '" & .お父さんの誕生日 & "' AND " & VBCRLF & _ 　
　 　 Str_Where = Str_Where & " お母さんの誕生日 = '" & .お母さんの誕生日 & "' AND " & VBCRLF & _
　 　 Str_Where = Str_Where & " 子供さん1の誕生日 = '" & .子供さん1の誕生日 & "' AND " & VBCRLF & _
　 　 Str_Where = Str_Where & " 子供さん2の誕生日 = '" & .子供さん2の誕生日 & "' AND " & VBCRLF& _
　 　 Str_Where = Str_Where & " 子供さん3の誕生日 = '" & .子供さん3の誕生日

　 　 　　End with

　　　 me.Recordsource = "SELECT TBL_A.* FROM クエリＡ基本 AS TBL_A " & VBCRLF & _
　　　　 "Inner JOIN クエリＢ基本 AS TBL_B " & VBCRLF & _　　　　
　　　　 "On TBL_A.家ＩＤ = TBL_B.家ＩＤ " & VBCRLF & _
　　　　 " WHERE " & Str_Where & " " & VBCRLF & _　　　　
　　　　 " Order BY TBL_A.家ＩＤ"

　 End Sub

お礼

ご丁寧なご回答、ありがとうございます。

急きょ、ネット・参考書で勉強しました。
「NOBNNN」さんのご教示のとうりで実現できそうです。
だた、私の技量ではまだ少し無理みたいです。

勉強中に下記の記述を発見しました。
http://support.microsoft.com/kb/207615/ja
wherecondition:=でなく、filtername:=フォームＡ.Filter
これだと、文字数に制限がないみたいで、エラーも発生しませんでした。

但し「Loopで回してＩＤを得る」が効率がいいとは思えませんが、「RecordsetClone」で回すのは瞬時で終わるので苦になっていません。

次にデータベースを組む時は「NOBNNN」さんのご回答を頭に入れて作りたいと思います。
誠にありがとうございました。

補足

お礼の後の補足になってしまいました。申し訳ありません。

filtername:=フォームＡ.Filterでエラーにならずいけると思ったのですが、勘違いでした。
エラーにはならず、常に全表示になっていました。

filternameは「クエリの名前を文字列で指定」でした。

やはりクエリを勉強しないといけないみたいです。

回答No.3

NOBNNN

#1です。

＞＞例えばフォームＢの検索条件に「お父さんの趣味=パソコン」とあってもフォームＡに「お父さんの趣味」フィールドはないので。

こういう場合は、動的にクエリを作成してしまえばできます。

#1　でお答えしました　CreateQueryDEF でクエリの中身を上書きしてしまう方法があります。

Access のクエリは　２５５文字が限界です。　だから　長い場合はあらかじある程度分割（別なクエリとして定義）
するかデザイナでの入力はあきらめて　手動でＳＱＬを書くことです・
デザイナのばあいは　中身が正規化されておらず　かならずしも　最良のＳＱＬではありません。

やたらと長く、短くはなりません。
デザイナに頼らず手動で行うには　ＳＱＬ　が自分で書けることが条件です。

リレーショナル　の記述法も知っている必要があります。

Access の場合は　長い　ＳＱＬ　をひとつのクエリとして　保存し、それを再利用してひとつのクエリにできます。

初期の　フォームＡ　のクエリ　を取り出して　クエリを作製します。
初期の　フォームＢ　のクエリ　を取り出して　クエリを作製します。

　例
　　フォームＡデータ（家ＩＤ、氏名、住所、電話番号）これを　クエリＡ基本　として作製してください。

　　フォームＢデータ（家ＩＤ、族構成　お父さんの誕生日、お母さんの誕生日などなど）これを　クエリＢ基本　として作製してください。

フォームＢ　より条件が入力されたら　動的に抽出クエリ（求めたいフォームＡデータ）を作成します。

　　フォームＢから入力された条件（たとえば「お父さんの趣味=パソコン」）を作製したい場合。

　　　　SELECT TBL_A.* FROM クエリＡ基本 AS TBL_A
　　　　 Inner JOIN クエリＢ基本 AS TBL_B

　　　　 On TBL_A.家ＩＤ = TBL_B.家ＩＤ

　　　　 WHERE TBL_B.お父さんの趣味=パソコン

　　　　 Order BY TBL_A.家ＩＤ

　とすれば良いのです。　

　これを　先にのべたCreateQueryDef で　抽出クエリ（求めたいフォームＡデータ）を作成します。
　これを仮に　「フォームＡ抽出クエリ」とします。

　あらかじめ　フォームＡおよびレポートＣのレコードソースを　「フォームＡ抽出クエリ」としてください。
　最初はつくられていないのでエラーになる場合は適当な内容でデザイナで「フォームＡ抽出クエリ」を
　作製してください。

　あとは　フォームＢから抽出条件が入力され、クエリ「フォームＡ抽出クエリ」を作製しましたので
　フォームＡとレポートＣを開くだけで　大丈夫です。

　これでできるはずです。

************ つづく　*********

回答No.2

NOBNNN

#1　です。

気になる箇所があります

質問者さまの

フォームＢでフィルターをかけ、「家ＩＤ」を元にフォームＡで該当するデータを表示させています。

----------------------------------------------------
　　Set rs = Forms.フォームＢ.RecordsetClone
　　Do Until rs.EOF
　　　　フィルタ = フィルタ & "," & rs!家ＩＤ
rs.MoveNext
Loop

ですが
　これってフォームＢのデータが1000件あったら　全部対象てことですよね。

　わざわざ　ループさせなくても　フォームＢのデータから抽出できませんか？

　　　　SELECT TBL_A.* FROM TBL家データ AS TBL_A
　　　　 Inner JOIN TBL家族内データ AS TBL_B

　　　　 On TBL_A.家ＩＤ = TBL_B.家ＩＤ

　　　　 Order BY TBL_A.家ＩＤ

　このクエリを　フォームＡおよびレポートＣのレコードソースに指定すれば大丈夫では？

　もうすこし　フォームＢの絞込みの方法を教えてください。

お礼

補足の２です

気が付いたのですが、
>ＳＱＬ文を取り出して、それにフォームＢの検索条件を追加
はダメでした。

例えばフォームＢの検索条件に「お父さんの趣味=パソコン」とあってもフォームＡに「お父さんの趣味」フィールドはないので。

う～～ん

補足

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

「Inner JOIN」は使ったこともなく、あわてて勉強してます。

フォームＢで
s = "SELECT * FROM フォームＡのＴＢ Inner JOIN フォームＢのＴＢ
　　　On フォームＡのＴＢ.家ＩＤ = フォームＢのＴＢ.家ＩＤ"
Forms.フォームＡ.RecordSource = s

としましたが、フォームＡを見にいくと「#Name?」がたくさんあり、レコード数も「フォームＢのＴＢ」分です。
「Inner JOIN」の意味を体験した次第です。

初期のフォームＡのレコードソースはフォームＡクエリを指定しています。かなり複雑です。
（クエリはデザインで作っています）
この初期のクエリのＳＱＬ文を取り出して、それにフォームＢの検索条件を追加したらと思っていますが、方法が？です。

>フォームＢの絞込みの方法
専用の検索フォームを作り絞込みをしています。
例えば、お父さんの誕生日、趣味とか・・・です。

回答No.1

NOBNNN

たぶん文字列の長さ255文字の限界を超えているのでは？

フィルターは使わず。　レコードソースのほうで工夫してみてはどうでしょう

たとえば　フォ－ムからレポートを　一旦開き。 レコードソースを変更してしまえばできるのでは

ＶＢＡ　で　クエリを　動的に作製する方法があります。（CreateQueryDefメソッド ）

参考:http://www.accessclub.jp/dao/CreateQueryDef.html

あらかじめ　雛形になるクエリを登録しておきます。

　これを「クエリ雛形Ａ」とします。

　　　　　SELECT * FROM 住所録

「クエリ雛形Ａ」　から　「クエリ_レポートデータ」を作成します。
SELECT * FROM クエリ雛形Ａ Where 家ＩＤ IN (2,5,7)

フォームＡとレポートＣのレコードソースのプロパティをこのクエリ名に変更します。
フォームＢより、フォームＡとレポートＣのRecordsourceのプロパティ
　　　をあらかじめ「クエリ_レポートデータ」　とすればＯＫです。

　ＶＢＡで変更する場合は下記のＨＰが参考になります。

　参考:http://office.microsoft.com/ja-jp/access/HA012327891041.aspx

以上