MS Access Tips/Sample and VBA and Blog customize etc...

列数が変化するクロス集計クエリと連結するフォーム

以前の記事 列名が変化するクロス集計クエリと連結するフォーム では列数が固定でしたが、今回は列数も変化する場合のサンプルです。

FrmCrossQuerySourceVFld.gif

難易度:

仕様

テーブル

T_受注
ID品番カラーサイズ受注数
1AS4
2AM2
3AL3
4AS1
5AM2
6AL5
7BSS4
8BS3
9BM2
10BL2
11BLL3
12BSS2
13BS3
14BM1
15BL1
16BLL2

下記のクエリ「Q_受注クロス」を作成

フォーム名は F_受注。
ヘッダーにコンボボックスを配置して名前を cb品番 とする。レコードソースは空白にしておきます。また、ヘッダーのサイズ名表示ラベルの名前は、lbl2, lbl3, ・・・lbl8、詳細セクションの値表示用のテキストボックスの名前は、 txt2, txt3, ・・・txt8 とします。

フォームモジュール

サンプルファイルが下記からダウンロードできます。
FrmCrossQuerySourceVFld_07.zip (Access 2007-2010 形式 - 27kb)
FrmCrossQuerySourceVFld.zip (Access 2002-2003 形式 - 22kb)
FrmCrossQuerySourceVFld_2k.zip (Access 2000 形式 - 21kb)

拍手する

26 Comments

うみ says..."感動しました"

今までは期間別で集計クエリを作成したいのが
期間は固定になるけど開始と終了が変動できるように
なったのが最高です!

ありがとうございました

2010.08.26 10:39 | URL | #JalddpaA [edit]
やの字 says..."すばらしい"

クロス集計でフォームへの可変フィールド表示は無理とばかり思っていましたが感動しました。
欲が出て
可変フィールドの合計をフォームフッターに同じように可変で表示させたいのですが勉強不足でSUMのやり方がわかりません。
やり方はありますでしょうか

2011.02.08 13:37 | URL | #8yNmqUjw [edit]
hatena says..."Re: すばらしい"

やの字さん、お褒めの言葉、ありがとうございます。

> 欲が出て
> 可変フィールドの合計をフォームフッターに同じように可変で表示させたいのですが勉強不足でSUMのやり方がわかりません。
> やり方はありますでしょうか

フォームフッターのテキストボックスの名前は、 sum2, sum2, ・・・txt8 とします。
フォームモジュールの15行目からを下記のように変更、挿入します。

  For i = 2 To rs.Fields.Count - 1
    Me("lbl" & i).Caption = rs(i).Name
    Me("txt" & i).ControlSource = rs(i).Name
    Me("txt" & i).Visible = True
    Me("Sum" & i).ControlSource = "=Sum(" & rs(i).Name &")"
    Me("Sum" & i).Visible = True
  Next
  For i = rs.Fields.Count To 8 
    Me("lbl" & i).Caption = ""
    Me("txt" & i).ControlSource = ""
    Me("txt" & i).Visible = False
    Me("sum" & i).ControlSource = ""
    Me("sum" & i).Visible = False
  Next

以上でいかがでしょうか。

2011.02.08 17:48 | URL | #- [edit]
やの字 says...""

ご連絡ありがとうございます。
できました。
最初はエラーが出ていたのですがカギカッコでくくったら見事できました。
"=Sum([" & rs(i).name & "])"
クォテーションと&の使い方なのですねもっと勉強します。
ありがとうございました。

2011.02.09 13:11 | URL | #8yNmqUjw [edit]
hatena says..."ミスでした"

> 最初はエラーが出ていたのですがカギカッコでくくったら見事できました。
> "=Sum([" & rs(i).name & "])"

あっ、そうでした。カギカッコでくくるのを失念してました。
お手数をお掛けしてしまい、申し訳ありませんでした。

2011.02.10 11:04 | URL | #5uE6dEgY [edit]
やの字 says..."御礼"

ご丁寧にご連絡ありがとうございます。
構築中DBはおかげさまで大前進しました。
技満載の本サイト本当に助かります。
今後とも宜しくお願いします。

2011.02.10 21:02 | URL | #8yNmqUjw [edit]
ひらら says..."クエリ、フォーム、プロパティでやるには"

楽しくサイトを拝見させて頂きました。
よろしければ、VBAを使わず、クエリ、プロパティでやる手順を
アドバイス頂けないでしょうか。
テーブル(T_受注)、クエリ(Q_受注クロス)を作り、
フォームを作ろうとしました。
しかし、下記警告がでてでなかったためです。
------------------------------------------
![Forms]![F_受注]![CB品番]'を有効なフィールド名、
または式として認識できません。
------------------------------------------

