Access (VBA)

Access VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 11 Home : Microsoft 365)
フォームで連番の振り直し
投稿日時: 23/12/17 15:35:28
投稿者: yurappy

お世話になります。
 
レコードの中間に、新しいレコードを追加して連番を振り直したいのですが、よい方法がわからずに悩んでおります。
 
 
テーブルはこの様になっています。
 
 A列 テキスト型 連番用の値を入力
 C列 テキスト型 商品名称を入力
 
 
このテーブルを入力用のクエリを設けて、表形式のフォームで次のように表示してます。
 
A列   B列    C列
 1    1    メロン ← 削除する
 2    2    みかん
 3    3    りんご
 4    4    ぶどう
 5    5    もも
 6    6    バナナ
 100  100    いわし
 101  101    鰺
 104  104    ぶり
 200  200    トマト
 201  201    きゅうり
 
B列は、並び順を揃えるように Val([A列]) の様にしてます。
また、特定のグループから番号を変えてます
 
テキスト型で並べ替えをすると次のようになったので、ネットで調べて Val 関数で数値型の列を設けて、昇順に並べ替えるようにします。
 
 1
 11
 12
 2
 3
 4
 41
 42
 
そして、エクセルのように、行と行の中間に新しい行を追加できないので、ネットで調べていたら、不要なレコードをまず削除して、その後、新しい値を最後の行に入力して、並べたい箇所に入るように小数点を付けて、B列を基準に並び順の修正をしてます。
 
 
 
A列  B列    C列
 1    1    メロン ← 削除する
 2    2    みかん
 3    3    りんご
 4    5    ぶどう
 5    5    もも
 6    6    バナナ
 100   100   いわし
 101   101   鰺
 104   104   ぶり
 200   200 トマト
 201   201   きゅうり
 
 3.5    3.5   すいか  追加して入力
 103    103 マグロ   追加して入力
 
 
この状態で、B列を基準にして並べ替えると次のようになります。
 
 
A列  B列    C列
 2    2    みかん
 3    3    りんご
 3.5    3.5    すいか  追加して入力
 4    4    ぶどう
 5    5    もも
 6    6    バナナ
 100   100   いわし
 101   101   鰺
 103    103 マグロ
 104   104    ぶり
 200   200 トマト
 201   201   きゅうり
 
 
この状態から、手作業ではなりきれなくなったので、次のようにVBAなどで直せないかと悩んでおります。
 
 
A列  B列    C列
 1    1    みかん
 2    2    りんご
 3    3    すいか
 4    4    ぶどう
 5    5    もも
 6    6    バナナ
 100   100   いわし
 101   101   鰺
 103   103 マグロ
 104   104   ぶり
 200  200 トマト
 201  201   きゅうり
 
 
ネットで調べていると、クエリでDcount関数を使ったりする方法などがあったのですが、途中で3桁にしてある箇所も2桁の番号の続きになってしまったりして、どれも思うように動かなくて困ってます。
 
そもそも、小数点でむりやり並べ直す方法が間違っているのかもしれなくて、他の方法を探してますがなかなか見つからず、検索の語句にも悩んでいる次第です。
 
 
 
説明の仕方も、下手でわかり難いかと思いますが、よい方法がございましたらご教授ください。
 
お願いいたします。
 
 
 
 
 

回答
投稿日時: 23/12/17 18:34:59
投稿者: hatena
投稿者のウェブサイトに移動

下記が参考になるでしょう。
 
帳票サブフォームで行番号フィールドの連番を維持する - hatena chips
https://hatenachips.blog.fc2.com/blog-entry-58.html
 
帳票サブフォームで行番号フィールドの連番を維持する その2 - hatena chips
https://hatenachips.blog.fc2.com/blog-entry-61.html
 
帳票サブフォームで行番号フィールドの連番を維持する その3 - hatena chips
https://hatenachips.blog.fc2.com/blog-entry-294.html

投稿日時: 23/12/17 19:25:15
投稿者: yurappy

hatena様
 
お世話になります。
ご親切にすみません。
 
参考ページ、ありがとうございます。
試してみてから、また連絡させていただきます。
 
VBAなど、まったく初心者なので変な質問をするかもしれませんが、お願いいたします。
 
 
よろしくお願いいたします。

回答
投稿日時: 23/12/18 09:38:55
投稿者: hatena
投稿者のウェブサイトに移動

質問の内容から判断するに、VBA以前にデータベースの基本についての学習が必要かなとの印象です。
エクセルの表計算ソフトとAccessのデータベースソフトではまったく別物ですので、エクセルでの知見はとりあえず捨てて、取り組んだ方がいいでしょう。
 
