ACCESS2000でコードごとの連番を振るには

規約違反で削除されましたので再質問させていただきます。
・access2000で部門コードごとに連番を振りたいが、その際「単価」が入力されていないものは省きたい。
アドバイスを受けて以下のコードを作りましたが、連番を付与したあとで単価を削除しても連番が消えません。その次の数値と同じ連番が入ったままになっています。
文字数の関係で関係のありそうな後半だけしか書くことができませんが、よろしくお願いします。

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(sql)
Set fldCode = rst.Fields("部門コード")
Set fldNo = rst.Fields("番号")
If rst.BOF = False Then
rst.MoveLast
rst.MoveFirst
count = 1
code = "00"
Do Until rst.EOF
rst.Edit
If fldCode <> code Then
Select Case fldCode
Case "01": count = 21
Case "02": count = 31
Case Else: count = 1
End Select
code = fldCode
End If
If rst.Fields("単価") > 0 Then
fldNo = count
rst.Update
count = count + 1
End If
rst.MoveNext
Loop

End If
dbs.Close

cmd連番_Click_Err_Exit:
Exit Sub
cmd連番_Click_Err:
MsgBox Error$
Resume cmd連番_Click_Err_Exit

End Sub

No.4 ベストアンサー 回答者： tsukasa-12r 回答日時： 2004/02/23 15:03

＞連番が動かない方法はありますでしょうか？

あります。ヒントだけさしあげます。
・まず、tbl商品を元にしたレコードセットをもう一つ用意します。仮にこれを rst2 とします。
・次に、
＞　　　　fldNo = count
＞　　　　count = count + 1
の前に
(1) (部門コード=rstのカレントレコードの部門コード)、かつ、(番号=count)、かつ、(単価≠0)
の条件で rst2 を検索します。
(2-1) rst2 に見つからない場合
その count を採用して、rst を Update
(2-2) rst2 に見つかった場合
count = count + 1 をしてから、増えた count を使用して、(1) と同じ検索を行う。以下、これを繰り返す。

というふうにします。
この方法だと、欠番もできません。（ただし、実行スピードは遅いと思います。欠番ができてもよいので実行スピードを上げたい場合、同じ部門コードでの番号の最大値を取得して、それを +1 していけばいいでしょう。ただし、その場合、部門コードごとの開始番号（部門01→21、部門02→31・・・）が割り当て済みでなければなりません。）

No.6 回答者： papayuka 回答日時： 2004/02/23 22:42

欠番でも構わないなら、消す必要もないように思いますが、、

提示された元のコードも番号は全部振り直しですしね。

まぁ解決したようにも思いますが、折角書いたので載せます。

　1.採番済み最終番号を取得
　2.単価のある未採番のみに採番
　3.単価のない採番済みを消す

の処理にしたつもりです。
Accessは殆ど経験が無いため非常に見苦しいです。（^^；

試すならテスト環境で。

Sub Test()
Dim dbs As Database, rst As Recordset, sql As String
Dim cnt, cd, i As Integer

