Access2003 伝票番号を自動的につけるには

質問

お世話になっております。

受注テーブルに[伝票番号]があって、番号を自動的につけたいと思っています。
入力用の[受注伝票]フォームがあります。

３月１７日最初の入力
2006031701

３月１７日の３番目の入力（最初、２番目は入力済み）
2006031703

このように日付＋自動的に２桁の番号をつけたいのですが方法がわかりません。
参考になるページがあってこのように書いてみましたがエラーになりました。

[受注伝票]フォームの[伝票番号]テキストボックスプロパティの規定値

=IIf(DMax("伝票番号","受注伝票","伝票番号 Is Not Null") Is Null,CLng(Format$(Now(),"yyyymmdd"))*100+1,IIf(Left(CStr(DMax("伝票番号","受注伝票","伝票番号 Is Not Null")),8)=Format$(Now(),"yyyymmdd"),DMax("伝票番号","受注伝票","伝票番号 Is Not Null")+1,CLng(Format$(Now(),"yyyymmdd"))*100+1))

ご教授いただけると幸いです。よろしくお願いいたします。

回答No.6

noname#22222 · ベストアンサー

s_husky です。

やっぱ、プロパティのセットでの管理には限界があるようです。
そこで、最終案を示します。

１、受注伝票フォームのコピーを作成し、
２、伝票番号.規定値を消して、
３、以下のコードを全てコピーしてみて下さい。

＜ただ、コピーすれば動作する筈です＞

30分ばかりで、なぐり書きしたのでバグがあればお許し下さい。
一応の、伝票入力と伝票番号管理の基本が判ると思います。
なお、あくまでも Husky 流ですので、それなりに自分の作法を確立することが重要です。

' -------------------------------------------------------------------------------------------------------
'　GetNewNumber("伝票番号", "受注伝票", 条件文)
' -------------------------------------------------------------------------------------------------------
Private Function GetNewNumber(ByVal fldName As String, _
　　　　　　　　　　　　　　　ByVal tblName As String, _
　　　　　　　　　　　　　　　ByVal Hiduke As Date) As Long
　　Dim lngMaxPlus As Long

　　lngMaxPlus = Nz(DMax(fldName, tblName, "受注日=#" & Hiduke & "#"))
　　GetNewNumber = IIf(lngMaxPlus = 0, Val(Format(Hiduke, "yyyymmdd01")), lngMaxPlus + 1)
End Function

' -------------------------------------------------------------------------------------------------------
'　新規伝票発生時の処理: Me.伝票番号を更新
' -------------------------------------------------------------------------------------------------------
Private Sub Form_BeforeInsert(Cancel As Integer)
　　If Len([受注日] & "") = 0 Then
　　　　Me.受注日 = Date ' 新規で [受注日]=Null はＤａｔｅ に
　　End If
　　Me.伝票番号 = GetNewNumber("伝票番号", "受注伝票", [受注日])
End Sub

' -------------------------------------------------------------------------------------------------------
'　フォームを閉じる時の処理: 不具合伝票の削除
' -------------------------------------------------------------------------------------------------------
Private Sub Form_Close()
　　Dim dbs As DAO.Database

　　Set dbs = CurrentDb
　　dbs.Execute "DELETE FROM 受注伝票 WHERE 伝票番号=0 OR 受注日 IS NULL"
　　dbs.Close
End Sub

' -------------------------------------------------------------------------------------------------------
'　受注日更新後の処理: Me.伝票番号を更新
' -------------------------------------------------------------------------------------------------------
Private Sub 受注日_AfterUpdate()
　　Form_BeforeInsert True
End Sub

' -------------------------------------------------------------------------------------------------------
'　受注日Exit後の処理: [受注日
' -------------------------------------------------------------------------------------------------------
Private Sub 受注日_Exit(Cancel As Integer)
　　If Len([受注日] & "") = 0 Then
　　　　MsgBox "[受注日] が空（ヌル）の伝票は、フォームを閉じると削除されます。", vbExclamation, " 警告"
　　End If
End Sub

お礼

できました！
このとおりにやりましたら問題なく動作しました。
空番号の処理もできていますので大変ありがたいです。

流れもよくわかりましたので、これからいろいろと勉強して自分なりに活用していきたいと思います。
お忙しい中、本当にありがとうございました。

回答No.5

noname#22222

s_husky です。

ウィスキー飲んでガーッ、ガーッ寝て起きたら「動かない」とのこと。
我が　Test.mdb　では快適に動いているのに！うーん！

※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※

「受注伝票フォームに受注日のテキストボックスを作り・・・」が原因か？

※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※