まずフィールド名をA列、B列・・・とせずにフィールドの内容をあらわす名前にすべきです。
質問用の仮の名前ならいいのですが。
 
例えば、下記のような感じ。
 
テーブル名 商品マスター
 

フィールド データ型
商品ID   数値型 主キー
商品種別  数値型 外部キー
商品名   テキスト型
連番    数値型

 
テーブル名 商品種別マスター
 
フィールド データ型
商品種別ID 数値型 主キー
商品種別名 テキスト型

データベースのテーブルには主キーは必須です。
また、商品マスターと商品種別マスターに分割したのは「正規化」という設計手法です。
これもデータベース設計の基本事項です。
 
商品マスター
商品ID  商品種別    商品名  連番
1            1      メロン    1
2            1      みかん    2
3            1      りんご    3
4            1      ぶどう    4
5            1      もも      5
6            1      バナナ    6
7            2      いわし    1
8            2      鰺        2
9            2      ぶり      3
10           3      トマト    1
11           3      きゅうり  2

 
商品種別マスター
商品種別ID  商品種別名
1           果物
2           魚介類
3           野菜

 
上記は質問のデータから想像した1例ですのでこれを参考に実態に合わせたものにしてください。

投稿日時: 23/12/20 09:02:04
投稿者: yurappy

hatena 様
 
お世話になります。
ご親切にアドバイスいたいだいてありがとうございます。
 
お教えいただいたページとアドバイスを参考にやり直しておるのですが、
受注明細のフォームの部分だけで行おうと、1つのテーブルでいろいろ試しているのですが
エラーになってしまいます。
 
アドバイスいただいた
 

引用:

 
テーブル名 商品マスター
   
テーブル名 商品種別マスター

 
のように、きちんと2つのテーブルに別けたり、メインテーブルの受注伝票 と サブテーブルンの受注明細 のように、別けないとできないものでしょうか。
 
ほとんど、VBAなど知識が無いに等しく、アクセスもまだまだ初心なので、トンチンカンな質問ですみません。
 
上と下に移動する動作は、なんとかできたのですが一番行いたい1行追加する動作と削除ができなくて、つまづいてます。
 
厄介をおかけして申し訳ございませんが、アドバイスいただければうれしいです。
 
申し訳ございません
 
よろしくお願いいたします。

回答
投稿日時: 23/12/20 15:30:22
投稿者: Suzu

VBA 初心者であれば、
VBA でどうにか する と言う考え方は 避けた方が良いですよ。
 
疑問点についても、ご自身でどうにかする方法も無いですよね。
 
 
一般機能での方法を。。。
 
テーブル構造
 ID:オートナンバー(主キー)
 Gr:数値型
 Order:単精度浮遊小数点
 
の様にして
 
 
ID  Gr  Order  Name
----------------------------
 1  1   1   メロン
 2  1   2   みかん
 3  1   3   りんご
 4  1   4   ぶどう
 5  1   5   もも
 6  1   6   バナナ
 7  2   1   いわし
 8  2   2   鰺
 9  2   3.5  ぶり
 10  3   1   トマト
 11  3   2   きゅうり
 
こんな構造のテーブルにすれば
 
DCount関数の方法を応用し
 
クエリにて
Order2 : DCount("*","テーブル名","Gr="& [テーブル名].[Gr] & " And Order<=" & [テーブル名].[Order])
の様な演算フィールドを用意すれば
下記の様にはできます。
 
ID  Gr  Order  Order2  Name
-------------------------------------
 1  1   1    1   メロン
 2  1   2    2   みかん
 3  1   3    3   りんご
 4  1   4    4   ぶどう
 5  1   5    5   もも
 6  1   6    6   バナナ
 7  2   1    7   いわし
 8  2   2    8   鰺
 9  2   3.5   9   ぶり
 10  3   1    10   トマト
 11  3   2    11   きゅうり
 
ただし、レコード数が多くなれば、表示までに時間がかかります。
 
 
今のテーブル
 ID    Name
  1    メロン
  2    みかん
  3    りんご
  4    ぶどう
  5    もも
  6    バナナ
100    いわし
101    鰺
104    ぶり
200    トマト
201    きゅうり
 
のまま
 
クエリで
 
Gr : [ID] \ 100
Order : [ID]-([ID]\100)*100
 
とすれば、
 
ID  Gr  Order  Name
----------------------------
 1  0   1   メロン
 2  0   2   みかん
 3  0   3   りんご
 4  0   4   ぶどう
 5  0   5   もも
 6  0   6   バナナ
