MA ACCESSデータベースに詳しい方お願いします。

Question

アクセス超初心者です。
現在、仕入管理のデータベース作成中です。

仕入管理テーブル
・ID………………………オートナンバー（主キー）
・仕入日…………………日付/時刻型
・品名……………………テキスト型
・商品数…………………数値型
・管理番号1 ……………テキスト型
・管理番号2 ……………テキスト型
・仕入場所_CD …………テキスト型

仕入日テーブル
・ID……………オートナンバー
・仕入日………日付/時刻型
・仕入日_CD …テキスト型（例：0107A:01=日・07=年・A=月）※月はA=１月・B=２月・C=３月…

仕入管理テーブル
ID　仕入日　　　　品名　　　商品数　管理番号1　　管理番号2　　仕入場所_CD

1　2010/09/26　　A財布　　　3　　　　001　　　　　01　　　　　　AB
 2　2010/09/26　　A財布　　　3　　　　001　　　　　02　　　　　　AB
 3　2010/09/26　　A財布　　　3　　　　001　　　　　03　　　　　　AB
 4　2010/09/26　　B財布　　　2　　　　002　　　　　01　　　　　　AB
 5　2010/09/26　　B財布　　　2　　　　002　　　　　02　　　　　　AB
 6　2010/09/26　　C財布　　　1　　　　003　　　　　01　　　　　　AB

仕入日テーブル
ID　仕入日　　　　仕入日_CD

1　2010/09/26　　2610I
 2　2010/09/27　　2710I
 3　2010/09/28　　2810I
 4　2010/09/29　　2910I
 5　2010/09/30　　3010I
 6　2010/10/01　　0110J
・
・
・

上記の二つのテーブルを基にクエリで「仕入場所_CD・仕入日_CD・管理暗号1・管理番号2」を連結しています。

管理番号例:AB2610I001-01

教えて頂きたいこと

1．入力フォームにて「仕入日_CD・管理暗号1・管理番号2」の三つのフィールドが重複した時にメッセージを出して更新できないようにする。
2．商品数の値が「3」の場合に、管理番号2の値が「03」以上の値を入力できないようにする。
3．管理番号1の値が「999」を超えたら「001」の戻るようにする。

何方か詳しい方お願いします。<m(__)m>

nicotinism · Accepted Answer

Private Sub 複数_Click() と Private Sub 次へ_Click()
の機能をまとめてあります。
商品数の数によってコマンドボタンを押す都度
管理番号1と2は増加、
他は前レコードの値が入ります。
手抜きのため、サンプルコードは仕入場所_ID だけですが
よ～く動作検証＆読んで見てください。

Private Sub コマンド_Click()
　　Dim Data1 As Date
　　Dim oldBKNo1 As Long
　　Dim newBKNo1 As Long
　　Dim oldBKNo2 As Long
　　Dim newBKNo2 As Long
　　Dim shohinSuu As Long
　　Dim basyoID As String
　　
　　If Me.Dirty Then
　　　　If MsgBox("編集中です。保存しますか", vbYesNo) = vbNo Then
　　　　　　Exit Sub
　　　　End If
　　　　DoCmd.RunCommand acCmdSaveRecord
　　End If
　　
　　DoCmd.GoToRecord Record:=acLast
　　'最終行の各値を変数に保存。
　　Data1 = IIf(IsNull(Me!仕入日), Date, Me!仕入日)
　　oldBKNo1 = CLng(Nz(Me!管理番号1, 0))
　　oldBKNo2 = CLng(Nz(Me!管理番号2, 0))
　　shohinSuu = Nz(Me!商品数, 0)
　　basyoID = Nz(Me!仕入場所_ID, "")
　　
　　If Me!商品数 <= DCount("管理番号1", "T仕入管理", "管理番号1='" & Format(oldBKNo1, "000") & "'") Then
　　　　newBKNo1 = oldBKNo1 + 1 + Abs(oldBKNo1 Mod 9 = 0) + (oldBKNo1 = 0)
　　'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~↑の9は本番では999に変えてください
　　　　newBKNo2 = 1
　　　　shohinSuu = 0
　　　　basyoID = ""
　　Else
　　　　newBKNo1 = oldBKNo1
　　　　newBKNo2 = oldBKNo2 + 1
　　End If
　　
　　'新規レコードに移動
　　DoCmd.GoToRecord Record:=acNewRec
　　'Me.Recordset.AddNew
　　'変数の値を加工して代入
　　Me!仕入日 = Data1
　　Me!管理番号1 = Format(Right(CStr(newBKNo1), 3), "000")
　　Me!管理番号2 = Format(newBKNo2, "00")
　　Me!商品数 = shohinSuu
　　Me!仕入場所_ID = basyoID
　　Me!ID.SetFocus
End Sub