=IIF(式, 式が真の時の値, 式が偽の時の値）

＜＜＜＜＜＜式の確認手順＞＞＞＞＞＞

? DMax("伝票番号", "受注伝票", "受注日=#2006/03/17#")
2006031701

と、イミディエイトを開いて、表＜受注伝票＞の列＜伝票番号＞の最大値を　DMAX関数で取得することに成功しているか否かを確認。
この時、条件は、列＜受注日＞が一致しているということです。

実態としては、SELECT MAX(伝票番号) FROM 受注伝票 WHERE 受注日=#2006/03/17#

というＳＱＬ文を実行しているだけです。

＊表に列’受注日’が無ければ当然にエラー！！
＊受注日が yyyy/mm/dd 形式でなければ検索不可！

? DMax("伝票番号", "受注伝票", "受注日=#2006/03/18#")
Null

次に、新しい日付だと　ＤＭＡＸ関数が Null を返すことも確認。

・2006031701 <--- IIF関数では、真(True) と判定されます！
・Null <---------- IIF関数では、偽(False) と判定されます！

このことから、

式=DMax("伝票番号", "受注伝票", "受注日=#" & [受注日] & "#")

が正しいことが判ります。

? Date
2006/03/18
? Now
2006/03/18 4:29:54

とイミディエイトウィンドウで確認し、[受注日]の規定値=Date()で宜しいことも確認。

＜＜＜＜＜＜真の時の確認手順＞＞＞＞＞＞

? DMax("伝票番号", "受注伝票", "受注日=#2006/03/17#") + 1
2006031702

式の値に+1 しているので確認するまでもないことです。
よって、ここもＯＫ！

＜＜＜＜＜＜偽の時の確認手順＞＞＞＞＞＞

? CLng(Format$("2006/03/19", "yyyymmdd01"))
2006031901
? CLng(Format("2006/03/19", "yyyymmdd01"))
2006031901
ちゃんと、伝票番号初期値が作成されているのでＯＫ！

＜＜＜＜＜＜フォームに組み込んで確認＞＞＞＞＞＞

後は、テストフォームに組み込んで確認するだけです。

※つまり、表に＜受注日＞を追加していないことがエラーの原因です。

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
[受注日]が訂正された時の対策を追加すれば何とかＯＫかと！
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊

お礼

何度もありがとうございます。
テーブルに受注日を追加してフォームと連動させたら表示されました。
初歩的なミスで申し訳ございませんでした。
確認手順は大変参考になりました。
ありがとうございました。

回答No.4

noname#22222

s_husky です。

コピペは、伝票番号にしたんですよね！

補足

何度もありがとうございます。

受注日の規定値を=Date()にすれば今日の日付は表示されますが、やはりエラーが出ます。
コピペは伝票番号の規定値にしました。

この方法はネットで検索してたまたま見つけたのでやってみました。
知識が乏しいのでご指摘の問題についてはまったく考えていませんでした。
関数についてはまったくわかりませんので、ほかに良い方法がありましたら教えていただければありがたいです。

すみませんがよろしくお願いいたします。

回答No.3

noname#22222

エラーは、想定内です。

[受注日]がヌル値だからです。
規定値を=Date()
にすればエラーは回避されます。

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
もう一つ問題が...
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
質問者が採用されている手法ですと、新規レコードに誤って移動しても伝票番号が生成されます。
通常は、受注伝票がインサートされた直後に伝票番号を生成させます。
この空レコードをフォームを閉じた時にどう削除するかが問題ですよ！

※'様'は止めて下さいね！当方、還暦まじかのスーツのデザーナーに過ぎません！

回答No.2

noname#22222

s_husky です。

質問そのものに答えるならば...

=IIf(DMax("伝票番号", "受注伝票", "受注日=#" & [受注日] & "#"), DMax("伝票番号", "受注伝票", "受注日=#" & [受注日] & "#") + 1, CLng(Format$([受注日], "yyyymmdd01")))

修正点１、Now()->[受注日]

伝票は、当日に当日分を入力するとは限らないので[受注日]が必要な訳です。

修正点２、CLng(Format$([受注日], "yyyymmdd01"))

Format$関数で　01　を発生させています。

補足

s_husky 様　何度もありがとうございます。

ご指示のとおりにやってみましたが、新規入力でエラーが出ます。
受注日を入力し始めるとエラーが消えますが、番号が表示されません。

受注伝票フォームに受注日のテキストボックスを作り、コードをそのままコピー・ペーストしました。

何かわかりましたらお願いしたいと思います。

受注日はそのとおりです。ご指摘ありがとうございました。

回答No.1

noname#22222

お勧めのやり方は、

GetNewNumber（"受注テーブル", "伝票番号", [受注日])

とい関数を作成することです。

Public Function GetNewNumber(ByVal tblName As String, ByVal fldName As String, ByVal Hiduke As Date) As Long
　　Dim lngMaxPlus As Long

　　lngMaxPlus = Nz(DMax(fldName, tblName, "受注日=#" & Hiduke & "#"))
　　GetNewNumber = IIf(lngMaxPlus = 0, Val(Format(Hiduke, "yyyymmdd01")), lngMaxPlus + 1)
End Function

? GetNewNumber("受注", "伝票番号", cdate("2006/03/17"))
2006031702

表＜受注＞に伝票番号= 2006031701 があれば 2006031702を戻します。
新しい日付であれば 2006031801を戻します。

? GetNewNumber("受注", "伝票番号", cdate("2006/03/18"))
2006031801

お礼

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

初心者ですので意味がよくわかりませんでした。
また関数の勉強をしてみようと思います。
ありがとうございました。