アクセスVBA超初級

依頼伝票を、アクセスのデータベースで、１枚ごとに入力しています。
伝票には、一意の伝票Noが振ってあります。

上段には、おおまかな、請求先や日付や、金額の総合計が入力してあり、
その下に、サブフォームとして、細かい項目分類ごとの、品目や数量、単価、小計などを
入力するようにしています。（分析用クエリ、というタイトルで）
その項目分類ごとに、１行、項目が増えるごとに、項目番号として、行の始めに、
１、２、３、４、５、と、順次増えるように、自動的に、連番を振りたいのです。


Private Sub Form_BeforeInsert(Cancel As Integer)

If DCount("*", "Q_分析用 クエリ") = 0 Then
項目番号 = 1
Else
項目番号 = DMax("項目番号", "Q_分析用 クエリ") + 1

End If

End Sub

それで、上記のようなVBEを考えたのですが、これですと、各１枚ごとの伝票に
連番が振ることができずに、全ての伝票を通しての、連番になってしまいます。
１枚ごとに、また、１から連番を振るようにするには、どうすればいいでしょうか。

わかりにくい質問で申し訳ありませんが、よろしくお願いします。

No.1 ベストアンサー 回答者： hakkiriitte 回答日時： 2007/09/12 19:14

Private Sub Form_BeforeInsert(Cancel As Integer)

こんばんは。

DCount や DMax には、３番目のパラメータがあります。
テーブルやクエリから特定の希望するレコードだけを取り出す条件式です。（SQLのWhere句のWhereを除いた式になります。）

具体的には以下のようにしたらよいでしょう。
「Q_分析用クエリ」に、伝票No.用のフィールド「伝票No.」があるとして、フォーム上にある「伝票No.」というテキストボックスに関連付けられているとします。、

If DCount("*", "Q_分析用 クエリ","[伝票No.]=" & [伝票No.]) = 0 Then
項目番号 = 1
Else
項目番号 = DMax("項目番号", "Q_分析用 クエリ","[伝票No.]=" & [伝票No.]) + 1

End If

この回答への補足

さっそくにどうもありがとうございます。

質問の内容だけを持ち帰り、実際のファイルは職場に置いてきてしまってたため、明日、それでやってみようと思います。
手順がおかしくなってしまい、申し訳ありません。

伝票ごとに依頼ナンバーを振っていて、主テーブル、サブのテーブルにも同じナンバーで関連づけてますので、それでいけるように思います。

補足日時：2007/09/12 21:27

この回答へのお礼

説明の足りない質問で、大変お騒がせしてしまい、本当に申し訳ありませんでした。そして、お答えいただき、どうもありがとうございました。

今日、仕事に行き、上の回答を参考にして、修正し、希望通りにすることができました。

If DCount("*", "Q_分析用 クエリ","[伝票No.]='" & Forms!F_依頼書[伝票No.]&"'") = 0 Then
項目番号 = 1
Else
項目番号 = DMax("項目番号", "Q_分析用 クエリ","[伝票No.]='" & Forms!F_依頼書[伝票No.]&"'") + 1
End If
End Sub

と、して、
Private Sub Form_Click()
の後にも、同じように書いてみました。
伝票ごとに、自動的に、１、２と行番号が連番として振れるようになりました。

インプレス社の、「できるアクセス2000」ビジネス活用編の、
「連番の見積もり番号を自動的に入力するには」の項で、最初の、書き方を真似てやったのですが、３つ目の、条件を設定する部分が、よくわかっていませんでした。

どうも、ご迷惑をかけ、またお世話になり、ありがとうございました。

お礼日時：2007/09/13 18:26

No.10 回答者： CHRONOS_0 回答日時： 2007/09/13 18:05

>データベースの初心者であるとどうして言えるのか、

たくさんの初心者の質問を読んできた経験からです
質問の中身もそうですし、用語の使い方、
テーブルにはほとんど触れずインタフェースであるフォームの説明がやけに詳しい
など初心者の質問の特徴ですね

>主キーであるなどとはどこにも書いていない
確かに書かれていませんが、それは話がそこにいっていないだけで
親子関係の子テーブルで親の主キーと親の主キーに対してユニークな
例えば行ナンバーといったものは
親の主キー+行ナンバーで子テーブルの主キーとすべきものです

この回答へのお礼

今年の１月ごろより、本などを読みながら、勉強を始めたばかりで、質問のしかたや内容など、稚拙で、ご迷惑をおかけしました。
また、たくさんの参考意見をありがとうございました。

また、データベースなど、きちんと学習しなければと思います。

お礼日時：2007/09/13 18:41

No.9 回答者： hakkiriitte 回答日時： 2007/09/13 16:32

そもそも、他人のスレッドで言い争いをしてはいけないのですが、naka66さんが、データベースの初心者であるとどうして言えるのか、不思議です。

VBスクリプトが不得手なだけで、データベースについては十分に理解してらっしゃると思います。
さらに、「正規化」を軽視する発言をしたつもりもありません。
ここで「正規化」を語るのは、そぐわないのでは、と言っているだけです。
「正規化」はデータベースを構築してきた先人の知恵です。
プログラムにもスクリプトの記述にもそれぞれセオリーがあるのは、そういった先人の知恵なのであって、それらの知恵を学ばずに立ち向かっても、過去の多くの人が躓いた失敗を繰り返すだけです。

そして、リナンバーについては、伝票につける行番号であって主キーであるなどとはどこにも書いていないことを申し添えておきます。
そもそも、私が書いた手順では、主キーの変更はできません。

この回答へのお礼

どうもありがとうございました。
正規化等、基本情報の勉強をしたとき、少し用語の意味を知っただけで、よく勉強していませんでした。

連番にした行番号は、主キーではありません。
ただ、１枚の伝票に、いくつの項目があるのか、その行数をカウントするために、手で１、２、と順番に入力すればいいのを、その手間を省くために、今回の質問になりました。

解決しまして、ありがとうございました。

お礼日時：2007/09/13 18:45

No.8 回答者： CHRONOS_0 回答日時： 2007/09/13 13:12

>市場に出回っているパッケージ商品には、その「正規化規則」とやらを

>無視したデータ構造を持っているものがあります。
初心者の人に対してこのようなことを言って
[正規化]をあたかも無視していいようなものであるかのような印象を植え付ける発言は無責任なのでは

確かに大きなシステムではどこかで正規化を外した部分を持つものが多いですが
これは正規化を無視や軽視しているわけではなく
正規化について十分に熟知している設計者が正規化を外すデメリットと
外したことにより得られるメリットを比較して意図的に正規化を外しているのです
さらに外すことによるデメリットに対する対策も組み込まれています

初心の方はまず正規化について理解し
それに則ったテーブル設計をするようにしましょう
データベースの基本はテーブルです
ここをおろそかにして先に進めても要らぬ大回りをするだけです

>「削除時のリナンバー」
こんなことは絶対にやってはいけません
この番号は伝票Noと組み合わせて詳細テーブルの主キーになるものです
主キーの振りなおしなんてのはデータベースの改造時にしかするべきものではありません

No.7 回答者： hakkiriitte 回答日時： 2007/09/13 11:17

ANo1.、ANo.3 です。

ちょっと理解に苦しみますが、質問者の方の意図するところと外れた回答が多い気がします。
データベースについての解説ならば、相応の書籍やサイトがあるので、そちらを紹介すればよいのであって、ここで説明するのは、話がふくらみすぎる気がします。
私の全くの個人的な意見なので、無視してくださって結構ですが、「正規化規則」というと、絶対守らなければならないような気がしますが、市場に出回っているパッケージ商品には、その「正規化規則」とやらを無視したデータ構造を持っているものがあります。
特に移行時やメンテナンスの際などに効率よく運用するために、推進されている規格ぐらいにとらえればよいと思います。

ああ、そうそう。データ構造ですが、DMaxなどを使う以上、１本のテーブルの、同じフィールドに集計する数値が収まっていると考えるのがふつうです。違いますか？

また、「主フォームに総計を表示」、「削除時のリナンバー」というのは、ANo.4さんがおっしゃるほど難しい作業ではないと思います。
比較的よくつかうテクニックといえます。
「主フォームに総計を表示」は、
１作業中のレコードを保存。
２元のテーブルから、合計値を計算。
３所定のテキストボックスに表示。
「削除時のリナンバー」
１リナンバーしたいレコードだけを抽出してナンバー順に並べるクエリを作製。
２念のため、旧ナンバーを格納するフィールドを追加。
３旧ナンバーを２のフィールドに格納。
４先頭から読み取って、番号を振りなおす。
５旧ナンバーフィールドは邪魔だったら、作業後、消去。

そもそも、ANo.6さんもおっしゃっているように、削除時のリナンバー自体それほど必要とも思えません。
どうしても直したいのならば、手作業で直したほうが早いかもしれません。

以上、自分で「関係ない議論」といいながら、蛇足的で申し訳ありません。

この回答へのお礼

再度やり直して、
下に書きました、
Private　Sub　Form_Click()　の部分
は、不要でした。

hakkiriitteさんのアドバイスどおりで、お世話になりました。

お礼日時：2007/09/13 20:24

No.6 回答者： CHRONOS_0 回答日時： 2007/09/12 23:56

>ANo.1です。

>私には、テーブルの構造がすでにANo.2さんの回答のようになっているように思えるのですが

>細かい項目分類ごとの、品目や数量、単価、小計などを入力するようにしています。
これでそう読めますか?
伝票Noごとに番号を振るというのならいいのですけどね
小計を取るというのは同じ項目番号で複数の品目があるということなのでは

>行１、行２、行３と入力していって行２を削除することも考慮するべきでしょう。
これはデータベース的には何の問題もありません
伝票Noと行番号の組み合わせがユニークであればいいので
連続性が失われても何の問題もありません

No.5 回答者： noname#140971 回答日時： 2007/09/12 20:59

補足：

No1さんの回答は質問の正答だと思います。
ただ、中途削除にはどのように・・・と思っただけです。

この回答へのお礼

連番になった項目を削除すると、そこは飛び番になりました。
ただ、他の方が記入した伝票を見ながら、私が入力して、その後ファイリングしていますので、削除するようなことは、ほぼ起きないような感じです。

いろいろ考えてくださり、ご回答いただき、どうもありがとうございました。

お礼日時：2007/09/13 18:38

No.4 回答者： noname#140971 回答日時： 2007/09/12 20:42

私は、主表と従表との関係で主表に総合計を設けるのが是が非かの論議には中立的です。

が、少なくとも主フォームにそれを表示するのは至難ですよ。
ベリファイのため、後段の処理の手抜きのために設ける位の考えがお勧めです。

さて、肝心の質問の回答ですが、これはチト考えられておられるやり方には疑義ありです。
まず、行１、行２、行３と入力していって行２を削除することも考慮するべきでしょう。
その場合は、行３を行２に変更しなきゃならんです。
つまり、DempyoRenumber(テーブル名,ID)なる関数を作成することがテーマになるかと思います。
この場合、SQL文を発行するということも考えられます。
この場合、レコードセットを呼び出し更新するということも考えられます。
DMAX関数という発想では、ユーザのオペレーション頼りになるかと思います。

この回答へのお礼

どうもありがとうございます。

主テーブルには、総合計は、手で金額を入力しただけのものが入れてあり、サブで、項目ごとに小計を出して、最後にその小計の合計を計算して主テーブルに表示するようにしているので、手で入力した総合計と、計算で出た総合計の数字があっていれば、まちがいないと確認できるようにしています。

途中で行を削除するところまで、考えていませんでした。

お礼日時：2007/09/12 21:54

No.3 回答者： hakkiriitte 回答日時： 2007/09/12 19:47

ANo.1です。

私には、テーブルの構造がすでにANo.2さんの回答のようになっているように思えるのですが。
naka66さんのご質問にデータ構造の説明が一切されていないので、想像で言っているだけですが。
DMaxやDCountにCriteriaを指定するのも、テーブル構造がそのようになっているという前提です。
naka66さんのご要望が、伝票No.ごとの連番をつけたいということであると判断して回答しました。単純に全ての連番にするのだったら、フィールドのデータ型をオートナンバーにしてやればいいだけですから。

この回答へのお礼

＞naka66さんのご要望が、伝票No.ごとの連番をつけたいということであると判断して回答しました。

その通りです。
説明不足で申し訳ありません。

仕事の手順として、まず、依頼先に対して、総合計の金額の請求を先に済ませて、その後、こちら側の分析資料として（どういう内訳の依頼であったか、とか）サブフォームに、項目を入力していきます。
ですので、上段の主テーブルの分は、先に入力した分が既に表示されています。
それで、下段のサブフォームに項目を入力していくのですが、（各１枚の伝票ごとに、）項目の連番を自動的に振れるようにしたいのです。

お礼日時：2007/09/12 21:40

No.2 回答者： CHRONOS_0 回答日時： 2007/09/12 19:30

作る(考える)順序が変ですよ

データベースではまずテーブルを設計し
それからユーザーインタフェースを考えます

このケースでは伝票テーブルをどのように作るかをまず考えてから
入力インタフェース(フォーム)を考えます

それともう一点
１レコード内に複数の品目、数量、単価、小計を入れる
などというのは正規化規則にもろ違反した構造です

例としては
[伝票見出し](伝票No、請求先、日付、・・・)
[伝票詳細](伝票No、伝票行No、項目番号、品目、数量、単価）
[項目マスタ](項目番号、項目名、・・・)
こんなところでしょうか
ここを変更せずに先に進めても早晩行き詰ります

考え直してから再質問されたほうがいいですね