あと、いまさらなのですが・・・気になっていまして・・
今後のことですが、ここも読んでみれば良いかと
データベースの正規化の基礎
http://support.microsoft.com/kb/283878/

yorozu_ya · Answer

> 1．入力フォームにて「仕入日_CD・管理暗号1・管理番号2」の三つのフィールドが重複した時にメッセージを出して更新できないようにする。

仕入管理テーブルの「仕入日・管理番号1・管理番号2」の三つのフィールドで
一つのインデックスを作り、「固有」を「はい」にする。

> 2．商品数の値が「3」の場合に、管理番号2の値が「03」以上の値を入力できないようにする。

入力規則に設定する。

> 3．管理番号1の値が「999」を超えたら「001」の戻るようにする。

どうやって増やしているのですか？
プログラムで増やしてるなら、そこにロジックを書く。

piroin654 · Answer

「仕入日テーブル」の「仕入日_CD」の入力は
「仕入管理テーブル」の「仕入日」に日付が入力される前に
入力済みとなっているのですか。

少し話は変わりますが、
入力チェックはシステムの作り方によっては
結構大変で、こういう場合でもなるべくなら
入力フォームは「仕入管理テーブル」を直接の
レコードソースとせずに作業用のテーブルで
一旦データを保存し、入力チェックをして
入力チェックを通過すればデータを「仕入管理テーブル」
に追加する形式をとったほうがやりやすい
のですが。

layy · Answer

１と２
「重複しているか？」「０３以上か」と入力の度リアルタイムで確認を行うと、
それはそれで処理が重たくなります。
入力させておいて、ボタン押下でもってチェックする、
チェックＮＧの場合はその項目へカーソルを遷移させてまた入力、
こんな仕組みのがよさそうです。
本来のテーブルとは別にテーブルを用意しておき、
そこにひたすら入力、複数レコードをまとめてチェックする方法でも
いいときあります。

「入力させておいて、ボタン押下でもってチェックする」
を考える。

既存レコードと重なってしまうかどうかは
DLOOKUP関数使うとか、
抽出クエリを行って１件でもあれば存在、０件なら存在しないと判断するとか。

３
今どこまで使われているかをクエリで最大値抽出する、
「９９８」まで使いました、となれば、
「９９９」のときにあと１つです、とでもできそう。

「今どこまで使われているかをクエリで最大値抽出する」
を考えてみる。

最終目的に至るには、となるとわからないと思いますから、
そのためにどんな情報が必要かを考えてみましょう。
クエリでどんな種類、マクロでどんな仕掛け、があるかを整理する。

nicotinism · Answer

仕入日テーブル
ID　仕入日　　　　仕入日_CD
↑なぜ同じような内容のものが二つ必要なのだろう？
仕入日_CD は関数で求められますよね。要らないのでは？
例?format(#2010/12/3#,"ddyy") & chr(month(#2010/1/3#)+64 )
クエリでは、仕入日_CD:format(仕入日,"ddyy") & chr(month(仕入日)+64 )
と思ったので、クエリの『仕入日_CD』を『仕入日』にしちゃいました。

１・
べたな方法ですが、仕入日、管理番号1、管理番号2のそれぞれの更新前イベントに
If DCount("*", "T仕入管理", _
    "仕入日=#" & Me!仕入日 & "# and 管理番号1='" & Me!管理番号1 _
    & "' and 管理番号2='" & Me!管理番号2 & "'") > 0 Then
    MsgBox "だぶり"
    Cancel = True
    'Me.Undo
End If
定義域集計関数でチェックしているのでレコード数が多くなるとレスポンスが悪いかも。

２・
フォームの管理番号2の更新前イベントに
If Me!商品数 = 3 And Clng(Me!管理番号2) >2 Then
msgbox "まちがい"
Cancel = True
End If
とか。

３・
安直な方法として、下３桁（文字）だけ表示するようにするとか。Right（[管理番号1],3)
『000』が許されるなら・・・
駄目なら、Clng([管理番号1]) mod 1000 =0 なら ひとつ飛ばして・・・
あるいは
標準モジュールに自前の関数を作成して
Function getKBNo1() As String
'管理番号1の次の値を求めます
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSql As String
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
strSql = "SELECT ID,管理番号1 FROM T仕入管理 ORDER BY ID DESC"
rs.Open strSql, cn, adOpenStatic