2011.02.20 12:51 | URL | #MbpuNcGA [edit]
hatena says..."re: クエリ、フォーム、プロパティでやるには"

列数が変化する場合は、VBAを使わずにするのは難しいです。

列数固定なら、下記で紹介している方法で可能ですが。

http://hatenachips.blog34.fc2.com/blog-entry-117.html

2011.02.20 13:25 | URL | #5uE6dEgY [edit]
ひらら says...""

ありがとうございます。
難しいことがはっきりしてよかったです。
これを機会にVBAも勉強していきたいと思います。

2011.02.20 22:26 | URL | #MbpuNcGA [edit]
名無し says..."参考にさせて頂きました"

betweenで範囲設定して抽出させています
抽出ボタンを追加して クリックで抽出しました
従来 me.Requery を sourceReset に替えて実行 OKでした
ここから同じようにレポートにして
印刷方法がありましたら 教えて下さい。

2012.10.05 17:36 | URL | #- [edit]
hatena says..."re:参考にさせて頂きました"

レポートでするなら、下記の方法を使う方が簡単だと思います。

グループ化したレコードを横に展開する - hatena chips
http://hatenachips.blog34.fc2.com/blog-entry-105.html

2012.10.05 18:41 | URL | #5uE6dEgY [edit]
たそがれ says..."御礼"

御礼の連絡が遅くなり 申し訳ございません。
当方の説明不足もあり 考えていたものではありませんでした
レポートの表示方法を管理者のフォームを参考に組み直してみます。
お手数を掛けました、ありがとう御座います。
VBAは奥が深いですチャレンジして行きます!

2012.10.09 20:01 | URL | #0LWfPSA. [edit]
ringojuice says..."サブフォームとクロス集計クエリの連結"

hatenaさんのやり方を使って、クロス集計クエリとサブフォームの連結はうまくできましたが、
サブフォームのフィールド名の表示はちょっとおかしくなりました。
サブフォームはデータシートビューです。
サブフォームに全部23個のラベルとテキストボックスを配置しました。
フォームを実行して、2つの結果が出ました。
① 短い年月範囲だったら、名前があるフィールド名はちゃんと表示されましたが、名前がないフィールド名は「txt17」から「txt17」そのままに表示されました(txt23まで)。
② 長い年月範囲だったら、名前があるフィールド名も「txt17」から「txt17」そのままに表示されました(txt23まで)。

*「lbl2」と「txt2」は表示したくないので、サイズ調整で勝手に隠しましたが、なんかかかわりありますか?

ご意見をいただけますでしょうか
大変お手数ですが、よろしくお願いいたします。

2013.07.24 15:42 | URL | #- [edit]
hatena says..."データシートビューの列名"

データシートビューの列名は、テキストボックスと関連付けられたラベルの標題になります。

現状はラベルが関連付けられていないのではないでしょうか。

ラベルをテキストボックスに関連付けるには、まずラベルを「切り取り」して、関連付けたいテキストボックスを選択して「貼り付け」してください。

2013.07.24 15:52 | URL | #5uE6dEgY [edit]
ringojuice says..."ご返信ありがとうございます!"

早速のご返信ありがとうございます!
hatenaさんのおかげで、うまくできました!
さっきの質問ばかすぎて申し訳ございませんでした。

もう一つの質問をさせていただけますでしょうか
メインフォームの合計の列数がサブフォームの列数と同じく表示されるのは可能でしょうか。
いろいろ試してみましたが、だめでした。

例えば、2013/06~2013/07の範囲で、サブフォームの列数は6列、
メインフォームの合計欄に同じ列数を表示したいのですが。

2013.07.24 16:55 | URL | #- [edit]
hatena says..."メインフォームの合計欄"

メインフォームの合計欄とサブフォームのテキストボックスの可視が同じになるようにすればいいでしょう。

合計欄の名前が、txtSum2、txtSum3、・・・txtSum8 として、

  For i = 2 To rs.Fields.Count - 1
    Me.サブフォーム("lbl" & i).Caption = rs(i).Name
    Me.サブフォーム("txt" & i).ControlSource = rs(i).Name
    Me.サブフォーム("txt" & i).Visible = True
    Me("txtSum" & i).Visible = True
  Next
  For i = rs.Fields.Count To 8
    Me.サブフォーム("lbl" & i).Caption = ""
    Me.サブフォーム("txt" & i).ControlSource = ""
    Me.サブフォーム("txt" & i).Visible = False
    Me("txtSum" & i).Visible = False
  Next

2013.07.24 17:35 | URL | #5uE6dEgY [edit]
ringojuice says..."ご返信ありがとうございます!"

親切なご指導ありがとうございます!
できました!
やっぱり考え方の違いですね!
もっといろいろな考え方を吸収していきたいです!
本当にありがとうございました!

2013.07.24 17:55 | URL | #- [edit]
ringojuice says...""

ご教授いただいたコードに、メインフォーム合計欄のレコードソースを賦与するコードを追加したいのですが、
このコードでだめでした。
Me("txtSum" & i).ControlSource = "=[Sub].[Form]![("Sum" & i)]"
他にも、いろいろ試しましたが、うまく行かなかったです。
どうすればよいのでしょうか

2013.07.29 12:41 | URL | #- [edit]
hatena says..."サブフォームの合計"

合計はサブフォームのフォームフッターのテキストボックスに設定します。
メインフォームの合計テキストボックスは、サブフォームの合計テキストボックス名を参照すればいいので固定でいいです。

サンプル

サブフォーム詳細
ラベル      lbl2, lbl3 ・・ lbl8
テキストボックス txt2, txt3 ・・ txt8

サブフォームフッター
テキストボックス sum2, sum3 ・・ sum8

メインフォーム
テキストボックス txtsum2, txtsum3 ・・ txtsum8
このテキストボックスのコントロールソースはそれぞれ、下記のように設定します。

=[サブフォーム].[Form]![sum2]

=[サブフォーム].[Form]![sum3]

・・・
以下同様。

メインフォームのコード(抜粋)

  For i = 2 To rs.Fields.Count - 1
    Me.サブフォーム.Form("lbl" & i).Caption = rs(i).Name
    Me.サブフォーム.Form("txt" & i).ControlSource = rs(i).Name
    Me.サブフォーム.Form("txt" & i).Visible = True
    Me.サブフォーム.Form("sum" & i).ControlSource = "=Sum(" & rs(i).Name & ")"
    Me.サブフォーム.Form("sum" & i).Visible = True
    Me("txtSum" & i).Visible = True
  Next
  For i = rs.Fields.Count To 8
    Me.サブフォーム.Form("lbl" & i).Caption = ""
    Me.サブフォーム.Form("txt" & i).ControlSource = ""
    Me.サブフォーム.Form("txt" & i).Visible = False
    Me.サブフォーム.Form("sum" & i).ControlSource = ""
    Me.サブフォーム.Form("sum" & i).Visible = True
    Me("txtSum" & i).Visible = False
  Next


あくまで例なので考え方を参考にしてコードを作成してください。

もし、どうしてもうまくいかないのであれば、下記に現状のファイルをアップロードしてください。
http://hatena-access.progoo.com/bbs/
hatena の Microsoft Access 掲示板

2013.07.29 13:08 | URL | #5uE6dEgY [edit]
ringojuice says...""

ご返信ありがとうございます。

前は上記のやり方でフォームを作ってみましたが、
メインフォームの合計欄にエラーが出てしまいました。
「検索」ボタン二回押すと、計算してくれましたが、

また、動的にテキストボックスを増やしたいので、
いちいちテキストボックスのコントロールソースを書くのもちょっと不便と思いまして、
メインフォームのテキストボックスに対して、サブフォームの合計欄のような動的に値を賦与するコードを書きたいです。
私の説明不足でご迷惑をおかけしまして、申し訳ございませんでした。
メインフォームのテキストボックスに対して、サブフォームの合計欄のような動的に値を賦与するコードはありますか?

2013.07.29 13:40 | URL | #- [edit]
hatena says..."あくまでサンプル"

そちらのフォームの構成、レコードソース、コード、の全体像が分かりませんので、考え方のみのサンプルです。

これを参考にコードを書かれたとして、具体的にどこを間違えているのか指摘するのはここで提示される情報だけでは難しいのです。

上で紹介した掲示板にファイルをアッドロードしてもらえれば、具体的に間違い箇所を指摘できると思います。

> また、動的にテキストボックスを増やしたいので、

この考え方は支持できません。
フォームのサイズには制限がありますので、配置できるテキストボックスの最大数にも制限が出てきます。
配置できる最大値のテキストボックスを配置しておいて、可視をコントロールするのがベストの方法だと思います。

CreateControl で動的にコントロールを作成出来ますが、デザインビューで開く必要がありますので、ファイル破損の危険性が高いです。また、動作も重くなります。

2013.07.29 14:49 | URL | #5uE6dEgY [edit]
ringojuice says...""

親切なご説明ありがとうございます!
非常に参考になりました。

2013.07.30 11:50 | URL | #- [edit]
中野 豊 says..."メインとサブフォームの連携"

今までのコメントも読みましたが、メインフォームとサブフォームの同期がうまく行かないので質問させていただきます。抽出条件をメインフォーム上に置き、検索ボタンでテーブルに対象データを書きます。サブフォームはこの可変のフォームを利用し、検索でできたテーブルをクロス集計するクエリーをレコードソースとVBA内で指定しています。
メインフォームからこのサブフォームをRequeryをしても変化せず、フォームとして呼び出したりすれば正常に可変のフォームとして結果を表示してくれます。
「Requeryではリセットされない」の記述があり、気になりますが、メインフォームに埋め込み、同期をとる場合はメイン側に何か記述が必要なのでしょうか。
よろしくお願いします。


2016.03.16 16:03 | URL | #L99P1.cs [edit]
hatena says..."re:メインとサブフォームの連携"

> メインフォームからこのサブフォームをRequeryをしても変化せず、フォームとして呼び出したりすれば正常に可変のフォームとして結果を表示してくれます。
> 「Requeryではリセットされない」の記述があり、気になりますが、メインフォームに埋め込み、同期をとる場合はメイン側に何か記述が必要なのでしょうか。

「Requeryではリセットされない」ので、サブフォームのレコードソースを書き換えるようにしてください。

メインフォームにコードを記述するなら、下記のような感じです。

  Me!サブフォームコントロール名!Form.RecordSource = ""
  Me!サブフォームコントロール名!Form.RecordSource = "クエリ名"

2016.03.18 10:31 | URL | #5uE6dEgY [edit]
YY says..."レポートに反映したい場合"

いつも参考にさせて頂いています。
お陰様で、フォームにてクロス集計クエリの列タイトルを変数にすることができました。ありがとうございます。
そこで、その集計結果を印刷したくてレポートを同じような設定にしてみたところ、「印刷プレビュー時または印刷を開始した後は、レコードソースプロパティを設定できません」というエラーが出てしまいました。
どうにか、集計結果をレポートに反映することは可能でしょうか?

2016.10.31 15:27 | URL | #CPbbZvYc [edit]
hatena says..."re:レポートに反映したい場合"

> そこで、その集計結果を印刷したくてレポートを同じような設定にしてみたところ、「印刷プレビュー時または印刷を開始した後は、レコードソースプロパティを設定できません」というエラーが出てしまいました。
> どうにか、集計結果をレポートに反映することは可能でしょうか?

フォームとレポートではイベントや仕様が異なりますので、そのままでは当然駄目ですね。

ここのサンプルのフォームを開いた状態で、
フォームと同じ表示、レイアウトのレポートを表示するという
ことだとして回答します。

まずは、サンプルファイルをもとに作業をして、要領を確認してから実際のものに適応させてください。

まず、「F_受注」フォームを選択しておいて、[ファイル]-[オブジェクトに名前を付けて保存]で、
保存先 「R_受注」
貼り付ける形式 「レポート」
としてOKをクリックします
これで、「R_受注」レポートが作成されますので、それをデザインビューで開きます。

リボンの「デザイン」-「コードの表示」をクリックしてレポートのモジュールを開きます。
フォーム用のコードがありますが、それをすべて削除します。
代わりに下記のコードを貼り付けてください。

Option Compare Database
Option Explicit

Private Sub Report_Open(Cancel As Integer)
  Dim i As Integer
  Dim EmptyCol As Boolean
  Dim rs As DAO.Recordset

  Me.RecordSource = "Q_受注クロス"
  Me.cb品番.ControlSource = "=Forms!F_受注!cb品番"
  Set rs = Forms!F_受注.Recordset

  For i = 2 To rs.Fields.Count - 1
    If rs(i).Name = "<>" Then
      EmptyCol = True
    Else
      Me("lbl" & i + EmptyCol).Caption = rs(i).Name
      Me("txt" & i + EmptyCol).ControlSource = rs(i).Name
      Me("txt" & i + EmptyCol).Visible = True
    End If
  Next
  For i = rs.Fields.Count + EmptyCol To 8
    Me("lbl" & i).Caption = ""
    Me("txt" & i).ControlSource = ""
    Me("txt" & i).Visible = False
  Next

End Sub

フォーム上にはコマンドボタンを配置してそのクリック時のイベントプロシージャに下記のコードを記述してください。

DoCmd.OpenReport "R_受注", acViewPreview

2016.10.31 16:54 | URL | #5uE6dEgY [edit]

Leave a reply






Trackbacks

trackback URL
http://hatenachips.blog34.fc2.com/tb.php/142-6dfb18ff
該当の記事は見つかりませんでした。