100  1   1   いわし
101  1   2   鰺
104  1   3.5  ぶり
200  2   1   トマト
201  2   2   きゅうり
 
を得る事ができますので、
 
先の DCountの方法と組み合わせれば 出す事はできるでしょうが、更に遅くなるでしょうね。

投稿日時: 23/12/20 16:50:44
投稿者: yurappy

Suzu様
 
お世話になります。
ご親切に、ありがとうございます。
 
丁寧に説明していただいてありがとうございます。
 
試してみて、またご報告いたします。
 
ご報告に、勉強しながらなので少し日数がかかるかと思いますが、お許しください。
 
よろしくお願いいたします。

回答
投稿日時: 23/12/20 17:10:23
投稿者: MMYS

そもそもですが、リレーショナルデータベースは、格納順という考え方がありません。レコードの物理的な順番は並んでおりません。レコードつまり実データがどの位置に格納されているかは、インデックスに記録されています。インデックスはソートされており、インデックスから実データにたどり着く仕組みです。
 
テーブルには主キーが必要ですが、主キーはインデックスを使用しています。データを取り出すとき、主キーのインデックスから実データの格納位置をを取得、実データを取り出す仕組みです。このことから、主キーは重要でレコード位置を特定するにのに必要なのです。
 
 
つまり、データの並び順は、保証されれていないので、取り出す順番、つまり並び順は呼び出し時に指定します。
並び順はフィールドで指定しますが、このとき、テーブルにインデックスがあれば、インデックスを使用します。インデックスはソートされているので、高速にアクセス出来ます。インデックスがないフィールドは、その時点でソートを行いますので遅くなります。
 
 

回答
投稿日時: 23/12/21 11:23:50
投稿者: hatena
投稿者のウェブサイトに移動

yurappy さんの引用:

お教えいただいたページとアドバイスを参考にやり直しておるのですが、
受注明細のフォームの部分だけで行おうと、1つのテーブルでいろいろ試しているのですが
エラーになってしまいます。

現状のテーブル構成はどうなっているのですか。
受注明細の入力フォームというのは初めて出てきた情報ですが、
受注明細のデータを扱うのなら、
すくなくもと今出ているフィールド以外に取引先、受注日のフィールドは最低限必要です。
 
また、どのような仕様で入力するのでしょうか。
通常は、取引先毎、受注日毎に入力すると思いますが、その部分の設計はどうしてますか。
 
Accessの一般的な設計ではメイン/サブフォーム形式にしますが、そのようにしてますか。
 
データペースでは、テーブル設計が正しくできているかが大前提です。それがなしに枝葉の部分(今回の質問のデータの並び替えとか)の設計の話をしても意味がないです。
 
現状のテーブル設計、それをもとにしたフォーム設計がどうなっているか、提示してください。
 
yurappy さんの引用:

のように、きちんと2つのテーブルに別けたり、メインテーブルの受注伝票 と サブテーブルンの受注明細 のように、別けないとできないものでしょうか。

はい、できません。
それが、できていないと今回の質問以前にデータベースとして使えないものになります。

投稿日時: 23/12/25 11:16:03
投稿者: yurappy

おはようございます。
 
MMYSさま
hatenaさま
 
お世話になります。
 
ご親切に、ありがとうございます。
お返事が遅くなって、申し訳ございません。
 
現状ですが、アドバイスを参考に最初から見直し(設計?)をしはじめました。
 
毎月の集計で使うので、メインテーブルのレコードを月単位で1レコード増やすようにして、サブテーブル(サブフォーム)は品名や単価と入金などを入力する様にしようかと思ってます。
 
ただ、連番がAのグループは1から始まっていつも50番くらいまで、Bのグループは100番から始まるのでどの様にしようかと思ったり、毎月 サブテーブル(サブフォーム)は、ほぼ同じ様な内容で数件削除したり追加になる位なので、できたら前月の値を引き継げないかと悩んだりしてます。
 
他に、テーブルを複製して別名しようかと悩んだり、私なりに考えましたが、やはりhatenaさまが教えて下さった、ブログのような動作が思っていた通りの動きなので、どうやって私の業務内容に合わせておき変えようかと、思案してます。
 
後2日で、年末の業務が終了になるので、お正月休みに集中して考えて作り直しをする予定です。
 
本当に、ご親切にありがとうございます。
 
自分の力以上の事をしようとして、反省してますが頑張って乗り越えたいと思ってます。
 
またトンチンカンな質問をするかと思いますが、お許しください。
 
他の仕事で、ご返信が遅くなってほんとうにすみませんでした。
 
感謝いたします。
 
よろしくお願い申し上げます。

