アクセスで重複しない商品コードの作成

質問

AccessでDBを作成中です。まだ初心者で勉強中ですが、お知恵を貸して頂けますでしょうか。
服の形、生地の種類、色をフォームでそれぞれを組み合わせて、重複しない商品コードを作成したいと考えています。
01（形）-05（生地）-03（色）-001（オート番号）＝010503001等

フォームでコンボックスリストから形・生地・色を選べるようにして、オートナンバーは自動、最後に自動的にこれら4つを組み合わせた商品コードが表示されればと思っていますが、フォームでこれが出来るためにはどうしたらよいでしょうか？
最後にオート番号を入れたのは、同じ組み合わせでも違う商品となる場合があるため、重複しないようにするためです。

３つのテーブルから出来なさそうだったので、考えたのは４つ目のテーブルを商品数テーブルとしてオート番号を振り、４つのテーブルからクエリをつくる。ここでづまずいてしまいました。

服のスタイル＿テーブル
・スタイルID
・スタイル名

生地種類＿テーブル
・生地ID
・生地名

色＿テーブル
・カラーID
・カラー名

商品数＿テーブル
・商品ID（00001～オートで）

また、４つもテーブルを作る必要がない、他の方法がある場合はご教授頂けますようお願いいたします。長文失礼致しました。

回答No.7

piroin654 · ベストアンサー

大変遅くなりました。

[補足その1]について

＞SQL文を訂正いただきましたが、SELECT 商品数＿テーブル2.となっておりましたが、
＞この2.はどこからきたものでしょうか？

すみません。これは私のほうでの確認したときのテーブル名で
SELECT 商品数＿テーブル　が正しいです。失礼しました。

＞２を除いて取り急ぎ、SQL文を貼り付けましたところ、問題なく実行されたようです

その通りです。

[補足その2]について

コード中の、
＞strSQL = "UPDATE T-Number SET T-Number.Number = [Number]+1;"
のところで、テーブル名を
T-Number
としていますが、「-」はOfficeあるいは他のプログラミング言語
などにおいても特別な記号とされています。したがって、テーブルなどの
名称においても、
生地種類＿テーブル
のように、「_]を用いることをお勧めします。
T_Number
なお、
＞T-Number
を
[T-Number]
のように[]で囲むと認識されるかもしれません。確認はしていません。
＞strSQL = "UPDATE T-Number SET T-Number.Number = [Number]+1;"
strSQL = "UPDATE [T-Number] SET [T-Number].Number = [Number]+1;"

[補足その4]について（たぶん3かもしれませんが）

＞テーブル等にも書式で00にはしているのですが、表示は一桁になって
＞しまっております。

テーブルでの書式は見かけの書式ですので、これを解決するためには、
フィールドの型をテキスト型にし、テーブルに入力するときに、

01
02
03

のように入力するか、あるいはコード中でFormat関数を用いて
変換する必要があります。
No6の中で、

'商品IDは5桁に設定
Me!商品ID = Format(DMax("番号", "番号_テーブル") + 1, "00000")
'商品コードの設定
Me!商品コード = Me!スタイルID.Column(0) & Me!生地ID.Column(0) & Me!カラーID.Column(0) & Me!商品ID

のようにしているところです。ここのところで、

Me!商品コード = Me!スタイルID.Column(0) & Me!生地ID.Column(0) & Me!カラーID.Column(0) & Me!商品ID

を

Me!商品コード = Format(Me!スタイルID.Column(0),"00") & Format(Me!生地ID.Column(0),"00") & Format(Me!カラーID.Column(0),"00")

& Me!商品ID

のようにするかです。この場合は対象の数値が一桁ならば数値の先頭に「0」が
付加されます。しかし二桁になると「0」は付加されません。二桁になる可能性が
あるならば、
"00"
を
"000"
にする必要があります。

以上が、補足への回答ですが、どのような人がどのように入力しても
所定の結果が得られるように徹底的にエラー処理をしたものもあります。
上記の補足への回答を確認され、テーブルの設定や入力方法を
確定されたら、また補足してください。よければエラー処理を
施したコードを表示します。

お礼

Piroin654様