If rs.RecordCount = 0 Then
    getKBNo1 = "001"
    Exit Function
End If

If rs!管理番号1 = "009" Then
    getKBNo1 = "001"
Else
    getKBNo1 = Format(CLng(rs!管理番号1) + 1, "000")
End If
rs.Close: Set rs = Nothing
End Function

コマンドクリック時に
Me!管理番号1 = getKBNo1
とか。

nicotinism · Answer

１・入力する立場とすれば、誤入力の際にすぐにエラー表示された方がありがたいと思います。
１レコード入力が終わった段階まで分からないと、
『なんで今頃出るんだよ！ キーッ』てな人もいますので。(-_-;)
遅くなってきたなーと思える頃に考え直しても良さそうに思います。
その頃には、質問者さんも自力解決出来る力量がついていて欲しいと思います。

２・最初の質問に
>2．商品数の値が「3」の場合に、管理番号2の値が「03」以上の値を入力できないようにする
とあったので、その条件に合うようにしたつもりなのですが・・・？
もしかして、私勘違い？
詳しくご説明をお願いします。

３・安直な方法で・・・良いのですかね？
少し考えを変えて、まともにしましたつもり。
提示されたコマンドクリック時のイベントを書き換えてみました。
Private Sub コマンド_Click()
Dim Data1 As Date
'カレント行の各値を変数に保存。
Dim oldKBNo As Long
Dim newKBNo As Long

If Me!ID <> DMax("ID", "T仕入管理") Then
    MsgBox ("残念") '※最終レコードがカレントレコードではない
    Exit Sub
End If

Data1 = IIf(IsNull(Me!仕入日), Date, Me!仕入日)
oldKBNo = Nz(Me!管理番号１, 0)
newKBNo = oldKBNo + 1 + Abs(oldKBNo Mod 9 = 0) + (oldKBNo = 0)
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~↑の9は本番では999に変えてください。9の次は1
'新規レコードに移動
Me.Recordset.AddNew
'
'変数の値を加工して代入
Me!仕入日 = Data1
Me!管理番号１ = Right(CStr(newKBNo), 1)
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~↑ここも1から3へ
End Sub

関数は自分でヘルプを参照してね。分からん関数のとこにカーソルを合わせF1キーで出ます。
Abs(oldKBNo Mod 9 = 0) と (oldKBNo = 0) は判別式です。
Trueの場合は-1がFalseでは0となるのを利用しています。