回答
投稿日時: 23/12/26 11:47:51
投稿者: hatena
投稿者のウェブサイトに移動

まずはテーブル設計から始めるのが先決です。
それが決まらないと、入力フォームや出力レポートなどの設計が決まりません。
 
受注データの管理なら、最初の質問のデータだけでは無理です。
少なくとも、だれ(顧客)がいつ(受注日)発注したのかという情報も必要になりますよね。
 
どのような業界のはなしなのが不明なので(質問のデータは実際のものではないですよね)、一般的な例でいくと下記のようなテーブルが必要になります。
 
マスターテーブル系
 商品マスター
 商品種別マスター
 顧客マスター
 
トランザクションテーブル
 受注伝票テーブル
 受注明細テーブル
 
あと、
入力に関しては、
入力フォーム
 通常メイン/サブフォーム形式になります。
 
出力、集計に関しては、
 集計クエリ
 集計レポート
 請求書レポート
 
 

yurappy さんの引用:

毎月の集計で使うので、メインテーブルのレコードを月単位で1レコード増やすようにして、サブテーブル(サブフォーム)は品名や単価と入金などを入力する様にしようかと思ってます。

 
入力は月単位でするのですか。
 
通常の業務フローとしては、例えば、
 
顧客から発注を受ける。
受注データを入力する。
月ごとにまとめて請求書を発行する。
あるいは月ごとの集計表を出力する。
 
というような感じになると思いますが、そうではないですか。
 
月にまとめて入力するとしても、紙の受注伝票をみて入力することになりませんか。
 
出力時、集計時に月単位にまとめるのは、クエリやメイン/サブフォーム形式、レポートのグループ化の機能で簡単にできます。
月単位で入力するという入力フォーム設計は通常はしません。
入力作業と出力作業は別に考えます。
 
あくまで一般論ですので、そちらの業務フローがどのようなものかによりますが。
この業務フローに合わせて、入力フォームや出力系の設計をすることになります。
 
yurappy さんの引用:

ただ、連番がAのグループは1から始まっていつも50番くらいまで、Bのグループは100番から始まるのでどの様にしようかと思ったり、

 
連番にグループ情報を含めるという設計はデータベース的にあまり適切ではないと思います。
下二桁が連番、百の位以上がグループという設計だと、グループ内の件数が100件を超えたら破綻します。
 
投稿日時: 23/12/18 09:38:55の回答で提示した商品マスターテーブルのようにグループ(商品種別)と連番は分けた方がいいです。
 
yurappy さんの引用:

毎月 サブテーブル(サブフォーム)は、ほぼ同じ様な内容で数件削除したり追加になる位なので、できたら前月の値を引き継げないかと悩んだりしてます。

これは、追加クエリで簡単に実現可能ですね。
 
ただ、まずはテーブル設計が先です。
 
連番の振りなおしについても、テーブル設計が決まらなければ、できません。
場合によっては、連番の振りなおし、レコードの途中挿入というのが本当に必要なことなのかも検討余地があります。

投稿日時: 23/12/27 13:58:00
投稿者: yurappy

hatena様
 
お世話になります。
ご親切に、ありがとうございます。
 
年末の忙しないときにも関わらず、すみません。
 
いろいろと、詳しく丁寧にありがとうございます。
 
私のレベルでは、半分わかるようなわからないような感じですが、お正月の連休にじっくりと取り組んでみます。
 
 
ほんとうに、ご親切にありがとうございます。
感謝しております。
 
よろしくお願い申し上げます。

投稿日時: 24/01/08 15:07:04
投稿者: yurappy

hatenaさま
MMYSさま
Suzuさま
 
たいへんお世話になって、ありがとうございます。
とくに、hatenaさまにはご丁寧に説明などまでしていただいて、ありがとうございます。
 
遅くなりましたが、何とか思うような動きができてまいりました。
 

引用:
下記が参考になるでしょう。
  
帳票サブフォームで行番号フィールドの連番を維持する - hatena chips
https://hatenachips.blog.fc2.com/blog-entry-58.html

 
こちらのサンプルファイルをダウンロードさせていただいて、何回も同じ様に作って必用なフィールドと不要なフィールド、また追加したいフィールドなど失敗を繰り返しながら作り直していたら動くようになってきました。
 
スマートなやり方では無いと思いますが、100番台と200番台はテーブルとサブフォームを増やして、入力後にクエリで1つにまとめる様にして対処しました。
 
各ボタンのVBAの内容の詳細は、まだ完全に理解できていないのですが、サブフォームを増やすときに何度もエラーになって、たいへん勉強になりました。
 
 
 
