Microsoft Access 掲示板

[登録時]グループ毎の自動採番/テキスト型の管理番号自動入力

5 コメント
views
4 フォロー

お世話になります。
AccsessVBA初心者の者です。

テーブル、クエリから検索フォームまでは参考書を見ながら自力でできたのですが、
フォームからのテーブルのレコード修正や新規登録は初の試みで、
テーブルへの新規登録、修正フォームを作成していますが、
自動採番、管理番号への転記等がわからず、
質問させていただきます。

AfterUpdate等を利用するのかと思い調べたのですが解決できず、
お知恵をお借りできたら幸いです。
別のスレッド等でも同様の質問あるかもしれませんが、
質問ばかりで恐縮ですが、よろしくお願いします。

[テーブルの構成]
テーブル名:T統合

(フィールド)
ID:数値型 ⇒例:1~
管理番号:テキスト型 ⇒例:t_2019_0001~
年度:テキスト型 ⇒例:2018、2019、2020等
年度別連番:数値型 ⇒データ例:1~
 ⇒年度をグループとして、同年度の1~の連番

[新規登録フォームの構成]
非連結のテキストボックス等を利用したフォーム

TxID:テキストボックス(非連結)
 ⇒既定値:Nz(DMax("[ID]","T統合")+1,1)
 ※IDの最大数から+1の数字が自動で入るように

Tx管理番号:テキストボックス(非連結)

cmb年度:コンボボックス(非連結)
 ⇒値集合ソース:SELECT DISTINCT [T統合].年度 FROM T統合 ORDER BY [年度] DESC;

Tx年度別連番:テキストボックス(非連結)

【質問:実現したいこと】
1.新規登録の際、ユーザーが[cmb年度]で年度を選択した後に、
[Tx年度別連番]の値が、
選択したテーブルの(年度)グループに紐付いた(年度別連番)で
最大値に+1した値(値がない場合は1~の連番)
が自動で入るようにしたい

2.(1.)で年度を選択した後、[Tx管理番号]に、
テキスト形式で、「t_yyyy_000」という形式で自動で値が入るようにしたい
⇒yyyy:[cmb年度]で選択した値
⇒000
:[年度別連番]を000形式にした値

[サンプルコード]
※別途用意した検索フォームで、
リストから選択した[ID]でデータを呼び出し
登録ボタン⇒新規登録フォーム(値が空のフォーム)
修正ボタン⇒修正フォーム(選択したIDに紐付いた値が入った同フォーム)
を開くようにしています。

今回の質問では、3.4の後半のコードは関係ないかもしれませんが、
念の為記載します。

Option Compare Database
Option Explicit

'===============================================================================
'  1.保存ボタンを押した際の処理
'===============================================================================
Private Sub btnSave_Click()
'    必須項目のメッセージボックス
    If Nz(Me.Tx管理番号) = "" Then
        MsgBox "管理番号が入力されていません"
       Exit Sub
    Else
    End If
  
    If Nz(Me.Tx年度) = "" Then
        MsgBox "年度が入力されていません"
       Exit Sub
    Else
    End If
 
    'OpenArgsに何も入っていないときは終了します
    If IsNull(Me.OpenArgs) Then
       Call Touroku
    Else
       Call Kakikae
    End If

    '完了メッセージを表示しフォームを閉じます
    MsgBox "保存しました。" _
         , vbOKOnly + vbInformation _
         , "保存"

    DoCmd.Close acForm, Me.Name

End Sub

'===============================================================================
'  2.レコードの新規登録
'===============================================================================
Private Sub Touroku()

    Dim oRS As DAO.Recordset
   
    'テーブルを開きます
    Set oRS = CurrentDb.OpenRecordset("T統合", dbOpenDynaset)

    'テーブルに新しいレコードを挿入します
    oRS.AddNew

    'フィールドを書き換えます
    With Me
            oRS("ID").Value = .TxID.Value
            oRS("年度別連番").Value = .Tx年度別連番.Value
            oRS("管理番号").Value = .Tx管理番号.Value
            oRS("年度").Value = .cmb年度.Value
       End With

    'テーブルに保存します
    oRS.Update

    'レコードセットを閉じて終了します
    oRS.Close
    Set oRS = Nothing
End Sub

'===============================================================================
'  3.選択したデータのレコード書き換え
'===============================================================================
Private Sub Kakikae()

    Dim oRS As DAO.Recordset

    'テーブルを開きます
    Set oRS = CurrentDb.OpenRecordset("T統合", dbOpenDynaset)

    '書き換え対象のデータを検索します
    oRS.FindFirst "ID=" & Me.OpenArgs
    
    'データが見つかったときはレコードの書き換え処理を行います
    If oRS.NoMatch = False Then

       'レコードを書き換え可能にします
       oRS.Edit

       'フィールドを書き換えます
       With Me
            oRS("ID").Value = .TxID.Value
            oRS("年度別連番").Value = .Tx年度別連番.Value
            oRS("管理番号").Value = .Tx管理番号.Value
            oRS("年度").Value = .cmb年度.Value
        
       End With

       'テーブルに保存します
       oRS.Update

    End If

    'レコードセットを閉じて終了します
    oRS.Close
    Set oRS = Nothing


'===============================================================================
'  4.検索フォームから選択したデータの値取り込み
'===============================================================================
Private Sub Form_Open(Cancel As Integer)

    Dim oRS As DAO.Recordset
    
    'OpenArgsに何も入っていないときは終了します(登録モードのため)
    If IsNull(Me.OpenArgs) Then
       Exit Sub
    End If

    'テーブルを開きます
    Set oRS = CurrentDb.OpenRecordset("T統合", dbOpenDynaset)
    
    '表示するデータを検索します
    oRS.FindFirst "ID=" & Me.OpenArgs

    'データが見つかったときは
    If oRS.NoMatch = False Then
       'フォームのテキストボックスなどを使ってデータを表示します
       With Me
           .TxID.Value = oRS("ID").Value
           .Tx年度別連番.Value = oRS("年度別連番").Value
           .Tx管理番号.Value = oRS("管理番号").Value
           .cmb年度.Value = oRS("年度").Value
 
    'レコードセットを閉じて終了します
    oRS.Close
    Set oRS = Nothing
End Sub
keepit
作成: 2021/01/22 (金) 14:27:13
通報 ...
1
hiroton 2021/01/25 (月) 08:53:23 7a1b5@f966d

軽く見た感じVBAでどうこうしたいということではないのかな

DMax関数は第三引数に条件を指定できます

1.新規登録の際、ユーザーが[cmb年度]で年度を選択した後に、
[Tx年度別連番]の値が、
選択したテーブルの(年度)グループに紐付いた(年度別連番)で
最大値に+1した値(値がない場合は1~の連番)
が自動で入るようにしたい

Tx年度別連番のコントロールソース

=Nz(DMax("年度別連番","T統合","年度='" & [cmb年度] & "'"),0)+1

2.(1.)で年度を選択した後、[Tx管理番号]に、
テキスト形式で、「t_yyyy_000」という形式で自動で値が入るようにしたい
⇒yyyy:[cmb年度]で選択した値
⇒000:[年度別連番]を000形式にした値

Tx管理番号のコントロールソース

="t_" & [cmb年度] & Format([Tx年度別連番],"000")
2
keepit 2021/01/25 (月) 11:17:27

hiroton様

ご回答いただき誠にありがとうございます。

2つのコントロールソースに、頂いた式を挿入し、
新規登録時(サンプルコードの1,2の処理)の際は、
やりたいことが実現できました。

しかし、1点問題が出てきてしまい、
申し訳ございませんが、
再度質問させて頂きたいと思います。

もともと2018.2019等過去データには値が入っており、

別途用意してある検索フォームから
「詳細/修正」ボタンを押すと
今回のフォームが開き、
検索フォームで選択された「ID」に連動した値が、
該当の登録フォームの、各レコードの値が挿入される仕様になっています。
(サンプルコードの「4.検索フォームから選択したデータの値取り込み」の部分。)

新規登録モードと修正モードが同じフォームで出来るような形になっています。

今回の修正後、
検索フォームで「詳細/修正」ボタンを押すと、

実行時エラー '2448'
このオブジェクトに値を代入することはできません。

'===============================================================================
'  4.検索フォームから選択したデータの値取り込み

           .Tx年度別連番.Value = oRS("年度別連番").Value
           .Tx管理番号.Value = oRS("管理番号").Value

の部分がデバックで表示され、該当フォームを開くことができなくなってしまいました。

もともとは、「詳細/修正」モードの場合、
非連結のテキストボックスで、
IDに連動したレコードを表示させていただので、
コントロールソースにあらたな数値を設定してしまったので、
エラーがでてしまったのでしょうか?
VBAでの処理が必要なのかどうか、
「新規登録」とは別に「詳細修正フォーム」を新たに作ったほうが良いのか、
質問ばかりで申し訳ありませんが、よろしくおねがいします。

3
hiroton 2021/01/25 (月) 11:44:41 7a1b5@f966d

思われている通りであっています

テキストボックスの使いまわしをするのであればコントロールソースは非連結にしてVBAで処理することにしましょう。タイミングは

1.新規登録の際、ユーザーが[cmb年度]で年度を選択した後に、

ということなので[cmb年度]の更新後処理で

Private Sub cmb年度_AfterUpdate()
    Dim new年度連番 As Long
    new年度連番 = Nz(DMax("年度別連番", "T統合", "年度='" & Me!cmb年度 & "'"), 0) + 1
    Me!Tx年度別連番 = new年度連番
    Me!Tx管理番号 = "t_" & Me!cmb年度 & Format(new年度連番, "000")
End Sub
4
hiroton 2021/01/25 (月) 11:57:09 7a1b5@f966d >> 3

ついでに、
新規・修正が同じフォームということなので、このコードだけだと、修正モードの時でも[cmb年度]を修正すると連番が変わります(処理が働きます)

年度(データ)は修正するけど一度取得した番号は変えたくないとかだと仕様に合わせてもうひと手間必要になりますね

5
keepit 2021/01/25 (月) 14:59:03

hiroton様

早速ご返答いただき誠にありがとうございます。

cmb年度_AfterUpdate()

を入れたところ、うまく動きました。

すでに入力済のデータで年度をいじることはほぼないと思いますので、
大丈夫かと思います。
本当にありがとうございます。