以下は独り言です。スルーしてください。
どうも管理番号1と管理番号2がテキスト型なのが気になる。
テーブルデザインで数値型にして、書式のところに必要なだけゼロを並べれば
数値型で有りながら、9→009になるのに。
単にデータ型変換とゼロ入力が面倒くさいからです。(^^ゞ

layy · Answer

イベントにＶＢＡで仕掛けるのはまだ早いでしょうか？。

いずれも作業用テーブルに入力データを保存してチェック、エラーなければ本当のテーブルへの反映を考える。

１は
重複しているかクエリ実行する、２レコード以上あればエラー

２については、
クロス集計クエリで入力結果を出せますからその結果から判定するのはどうですか。
仕入管理テーブルより
縦軸に品名と商品数、最大値
横軸に管理番号２
結果
Ａ財布 3 03で01 02 03
Ｂ財布 2 02で01 02
となりますがＡ財布で04が入力されていたら
Ａ財布 3 04で01 02 03 04Ｂ財布 2 02で01 02
で、３商品とも商品数と最大値を１レコードで判定できます。
商品数＜最大値がエラー商品
やってみて下さい。
１レコード分入力されたらボタン押す、このクエリ実行、エラーレコードあり、やり直し、繰り返し。

項目エラーチェックは
項目１入力、エラー、直す、項目２入力、エラー、直す、項目３入力、項目４入力、項目５入力、エラー、直す
とこまめにするか
項目１入力、項目２入力、項目３入力、項目４入力、項目５入力、３箇所項目エラー、直す
とするか
エラーのチェックのタイミングは、前者を煩わしいと思うとか随時のが親切と思うか使い手の希望もあります。


３は採番テーブルを用意し随時更新クエリで更新するとか。

nicotinism · Answer

よ～く分かりました。管理番号1は入荷商品全体での番号付与で、管理番号2はその商品ごとの（枝番）番号付与ですよね？ >「If Me!商品数 = 「？」 And Clng(Me!管理番号2) >「？」 Then」については管理番号2の更新前イベントに Private Sub 管理番号2_BeforeUpdate(Cancel As Integer) If Me!商品数 < CLng(Me!管理番号2) Then MsgBox "まちがい" Cancel = True End If If DCount("*", "T仕入管理", _ "仕入日=#" & Me!仕入日 & "# and 管理番号1='" & Me!管理番号1 _ & "' and 管理番号2='" & Me!管理番号2 & "'") > 0 Then MsgBox "だぶり" Cancel = True 'Me.Undo End If End Sub で良さそう。ついでにコマンドクリック時のイベントは下記に差し替えてください。 Private Sub コマンド_Click() Dim Data1 As Date Dim oldBKNo1 As Long Dim newBKNo1 As Long Dim oldBKNo2 As Long Dim newBKNo2 As Long Dim shohinSuu As Long Dim basyoCd As String DoCmd.GoToRecord Record:=acLast '最終行の各値を変数に保存。 Data1 = IIf(IsNull(Me!仕入日), Date, Me!仕入日) oldBKNo1 = CLng(Nz(Me!管理番号1, 0)) oldBKNo2 = CLng(Nz(Me!管理番号2, 0)) shohinSuu = Nz(Me!商品数, 0) basyoCd = Nz(Me!仕入場所CD, "") If Me!商品数 <= DCount("管理番号1", "T仕入管理", "管理番号1='" & oldBKNo1 & "'") Then newBKNo1 = oldBKNo1 + 1 + Abs(oldBKNo1 Mod 9 = 0) + (oldBKNo1 = 0) '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~↑の9は本番では999に変えてください newBKNo2 = 1 shohinSuu = 0 basyoCd = "" Else newBKNo1 = oldBKNo1 newBKNo2 = oldBKNo2 + 1 End If '新規レコードに移動 Me.Recordset.AddNew ' '変数の値を加工して代入 Me!仕入日 = Data1 Me!管理番号1 = Right(CStr(newBKNo1), 1) '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~↑ここも1から3へ Me!管理番号2 = newBKNo2 Me!商品数 = shohinSuu Me!仕入場所CD = basyoCd Me!ID.SetFocus End Sub 上記のIf ～行にカーソルを持っていって、F9 を押すとその行が赤く反転します。もう一度押せば消えます。赤く反転している状態で、フォームをフォームビューに切り替えて、コマンドボタンを押せば先の行で止まります。F8 でステップ実行してゆくのでマウスカーソルを持ってゆけば変数などの値が確認できます。追っていってください。

layy · Answer

＞次のレコードの入力時に「管理番号2」が「1000」になってしまいます。

「管理番号１」と思いますが
「管理番号１」が「1000」になるってことは
元の値が「999」

「1000」で下３ケタが欲しい場合は、右から３桁取り出して「000」
さらに「001」であって欲しい場合はそれに＋１

「999」のときは＋２にして「1001」から「001」にする

＞何かいい方法は無いでしょうか？
自分オリジナルのやり方を考えるのも策の一つです。
がんばりましょう。

nicotinism · Answer

提示された（挿入前イベント）をこちらでも入れてみましたが
質問のようには再現できませんでした。
当初に提示されたサンプルを元に検証しています。
そちらでコメントアウトしたらどうなりますか？
他に原因があるかもです。確かめてみてください。
（あの挿入前イベントの処理は必要なんでしょうか？）

見返してみたら、コマンド時のイベント処理に別の見落としていた部分が
有りましたので夜にでもまた挙げるつもりです。

MA ACCESSデータベースに詳しい方お願いします。

> 1．入力フォームにて「仕入日_CD・管理暗号1・管理番号2」の三つのフィールドが重複した時にメッセージを出して更新できないようにする。

この回答への補足

「仕入日テーブル」の「仕入日_CD」の入力は

１と２

仕入日テーブル

１・入力する立場とすれば、誤入力の際にすぐにエラー表示された方がありがたいと思います。

イベントにＶＢＡで仕掛けるのはまだ早いでしょうか？。

よ～く分かりました。

＞次のレコードの入力時に「管理番号2」が「1000」になってしまいます。

提示された（挿入前イベント）をこちらでも入れてみましたが

この回答への補足

Private Sub 複数_Click() と Private Sub 次へ_Click()

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング