（ACCESS）連番取得について

質問

アクセスで顧客管理をしています。
DMax関数を用いて、レコードを追加する度に自動で連番を振る設定に
したいと思っていますが、上手くいかないため質問させてください。

【テーブル名】T_顧客一覧
【入力用のフォーム名】F_顧客登録
【連番を入力させたい項目（フィールド名）】No（テーブルでは主キーに設定）

とし、フォームの”No”の既定値に

　　=DMax("No","T_顧客一覧")+1

と入力するのですが、フォームには1としか表示されません。

非常に初歩的な質問だとは思うのですが、
何か原因として考えられることがありますでしょうか？
情報が足りないようでしたら補足させていただきます。

よろしくお願いします。

回答No.5

chie65536 · ベストアンサー

追記。

一番小さい「空き番号」から埋めて行く方法を書いておきます。

フォームのテキストボックス「No」の「フォーカス取得時」のイベントプローシジャに、以下のコードを追加して下さい。

Private Sub No_Enter()
　　Dim db As Database
　　Dim rs As Recordset
　　Dim i As Integer
　　If Me.NewRecord Then
　　　　Set db = CurrentDb
　　　　Set rs = db.OpenRecordset("SELECT T_顧客一覧.[No] FROM T_顧客一覧 ORDER BY T_顧客一覧.[No];")
　　　　rs.MoveFirst
　　　　i = 1
　　　　Do Until rs.EOF
　　　　　　If rs.Fields(0) <> i Then
　　　　　　　　Exit Do
　　　　　　End If
　　　　　　i = i + 1
　　　　　　rs.MoveNext
　　　　Loop
　　　　[No] = i
　　　　rs.Close
　　　　db.Close
　　　　Set rs = Nothing
　　　　Set db = Nothing
　　End If
End Sub

これは「頭から１、２、３…になってるか見ていって、抜けてる番号があったら、その番号をテキストボックスにセットする」と言う事をしています。

テーブル、レコードにはセットせず、テキストボックスにセットするだけなので「ユーザーが手入力した場合と同じ動作」をする為、ユーザーがフォーム上で「ESCキーを押す」などすれば、セットした値は無効になり、レコードの追加をキャンセル出来ます。

お礼

ご回答ありがとうございます。
追記いただいた方法で満足のいく結果が得られました！
おまけに、今まで手入力していたデータのミスも見つかり
非常に助かりました。
勉強不足で恥ずかしい限りです。

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

回答No.4

noname#140971

補足: 出来ればオートナンバーではなく採番方式を！

私は、採番方式は、[DMax]->[DBMax]->[採番方式]と進化すると思います。

・テーブルが破損した場合の対策が容易です。
・なんと言っても高速処理が実現できます。

[id管理表]
id_name_____final_value
顧客一覧_ID___________0

Private Sub Form_BeforeInsert(Cancel As Integer)
　　If Nz(Me.ID) = 0 Then
　　　　Me.ID = NewID("ID", "顧客一覧_ID")
　　End If
End Sub

これですと、[final_value]を更新するだけですので超高速で処理できます。
なお、欠番対策なんて意味ないですから、それは考えられないことです。

お礼

ご回答ありがとうございます。
勉強不足のため理解するのに時間がかかってしまいました。
参考にさせていただきます！
ありがとうございました。

回答No.3

chie65536

＞オートナンバーはレコードを削除した場合などに欠番が発生すると聞いたので、出来ればそれ以外の方法で設定したいと思っています。

最大値を求める関数を使用しても、欠番が出るのは同じです。

だって「１、３、４、５、６、７、９、１０」の「最大値＋１」は「１１」ですから。でも、貴方の欲しいのは１１でなく「２」でしょう？

だったら「オートナンバーにしても、結果は同じ」ですよ。

問題なのは「規定値をどうするか？」ではなく「欠番が出た場合に、どう欠番を埋めるか？」であって、貴方は見当違いな事をしてます。

だって「欠番を埋める方法」は「オートナンバーでも、貴方のやりたい方式でも、方法は同じ」なんですから。

「方法が同じ」なら「楽な方法を使う」のが「賢い方法」ですよ。

回答No.2

noname#140971

DMax()ではなく 次の DBMax()を使えば試み自体は成功するでしょう。

Public Function DBMax(ByVal strField As String, _
　　　　　　　　　　　ByVal strTable As String, _
　　　　　　　　　　　Optional strWhere As String = "", _
　　　　　　　　　　　Optional ReturnValue = 0) As Variant
On Error GoTo Err_DBMax
　　Dim N
　　Dim strQuerySQL As String
　　Dim rst　　　　 As ADODB.Recordset

　　Set rst = New ADODB.Recordset
　　strQuerySQL = "SELECT MAX(" & strField & ") FROM " & strTable
　　If Len(strWhere) > 0 Then
　　　　strQuerySQL = strQuerySQL & " WHERE " & strWhere
　　End If
　　With rst
　　　　 .Open strQuerySQL, _
　　　　　　　 CurrentProject.Connection, _
　　　　　　　 adOpenStatic, _
　　　　　　　 adLockReadOnly
　　　　 If Not .BOF Then
　　　　　　 .MoveFirst
　　　　　　 N = Nz(.Fields(0), "")
　　　　 End If
　　End With
Exit_DBMax:
On Error Resume Next
　　rst.Close
　　Set rst = Nothing
　　DBMax = IIf(N <> "", N, ReturnValue)
　　Exit Function
Err_DBMax:
　　MsgBox "SELECT 文の実行時にエラーが発生しました。(DBMax)" & Chr$(13) & Chr$(13) & _
　　　　　 "・Err.Description=" & Err.Description & Chr$(13) & _
　　　　　 "・SQL Text=" & strQuerySQL, _
　　　　　 vbExclamation, " 関数エラーメッセージ"
　　Resume Exit_DBMax
End Function

もちろん、

　　DBMax = IIf(N <> "", N, ReturnValue)

と同じヌル対策をすれば DMax()でもOKです。
が、DMax()は実行速度がえらく遅いのでお勧めではありません。

なお、既定値を0にしておいて何かデータが入力されたら主キー値を更新するという手法もあります。
（主キーの列名は予約語と重複しない'ID'にしています）

Private Sub Form_BeforeInsert(Cancel As Integer)
　　If Nz(Me.ID) = 0 Then
　　　　Me.ID = DBMax("ID", "顧客一覧") + 1
　　End If
End Sub

回答No.1

naminami01

オートナンバー型で管理するのではだめなのですか？

補足

はい。オートナンバーはレコードを削除した場合などに欠番が発生すると聞いたので、出来ればそれ以外の方法で設定したいと思っています。