アクセスのレポート出力に関する質問です！

質問

アクセスの『サブフォーム上のクエリをレポートへの出力する』内容についての質問です！
お力添えをお願いします。

現在、サブフォーム上には数万レコードが表示されていて、優先順位が高い順に表示がされています。
そこで、優先順位が高いレコードを、指定したレコード数分だけ出力したいのです。

具体的に言うと、、、

レポート出力ボタンを押す
↓
msg boxが出てきて、出力するレコード数を指定
↓
そのレコード数分が（上から）レポートへ出力される

という感じにしたいのです。
※サブフォーム上にあるクエリには、オートナンバーがついていません。

よろしくお願いいたします。

回答No.3

piroin654 · ベストアンサー

>フォーム上に配置したボタンやコンボボックス
>でフィルタをかけたりしてから

ということなので、レポート用のテーブルに書き出す
方法をとります。

(1)　テーブルの作成
サブフォームに表示されているフィールドのうち、
レポートに表示するフィールドを、たとえば以下の
ように新しいテーブルに設定します。名前を
テーブル1とします。主キーは設定しません。

テーブル1：

順番　　　　オートナンバー
名前　　　　テキスト型
住所　　　　テキスト型
金額　　　　通貨型

このうち、順番はサブフォームにはないフィールドで
新しく設定します。
なお、順番フィールドはオートナンバーなので放って
おくと番号がとてつもなく大きくなるので、時には
フィルを最適化してください。最適化すると、
また1から始まります。順番のフィールドを番号として
表示するならば、一旦最適化してから、抽出、
レポート表示をしてください。
オートナンバー以外にも番号を振る方法はありますが、
まずはこれで確かめてみてください。

(2)　レポートの設定
テーブル1でレポートを作成します。
このとき、「順番」は必要がなければレポートには
表示する必要はありませんが、並びを確定させる
ためにレポートのレコードソースには入れておきます。
レポートの名前を

R1

とします。レポートのレコードソースを以下に設定
します。

SELECT テーブル1.順番, テーブル1.名前, テーブル1.住所, テーブル1.所持金
FROM テーブル1
ORDER BY テーブル1.順番;

(3)　クエリの作成
新たにテーブル1にデータを入れたりするときに
テーブル1の中を一旦、空っぽにするために使います。
名前を、Q初期化とします。

Q初期化：

DELETE テーブル1.*
FROM テーブル1;

(4)　メインフォームの設定
メインフォームにボタンを三つ設定します。
名前を、

cmd抽出
cmdレポート
cmdテーブル初期化

とします。
それぞれのボタンクリック時のイベントを以下にします。
なお、cmd抽出はレコード数が多ければなるべく早く
抽出が済むように少し工夫をしています。普通は
コメントアウトしたほうを使います。
なお、DAOを使っているので、
コード表のツール、参照設定からDAOにチェックを
いれてください。
Micrososft DAO 3.6 Object Library
のようなものです。チェックをいれたら
↑ボタンで上げられるところまで上げて
ください。

Private Sub cmd抽出_Click()
Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim ret As Long
Dim i As Long 'ループしたカウンタ数
'普通の方法での変数は上記まで
Dim fld11 As Field 'テーブル1の名前
Dim fld12 As Field 'テーブル1の住所
Dim fld13 As Field 'テーブル1の金額
Dim fld21 As Field 'テーブル2の名前
Dim fld22 As Field 'テーブル2の住所
Dim fld23 As Field 'テーブル2の金額

'###　　普通はコメントアウトした方法　#############
'##############################################################
'ret = CInt(InputBox("表示数を入力してください", "表示数"))
'
'Set db = CurrentDb
'Set rs1 = Me!埋め込み0.Form.RecordsetClone
'Set rs2 = db.OpenRecordset("テーブル2", dbOpenDynaset)
'
'i = 0
'If rs1.RecordCount > 0 Then
'rs1.MoveFirst
'Do Until rs1.EOF
''ループが指定数に達したら処理を抜ける
'If i = ret Then
' Exit Do
'End If
''サブフォームのデータをテーブルに書き込み
'rs2.AddNew
'rs2!名前 = rs1!名前
'rs2!住所 = rs1!住所
'rs2!金額 = rs1!金額
'rs2.Update
''カウンタのカウントアップ
'i = i + 1
'rs1.MoveNext
'Loop
'End If

'###########################################################

ret = CInt(InputBox("表示数を入力してください", "表示数"))

Set db = CurrentDb
Set rs1 = Me!埋め込み0.Form.RecordsetClone
Set rs2 = db.OpenRecordset("テーブル2", dbOpenDynaset)

'テーブル1のフィールド名を設定
Set fld11 = rs1!名前
Set fld12 = rs1!住所
Set fld13 = rs1!金額

'テーブル2のフィールド名を設定
Set fld21 = rs2!名前
Set fld22 = rs2!住所
Set fld23 = rs2!金額

'カウンタの初期化
i = 0
If rs1.RecordCount > 0 Then
rs1.MoveFirst
Do Until rs1.EOF
'ループが指定数に達したら処理を抜ける
If i = ret Then
Exit Do
End If
'サブフォームのデータをテーブル2に書き込み
rs2.AddNew
fld21 = fld11
fld22 = fld12
fld23 = fld13
rs2.Update
'カウンタのカウントアップ
i = i + 1
rs1.MoveNext
Loop
End If

rs1.Close: Set rs1 = Nothing
rs2.Close: Set rs2 = Nothing
db.Close: Set db = Nothing
MsgBox "抽出が終了しました"
End Sub

Private Sub cmdレポート_Click()
'レポートを開く
DoCmd.OpenReport "R3", acViewPreview
End Sub

Private Sub cmdテーブル初期化_Click()
DoCmd.OpenQuery "Q初期化"
End Sub

(4)　使い方
最初に、cmd初期化をクリックし、テーブル1の中を
空にします。次に、cmd抽出をクリックし、次にcmdレポートを
クリックします。

一応、確認はしていますが、時に落し物が
あったりするので何かあれば、補足してください。

お礼

ご回答、ありがとうございます！！
これで頑張ってみます！！！

少しレベルが高いので、理解＆設定に時間がかかるかもしれないですが、、、また質問させてください！！！

回答No.6

piroin654

少し説明不足だったので付け足しをします。

＞Set rs1 = Me!埋め込み0.Form.RecordsetClone

は、

Set db = CurrentDb
Set rs1 = Me!埋め込み0.Form.RecordsetClone
Set rs2 = db.OpenRecordset("テーブル2", dbOpenDynaset)

のところで、普通はレコードセットは
Set rs2 = db.OpenRecordset("テーブル2", dbOpenDynaset)
のようにテーブルを指定したり、クエリを指定します。
この回答では、
Set rs1 = Me!埋め込み0.Form.RecordsetClone
として、サブフォームで表示されているレコードを
レコードセットとして指定しています。したがって
あとは普通のレコードセットから情報を取り出すのと
同様にサブフォームの表示レコードから情報を
取り出すことができます。そのキーワードが
RecordsetCloneです。

Me!埋め込み0.Form.RecordsetClone
とは、
[自分自身(メインフォーム)]![サブフォーム表示コントロール].[Formコントロール(ここではサブフォーム)].[レコードセット]
というように考えてください。

お礼

詳しくお教えいただき、感謝しています！！
RecordsetCloneを今後活用していきたいと思います。

素晴らしい情報をありがとうございましたm(_ _)m

回答No.5

piroin654

テーブルを回答のテーブル1とします。レポートがこのテーブル1を
レコードソースにしているときに、金額が多い順に上位から入力数
だけ表示する場合は、

Private Sub Report_Open(Cancel As Integer)
Dim TopNum As Long

TopNum = InputBox("出力する上位件数を入力してください。")
Me.RecordSource = "SELECT TOP " & TopNum & " テーブル1.ID, テーブル1.名前, テーブル1.住所, テーブル1.金額 FROM テーブル1
ORDER BY テーブル1.金額 DESC;"

End Sub

のように書きます。また、作成済みのクエリをVBAで変更する方法は、
たとえば、適当な内容のクエリを作成しておき、その名前をクエリ1
とします。

Private Sub コマンド0_Click()
Dim qdf As QueryDef
Dim TopNum As Long

Set qdf = CurrentDb.QueryDefs("クエリ1")
TopNum = InputBox("出力する上位件数を入力してください。")
qdf.SQL = "SELECT TOP " & TopNum & " テーブル1.ID, テーブル1.名前, テーブル1.住所, テーブル1.所持金 FROM テーブル1 ORDER BY テーブル1.所持金 DESC;"
'確認のためクエリを開いてみる
DoCmd.OpenQuery "クエリ1"
End Sub

TOP値についてはこのようなところです。
ms access TOP値
でググればいろいろ出てきます。
何かわからないところがあれば補足してください。

なお、
＞画面上に表示されている"だけ"のデータをどうやってVBAで指定するのか、
については、

Set rs1 = Me!埋め込み0.Form.RecordsetClone

の部分のRecordsetCloneがキーワードです。
これはフォームで現在表示されている
レコードを対象にしています。単純に
テーブルをレコードソースにしても
フィルタやいろいろな絞込みで
フォームに表示するデータを限定しても
RecordsetCloneでレコードソースとしての
テーブルではなく、現在表示しているレコードを
取得できます。
サブフォームではなく、単票形式、あるいは帳票形式ならば、
Set rs1 = Me.RecordsetClone
という書き方になります。
Accessのファイルを細かに
使い勝手を良くしようとするとRecordsetClone
は強い味方になります。

回答No.4

piroin654

よく見るとNo3にいくつか間違いがありました。

(2)　レポートの設定
のレコードソースの中で、金額を所持金としていました。
正しくは、

SELECT テーブル1.順番, テーブル1.名前, テーブル1.住所, テーブル1.金額
FROM テーブル1
ORDER BY テーブル1.順番;

です。

(4) メインフォームの設定
の下のほうで、レポート名がR1のところをR3としていました。
正しくは、

Private Sub cmdレポート_Click()
'レポートを開く
DoCmd.OpenReport "R1", acViewPreview
End Sub

です。

補足

なんとか設定を終えることができました。
感謝しています！！

解決できたのに、質問なのですが、、、
最初の回答でお教えいただいたTOP句について教えてください。。。
私はこの関数を初めて知ったので、今後の参考にさせて頂けたら、、と思います。

今のところ、こんな感じで（↓）作ってみたのですが、◆部分が分かりません。。。

Private Sub Report_Open(Cancel As Integer)
Dim TopNum As Long

TopNum = InputBox("出力する件数を入力してください。")

Me.RecordSource = "SELECT TOP " & TopNum & "◆◆"

End Sub

私は画面上に表示されている"だけ"のデータをどうやってVBAで指定するのか、理解できていません。。。お教えくださいm(_ _)m

（補足）サブフォーム上のクエリの並び替えは、既にフォーム上に配置されているボタンやコンボボックスにより、ユーザーが優先度が高い順に並び替えをします。これらは、VBAでクエリのSQLを直接書きかえています。

よろしくお願い致します。

回答No.2

piroin654

No1です。テーブルを用意する場合はオートナンバーのフィールドは
必要ないですね。並べ替えが必要ならばレポートのレコード
ソースのクエリビルダでテーブルを指定して優先順位を
確定させるフィールドの並べ替えで降順、あるは昇順
を指定します。

補足

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

出力する前には、必ず並び替えをします。
その並び替えは、フォーム上に配置したボタンやコンボボックスでフィルタをかけたりしてから、上から欲しいレコード数を出力したいのです。。

出力するのによい方法をお聞きできたら、、、と思います！！

よろしくお願い致します！！！

回答No.1

piroin654

TOP値を使う場合は、下記にあるような方法で
サブフォームのクエリを書き換え、フォームの
Requeryをして、表示されたレコードをそのまま
レポートに送り込むか、

http://www.tsware.jp/tips/tips_246.htm

あるいは、レポートのレコードソース用の
オートナンバーを別途つけ、フィールドを設定した
空のテーブルを設定し、フォームの任意の
ボタンクリック時のイベントで、サブフォームの
RecordsetCloneをレコードセットとして
レコードの最初から必要な数だけテーブルに
書き出し、その後レポートを開く、という
方法だろうと思いますが。

VBAコードが必要ですか。

補足

丁寧なご回答、ありがとうございます！！
質問させてください☆
まだまだ未熟者なので、、、よろしくおねがいします！

(1)TOP値を使う方法だと、あらかじめ出力するレコード数を決めておかないといけないですよね？メッセージボックスで出力する件数を決定させたいのです。。。

(2)『レポートのレコードソース用のオートナンバーを別途つけ…』という手順について、教えてください。これは、レポートのレコードソース用に作成する空テーブルの中に、サブフォーム上に表示されたものをそのまま入れ込む、、、という方法ですか？？
ぜひ、VBAコードも一緒に教えてください！