Microsoft Access Club >SampleFile Q & A >インデックス >このページ >次頁 < 前項  サイト内検索


Q&A-021 連番をテーブルに書き込む方法 - 記号+年+連番 形式 -


タイトル : 連番の振り方を教えてください。 
記事No : 13011 
投稿日 : 2003/10/02(Thu) 22:27 
投稿者 : 豊子  

OS:windows2000
Access Version:2000

2つのテーブルから必要な情報を拾ってそこに新たな情報を付加して別のテーブルに
蓄積していくのですが、情報を付加する際に伝票番号を付けたいと思っています。
形式は、G+年度(西暦下2桁)+通し番号(5桁)をVBAを使わないで設定したいです。
年度は、新しく変わったら通し番号は00001から始まるようにしたいです。
 
> 別テーブルに蓄積・、これは、テーブル作成クエリまたは追加クエリ、いずれを利用 ?

追加クエリです。
> ここでいう年度とは、
> ・別テーブルに蓄積したその時なのか。
> ・フィールドデータとして日付があり、それを基準にしてなのか。

別テーブルに蓄積した時です。

> 形式は、G+年度(西暦下2桁)+通し番号(5桁)をVBAを使わないで設定したいです。

マクロのみでこれだけのものを仕上げることはできません。最小限のVBAとクエリ処理を組み合わせるしかないでしょう。また、この連番形式では、格納するテーブルフィールドデータは テキスト型 になります。

 

考え方

連番を格納しているテーブルフィールドの最大値を DMax関数を利用して引き出します。次に、この最大値に、追加クエリを用いて +1 づつ増やす方法を取ります。

但し、オプションとして、連番作成時の年を二桁で付加するのと、連番は全て5桁出揃える問題があります。更に、年が変われば連番を初期化し00001から始まる ということなので、

  • Format関数を用いて5桁に揃える。
  • 連番を格納しているテーブルフィールドの最大値の年を連番作成の実行前に取得し、実行時の年とそれぞれ比較する。その結果、異なっていれば00001に初期化する。

オプション設定のため、かなり難解な代物となっています。

  • プロシージャの配置の問題もあり、フォーム上のコマンドボタンからクエリを開く仕様とする。
  • テーブルデータは、前回No20のサンプルファイルから流用する。

 

テーブルサンプル(tbl_sample)

連番 ID No 日付 登録日 解約日
G0300001 1 1 2003/04/17 2003/01/01 2003/05/08
G0300002 1 1 2003/04/17 2003/01/02 2003/01/03
G0300003 1 1 2003/04/17 2003/01/03 2003/01/05
G0300004 1 1 2003/04/17 2003/01/02 2003/01/03
G0300005 1 1 2003/04/17 2003/01/01 2003/05/08
G0300006 1 1 2003/04/17 2003/01/02 2003/01/03

 

プロシージャ

下記のように標準モジュールを作成します。考え方が分かるように、コメントを多用しました。

 

Option Compare Database
Option Explicit

'枠外に変数を定義する。これは値を保持するため。
Dim VarValue As Variant
'クエリ実行時、年(yy)を保持する。
Dim VarYear As Variant
'*** 連番作成クエリを開く前に実行する。変数VarValueを初期化する ***

Function FrmOpen()
    
    'Dmax関数で、tbl_sampleテーブルの連番フィールドに存在する一番大きな値を代入する。
    VarValue = DMax("連番", "tbl_sample") '---この部分を自分のPCに合わせる。
            
    'Date関数で、tbl_sampleテーブルの連番フィールドに存在する最大値の年(yy)を代入する。
    VarYear = Left(Right(VarValue, 7), 2)

End Function
 
'*** クエリフィールドに記述し、連番を作成する ***

Function SamplePro(lngCounter As Long) '---Long 部分を自分のPCに合わせる。

    On Error GoTo エラー

    Dim intcount As Integer '変数を定義。
    Dim varNowDate As Variant  '変数を定義。
        
    'Date関数で実行時の年(yy)を取得する。
    varNowDate = Format(Date, "yy")
    
    '連番フィールドにデータが無き場合、000001からスタートする。
    If IsNull(VarValue) Then
        VarValue = "G0000000"
    End If
    
    'Gyy00000形式の中で、右から5桁を抽出する。
    VarValue = Right(VarValue, 5)
    
    '保持している年(VarYear)と実行時に取得した年(varNowDate)を比較する。
    If VarYear <> varNowDate Then
        
        '相違の場合は、連番を 1 から開始する。
        VarValue = Format(1, "00000")
        SamplePro = "G" & varNowDate & Format(1, "00000")
        
        '変数VarYearを実行時の年(yy)に変更する。
        VarYear = varNowDate
    
    Else
        
        '通常は最大値に +1 を加え続ける。
        VarValue = VarValue + 1
        SamplePro = "G" & varNowDate & Format(VarValue, "00000")
    
    End If
    
    Exit Function
            
エラー:

    msgbox "予期せぬエラーです。" & vbNewLine & _
            err.Number & vbNewLine & _
            err.Description
    End
    
End Function

 

これら、プロシージャをフォーム上に配置したコマンドボタン(cmd_連番作成)のクリック時イベントに記述します。以下、サンプルプロシージャです。

 

Private Sub cmd連番作成_Click()

    '警告メッセージを非表示に切り替え。
    DoCmd.SetWarnings False

    Call FrmOpen  'Functionプロシージャ FrmOpenを呼び出す。
    DoCmd.OpenQuery "qry_sample" 'クエリを開く。
    
    '警告メッセージを表示に切り替え。
    DoCmd.SetWarnings False
    
    msgbox "連番作成を完了しました。"

End Sub

 

 

追加クエリの修正

追加クエリに新規フィールドを作成し Functionプロシージャ SamplePro を下図のように配置します。

 

 

注意点

上記のプロシージャには、引数 No がありますが、これはオートナンバーフィールドです。特に、この値を使用するというのではなく、プロシージャにクエリフィールドの引数が無いと、連番が作成できないからです。

  • 引数の無いプロシージャである場合は、連番は全て同じ値となります。

これは、Accessの仕様ですから、ご注意下さい。

この時の注意点としては、オートナンバーフィールドNoと同じデータ型は長整数型ですから、Longと記述しています。

Function SamplePro(lngCounter As Long)

引数として、例えば、テキスト型フィールドを指定するなら、LongからStringに変更する必要があります。

 

 

2003/10/03


Q&A-021 連番をテーブルに書き込む方法 - 記号+年+連番 形式 -


 

-Microsoft Access Club-