MS Access 入力フォーム：自動採番

質問

Access2013での自動採番に関して2パターン教えて下さい。

質問(1)："顧客番号"の自動採番

【テーブル名】
顧客情報
【フィールド】
"顧客番号"、"顧客名"、"顧客名かな"、"顧客住所"、"電話番号"、"担当者"、"メール"・・・

＜"顧客番号"の採番方法＞
顧客名の頭文字を五十音順の「あかさたな」で行ごとに連番をつけて管理している。
例））
「ABC商事」→エービーシーしょうじ→「あ‐０１」
「東京印刷」→とうきょういんさつ→「た-０１」

《やりたい事！》
入力フォームに新規で、「東西工業」を入力する際に
"顧客名かな"を入力したら、"顧客番号"が自動的に採番されるようにしたい。

顧客名：東西工業
顧客名かな：とうざいこうぎょう
顧客番号：た-０２　←ココを自動採番にしたい

------------------------------------------------
質問(2)"取引番号"の自動採番

【テーブル名】
取引情報
【フィールド】
"顧客番号"、"取引番号"、"顧客名"、"顧客名かな"、"住所"、"電話番号"、"担当者"、"メール"・・・

＜"取引番号"の採番方法＞
取引が発生した順で、"顧客番号"ごとに枝番をつけて管理している。
例））
「ABC商事」→1/10の取引→"顧客番号"：あ‐０１、"取引番号"：01
「ABC商事」→1/31の取引→"顧客番号"：あ‐０１、"取引番号"：02

《やりたい事！》
入力フォームに新規で、「ABC商事」の2/15の取引情報を入力する際に
"顧客番号"を入力したら、"取引番号"が自動的に採番されるようにしたい。

顧客名：ABC商事
顧客番号：あ‐０１
取引日：2016/02/15
取引番号：03　←ココを自動採番にしたい

説明がうまくできず、分かりにくいかとは思いますが
何とかお知恵をお借り出来れば助かります。
よろしくお願い致しますm(__)m

回答No.2

山田 太郎（@f_a_007）

【補足】ADOとSQL文のワイルドカード

添付図では、テーブル[参加者名簿]の列ＩＤの'あ’の最大値を取得しています。注意を要するのは、そのSQL文です。

＞SELECT MAX(ID) FROM 参加者名簿 WHERE ID Like 'あ%'

と（ADOの場合には）ワイルドカードは'*'ではなく'%'を用います。

＊なお、このようにSQL文はイミディエイトウインドウでテストできます。

補足

回答ありがとうございます。

実はアクセスは基本操作しか使用したことがなく、一晩じっくり取り組んでみたのですが
回答内容が理解出来ませんでした。

勉強しなおして、回答内容を理解できるよう励みたいと思います。

回答No.1

山田 太郎（@f_a_007）

自動採番の二つのやり方は２つです。

１、テーブル「採番控え」を用意する。
２、個別テーブルの既存値を取得する。

【テーブル「採番控え」方式】

? NewID("ID列名")
10

Public Function NewID(ByVal strIDName As String) As Long
On Error GoTo Err_NewID
　　 Dim N　　　As Long
　　 Dim strSQL As String
　　 Dim cnn　　As ADODB.Connection
　　 Dim rst　　As ADODB.Recordset

　　 Set cnn = CurrentProject.Connection
　　 Set rst = New ADODB.Recordset
　　 strSQL = "SELECT final_value FROM id管理表 WHERE id_name='" & strIDName & "'"
　　 cnn.Errors.Clear
　　 cnn.BeginTrans
　　 With rst
　　　　 .Open strSQL, _
　　　　　　　 cnn, _
　　　　　　　 adOpenDynamic, _
　　　　　　　 adLockOptimistic
　　　　 If Not .BOF Then
　　　　　　 N = .Fields(0) + 1
　　　　　　 .Fields(0) = N
　　　　　　 .Update
　　　　 End If
　　 End With
　　 cnn.CommitTrans
Exit_NewID:
On Error Resume Next
　　 rst.Close
　　 cnn.Close
　　 Set rst = Nothing
　　 Set cnn = Nothing
　　 NewID = N
　　 Exit Function
Err_NewID:
　　 N = -1
　　 If cnn.Errors.Count > 0 Then
　　　　 ErrMessage cnn.Errors(0), strSQL
　　　　 cnn.RollbackTrans
　　 Else
　　　　 MsgBox "プログラムエラーが発生しました。システム管理者に報告して下さい。(NewID)", _
　　　　　　　　vbExclamation, " 関数エラーメッセージ"
　　 End If
　　 Resume Exit_NewID
End Function

　これは 15年前にアップした私のWEBサイトのコピペ。実際に利用していたNewID()ですが、アップの際に所々に写しミスが混入しているやも知れません。が、やることは伝わるかと思います。

【個別テーブルの既存値参照方式】

? DBLookup("SELECT MAX(列名) FROM テーブル名 WHERE XXXXX"）
10

Public Function DBLookup(ByVal strQuerySQL As String, _
　　　　　　　　　　　　　Optional ByVal ReturnValue = Null) As Variant
On Error GoTo Err_DBLookup
　　Dim DataValue
　　Dim rst As ADODB.Recordset

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

　個別テーブルの既存値参照方式は、単にSQL文を発行するだけですのでNewID()のような専用の関数は不要。なお、ここでDLookup()、DMax()を利用していないのは、ADOを利用した方がより高速で処理されるからです。

＞顧客名の頭文字を五十音順の「あかさたな」で行ごとに連番をつけて管理している。

これは考え直されたがいいですよ。

理由１：SQL文を作成するコードを書かなければならない。
理由２：意味がない連番体系。

ということで、「あかさたな」で行ごとに連番！の拘られるのであれば、そのＳＱＬ文を作成するコードが課題です。