アクセスのフォーム上で計算値の合計がエラーになる

質問

アクセスで製品オーダを入力したときに現状の部品在庫に対して不足する部品数を計算
し、その部品数に単価をかけた各不足部品の合計金額を計算できるよう、添付画像の
フォームを作成しました。（画像はデザインビューです。）
フォーム画像の(1)はフォーム上で入力する数です。(2)(6)は、コントロールソース値
にクエリのフィールドを指定、(3)のコントロールソース値も同じクエリで計算された
フィールドを指定して表示されます。(4)(5)(7)(8)(9)(10)は、(1)を入力したときに
計算されるよう、コントロールソース値にそれぞれ次の式を入れています。

(4)：=[員数]*[テキスト50]
(5)：=IIf([テキスト47]-[見なし在庫数]<0,0,[テキスト47]-[見なし在庫数])
(7)：=IIf([テキスト49]/[最少発注単位]=0,0,Round(([テキスト49]/[最少発注単
　　　 位])+0.49999,0))
(8)：=[最少発注単位]*[テキスト66]
(9)：=[部品単価]*[テキスト64]
(10)：=Sum([部品単価]*[テキスト64])

ここで、(1)のオーダに数を入力したら、(4)(5)(7)(8)(9)はそれぞれ計算結果が出てく
るのですが、肝心の(10)については、「＃エラー」となってしまいます。

ここ何日かこの問題でストップしていてどうしたら良いかわからず、非常に困ってしま
いました。（ここでは省略しますが、色々と試してみても全くダメです。（泣））

どなたかアクセスにお詳しい方、何卒お助けいただきますよう、宜しくお願い致します。

回答No.6

hatena1989 · ベストアンサー

前に書きました通り、Sum関数からテキストボックスは参照できません。
ですので、テキストボックスを参照しない式にする必要があります。

現状は、[テキスト66]のコントロールソースに[テキスト49]があり、[テキスト49]のコントロールソースには[テキスト47]があり、・・・・・というように連鎖しているわけですね。

このテキストボックスの参照をなくして、すべてフィールドのみを参照する式に書き換える必要がありますが、
そうすると非常に長く複雑な式になると思われます。

その場合、このテキストボックスの式をすべてレコードソースのクエリに演算フィールドに移行させるのがいいでしょう。

演算フィールドに設定する式の例

テキスト50: フィールド名

テキスト47: [員数]*[テキスト50]

テキスト49: IIf([テキスト47]-[見なし在庫数]<0,0,[テキスト47]-[見なし在庫数])

テキスト66: IIf([テキスト49]/[最少発注単位]=0,0,Round(([テキスト49]/[最少発注単位])+0.49999,0))

テキスト64: [最少発注単位]*[テキスト66]

テキスト50 のコントロールソースが分からなかったので、フィールド名 としましたが、
実態に合わせて変更してください。
もし、フォーム上のテキストボックスを参照するなら、

テキスト50: [Forms]![フォーム名]![テキストボックス名]

としてください。

「テキスト50」などの列名は、できれば、式の意味が分かる名前に変更した方がいいでしょう。

クエリが作成できたら、このクエリを単独で開いて、エラーなく開くことができ、正しい結果になるか確認してください。
[Forms]![フォーム名]![テキストボックス名]というようなフォーム名を参照するパラメータが出たら、
適切な値を入力してください。

これでエラーが出たり、想定外の結果になったり、想定外のパラメータが出たら、その詳細をお知らせください。
また、クエリをSQLビューにして、そのSQL文をコピーして返信に貼り付けてください。

お礼

出来ました！ありがとうございます。
アドバイスいただきましたように、各テキストに書かれていた式をレコードソース
としているクエリにフィールドを追加し、そこに式を入れてフォームのコントロー
ルソースにはそのクエリのフィールド名を指定していきました。
[テキスト50]は非連結の単独テキストでしたが、同じクエリにフィールドを設け、
オーダ: [Forms]![F_指定材料検索結果]![テキスト50]としてフォーム上にはテキ
スト50に入力した値をリクエリするボタンを設けて実行したら合計が出ました。
（勿論、フォームの合計欄であるテキスト62の式もフィールド名に直しました。）
クエリの式は非常に長いものになり、本当にできるかどうか不安でしたが、実行し
て値が出た時には非常に感激しました。

無知な質問に対してご丁寧にアドバイスをいただきまして、誠にありがとうござい
ました。感謝の気持ちでいっぱいです。
質問を挙げさせていただく時は、本当に行き詰った場合と心がけていますが、その
際は懲りずに何卒宜しくお願い致します。重ねてお礼申し上げます。

回答No.5

山田 太郎（@f_a_007）

【このコレクションには項目がありません】

とは？

いわゆるSum()は、先に示したMySum()と同じ処理をしているものと推察されます。ということは、Sum()は当該フォームのRecordset.fields()を対象に合計することになります。つまり、Recordset.fields()に含まれないテキストボックスは集計できないと言うことです。「このコレクションには項目がありません！」は、「指定のテキストボックスはレコードセットのフィールドではありません！」という意味です。よって、関数エラーが表示される結果をもたらしています。

【対策】

各テキストボックスを列に持つ一時テーブルを作成するというのが一つの回避策かと思います。

お礼

ありがとうございます。
VBAを利用したやり方ももっと勉強してみます。
大変勉強になりました。

回答No.4

hatena1989

[テキスト64] は、フォーム上のテキストボックスですか。

通常、Sum関数の引数には、レコードソースのフィールド名を指定する必要があります。
テキストボックス名だとエラーになります。

[テキスト64] のコントロールソースにはどんな式が設定してありますか。
フィールド名が設定してあるなら、

=Sum([部品単価]*[フィールド名])

と変更してください。式が設定してあるなら、その式にしてください。
ただし、その式でまたテキストボックスを参照していたらエラーになります。

レコードソースのクエリでフィールドに式を設定して(演算フィールド)、それをSumの引数に指定したほうがいいかもしれません。

補足

アドバイスありがとうございます。
[テキスト64]のコントロールソースは、

=[最少発注単位]*[テキスト66]

であり、[テキスト66]のそれは、

=IIf([テキスト49]/[最少発注単位]=0,0,Round(([テキスト49]/[最少発注
　単位])+0.49999,0))

としてあります。（割り切れた場合を除く切り上げ処理を行っているため、
少し長い式になっています。）
また、[テキスト49]のコントロールソースは、

=IIf([テキスト47]-[見なし在庫数]<0,0,[テキスト47]-[見なし在庫数])

[テキスト47]は、

=[員数]*[テキスト50]

となっており、最初にこのフォームを開いたときは、
[テキスト50][テキスト47][テキスト49][テキスト66][テキスト64]
[テキスト60][テキスト62]は空欄となっています。それ以外のテキスト
ボックスは、レコードソースとして指定しているクエリのレコード値が
入ります。
そこで、開いたフォームの[テキスト50]にオーダ数を入力してEnterを
押すと、[テキスト62]以外は、ちゃんと計算された値が入りますが、
[テキスト62]だけが「#エラー」と表示されてしまうのです。

アドバイスにありましたようにレコードソースのクエリに、[テキスト50]
を除くフィールドを作成して式を入れ、フォームの各テキストのコント
ロールソースには、クエリのフィールド名を指定してみましたが、テキ
スト50、47、49、66、64のパラメータ入力を求めるダイアログボック
スが順次現れ、仮に適当な数字を入力していってもクエリのレコードが
セットされず、うまくいきませんでした。

何か対処方法はありますでしょうか。

回答No.3

山田 太郎（@f_a_007）

添付図を忘れていました！

回答No.2

山田 太郎（@f_a_007）

