Microsoft Access 掲示板

リストボックスで複数選択しそれをテーブルに格納したい

既存のテーブルに追加したいということなら印刷するコード部分(DoCmd.OpenReport)を追加クエリを実行するコードに書き換えればいいでしょう。

追加元と追加先のテーブルのフィールド構成が同じ場合のコード例

    CurrentDb.Execute "INSERT INTO 追加先テーブル" & _
        " SELECT * FROM 追加元テーブル Where " & stFilter, dbFailOnError

Print # ステートメントは、標準の挙動として出力する文字列の最後に改行コードを自動で付与しているようですね。
失礼いたしました。

hatena様ありがとうございます。アドバイス頂いた方法で試し正常動作することが確認出来ました。この方法であれば売上毎に可変可能ですのでイレギュラーな事案にも対応出来ます。後は入力フォームの工夫だけです。助かりました。

hiroton様ありがとうございます。フォームを使っての処理がほとんどなのでレコードセットは色々試してみます。

事前に色々想定しているのですが、どうしても想定外の事案が発生しますのでその都度改訂が必用になります。その時のアプローチが重要ですね。大変勉強になりました。

おっしゃる通り最初の設計は正規化としてはよろしくない部分はありますね。
下記の部分
Ｅ：変更前支払条件テーブルを作成

そこから指摘して説明すると長くなるので対処法の一例を回答しました。

この手法が正規化崩しといえるかどうかについては異論はあるかとは思います。

その支払条件が変更になる場合が発生しました…過去の売上分も改訂された締め日になってしまうので、

　正規化崩し云々以前に、正規化が杜撰だったと気づいてどうしよう？というだけでしょ。設計から全部やり直しましょう。

レコードセットは、レコードの集合体です。レコードは管理されたデータのひと塊分です。レコードはイメージとしては「テーブルで表現したときの1行分のデータ」でも良いでしょう
主キーのないレコードセットもありますが、主キーがあればそれらは重複しないとか、必ず決められたフィールドを持つとか、フィールドに対してデータがあるとか、言葉にすれば当たり前のようなことですが、そういうルールで決められたデータのまとまりがレコードになります

レコードセットは「テーブルのようなもの」ととらえても問題ないですが、クエリをソースにして、複数のテーブルを繋げたり、必要なレコードだけ、必要なフィールドだけに絞り込んでデータをまとめて扱うことができます
また、フィールドによっては計算結果をソースとするものもあり、テーブルとして記録されないデータを持つ場合もあります

レコードソースのあるフォームであれば自動的にレコードセットが出来ているのでしょうか？

その通りです
レコードセットは必要なだけ作ることができるので、Aフォームを開けばAフォームのレコードセット、Bフォームを開けばBフォームのレコードセット、VBAで何か処理したいときにSet rs = CurrentDb.OpenRecordset("～")で読み込んだレコードセット「rs」のようにその時その時に応じて必要なだけ存在することになります

レコードセットはデータを直接操作できる仕組みなので、ACCESSがデータベースとして正しく動くための作法があります。VBAでレコードセットを扱うためには、その作法に従って間違いない記述が必要ですが、そういうところをできるだけ簡単にできるようにやってくれているのがACCESSのフォームという機能です

hatenaさん提案のように、もう少し全体像から改善を考える必要もあるのかなと思っています。質問の段階でだいぶ深いところからスタートしているのでその部分をさらに掘り込んで回答していますが、その内容が中途半端なのも別なアプローチの検討の余地があるだろうからです

データベース設計は正規化からスタートするわけですが、実務上は動くクエリのためにテーブル構造を変えるというのも避けられないことだと思っています（特に、ACCESSは貧弱なので）
VBAはデータベースの世界から見ればかなり特殊な対処法になるので、SQLでの対処も同時に考えて、必要ならテーブル構造の見直しから、なるべくメンテナンス性を犠牲にしたい手法を考えたいところです

正規化を突き詰めすぎるとテーブル構成が複雑になりパフォーマンスが悪くなったりすることがあります。
今回の質問もそのような状況だといえます。

そのような場合にあえて冗長なデータを追加して正規化を崩すことでパフォーマンスが改善できる場合があります。このような手法を「正規化崩し」といいます。「正規化崩し」について調べてみるとテーブル設計の幅がひろがると思いす。