ご返信とアドバイス有難うございました。
ご指摘頂いたテーブル名の(-)をアンダースコア(_）に変更しまして、他の箇所も注意しましたところ、作ることが出来ました！！
無知の私に長々お付き合い頂き、ご丁寧にお教えいただきましたことに心より感謝いたします。これからも勉強していきたいと思います。
有難うございました。

回答No.8

piroin654

No7のおわりのあたりのコードがずれていました。

Me!商品コード = Format(Me!スタイルID.Column(0),"00") & Format(Me!生地ID.Column(0),"00") & Format(Me!カラーID.Column(0),"00")　& Me!商品ID

です。

回答No.6

piroin654

No5です。
二つ訂正します。

(1)

「(2)　クエリの設定」のところでSQL文を以下に
置き換えてください。フォームでの並びを商品コード順
にしておきます。

SELECT 商品数＿テーブル2.スタイルID, 商品数＿テーブル2.生地ID, 商品数＿テーブル2.カラーID, 商品数＿テーブル2.商品ID, 商品数＿テーブル2.商品コード
FROM 商品数＿テーブル2
ORDER BY 商品数＿テーブル2.商品ID;

(2)

「(3) コードの設定」の更新後処理のコードを以下に
置き換えてください。

Private Sub カラー名_AfterUpdate()
　　Dim strSQL As String
　　Dim intRes As Integer

　　If IsNull(Me!商品ID) Then
　　　　'更新クエリのSQL文の設定
　　　　strSQL = "UPDATE 番号_テーブル SET 番号_テーブル.番号 = [番号]+1;"
　　　　'「番号_テーブル」の「番号」フィールドの最大値を取得しテキスト型に変換
　　　　'商品IDは5桁に設定
　　　　Me!商品ID = Format(DMax("番号", "番号_テーブル") + 1, "00000")
　　　　'商品コードの設定
　　　　Me!商品コード = Me!スタイルID.Column(0) & Me!生地ID.Column(0) & Me!カラーID.Column(0) & Me!商品ID
　　　　'「番号_テーブル」の番号フィールドの更新
　　　　DoCmd.RunSQL strSQL
　　Else
　　　　intRes = MsgBox("変更しますか？", vbYesNo + vbQuestion + vbDefaultButton1, "商品コードの変更")
　　　　'商品IDは変更しません
　　　　If intRes = vbYes Then
　　　　　　Me!商品コード = Me!スタイルID.Column(0) & Me!生地ID.Column(0) & Me!カラーID.Column(0) & strNum
　　　　End If

　　End If
End Sub

補足

早速のご返信誠に有難うございます。詳しくコードまで書いて頂き大変感謝しております！再度質問をお願いします。

1.　SQL文を訂正いただきましたが、SELECT 商品数＿テーブル2.となっておりましたが、この2.はどこからきたものでしょうか？
ご教授頂いた商品数＿テーブルは作りましたが、２というのはなく、２を除いて取り急ぎ、SQL文を貼り付けましたところ、問題なく実行されたようですが、念のためお教え頂けますと幸いです。

2.　フォームにてスタイル→生地→色を選ぶと、色を選択した際にエラーが出ました。エラー3144シナックスエラー　アップデート構文に誤りがあるとの事が出てしまいました。デバッグをクリックすると、何故かDoCmd.RunSQL strSQL部分が黄色くなっていました。

ちなみに下記が貼り付けた構文です。英語でフィールド名等を付けている為分かりにくいかもしれませんが、T-Number=番号＿テーブル等です。もしかしたら、こういう細かい名前を変更した時に誤ってしまったのかもしれません。色々試してみましたが、直す方法がわかりませんでした。お手数をお掛け致しますが、一度ご確認頂けますでしょうか？

Private Sub ColorID_AfterUpdate()

Dim strSQL As String
Dim intRes As Integer

If IsNull(Me!ProductID) Then
strSQL = "UPDATE T-Number SET T-Number.Number = [Number]+1;"
Me!ProductID = Format(DMax("Number", "T-Number") + 1, "00000")
Me!ProductCode = Me!StyleID.Column(0) & Me!MaterialID.Column(0) & Me!ColorID.Column(0) & Me!ProductID
DoCmd.RunSQL strSQL
Else
intRes = MsgBox("Do you want to change this?", vbYesNo + vbQuestion + vbDefaultButton1, "Change ProductCode")
If intRes = vbYes Then
Me!ProductCode = Me!StyleID.Column(0) & Me!MaterialID.Column(0) & Me!ColorID.Column(0) & strNum
End If

End If
End Sub

4.　スタイルID　3、　生地ID　6、　カラーID　13、　商品ID　00001の場合、361300001という結果が出るのですが、これを03061300001という様に、IDが一桁（3）の場合でも、03と表示するにはどうしたらよいでしょうか？商品数テーブルや各スタイル＿テーブル等にも書式で00にはしているのですが、表示は一桁になってしまっております。

色々とお手数をお掛けして申し訳ございませんが、何卒宜しくお願いいたします。

回答No.5

piroin654

No3です。補足への回答がおそくなりましたが。

一応、こちらで確認した方法を紹介します。

(1)　テーブルの設定

「商品数_テーブル」　を以下のように設定。
フィールドのデータ型はすべてテキスト型とします。

フィールド名
　　スタイルID
　　生地ID
　　カラーID
　　商品ID
　　商品コード

新しいテーブル「番号_テーブル」を以下のように設定。
フィールドのデータ型は数値型とします。

フィールド名
　　番号

なお、この「番号_テーブル」は設定したあと、テーブルを
開き、以下のように「0」を入力し、データを確定しておきます。
なお、テーブルの既定値が「0」の場合は、以下のように

　　番号
　　　0
　　　0

Nullの場合は

　　番号
　　　0

のように二行目がNullになります。

(2)　クエリの設定
以下のSQL文を新しいクエリのSQLビューに貼り付け、保存し、
名前をたとえば「Q商品コード_クエリ」という名前に
します。

SELECT 商品数＿テーブル.スタイル名, 商品数＿テーブル.生地名, 商品数＿テーブル.カラー名, 商品数＿テーブル.商品ID, 商品数＿テーブル.商品コード
FROM 商品数＿テーブル
ORDER BY 商品数＿テーブル.商品ID;

(3)　フォームの作成

新規作成から「オートフォーム表形式」を選択し、
「基になるテーブルまたはクエリの選択」で(1)で作成した
「Q商品コード_クエリ」を選択し、「OK」とします。
出来たフォームを適当な名前で保存します。

出来たフォームをデザインビューで開き、「スタイル名」、「生地名」、
「カラー名」の各フィールドのコントロールを各コントロールの上で
右クリックし「コントロールの種類の変更」から「コンボボックス」に
変更します。

「スタイル名」コンボボックスのプロパティを開き、「値集合ソース」を
以下のように設定します。

SELECT 服のスタイル＿テーブル.スタイルID, 服のスタイル＿テーブル.スタイル名
FROM 服のスタイル＿テーブル;

次に、
列数　2
列幅　0cm,2cm
連結列　1
リスト幅　3cm
とします。

同様に、「生地名」のプロパティの「値集合ソース」を

SELECT 生地種類＿テーブル.生地ID, 生地種類＿テーブル.生地名
FROM 生地種類＿テーブル;

「カラー名」のプロパティの「値集合ソース」を

SELECT 色＿テーブル.カラーID, 色＿テーブル.カラー名
FROM 色＿テーブル;

とし、この二つのコントロールについても同様に、

列数　2
列幅　0cm,2cm
連結列　1
リスト幅　3cm

とします。

(3)　コードの設定
フォームのコード表を開き、
Option Compare Database
Option Explicit

に続いて、

Dim strNum As String

を設定しておきます。

フォームをデザインビューを表示します。
「カラー名」のコントロールの上で右クリックし、
プロパティから、「フォーカス取得時」のイベントに、

Private Sub カラー名_Enter()
　　If Me!商品ID <> "" Then
　　　　strNum = Me!商品ID
　　End If
End Sub

のように設定します。

同じく、プロパティの「更新後処理」のイベントに、

Private Sub カラー名_AfterUpdate()
　　Dim strSQL As String
　　Dim intRes As Integer

　　If IsNull(Me!商品ID) Then
　　　　'更新クエリのSQL文の設定
　　　　strSQL = "UPDATE 番号_テーブル SET 番号_テーブル.番号 = [番号]+1;"
　　　　'「番号_テーブル」の「番号」フィールドの最大値を取得しテキスト型に変換
　　　　'商品IDは5桁に設定
　　　　Me!商品ID = Format(DMax("番号", "番号_テーブル") + 1, "00000")
　　　　'商品コードの設定
　　　　Me!商品コード = Me!スタイル名.Column(0) & Me!生地名.Column(0) & Me!カラー名.Column(0) & Me!商品ID
　　　　'「番号_テーブル」の番号フィールドの更新
　　　　DoCmd.RunSQL strSQL
　　Else
　　　　intRes = MsgBox("変更しますか？", vbYesNo + vbQuestion + vbDefaultButton1, "商品コードの変更")
　　　　'商品IDは変更しません
　　　　If intRes = vbYes Then
　　　　　　Me!商品コード = Me!スタイル名.Column(0) & Me!生地名.Column(0) & Me!カラー名.Column(0) & strNum
　　　　End If

　　End If
End Sub

のように設定します。

以上です。一応、設定した商品コードを途中で変更したりする
処理もいれましたが、なお改良の余地があるように思いますが、
とりあえず、これで質問への最低限の回答にはなっていると、
思いますが。

なお、「商品数＿テーブル」へ格納されるデータは各IDを格納し、
フォームでは名称を表示するために、コンボボックスのプロパティで
連結列を1にしています。これを2にするとそれぞれの名称が
格納されます。
したがって、フォームでのラベルが「スタイルID」や「生地ID」などに
なっていますが、見た目を考えればデザインビューでフォームの
各フィールドのラベルのプロパティで「標題」を「スタイル名」などに
変更すればいいです。

わからないことがあれば補足してください。

回答No.4

chayamati

各所に無理なところもありますが
その最たるものが
001（オート番号）：これがテーブル設計で主キーが
定義されていないと自動採番される項目ですね
このフィールドは変更不可能ですし重複は許されないのが普通です。
またフィールドのデータ形式は長整数型で１９桁になります。

商品コードは商品マスターテーブルの一つの項目と思います。
このテーブルの最初の項目がIDで主キーですね

直球でお答えします。
商品マスタテーブルに（形）（生地）（色）（枝No.）が抜けていれば
追加してこれら4項目を一括りにして主キーに設定します。
テーブルのデザインビューで、CTRLを押下したまま
（形）（生地）（色）（枝番号）のフィールドセレクター
クリックして最後に主キーアイコンクリックで主キーが入れ替わります。

商品コードはデータ入力のイベントで文字列の結合をします。

一例です。
Private Sub 色_LostFocus()
商品コード = 形 & 生地 & 色 & 枝No.
End Sub

商品コードは重複なしと設定します。

追伸
IDが主キーから外れたとき重複ありになりますので重複なしに戻します。
IDと商品コードは後にシステムを見直すときに必要とする項目なので残してください

回答No.3

piroin654

記述内容が統一されていないので確認ですが、

01（形）-05（生地）-03（色）-001（オート番号）＝010503001等

はテーブルの定義からすると、

01（スタイルID）-05（生地ID）-03（カラーID）-001（商品ID）＝010503001等

ということですか？
それで、「商品ID」は自動で設定したいということですか？
また、「商品ID（00001～オートで）」となっていますが、
商品IDはテキスト型のようなので、一応商品数がどのくらい
発生するのかを設定しておかないと桁が足らなくなる
可能性があるのでテーブル定義のように5桁にしておく
ということでいいのでしょうか？

補足

お返事が大変遅くなりまして申し訳ありません。3週間程海外におりアクセスできない状況でした。ご確認頂いた点ですが、全てpiroin654様のお察しの通りです。
現在のところ、商品数がどのくらいになるか分かりませんが、行数は5桁程でいいのではないかと思っております。

回答No.2

parurun123

メソッドの中身は、
コード.VALUE=生地+"-"+色+"-"+スタイル+"-"+TEXT(オート番号)
で。
出先のため動作確認してませんが、やってみてください。

補足

Parurun123様

ご返信有難うございます。ご指示の通り入力してみましたが、残念ながら動作しませんでした。お教えいただいたのと、最後の＋TEXT（オート番号）というところを、＋Auto または、＋Auto（オート番号）等にも変更してみましたが、出来ませんでした。　もし他のアイディアがあるようでしたら、ご教授頂けますと幸いです。お忙しい中有難うございます。

回答No.1

parurun123

そもそもオート番号を振るのに、それとは別に商品コードを作る理由がよく解りませんが。
どうしても商品コードが必要なのであれば、フォームのスタイル、生地、色の各項目に、値の変更イベントをキャッチして商品コードを編集するコードを書けばよろしいかと。
フォームをデザインビューにして、それぞれのプロパティシートを表示し、イベントの更新後処理としてコードビルダーを起動します。
自動で作成されたメソッドの中に、商品コードを更新するソースを書きます。
これで、商品コードの欄には、常に番号、スタイル、生地、色から編集した文字列が格納されるように。

補足

Parurun123様

早速のご教授誠に有難うございます。
商品コードから一目で、スタイル、生地、色が分かるようにしたい為に、オート番号の他にこの様な形で商品コードを設定したい為です。

4つ目のテーブルで商品コードを設定出来るように、Look Upで3つのテーブルから情報を得られるようにし、そこからフォームを作成しています。

コードビルダーの起動までは分かるのですが、まだVBA・マクロまで勉強が及んでいない為、調べてみましたが該当するようなものが見つかりませんでした。
大変お手数ではございますが、どのように書いたらよいかをお教え頂けませんでしょうか？