cd = Array("01", "02")
For i = 0 To 1
　sql = "SELECT 部門コード, 番号 FROM tbl商品 " & _
　　　　"WHERE (((部門コード) = """ & cd(i) & """))" & _
　　　　"ORDER BY 番号 DESC;"
　Set dbs = CurrentDb: Set rst = dbs.OpenRecordset(sql)
　If rst.RecordCount = 0 Then
　　 cnt = Null
　Else
　　 rst.MoveFirst
　　 If rst.Fields("番号") = Null Then
　　　　cnt = Null
　　 Else
　　　　cnt = rst.Fields("番号")
　　 End If
　End If
　If IsNumeric(cnt) Then
　　 cnt = cnt + 1
　Else
　　 If i = 0 Then
　　　　cnt = 21
　　 Else
　　　　cnt = 31
　　 End If
　End If

　sql = "SELECT 部門コード, 商品名, 単価, 番号 " & _
　　"FROM tbl商品 WHERE (((部門コード) = """ & cd(i) & """)" & _
　　" And ((単価) > 0) And (((番号) = 0) Or ((番号) Is Null))) " & _
　　"ORDER BY 部門コード, 商品名;"
　Set rst = dbs.OpenRecordset(sql)
　If rst.BOF = False Then
　　 rst.MoveLast: rst.MoveFirst
　　Do Until rst.EOF
　　　 rst.Edit: rst.Fields("番号") = cnt
　　　 cnt = cnt + 1: rst.Update: rst.MoveNext
　　Loop
　End If

　sql = "SELECT 部門コード, 単価, 番号 FROM tbl商品 WHERE" & _
　　　　" (((単価) Is Null) AND (Not (番号) Is Null))"
　Set rst = dbs.OpenRecordset(sql)
　If rst.BOF = False Then
　　 rst.MoveLast: rst.MoveFirst
　　 Do Until rst.EOF
　　　　rst.Edit: rst.Fields("番号") = Null
　　　　rst.Update: rst.MoveNext
　　 Loop
　End If
Next i

End Sub

この回答へのお礼

papayukaさん、お手数をおかけしました。
今日は別業務が忙しくて手をつけられませんでしたが、お二人の方にここまでアドバイスいただいので必ず完成にこぎつけたいと思います。
何度もありがとうございました。

お礼日時：2004/02/25 00:48

No.5 回答者： tsukasa-12r 回答日時： 2004/02/23 15:11

抜けがありました。

あと、
＞　　　　fldNo = count
＞　　　　count = count + 1
および、その直前の (1)、(2-1)、(2-2)
を実行する条件を、
「単価≠0」かつ「番号が空」
にします。

この回答へのお礼

tsukasa-12さん、何度もありがとうございました。
ちょっとむずかしそうですが挑戦してみます。
どうしてもうまくいかなければ単価をあとで消さないように周知や工夫をすれば業務に充分運用できそうです。
頑張ってみます！

お礼日時：2004/02/23 21:16

No.3 回答者： tsukasa-12r 回答日時： 2004/02/22 23:42

あ、なんか原因がわかったような気がします。

私は
　　If rst.Fields("単価") > 0 Then
　　　　fldNo = count
　　　　rst.Update
　　　　count = count + 1
　　End If
　　rst.MoveNext
Loop
を
　　If rst.Fields("単価") > 0 Then
　　　　fldNo = count
　　　　count = count + 1
　　Else
　　　　fldNo = Null
　　End If
　　rst.Update
　　rst.MoveNext
Loop
に書き換えればいいですよ、というつもりで書いたのですが、
＞rst.Update
＞Loop
ということですので、rst.MoveNext が抜けちゃってるんですね。それで、レコードセットの移動が行われずに、同じレコードに対してぐるぐるループが回る無限ループになっちゃってて、それで Integer の範囲を超えちゃってるんです。↑のように rst.MoveNext を入れてください。

この回答への補足

tsukasa-12rさん、ありがとうございました。
おかげさまでオーバーフローと「単価を消すと連番も」は解決しましが、
たとえば
01　あい　500
02　かき　1000
03　さし　100
04　たち　200
で、02の単価（1000）を消すと
01　あい　500
　　かき　
02　さし　100
03　たち　200
と、以後が繰り上がって振られてしまうのです。02は新規レコードで使用するか、無理なら欠番のままでよいので連番が動かない方法はありますでしょうか？
私の脳ミゾもかなり疲れてきましたが、どうかよろしくお願いします。

補足日時：2004/02/23 00:25

No.2 回答者： papayuka 回答日時： 2004/02/22 21:12

削除された先の質問に答えたものです。

#1さんの回答で良いと思いますが、オーバーフローしたって事は変数 count が Integer の範囲(32767)を超えてるのでは？

Dim count As Integer を
Dim count As Long　にしたらどうでしょう？

あと、Case内でCountは初期化しているので、

If rst.BOF = False Then
　 rst.MoveLast
　 rst.MoveFirst
　 count = 1 　　　　　' ←これは不要に思います。

それと
cmd連番_Click_Err_Exit:　と　Resume cmd連番_Click_Err_Exit　も不要に思います。

この回答への補足

papayukaさん、ありがとうございました。
＆削除前の回答のお礼を書いていたのですが、200文字オーバーでやむなく消していたのです。失礼しましたm(__)m

>Dim count As Long　にしたらどうでしょう？
このようにするとACCESSが無反応のまま固まってしまうのです（強制終了するしかないのです）
今、他の２個所の不要部分を削除したあとtsukasa-12rさんの方法を試しています。
何度もありがとうございました。

補足日時：2004/02/23 00:04

No.1 回答者： tsukasa-12r 回答日時： 2004/02/22 18:44

＞連番を付与したあとで単価を削除しても連番が消えません。

というのは、一度連番を付与したあとで、単価を消して、もう一度実行したら、単価を消したレコードの番号が残ったまま、ということですよね？

If rst.Fields("単価") > 0 Then
　　fldNo = count
　　count = count + 1
Else
　　fldNo = Null
End If
rst.Update
という感じですね。

この回答への補足

tsukasa-12rさんありがとうございました。

>単価を消したレコードの番号が残ったまま・・・
そういう状態です。
教えていただいたコードに差し替えて

（前略）
rst.Update
Loop
End If
dbs.Close
・
・
・
としましたが「オーバーフローしました」と表示されます。
「オーバーフロー」とは変数の型の違い？
あるいは私の貼り付け間違いでしょうか？

お手数ですがよろしくお願いします。

補足日時：2004/02/22 19:33