hatena様ありがとうございます。なるほど売上毎に持たせるといいですね（1レコードづつオートナンバーでユニークになっていますので）。売上テーブルにフィールド追加して過去の分は引っ張り出せるのでそれで代入しておき、新規レコードにはフォーム等で仕組み作る形で。
それが一番楽に出来る様な気がします、試してみます。
アイデア大変参考になりました。

hiroton様ありがとうございます。色々とハイレベルなテクニックがありますね。レコードセットが今ひとつ理解できていません（どういうものなのか・・・）。使いこなせれば大変便利とは思っています。イメージとしてはプロシージャ内でテーブルの様なものを作成しておき（それがレコードセット？）、それに対して代入や更新等が可能という感じでしょうか？　何かで聞いた事があるのですがレコードソースのあるフォームであれば自動的にレコードセットが出来ているのでしょうか？
初歩的な事ですみません。

リレーション先の値に更新があった場合、過去の値も変更されてしまう場合の対処法ということですよね。

いろいろな方法があると思いますが、自分は下記のような設計にすることが多いです。

締日の更新に対処する場合の例

Ａ：顧客テーブル　Ｂ：売上テーブル　Ｃ：締め日テーブル

このようなテーブル構成の場合、締め日IDはＡではなくＢに格納します。
Ｂの入力時に規定値として最新の対応する締め日IDを入力します。
これは規定値プロパティを使うか、入力フォームの更新後処理で入力します。

この方法だとクエリで単純にリンクさせるだけで対応する締日を取得できます。

良いか悪いかだと、うーん、ちょっとだけ、よくないですかねぇ
Eのテーブルは、変更があったものだけ、ではなく、履歴としてすべてを記録するようにすると余計な存在有無チェックがなくなるのでいろいろとはかどります（Dlookupを使うにせよ、SQLで全部やるにせよ高速化になります）

特定日のデータを期間の設定（開始日、終了日）から見つけ出す作業は、ACCESSでは特に苦手とする部類だと思います。モジュール化して対応しようとすると、どうしても「DLookupは遅い」のボトルネックに引っかかる形になるので、SQLを駆使してクエリ一つに書き上げるというのが一般的な回答になるんじゃないでしょうか？（SQLを直接記述するんでそもそもACCESSレベルでは一般的ではない）

遅い早いに関しては個人の感覚によるところなので、実際に運用する件数n件に対して、処理時間yでそれが、実運用に耐える/耐えない時間であるという評価基準が必要になります
それによって、手法のシンプルさ（メンテナンス性）と実処理時間のバランスが取れるように、最適な回答が変わってくるでしょう

自分はSQLゴリゴリ書くのはあまり得意ではないので、そっちの方向は他の人に任せます（お願いします）
VBAの話をするのであれば、まずは質問にあるところで、

このモジュールでいくつかの値（支払条件テキスト、締め実日等）も同じパターンで値を抽出している

これは、遅くなる原因としてかなりの比重がありそうです

Dlookup("ID")
Dlookup("支払条件テキスト")
Dlookup("締め実日等")

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("～")
id = rs!ID
支払条件 = rs!支払条件テキスト
締め実日 = rs!締め実日
rs.Close

上記Dlookupだけで処理した場合はDlookupのボトルネック＋検索捜査が3回発生するのに対し、下記、レコードセットを取得した処理では、ボトルネック部分が1回、捜査処理も1回でそのほかのデータ取得は一瞬で終わります
先に挙げたリンク先でも、こういう処理をしたいならSQL文を使ったほうが良いと言っていますね
特に、一つのレコードから複数のフィールドの値を参照したいのであればDlookupの出番はないです

もう少し、VBAの高度な話をすると、

Set dbs = CurrentDb

これにかかる時間が相当にありそうだ、と、先に挙げたリンク先に指摘があります。オリジナル関数の呼び出し事（実処理のレコード数分だけ）実行しているとこれもボトルネックになるので、これをやらないように組むということもできます

オリジナル関数の外で変数を用意しておく

Public dbs As Database
Public rs As Recordset

Sub Init()
  Set dbs = CurrentDb
End Sub