＞(4)(5)(7)(8)(9)はそれぞれ計算結果が出てくるのですが・・・

ちゃんと、全ての行で正しい結果を得ているのだろうか？
[帳票フォーム]でも全ての行で正しい結果を・・・。

＜確認要領＞

次の関数を登録して =Sum(xxx) を = MySum() に変えると・・・。
もしかしたら、添付図のようなエラーが表示されませんか？

Public Function MySum() As Currency
On Error GoTo Err_MySum
　　Dim curSum As Currency
　　Dim rst　　As DAO.Recordset

　　Set rst = Forms("フォーム名").RecordsetClone
　　With rst
　　　　.MoveFirst
　　　　Do Until .EOF
　　　　　　curSum = curSum + Nz(.Fields("コントロール名")) * Nz(.Fields("コントロール名"))
　　　　Loop
　　End With
Exit_MySum:
On Error Resume Next
　　rst.Close
　　Set rst = Nothing
　　MySum = curSum
　　Exit Function
Err_MySum:
　　curSum = 0
　　MsgBox "実行時にエラーが発生しました。(MySum)" & Chr$(13) & Chr$(13) & _
　　　　　　"・Err.Description=" & Err.Description & Chr$(13), _
　　　　　　vbExclamation, " 関数エラーメッセージ"
　　Resume Exit_MySum
End Function

【添付図のようなエラーが表示されない】

ならば、有り得ない状況。

【銀行丸めでない四捨五入関数】

は次のようです。

Option Compare Database
Option Explicit

Public Const 四捨五入 = 0
Public Const 切り捨て = 1
Public Const 切り上げ = 2

Public Function Rounds(ByVal M As Currency, _
　　　　　　　　　　　 ByVal A As Integer, _
　　　　　　　　　　　 Optional D As Integer = 0) As Variant
　　Dim R As Currency

　　Select Case A
　　　　Case 0 ' 四捨五入
　　　　　　R = Fix(M * 10 ^ D + 0.5@)
　　　　Case 1 '切り捨て
　　　　　　R = Fix(M * 10 ^ D)
　　　　Case 2 ' 切り上げ
　　　　　　If Int(M) <> M Then
　　　　　　　　R = Fix(M * 10 ^ D + 1@)
　　　　　　Else
　　　　　　　　R = Fix(M * 10 ^ D)
　　　　　　End If
　　End Select
　　Rounds = Sgn(M) * (R / 10 ^ D)
End Function

お礼

ご記載いただいたVBAを追加して実行しましたところ、添付していただいた
内容と同じエラーメッセージが出てしまいました。残念です。根本的におか
しいのでしょうか。
ともかく、早々に丁寧なアドバイスをいただき、大変有難うございました。

回答No.1

kkkkkm

どこに(10)があるのか画像で認識できませんが
=Sum(9のフィールド名)
ではだめですか。

いろいろ試したことを書かないと試したことを回答として書くことにもなりますし、回答者によってはどうせこれはやってるだろうなと思って、実際正解だったりすることを回答しない可能性もありますから、やったこと箇条書きにでもした方がいいと思いますけど。試したことを省略っていうのは質問者の手抜きってことと捉えられることもあります。

補足

アドバイスありがとうございます。
他に試してみたことは次の通りです。

I．(４)(５)(７)(８)(９)をレコードソースとしているクエリのフィールドに加え、
　　フォーム上からは、そのクエリのフィールドを指定する。
II．(10)のコントロースソースの式を=Sum（[テキスト60]）いする。
III．(８)と(９)の指す[テキスト66][テキスト60]をコントロールソースの式に指定
　　する新たなテキストボックスを同じフォーム上に追加し、(10)のコントロール
　　ソースの式にその追加したテキストボックス名（仮に[テキストA]、[テキスト
　　B]とします）を使用する。
　　=Sum([テキストA]*[テキストB])

いずれの場合も(10)は、「#エラー」でした。