帳票フォームに全レコードを表示できない

質問

アクセス初心者ですが宜しくお願いします。

アクセス２００２を使って在庫ＤＢを作っています。
レコードセットで抽出集計した全レコードを、「帳票形式」のフォームにすべて表示させたいのですがうまくいきません。フォームのテキストボックスは全て非連結にしてあります。

考えているのは次のような手順です。
１．表示したい期間の「開始日付=kaisi」と「終了日付=owari」を入力
２．「在庫場所」コンボボックスで表示したい在庫場所コードを選択
３．選択された在庫場所コードによって、SELECT～CASEステートメントで該当する在庫場所テーブルからデータを抽出集計し、フォームに表示させる

コードは次の通りです。

Private Sub 在庫場所_AfterUpdate()
Dim cnc As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim zaiko As Variant
Dim kaisi, owari As Date

Set cnc = CurrentProject.Connection
Set cmd.ActiveConnection = cnc

kaisi = Me!期間開始
owari = Me!期間終了
zaiko = Me!在庫場所

Select Case zaiko

Case "1"
cmd.CommandText = "SELECT 物品ID, Sum(仕入) AS 仕入計, Sum(払出) AS 払出計 FROM T_薬局受払 " & "WHERE (日付) Between #" & kaisi & "# And #" & owari & "# " & "GROUP BY 物品ID;"

Set rst = cmd.Execute
Do Until rst.EOF
　Me!物品ID = rst!物品ID
　Me!受入計 = rst!仕入計
　Me!払出計 = rst!払出計
　rst.MoveNext
Loop
rst.Close: Set rst = Nothing

Case "2"・・・・・・・・・・

End Select
cnc.Close: Set cnc = Nothing
End Sub

実行しますと、フォームには「最後のレコード」しか表示されません。
フォームプロパティの「規定のビュー」は帳票になっています。
よろしくお願いします。

回答No.1

noname#22222 · ベストアンサー

[ID][数量]
1,100
2,200
3,300

というテーブル[Test]を<帳票フォーム>に表示させるのに、

Private Sub Form_Load()
　　Dim cnc As New ADODB.Connection
　　Dim rst As New ADODB.Recordset
　　Dim cmd As New ADODB.Command

　　Set cnc = CurrentProject.Connection
　　Set cmd.ActiveConnection = cnc

　　cmd.CommandText = "SELECT * FROM TEST;"
　　Set rst = cmd.Execute
　　With rst
　　　　Do Until .EOF
　　　　　　Me.ID = .Fields("ID")
　　　　　　Me.数量 = .Fields("数量")
　　　　　　.MoveNext
　　　　Loop
　　End With
　　rst.close
　　cnn.close
End Sub

というコードでは、当然のように最後のレコードしか表示されません。
原因は、フォームの最初のレコードに上書きしているからです。
そこで、最初のレコードを書き込んだら新規レコードに移動するようにコードを書き換えます。

　　　　Do Until .EOF
　　　　　　Me.ID = .Fields("ID")
　　　　　　Me.数量 = .Fields("数量")
　　　　　　SendKeys "+({+})", False
　　　　　　DoEvents
　　　　　　.MoveNext
　　　　Loop

結果は、

3,300
3,300
3,300

この場合は、非連結タイプに起因する不具合です。
以上のような検討の結果、一旦、Select文で取得した値を一時テーブルに書き込んで連結モードで表示するのが手であることが判ります。

Private Sub Form_Load()
　　Dim cnc As New ADODB.Connection
　　Dim rst As New ADODB.Recordset
　　Dim cmd As New ADODB.Command

　　Set cnc = CurrentProject.Connection
　　Set cmd.ActiveConnection = cnc

　　cmd.CommandText = "SELECT * FROM TEST;"
　　Set rst = cmd.Execute
　　With rst
　　　　cmd.CommandText = "DELETE FROM TEMP;"
　　　　cmd.Execute
　　　　Do Until .EOF
　　　　　　cmd.CommandText = "INSERT INTO TEMP " & _
　　　　　　　　　　　　　　　"(ID, 数量) VALUES (" & _
　　　　　　　　　　　　　　　.Fields("ID") & ", " & _
　　　　　　　　　　　　　　　.Fields("数量") & ");"
　　　　　　cmd.Execute
　　　　　　.MoveNext
　　　　Loop
　　End With
　　Me.Requery
End Sub

これで、首尾よく、

1,100
2,200
3,300

と表示されます。

お礼

長文の回答をいただき誠にありがとうございました。
大変勉強になります。解読のうえ早速やってみます。

回答No.2

O_cyan

>フォームのテキストボックスは全て非連結にしてあります
>実行しますとフォームには「最後のレコード」しか表示されません。
根本的に非連結フォームの場合は詳細セクションは一つしかありません。そのため１レコード分のデータしか表示できません。
帳票・データシートのフォームに複数レコードを表示するには連結フォームでしか無理です。

質問の記述を見ると作り込んでやろうとしているので下記の仕様の説明で理解していただけると思いますが。
例えば別の方法として・・
期間開始・期間終了・在庫場所の抽出条件のコントロールをメインフォームに配置し抽出集計したレコードを表示するフォームをサブフォームとして配置してサブフォームのレコードセットにSelect Caseで分岐させ該当するSQLを代入するようにしてサブクエリをRequeryさせる仕様にするのはどうでしょうか。
質問にある宣言とstrSQLを追加して
Dim strSQL As String
strSQL = ""
strSQL = strSQL & "SELECT 物品ID, Sum(仕入) AS 仕入計・・・"　'SQLをセットします。
Set rst = cnc.Execute(strSQL)
Set Forms![メインフォーム名]![サブフォーム名].Form.Recordset = rst
サブフォームにRecordsetをすることによって連結フォームになりますのでWithを使い仕入計.ControlSource = "仕入計"と払出計.ControlSource = "払出計"としてコントロールソースもセットしサブフォームをRequeryする。
こんな感じではどうでしょうか。

お礼

回答いただきありがとうございます。
サブフォームも考えましたが、その方法がわからず行き詰まっていました。
じっくり解読させていただきトライしてみます。