Function オリジナル関数()
  Set rs = dbs.OpenRecordset("～")

Init関数を事前にどこかで1度だけ呼び出して、「ボトルネックになるDatabaseオブジェクトの生成」を1回のみにするなんて方法も考えられるでしょう

先に挙げたように、許容点はどこか？という話になるので、詳しい解説は抜きにしてひとまずこのくらいにしておきます

hiroton様ありがとうございます。「なぜDLookupが遅いのか？」はなんとなくレベルですが理解しました。
今回作成のモジュール概要は　Ａ：顧客テーブル　Ｂ：売上テーブル　Ｃ：締め日テーブル　Ｄ：売上明細テーブル 等があります。各テーブルにはオートナンバー型ＩＤがあります。売上毎締め日期日クエリでそれぞれ連結させて売上毎の締め日付、支払期日等を他のモジュールで算出してまして、それは問題なく機能してました（この時は処理速度も速かったです）。
Ａには顧客毎の支払条件があります（Ｃから選択）。その支払条件が変更になる場合が発生しました（例えば売上日付が2026/2/1から締め日が変更）。Ａテーブルの支払条件を改定後の内容にしてしまうと、過去の売上分も改訂された締め日になってしまうので、対策としてＥ：変更前支払条件テーブルを作成し（顧客ＩＤ、変更前の締め日ＩＤ、初期日、最終日　等のフィールド）それに変更のあった顧客分のみ変更前の支払条件を入力してます。今回作成のモジュールにＤＣｏｕｎｔでテーブルＥから対象顧客分をカウントして、それが０の場合はＡテーブルからから、1以上ならＥテーブルから締め日ＩＤを抽出しました。今後同一顧客で２回以上の変更もあるかもを想定して売上日付をＥテーブルの初期日、最終日の期間内かどうかも判定させて条件に合う締め日ＩＤを抽出させてます。このモジュールを売上毎締め日期日クエリに組み込み実行すると結構時間かかるので、Ｄｌｏｏｋで締め日ＩＤを抽出が原因？でしたのでＳＱＬでしたらと思ったのです。
このモジュールでいくつかの値（支払条件テキスト、締め実日等）も同じパターンで値を抽出しているのも遅い原因と思いますが。
この方法が良くないでしょうか？

いろいろ突っ込みどころはあるんだけど、
パッと見た感じ、「Dlookupが遅い理由をそのままに、Dlookupと同じ機能をわざわざ自前で作ろうとしている」感じですね
「なぜDLookupが遅いのか？」を理解しないと改善にはならないです

定義域集計関数とSQLの比較（T'sWare さん）

hatena様ありがとうございました。以前使っていたんですがどこにしまったか忘れまして。これを使えば
かなり体裁がよくなります。

下記のページの方法3の手法かな？

定型枠に一文字ずつはめ込み印刷 - hatena chips

コントロールソースを
=Format([フィルード名],"@@@@@@@@@@")

文字配置を均等割り付けに設定

@の数で桁数を指定してテキストボックスの幅で文字の間隔を調整します。

普通の方法でいくと123456と間隔が短く読みづらいため
　質問を編集して現状のスクリーンショットを追加できますか？

　その手法(以前レポートの数値コントロールに＠＠＠等の記号で調整した記憶)をご教示下さい？

Access レポートで数値を入力する際間隔を調整する方法をおたずねします。
普通の方法でいくと123456と間隔が短く読みづらいため、以前レポートの数値コントロールに＠＠＠等の記号で調整した記憶がありますので、その手法をご教示下さい。

JOINを使用すればテーブルを分けても、一度でデータを取り出せるケースは増えます。
しかし、フィールド数が少ないと、複数回SELECTする事は避けられないですよね。

「どのような構造を持ったテーブルを参照し、どのような条件に該当するレコードを抽出し、どのような演算、変換処理を行い、その実行結果を、どこに、どのようなレイアウトで出力しようとしているのか」によるとしか。

現時点では具体的にそれが示されていないので何とも答えようがありませんが、JOIN も SELECT も「する必要があればそうする」だけのことでしょう。

今度は、配列を大きくし過ぎると止まるという制約が出てきます。

配列を使わないでシートに書き出していたら、この状態に至る以前に処理できなくなってるはずです。

同上。
唐突に配列やシート書き出しの話をされましても、その辺りの詳しい事情や背景が一切不明であるため、答えようがありません。

例えば「参照元のテーブルに膨大な件数のレコードが格納されていて、その全てのレコードの全てのフィールドを取得しようとしている」といった場合、レコードの件数やフィールドの数、格納されているデータの大きさに比例してアウトプットに時間がかかってしまうのは当然の結果と言えます。
その上で「出来るだけ実行時間を短縮できるようにするにはどうすればよいか」という問題は全く別の話です。

必要事項には回答しないと許可が下りないので、項目を減らすわけには行きません。

「必要事項には回答しないと許可が下りない」がどういうことなのかが不明瞭ですが、その項目が本当に必要なものなのであれば、無理に減らす必要はありません。
ただ「リレーショナルデータベース上で管理するテーブルとして適切に構造化されているか」ということが問題なのです。

過去のスレッドから推定し得る限り、実際のフロントエンドは Access ではなく Excel であると思われますが、もし「元々 Excel で作っていた表（やたら列の多い非正規形テーブル）のレイアウトをそっくりそのまま Access のテーブルに落とし込んだ」ということであるなら、少なくともそのようなテーブル設計手法は Access に限らず、あらゆるリレーショナルデータベースシステムに適していません。

「どのような構造のテーブルをデータベース上に定義するか」
「どのようなインターフェースを用いてデータベース上のデータにアクセスするか」
「データベースに対してどのような問い合わせを行い、どのようなレイアウトの出力結果を得るか」
は、基本的に分けて考えるべきでしょう。

hatenaさん　曖昧な返信になってました（すみません）。区分条件をそれにするならば同じ区分内での重複は不可にしなくてはなりませんので「編集を続けるか」確認はなしになります。

JOINを使用すればテーブルを分けても、一度でデータを取り出せるケースは増えます。
しかし、フィールド数が少ないと、複数回SELECTする事は避けられないですよね。
複数回SELECTしても、配列に追加すれば済む話なので大したロスにはなりませんが、
今度は、配列を大きくし過ぎると止まるという制約が出てきます。

配列を使わないでシートに書き出していたら、この状態に至る以前に処理できなくなってるはずです。
必要事項には回答しないと許可が下りないので、項目を減らすわけには行きません。

難しくて、色々費用も掛かってくるであろうMy SQLは使いたくはないので、できるだけしがみつきますが、、、
「そろそろ、お前もっと金使えよ」という、IT君からの忠告げなんでしょうね。

どうしようもなくなったらMySQL当たりに変更するしかなさそうです。
 どこに変更しても上限はあるんですよ。根本的に見直しましょう。

フィールド名が限界に近づいていたのが原因でした。
とりあえず、テーブルを分割して凌ぎましたが、
どうしようもなくなったらMySQL当たりに変更するしかなさそうです。

ただ、AIで他の言語の書き換えは簡単にできるようになっているので、
いままで作ったものが、全く無駄になる事が無いのは救いです。
おそらく、AIに命令しただけでは、同じ処理ができるシステムを作ってくれないと思います。

当面の対応策としては、
[ファイル]タブ > [オプション] > [現在のデータベース] を選択し、「閉じるときに最適化する」にチェックを入れて最適化を試してみます。

strSQL = "INSERT INTO 事業所データ([事業所番号]) VALUES('test2')"
adoCn.Execute strSQL 'SQLを実行して対象を追加

実行時エラー-2147467259(80004005)　引数が無効です

手動でデータを入力してみたところ、１回目は引数が無効ですで入力できず
２回目には登録ができる状態が、２回続けて起こりました。

状況的には、既に行われた「XMLファイルのエクスポート→インポート」が最も有効な解決方法であると思われますが。

このテーブルはフィールドが238ありまして限界に近いのですが、

どうも、200超えたあたりから動作が怪しくなる気がしますが、こんなことがあるのでしょうか？

• Access において、1 つのテーブルに定義できるフィールドの数は 255 個までである。

• 1 つのテーブルにおいて、フィールドの追加/変更/削除を繰り返しているうちに、「実際に画面上で確認できるテーブルのフィールド数よりも多いフィールド数分の設定情報が保持されている」と認識される状態となることがある。

• この「フィールド数のずれ」が含まれているテーブルに対してレコードを追加しようとすると、Access データベースエンジンが正常にデータを処理することが出来ず、件のエラーが発生する。

上記の「設定情報」を Access のユーザーインターフェース上で閲覧、編集することは出来ませんので、前述の方法によってそのテーブルをまるごと作り直すことでしか修正できません。

参考: access 実行時に、「引数が無効です」のエラー

いずれにせよ、この現象の性質上、フィールドの数を上限に近いほど増やしたり、1 つのテーブルのデザインの変更（フィールドの追加/削除からの上書き保存）を頻繁に繰り返すことはあまり推奨されません。

[事業所番号]と[事業所名]の他にどのようなフィールドが定義されているのか不明ですが、可能であればテーブル構成の再設計や正規化を検討されることをお奨めします。

strSQL = "INSERT INTO 事業所データ([事業所番号]) VALUES('てすと')"
adoCn.Execute strSQL

「事業所名フィールドに値を入力してください」（主キーは事業所番号に設定されています）で、エラー

テーブル[事業所データ]のフィールド[事業所名]の[値要求]プロパティはどのように設定されているのでしょうか。

事業所データテーブルのフィールドを190にまで減らして

 190まで減らしても多い。その数字が明らかに杜撰な設計とバレバレだから正規化やり直しましょうね。

事業所データテーブルのフィールドを190にまで減らして試してみましたが、同じエラー。
テーブル削除してから、手動で新規作成事業所番号のフィールドを作ったところ、処理はできました。
しかし、XMLファイルのエクスポート→インポートで、テーブルを入れ替えられないとなると、
エラーが起きた時に、データが取り出せなかった場合は、致命的な問題となってしまいます。
取り出せたとしても、フィールドを手動で作り直していたのでは、お話になりません。

そもそも、バックアップしてあったファイルも同時に書き込みできなくなったのでは、バックアップに意味がありません。

正しい対応方法をご存じの方がいらっしゃいましたら、ご指導願います。

仕方ないので、XMLファイルでエクスポート→テーブル削除→インポートで、テーブルを入れ替えました。
今度は、
strSQL = "INSERT INTO 事業所データ([事業所番号]) VALUES('てすと')"
    adoCn.Execute strSQL
「事業所名フィールドに値を入力してください」（主キーは事業所番号に設定されています）で、エラー
試しに
strSQL = "INSERT INTO 事業所データ([事業所名]) VALUES('てすと')"
「事業所番号フィールドに値を入力してください」（主キーは事業所番号に設定されているので当然ですが）で、エラー

いったい、なぜこんなエラーが発生するのでしょうか？

追伸ですが、
手動でデータを入力してみたところ、１回目は引数が無効ですで入力できず２回目には登録ができる状態が、２回続けて起こりました。
やはり、フィールド名が増えてACCESSがキャパオーバーになっていることが原因のエラーなのでしょうか？

hatenaさん　ありがとうございます。流れとしてはそうなります。部品区分があり、異なる区分で同じ品名を登録する場合がありますので。うっかりしてましたが条件を下記の様にした方がいいですね　
Dim　Kubun　AS　Long　　　Kubun=Me.[区分]　の変数追加して　
joken = "[部品型番] = '" & buName & "' And [部品ID] <> " & buID & " AND [区分]=" ＆ kubun
これで同じ区分内での同じ品名の重複をチェック（編集中の部品ID分は除く）
宜しくお願いします。

バックアップのACCESSファイルでも、エラーが出るのでテーブルが壊れたの原因とは思えないのですが、
コードに間違えは無いので、問題はACCESSですよね。
このテーブルはフィールドが238ありまして限界に近いのですが、
こういう事も、エラーが起きる原因になりうるのでしょうか？

実は、当初はEXCELだけで処理していたのですが、
シート数やデータが多くなると不具合が頻発するようになってSQL ＋ ACCESSに移行しましたが、
ACCESSでもフィールド数が増えると、原因不明のエラーが増えているような気がします。
フィールド名の合計バイト数みたいなものもあったような気がします。
フィールド数が多いとここに抵触する可能性も出てきますが、
そうであればオーバーした時点で、フィールドが登録できなくなるはずですし。

どうも、200超えたあたりから動作が怪しくなる気がしますが、こんなことがあるのでしょうか？

確かにテーブルインデクスで重複なしにするといいのですが、品名入力時に注意喚起メッセージを出せるといいと思ったのです。

なるほど。レコード保存時（フォームの更新前処理）ではなく品名入力時（テキストボックスの更新前処理）でチェックしたいと言うことですね。そういうUIもありますね。
重複を排除するにはインデックスとこれの両方とも設定しておくと確実ですね。

今回は部品名や会社名等の入力フォームで名前の重複入力チェックを下記コードで行っています

ということなので、今回の[部品型番]だけでなくいろいろな場面での重複チェックを共通化したいと言うことですね。

まずは共通化する前に品名テキストボックスの更新前処理にコードを書くことになります。
その場合、質問のコードだと下記のようなフローになってます。

自レコード以外のIDで[部品型番]に重複がないかチェック(DCount）
　↓
重複がなければ Exit Sub
重複があるときはメッセージで「編集を続けるか」確認
　↓
「はい」で更新して次へ
「いいえ」で[部品型番]の入力を元に戻す

これだと、重複があった場合に「はい」を選択すると部品型番の重複を許すことになりますが、
それでいいのでしょうか。
とりあえずこれを確認したいと思います。

「insert into 引数が無効です」でググってみるとテーブルが壊れているんじゃないか？的な対応策があるようです

漢字の諸々をアルファベットに変更してもエラーになるかしら？

hatenaさん ありがとうございます。確かにテーブルインデクスで重複なしにするといいのですが、品名入力時に注意喚起メッセージを出せるといいと思ったのです。[部品ID] <>としてますので編集中の分以外をチェックさせています。というのは例えば品名を”あいう”でレコード保存後に”え”を追加入力後に”え”を削除した場合にはメッセージが出ない様にしたいからです。あと Me.Undo にすると確かにすべての入力が取り消されるので品名部のみを元に戻した方がいいですね。

車輪の再開発に意味があるのかしら？データベース設計が杜撰なだけでしょう。

共通化する前に、そのコードで問題ないのか疑問があります。

部品IDと部品型番の組み合わせで重複をチェックしてますが、部品IDが異なれば部品型番に重複があってもいいのでしょうか。現状のコードだと部品型番に重複があっても登録可能になっています。

あと、更新前処理で Me.Undo してしまうとすべての入力が取り消されてしまいますがそれで問題ないですか。

あと、データベース設計で重複チェックは、テーブル設計の方でインデックスなどを使用して設定するのが確実ですが、その設定はしていますか。

プリンタの設定で出力されるデータが変わることはありえません。

マクロの「where条件式」に設定した式が間違っているのでしょう。
設定した式を提示してください。

支払に関しては、本社が支払う場合と工場が支払う場合とでは口座が違うので、メインには格納できないかと。

そういうことなら、両者の関係は一対多の関係になりますので、口座情報は別テーブルにすればいいでしょう。

一例をあげると下記のようなテーブル構成になります。

会社マスター
※会社ID
　会社名
　代表者名
　・・・

本社・支社マスター
※本社・支社ID
　会社ID
　支払口座ID
　本社・支社区分
　本社・支社名
　住所
　・・・

支払口座情報
※支払口座ID
　金融機関名
　支店名
　預金種類（普通 or 当座）
　口座番号
　口座名義
　・・・・
　
リレーションシップ設定
会社マスターﾎ.会社ID → 本社・支社マスター.会社ID
支払口座情報.支払口座ID → 本社・支社マスター.支払口座ID

hatena様
メインテーブルに会社のデータと支払情報のデータを格納し、
サブテーブルに本社・支社区分と住所のデータを格納するような感じの設計になるということでしょうか。

支払に関しては、本社が支払う場合と工場が支払う場合とでは口座が違うので、メインには格納できないかと。

かといってサブに支払情報を持たせると、
ある会社によっては複数ある工場が同じ支払情報を持っているので、同じ支払情報のデータが重複してテーブル内に格納されます。

話が食い違っていたらすみません。

上記の投稿は私のものです。
外出先からログインせずに投稿したので名前なしになりました。