If .RecordCount = 0 Then
For i = 0 To monthNum Step s
'全部新規登録
Next
Else
For i = 0 To monthNum Step s
.FindFirst "入金月 = " & "#" & DateAdd("m", i, startDate) & "#"
'(省略)
Next
End If
Dim rs As Recordset
Set rs = Me.月額サブフォーム.Form.RecordsetClone
With rs
For i = 0 To monthNum Step s '請求頻度ごとに
'既に同じ入金月のレコードが登録されているかチェック
.FindFirst "入金月 = " & "#" & DateAdd("m", i, startDate) & "#"
If .NoMatch Then
.AddNew
![契約番号] = Me.契約番号
![月額] = Me.月額
![入金月] = DateAdd("m", i, startDate)
![税率] = Me.税率
![税の処理] = Me.税の処理
.Update '★ここでデバッグが出る
'同じ入金月のレコードが登録されていない場合
Else
.Edit
![月額] = Me.月額
![税率] = Me.税率
![税の処理] = Me.税の処理
.Update
End If
Next
.Close
End With
ある属性X( 1 )を決めると、他の属性Y( AA )の値が一意に決まる
ある属性X( 2 )を決めると、他の属性Y( AB )の値が一意に決まる
ある属性X( 3 )を決めると、他の属性Y( AC )の値が一意に決まる
...
場合、「 Y は X に関数従属している 」といいます。
Y の種類が少数の場合は
IIf関数やSwitch関数を使って X を定義するのもいいでしょうけど
Y の種類が多い場合や、今後、Y の種類が増えることが想定される場合は
テーブル設計を見直したほうがいいでしょう。
CREATE TABLE t_分類マスタ
(
分類CD INT NOT NULL PRIMARY KEY
, 分類名 VARCHAR( 50 ) NOT NULL
);
分類CD
分類名
1
AA
2
AB
3
AC
4
AD
`sql
CREATE TABLE t_分類トラン
(
入力ID COUNTER NOT NULL PRIMARY KEY
, 分類 VARCHAR( 50 ) NOT NULL
);
If Not IsNull(Me.総合検索) Then
strFilter = strFilter & “ AND “ & BuildCriteria(“[質問]&[回答]&[種別]”, dbText, _
“(*” & Replace(StrConv(Me.総合検索, vbWide), “ “, “ * OR *") & "*)")
End If
// *が全角の場合
?BuildCriteria("abc" , dbtext,"(*" & Replace (StrConv("a b c", vbwide), " ", "* OR *") &"*)")
(abc="*a*" Or abc="*b*" Or abc="*c*")
// *が半角の場合
?BuildCriteria("abc" , dbtext,"(*" & Replace (StrConv("a b c", vbwide), " ", "* OR *") &"*)")
(abc Like "*a*" Or abc Like "*b*" Or abc Like "*c*")
>りんごさん
hirotonさんが書いてくださったコードを使用したことで、無事解決いたしました!
>りんごさん

テーブル構造をうまく伝えられず申し訳ありません…
以下の画像で伝わりますでしょうか…?
月額マスタには単独主キーが存在しません。
(参考書でテーブルには必ず主キーを設定すべしと書いてあったため、
最初は月額IDフィールドを作成していたのですが、
編集したり、消したりという処理が多く、連番を管理する技術がなかったため
フィールド自体を消してしまいました…)
業務内容は、数年単位のリース契約が主です。契約期間が終わるまで、特定の月に請求するという感じです。
本テーブルという分かりづらい言葉を使ってしまい、申し訳ありません…
新規登録や編集に使うワークテーブルに対し、
データを蓄積するテーブルという意味で「本テーブル」と書きました。
例えば、編集する場合の流れだと
① 帳票形式の検索フォームで、ユーザーが編集したいレコードを選択する
② 選択したレコードの契約番号をフラグテーブルに追加し、同じ契約番号のレコードの編集を防ぐ
③ データを蓄積しているテーブル(本テーブル)から、選択したレコードの契約番号を検索する
④ 検索したレコードを空のワークテーブルに移す
⑤ ワークテーブルがレコードソースの編集フォームを開く
⑥ ユーザーが編集する
・フォーム上の登録ボタンを押した場合
⑦ 編集したレコードの契約番号を本テーブルから検索し、そのレコードを削除する
⑧ ワークテーブルから本テーブルにレコードを移す
⑨ ワークテーブルからレコードを全削除する
⑩ フラグテーブルから編集したレコードを消す
・フォーム上のキャンセルボタンを押した場合/ユーザーが画面を閉じた場合
⑦ ワークテーブルからレコードを全削除する
⑧ フラグテーブルから編集したレコードを消す
>hirotonさん
参考資料をありがとうございます!とても分かりやすかったです!
そもそもRecordsetChoneとRecordset.Cloneの2種類があることをはじめて知りました!
千件単位のデータを処理すると、処理時間にはっきりと差が出るのですね…
これからはこの差を念頭に置いて、どの処理を使用すべきかを考えたいと思いました。
確かにForループを2つに分けた方がごちゃごちゃせず見やすいです!
そして、「実行頻度の高さから処理方法を考える」という視点を持っていなかったので、肝に銘じました…
だいたいひとつくらいしか処理するアイデアが浮かばないので
もっとこうできないかな?と発想を広げられるよう頑張ります!
質問文中のコードが解決済みコードに書き換えられているようですが、なお不具合ありという事でしょうか?
>> 4
まず、テーブルがわからなかったのですが、こんな感じですか?
契約マスタ_ワークテーブル:契約番号,契約名,請求先,顧客ID,…
単独主キー 請求先と顧客の関係性は何かありますか?
月額マスタ_ワークテーブル:月額ID,契約番号,入金月,税抜価格,…
単独主キー
次に、業務がわからなかったのですが、使った分だけお支払い、上限ありのサービスをイメージすれば良いですか?
最後に、本テーブルって何でしょうか?毎回、ワークテーブルのレコード全削除、本テーブルのレコードを全コピー、登録ボタンを押すと、全置換されたりするのでしょうか。
サブレポート方式でいけば比較的簡単にできると思います。
サブレポートコントロールの「印刷時拡張」プロパティを「はい」にしておけば件数に応じて高さが拡張します。
ACCESSのレポートの設計からは逸脱しているのでむずかしいですかね?
速度関係はよくわかりませんが
これをTrueで抽出するとかどうですかね?
余談
カンマ区切りにしてみたら
] & ",","," & [
この辺でめっちゃ混乱しそうでした高速化を考えるなら
フォームの Recordset, RecorsetClone, RecordSet.Clone の違いとは?(hatena chipsさん)
データの操作・更新とともにフォームの表示も一緒にやるかどうかでかなり速度が変わります
If .RecordCount = 0 Then
を使うなら「ループ自体を2パターンにする」ですね新規契約(
.RecordCount = 0
)の割合が高ければ効果的ですが、正直あまり使う形じゃないかなぁと。もう少し条件を変えて「startDate
以上の[入金月]が無ければ」でレコード数チェック(Dcount
)するなら実行頻度もあがるかも?もう一つは、チェックするデータがなくなったら残り全てを登録するという手法もありますが、「チェックしなくていいデータ」(チェックの終えたデータ)をどう表現するかということになるのでちょっと難易度が上がります
まぁ、今回の件だとデータ数はかなり少ないでしょうからチェック手法での差はほとんど出ないと思います。まずは冒頭の「フォームの更新が同時に起こる操作」に注目してみてください
クエリデザインで下記のように設定してください。
これで、パラメータに 1A,3C,5B とカンマ区切りで入力すると該当のタグで抽出できます。
ただし、件数が多いと重くなるので(インデックスが効かないので)、その場合は、
条件入力用のテーブルを作成して、そのテーブルと結合させるクエリにした方がいいでしょう。
条件入力用テーブルをレコードソースとするフォームを作成して、ユーザーにはそこで条件を入力させて、コマンドボタンでクエリを開くようにすればいいでしょう。
>hirotonさん
で、できました…!!希望通りの処理ができました!!
先週からずっーと悩んでいていたのでとてもすっきりしました…!!
本当にありがとうございます…!!思わず目頭が熱くなりました…!!
恐らく初歩的な内容であるにも関わらず、なぜレコードセットをcloseで閉じる必要があるかを
丁寧にご説明いただきありがとうございます。
テーブルを開きっぱなしと言われると、恐ろしさが重々理解できました…
また、コードの訂正に関してもありがとうございます…!
処理が早くなるかなと思って入れたIf .RecordCount = 0 Thenでしたが、
今試してみたら全く変わらない上に可読性も落ちていましたね…ご指摘ありがとうございます!
RecordsetCloneはこのような場合に使えるのですね!
存在自体は薄々知っていましたが、どう使いこなすべきかよくわからないプロパティでしたので
今回の件で大変勉強になりました。
見返してみると、recordsetの中でもう一度recordsetを開いていたことが複雑化する原因のようでした。
これからはシンプルな作りを心がけます!この度は助けていただき本当にありがとうございました…!
心からの感謝を申し上げます…!
>> 2
問題なのはOpenに対するCloseがないのほうです。このくらいの規模であれば
For
の外で宣言して終わったらrs.Close
しますが、超巨大なFor
文とか、一概にループの外でというわけではありません今のコードだと、開いたテーブルが開きっぱなしのまま放っておかれるので何かしらの不具合の原因になってもおかしくありません。今回のエラーの原因かどうかはわかりませんが、同じテーブルを複数開いてそのうちどれかで編集をしようとしているので原因にもなりそうです
あとは、全体の処理として
If .RecordCount = 0 Then
のところがループの中なのに最高でも1回しか実行されない作りなのが気になりますねこのくらいの処理なら
FindFirst
で全数チェックかけても大した問題じゃないのでいっそIf .RecordCount = 0 Then
の制御処理は無くてもいいと思います(実際今のコードはほぼFindFirst
で全数チェックになっています)その他変更点として
これはフォームのレコードセットをVBA上で扱う場合、テーブルを直接開く以外の便利な記述方法の内の一つです。より意味の明確なコードにしてるだけで「やりたいこと」自体は同じです
>りんごさん
ご回答いただきありがとうございます!
現在は年度別のExcelシートで管理している売上表を、
データベースで一貫して閲覧できるようにしたいというのがはじまりです。
ワークテーブルの設計が不味いということでしょうか…?汲み取れず申し訳ありません…
実はクラウドサービスや外注も検討したのですが、契約数も数百件規模ですし予算上厳しく…
また、入社してはじめて取り掛かった業務のひとつですので、できれば完成させたいという気持ちがあります…
開発中の契約管理システムがよくわからないのですが、月次売上サマリみたいなものが職場で求められているのでしょうか?個人的には、テーブル設計が気になります。
趣味で開発するのは、いいと思いますが。仕事で使うのであれば、外注やパッケージを検討するといいと思います。
>hirotonさん
ご指摘いただきありがとうございます!
「T_月額マスタ_編集_仮」は「月額マスタ_ワークテーブル」の書き間違いです。大変失礼いたしました…
無知でお恥ずかしい限りなのですが、このような場合、Forループ外で
recordsetを指定するのが通常なのでしょうか?
エラーが発生しない場合、希望通りの動きができていたので特に気にしておりませんでした…
CurrentDb.OpenRecordsetをForループ外に置いて試してみます!
回答ではありませんが
フォーム:契約マスタ_ワークテーブル
サブフォーム:月額マスタ_ワークテーブル
コード中に出てくる「T_月額マスタ_編集_仮」は全く無関係の別なテーブルですか?
またこのテーブルは閉じられる(
rs.Close
される)ことなくFor
ループの中で何度もCurrentDb.OpenRecordset
されるようですが大丈夫ですか?素早いお返事に感謝いたします!
試したところ理想通りの結果になりました。
勉強になります、ありがとうございました!
SELECT句で、英語・日本語どちらの表記を優先して表示させるかを明示しましょう。
問題点を指摘する前に、
そのクエリを一度削除してもう一度作り直すとどうなりますか?
お世話になります。気づかず申し訳ありません。
SELECT 基本情報.苗字
FROM 基本情報, 基本情報 AS 基本情報_1
WHERE (((基本情報.苗字カナ) Like [Forms]![基本情報1]![抽出用カナ]));
このようになっています。
上記はボタンに適してテキストボックスに表示され、同時にクエリの内容も同じものが表示されていました。
すみません。質問文にはった画像が間違っておりました。正しくはこのようにしたいです

日付は重複は表示しない設定にして、それぞれの日毎に複数の従業員名が表示されます
重複しない日付は空欄になるので、そのあまったスペースに営業目標を表示させたいです
従業員のレコード数が少ない場合は、営業目標2のように、したいです
営業目標を重複なしで日付の下にはって印刷時縮小にしてみましたが、透明なレコードが存在しているという
扱いのようで、従業員のレコードのたびに営業目標分のスペースが空くことになってしまいました
画像を貼り間違えご迷惑をおかけしました
>> 8
明日、来週、来月、来年、いつか振り返った時のためにメモさせて下さい。
AA-02→ AB-011→ 202→ 01じっとみているうちに、思い出してきただろうか?
もともとのオリジナルは、こんな感じでした。
1→ 2 ❌02→ 01 ❌AA-02→ AB-01 ❌きっと、見えない、忘れた、気づかないんだと思う。
アレンジするなら、制約を組み込んでおかないと、おかしくなる.
テクニックを使ってアレンジするのは、大変。パッと見てもわからないし、オリジナルの事を忘れるし、そもそも気づかれない。
分類名の重複は、データの登録前後ともに、集計クエリ・DCountのような定義域集計関数で
誤りをチェックできるため、設計上問題ないという見解です。
私とhirotonさんで質問文の解釈に違いがありそうです。
mabeeさんは、( おそらく )便宜上「フラグ」と表現されましたけど
通常、フラグという言葉は、立つ・折るという
二者択一に近い条件分岐や選択で用いられます。
分類を文字列だけでなく、数値でも表現したい とご希望なのは
並び替えの基準となる「識別子」が必要とお考えになったのではないか
と推測しました。
したがって、私は{ フラグ = 識別子 }と解釈し、識別子を主キーとしています。
( 分類CD, 分類名, フラグ の3列構成にしない理由にもなっています )
また、余談にはなりますけど
分類をキーとした場合は、差別化を図るかどうかは別として
AA, Aa, aA, aa AA それぞれを異なる値として扱えません。
( バイナリ比較が必要になり、カラムも GUI では作成不可 )
( 差別化を図る場合は>> 3の回答自体が無効という盛大なブーメランですね )
数値型フィールドを識別子とすれば、全角半角大文字小文字を
同一の値で扱うことも差別化も容易で、高度なスキルも必要ありません。
常時バイナリ比較になり、経験の浅いDBAでも扱いが容易なことから
値の識別となる大小比較や結合・並び替えは、できる限り文字列より数値で実施するべき
というのが私のポリシーですので
>> 3でのSELECT文も、苦肉の策に近く、ベストプラクティスではない
というのが本音にはなりますね。
用途が不明ですが、完ぺきな対応関係(ZZ=676)なら26進数から10進数への変換なんで
で表せますね
んー、質問の内容に対応する場合と、新規で構築する場合とで話も変わってくるとは思うんですが>> 3の段階の話だと
こういう登録が許容されるテーブルを使うのはどうかと思うんですよね
改めて構築するということだと分類CDがどこまでサロゲートキーとして有効かというのもあって、可能性の話なら
もあり得るんじゃないでしょうか。質問上だと、「フラグ」というカラムにしたいということなので
この形でサロゲートキーな分類CDを使うならわかるんだけどという感じです
hirotonさん、ご質問ありがとうございます。
分類CDをPRIMARY KEYとする理由を3つほど述べますね。
[理由1.]
PRIMARY KEY はソートに利用することが多いから、です。
文字列ですと、全行パディングされていない限りソートが難しくなります。
また、列数が2つのテーブルに
PRIMARY KEY と INDEX 両方を付与するのは
DBの容量増加、UPSERTの遅延、INDEX破損率の上昇
といったリスクを抱えることになりますので
列数の少ないマスタテーブルでは、設計上の理由から回避したいです。
( 経験則上、テーブルの破損 = INDEXの破損 が圧倒的に多いです )
[理由2.]
( あくまで可能性として )分類の再編が発生して
サロゲートキーを利用する一因として
理由2.のように、主キーの体系が変化した場合などの影響が
ナチュラルキーに比べて( サロゲートキーのほうが )少なくなります。
mayuさんに質問なんですが、なぜ
分類CD
がPRIMARY KEY
なんでしょう?文字に対して番号を割り当てるなら
分類名
側をPRIMARY KEY
としたほうがそれっぽいと思うのですがクエリの結果が望んだものにならない問題について
起きている現象から想像すると、クエリの元データとして使うテーブルを余計に読み込んでいる可能性が考えられます
確認すべき点はりんごさんが指摘している通りです
クエリの作りはテキストで確認することができるので、その内容をコピペしてください
確認方法については次のサイトが参考になります
SQLビューでSELECT文を使ったSQLを入力する(Accessの使い方さん)
クエリの内容でコンボボックスに見られるような異常が起きないのであれば、改めてボタンをクリックしたときの動作を確認してください。
「あ」のボタンを押す
→「抽出用カナ」テキストボックスに「[ア-オ]*」と入力される
→クエリ「Q従業員抽出」を開いて内容を確認する
→コンボボックスをクリックしてクエリの内容と同じものが表示されるか確認する
「か」のボタン「さ」のボタンでも同様の確認をする
ひとまずこれでフォームの作り、マクロの設定に異常がないか確認できます
クエリの結果が望んだ内容になっていないことは少し問題がずれるのでこのツリーからは外して改めて回答しなおします
>> 7
こんな感じになるのでしょうか?
親テーブル:英字分類No、英字分類、(英字分類Noで決まる)分類名
単独主キー AAとABなど、頭文字がAで始まるが、特に意味はない。
子テーブル:英字分類No、数字分類、(英字分類Noと数字分類で決まる)分類名
複合主キー AA01とAB01など、下2桁の行番号に、特別な意味はない。
分類フィールドは、導き出せるので、基本的にテーブルに保管しなくていいと思います。
この回答は、お遊びですので、軽く流して下さい。
テーブル1:フィールド1(短いテキスト型)
テーブル2:フィールド1(短いテキスト型)・・・テーブル1をコピペして下さい。
クエリ1(テーブル作成クエリ)
テーブル作成クエリを実行したら、1列目にオートナンバー型のフィールド「カラム」を挿入して下さい。データシートビューに切り替えると、オートナンバリングが実行されます。
英字分類のテーブル
同じ要領で、数字分類(短いテキスト型)のテーブルを作ります。
数字分類のテーブル
※最初のレコード、00は削除して下さい。
最後に、英字分類テーブルをテーブル1、数字分類テーブルをテーブル2に見立て、同じ要領で進めて下さい。
クエリの途中経過
クエリを Like "[ア-オ]" "[カ-コ]"等に書き換えた場合、名字のカタカナが表示されたり、データがない、ということはありませんでした。
さらに、Like [Forms]![基本情報1]![抽出用カナ]に戻して、「抽出用カナ」テキストボックスに[ア-オ]*と入れてクエリを開くと、同じように太田・岡田・安西・石川・伊藤・太田・岡田・安西・石川・伊藤・太田・岡田・安西・石川・伊藤・・・・と繰り返されて出てきます。
もとのテーブルでは、従業員コードを重複なしにしており、実際のテーブルのデータも同姓は一部おりますが、「あ」のボタンを押したときにはフォームには正しい人数で抽出されており、クエリの結果だけが何十回も繰り返されている状況です。
mayuさんとがぶっちゃいました。
かつ、mayuさんの方が完璧かつ詳細な回答でした。
mayuさんの回答を参考にしてください。
下記のようなテーブルを作成します。
テーブル名 T_フラグ
`
`
( 続き ) 理想は
`
sql入力ID COUNTER NOT NULL PRIMARY KEY
, 分類CD INT NOT NULL
, 分類別番号 INT
);
SELECT y.入力ID
, y.分類CD
, y.分類別番号
, x.分類名 & Format$( y.分類別番号, '-00' ) As 分類
FROM t_分類マスタ x
INNER JOIN t_分類トラン y
ON x.分類CD = y.分類CD
ORDER BY 1 ;
ある属性X( 1 )を決めると、他の属性Y( AA )の値が一意に決まる
ある属性X( 2 )を決めると、他の属性Y( AB )の値が一意に決まる
ある属性X( 3 )を決めると、他の属性Y( AC )の値が一意に決まる
...
場合、「 Y は X に関数従属している 」といいます。
Y の種類が少数の場合は
IIf関数やSwitch関数を使って X を定義するのもいいでしょうけど
Y の種類が多い場合や、今後、Y の種類が増えることが想定される場合は
テーブル設計を見直したほうがいいでしょう。
`
sql入力ID COUNTER NOT NULL PRIMARY KEY
, 分類 VARCHAR( 50 ) NOT NULL
);
SELECT y.入力ID
, y.分類
, x.分類CD
FROM t_分類マスタ x
, t_分類トラン y
WHERE x.分類名 = Left$( y.分類, 2 )
ORDER BY 1 ;
hiroton 様
ご教示いただきまして誠にありがとうございます。
ご指摘いただきました「*」を半角にした上で、それ以外の細かい修正も行い以下コードで期待する動作ができるようになりました。お陰さまで本当に助かりました。これからも、今回教えていただきました内容を踏まえまして引き続き勉強させていただきます。
りんご様
返信ありがとうございます。
switch関数で動くことは確認できたのですが、場合分けの数を増やしていったところ、16個ほどの条件で、指揮が複雑すぎます、というエラーが出るようになりました。
他に手法はないのでしょうか?(そもそもaccessはそういうツールではないし、Excelでやればどうにかなるのですが…)
ご教授いただけますと幸いです。
よろしくお願いいたします。
修正後のコードが実際のコード(実際に動かしたコードをコピペしたもの)であるならうまく動いてないんじゃないですか?
抽出条件に使うワイルドカードは半角の記号である必要があります。以下に変数部分を適当に置き換えてイミディエイトウィンドウでテストした結果を提示します
本当に全角の「*」で挟んだ形で完全一致判定をしたいのであれば提示のコードですが多分そうじゃないですよね?