引用:
ただ、連番がAのグループは1から始まっていつも50番くらいまで、Bのグループは100番から始まるのでどの様にしようかと思ったり、毎月 サブテーブル(サブフォーム)は、ほぼ同じ様な内容で数件削除したり追加になる位なので、できたら前月の値を引き継げないかと悩んだりしてます。

 
100番までと100番台、200番台をまとめたテーブルを、複製して保存する様にしてhatenaさまのサンプルフォームを毎月使えるようにしたりなど、拙い動作でお恥ずかしいながらもなんとかできました。
 
ちょうど、お正月休みでこれだけに集中してできたので、なんとかできたと思いますがとても難しく、まだまだ勉強しなければと反省ばかりでした。
 
本当にありがとうございました。
 
とても、勉強になりましたし助かりました
 
ありがとうございました。
 
よろしくお願い申し上げます。

回答
投稿日時: 24/01/08 16:34:58
投稿者: Suzu

引用:
ただ、連番がAのグループは1から始まっていつも50番くらいまで、Bのグループは100番から始まるのでどの様にしよう

 
A も B も 同じサブフォームで表示しようとされていませんか?
 
親テーブル
 ID   : 主キー
 顧客ID  
 種別  : A/B 等入力フィールド
  :
 
子テーブル
 主キー
 ID : 親テーブル ID とリンク
 種別    : 親テーブル 種別とリンク
 行番号
 品名
  :
 
の様な構造だとして
 
リンク親/子フィールド ID;種別 の様な複数フィールドでリンク親子を設定すれば良いです。
 
 
 
引用:
毎月 サブテーブル(サブフォーム)は、ほぼ同じ様な内容で数件削除したり追加になる位なので、できたら前月の値を引き継げないかと悩んだりしてます。

 
上記の説明では詳細が不明ですが
 
おおまかな流れとして
1. 同じ構造の ワークテーブルを用意
2. 前月のデータを 追加クエリを使い ワークテーブルに追加
3. ワークテーブルの 年月の 判定フィールドのデータを 更新クエリを使い 当月に書き換え
4. (必要なら、親テーブル側に 当月の親データを追加)
5. ワークテーブルのデータを 元テーブルに追加
6. ワークテーブルのデータを削除
で行けませんか?

回答
投稿日時: 24/01/08 16:43:00
投稿者: Suzu

引用:
A も B も 同じサブフォームで表示しようとされていませんか?

 
不明瞭な書き方でした。
 
 
 ID     : 1
 年月    : 2024年1月
 顧客 ID  : 101
 A/B 区別  : A
+-----------------------------------------------+
| A/B区分  行番号  ID   商品     |
|  A     1    1   いちご    |
|  A     2    2   りんご    |
+-----------------------------------------------+
の様に、
詳細フォーム側 で A 群のみを表示させる
////////////////////////////////////////////////////////////////////////
それと、
 ID     : 2
 年月    : 2024年1月
 顧客 ID  : 101
 A/B 区別  : B
+-----------------------------------------------+
| A/B区分  行番号  ID   商品     |
|  B     1   101   みかん    |
|  B     2   102   かき     |
+-----------------------------------------------+
 
詳細フォーム側 で B 群のみを表示させる
 
と 分ける方が良いと思います。
(親テーブル の 101 が A の分と、B の分 2件必要になります)

投稿日時: 24/01/09 10:53:43
投稿者: yurappy

Suzu様
 
お世話になります。
ご親切にありがとうございます。
 
 

引用:
A も B も 同じサブフォームで表示しようとされていませんか?

 
フォームの名称、テーブルの名称、フィールドの名称、テキストボックスの名称など、
すべて枝番を付けたり、異なる名称にするなどして作ってみました。
 
 
 
 
引用:
毎月 サブテーブル(サブフォーム)は、ほぼ同じ様な内容で数件削除したり追加になる位なので、できたら前月の値を引き継げないかと悩んだりしてます。

 
 
Aのサブフォームの値とB移行のサブフォームの値を追加クエリで、異なるテーブルでまとめるようにして、
そのテーブルを複製して、毎月保存してして行くようにしました。
 
素人考えなので、何回もクエリをしたりテーブルが増えてしまい、きれいな振る舞いや構成とかではないですが
何とか利用できるレベルになりました。
 
ほんとうに、ご親切にありがとうございます。
 
エクセルでは、簡単にできていたのでアクセスでもと思って簡単に考えてしまい、
みな様にご面倒やお手数をかけてすみませんでした。
 
とても勉強になって、助かりました。
 
ご親切にありがとうございました。
 
また困った時には、助けてください。
 
ありがとうございました。