ACCESS VBAにてDlookupとDmin

質問

質問させてください。

ACCESSで通信販売のための商品管理と発送システムを作り運用しております。
今回梱包作業を少しでも簡素化するためにデータベースに登録されている商品サイズをもとに梱包用の段ボールの種類を自動で取得したいと思っております。

段ボールなどの種類は15種類ほど使用しており、現状その都度商品にフィットするものを探して作業しております。

現在のACCESSの内容は以下の内容です（説明しやすいように必要な部分のみ記載しております。）

【T商品】
ID
商品名
SizeW
SizeD
SizeH
その他価格情報や在庫状況など

【T梱包材マスター】
ID
梱包材名（当社での呼び名）
内寸1
内寸2
内寸3
内寸三辺合計（上記1-3の合計の集計フィールド）
梱包材重量

今回、フォーム上で商品の三辺のサイズを入力後に商品サイズのすべての辺より大きく、なおかつ一番軽いの梱包材を取得したいと考えています。

要するに商品を梱包できる箱で一番軽おい箱を自動で取得するということです。

T商品のW>D>H
T梱包材マスターの内寸1>内寸2>内寸3
と実際の縦横高さとは無関係にサイズが大きい順に入力はされています。

T商品をデータソースにしたフォームではルックアップで表示もしますので、T梱包材マスターのIDで取得したいと思います。

DMinとDlooupの組み合わせかな？と思うのですが。。。

DMin("梱包材ID", "T梱包材マスター", "内寸1 >" & Me.W & " And " & "内寸2 >" & Me.D & " And " & "内寸3 >" & Me.H)
例えばこんな感じですとすべてのサイズをクリアしたIDの一番小さいもの（一番登録の早いもの）というものになってしまうと思うのですが、条件やDMinで取得するとしたらどのようなVBAになるのでしょうか？

よろしくお願いします。

回答No.8

山田 太郎（@f_a_007） · ベストアンサー

【お詫び】完全に質問内容を曲解していました。

要は、添付図のようなイメージだったんですね。

１、[梱包材台帳_ID]をドロップボックスにしたい。
２、加えて、最適サイズの[梱包材台帳_ID]を自動決定したい。

よく読めば、この１、２は理解できたのに・・・。

【[梱包材台帳_ID]を自動決定するのは難儀な話】

１、自動決定する指針列が要る。例えば、(1)キッチリサイズ、(2)ユトリサイズなど。
２、加えて、コンボボックスの各列と比較するVBAを書く必要がある。
３、コンボボックスに格納しておくデータは精々に100レコード程度。

などの理由により、コンボボックス路線は断念されたが吉。

【一般的な手法】

１、[梱包材台帳_ID]の初期値はヌル。
２、[梱包材台帳_ID]の値がヌルだったら”梱包サイズ選択フォーム”を開く。
３、選択したら、[[梱包材台帳_ID]と[梱包材サイズ]を更新してフォームを閉じる。
４、再選択する場合には、[梱包材台帳_ID]の値を消す。

【難儀なやり方】

Me.梱包材台帳_ID=DBLookup(SQL文、1)

難儀なやり方と言っても、１番目を参照するのか２番目を参照するのかを指定できる関数を利用するだけです。もちろん、そのための列[選択指針]は必要とならますが・・・。

PS、【一般的な手法】でいくのであれば・・・。

１、フォームを開くには？
２、開いたフォームに該当レコードを表示するには？
３、選んだレコードに基づいて呼び出し元を更新するには？

などの課題をクリアする必要があるかと・・・。ですから、この質問はこれで閉じて仕切り直しがよいと思います。

お礼

深夜にも関わらずいろいろサンプルも添えてご回答ありがとうございます。SQLの具体的なサンプルもいただきましたので、そちらも参考にしてもう少し取得方法も含め見直してみたいと思います。

本当に親切にありがとうございました。

回答No.7

山田 太郎（@f_a_007）

【添付図の訂正】

回答No.6

山田 太郎（@f_a_007）

【補足】ドロップボックスでは・・・。

添付図のようにどちらも同じ結果を得ています。

SELECT TOP 2 * FROM 梱包材台帳
　　WHERE (((梱包材台帳.内寸1)>Forms!フォーム1!sizeW) And
　　　　　　　　 ((梱包材台帳.内寸2)>Forms!フォーム1!sizeD) And
　　　　　　　　((梱包材台帳.内寸3)>Forms!フォーム1!sizeH))
　　 ORDER BY 梱包材台帳.重量;

ビルドするとこのような値集合ソースを生成します。ドロップボックスに幾つの列を表示するのかは定かではないので＊を使っていますが、これで目的は達成できるのではないでしょうか？？？？？

補足

何度も何度も詳しい回答ありがとうございます。
値集合ソースのデザインビュー、SQLビューともに自分の環境に置き換えてエラーは出ないのですが、ドロップダウンのリストには何も表示されないという状況です。

値集合ソースのデータシートビューでも梱包材のリストは1件も表示されておらず、（新規）というレコードができているだけという状況です。

ちなみにドロップダウンのコンボボックスはサブフォーム内に配置されており、以下のSQLを記入してみました。

SELECT TOP 2 T梱包材マスター.梱包材ID, T梱包材マスター.梱包材名, T梱包材マスター.梱包材重量, T梱包材マスター.内寸1, T梱包材マスター.内寸2, T梱包材マスター.内寸3
FROM T梱包材マスター
WHERE (((T梱包材マスター.内寸1)>[Forms]![Fアイテム]![Fアイテムリスト]![sizeW]) AND ((T梱包材マスター.内寸2)>[Forms]![Fアイテム]![Fアイテムリスト]![sizeD]) AND ((T梱包材マスター.内寸3)>[Forms]![Fアイテム]![Fアイテムリスト]![sizeH]))
ORDER BY T梱包材マスター.梱包材重量;

Fアイテムというフォームの中に、Fアイテムリスト（帳票フォーム）というサブフォームを配置し、その中にドロップダウン（コンボボックス）があります。

回答No.5

山田 太郎（@f_a_007）

【お詫び】バグがありました。

'
'　オプションによって選択する材料を決定
'
Me.リスト_該当する梱包材料.Value = Me.リスト_該当する梱包材料.Column(0, Me.選択オプション - 1)

先の添付図は、たまたま選択したレコードのIDが１と２だったので正常に動いていました。が、正しくは上のようです。

補足

ご回答ありがとうございます。
サンプルまで作っていただき感謝いたします。
箱データの取得は理想のものができそうです。

ただ私のわがままになってしまいますが、あくまでコンボボックスで取得したくて、回答いただきましたサンプルコードをもとに何か参考にして運用できないかと考えてみました。

今現在は、箱を選択するコンボボックスはルックアップウィザードでT梱包材マスターをルックアップしておりますが、よくよく考えたらそもそも商品が収まらない箱が表示される必要がないなと考えました。

そこでf_a_007様に書いていただいたSQLをもとにフォーム上の梱包材を選択するコンボボックスのプロパティーで
コントロールソース：梱包材（T商品）
集合ソース：ここにSQLを参考に記述すればいいかなと考えました。
SQLをいきなりは書けそうにないので、集合ソースのクエリビルダーで、
追加フィールドは、T梱包材マスターから
・梱包材ID
・梱包材名
・梱包材重量（並べ替え昇順（軽い順））
・内寸1（抽出条件）
・内寸2（抽出条件）
・内寸3（抽出条件）
としてみました。

上記の（抽出条件）のところにWHERE句を書けばうまくいきそうなのですが、「>Me.sizeW」などと入れてもうまくいきませんでした。
表示モードに切り替えると、Me.sizeWの値を入力するように求められる感じです。

そもそもコンボボックスの値集合ソースでSQLで現在のレコードのsizeWなどをWHERE条件で使用できるのかなど、行き詰っております。

TOP3などどしてご提案いただきましたように、候補を3つくらい表示できるのが便利そうなのでそちらはぜひ採用して検討しております。

回答No.4

山田 太郎（@f_a_007）

＞SizeW < 内寸1
＞SizeD < 内寸2
＞SizeH < 内寸3
＞をすべてクリアした段ボールの中で一番軽い箱のIDを取得がしたいです。

SELECT TOP 1 * FROM 梱包材台帳
　　WHERE (内寸1>18 AND 内寸2>19 AND 内寸3>20)
　　ORDER BY 重量

となりますが・・・。ただ・・・。

SELECT TOP 1 * FROM 梱包材台帳
　　WHERE (内寸1>18 AND 内寸2>19 AND 内寸3>20)
　　　　　　　　OR
　　　　　　　 (内寸1>19 AND 内寸2>18 AND 内寸3>20)
　　ORDER BY 重量

と、天地は厳守で左右と奥行とはどちらに合致してもOKなのでは？？？？？

Q、上記のようなSQLを書く場合どこに書くものなのでしょうか？
A、通常はイベントプロシージャに書くことになるかと思います。

例えば、添付図のようなフォームだと仮定すると・・・。

１、幅、奥行、天地の寸法を入力する。
２、選択オプションは、キッチリとユトリの二つ。
３、検索結果はリストボックスに表示する。

テストでは、10×10×10に合致するユトリサイズを表示すると共に選択しています。このフォームのコードは次のようです。

Option Compare Database
Option Explicit

Private Sub Form_Load()

End Sub

Private Sub sizeD_AfterUpdate()
　　DoSelect
End Sub

Private Sub sizeH_AfterUpdate()
　　DoSelect
End Sub

Private Sub sizeW_AfterUpdate()
　　DoSelect
End Sub

Private Sub DoSelect()
　　Dim intW As Integer
　　Dim intD As Integer
　　Dim intH As Integer
　　Dim strSQL As String

　　intW = Val(Me.sizeW & "")
　　intD = Val(Me.sizeD & "")
　　intH = Val(Me.sizeH & "")
　　If intW * intD * intH > 0 Then
　　　　'
　　　　' SQL文の基本形
　　　　'
　　　　strSQL = "SELECT TOP 2 * FROM 梱包材台帳 WHERE " & _
　　　　　　　　 "内寸1> WWWWW AND 内寸2> DDDDD AND 内寸3> HHHHH " & _
　　　　　　　　 "ORDER BY 重量"
　　　　'
　　　　' SQL文のWWWWW、DDDDD、HHHHHを入力値と置換
　　　　'
　　　　strSQL = Replace(strSQL, "WWWWW", Trim(intW))
　　　　strSQL = Replace(strSQL, "DDDDD", Trim(intD))
　　　　strSQL = Replace(strSQL, "HHHHH", Trim(intH))
　　　　'
　　　　' リストボックスに該当する梱包材料のトップ２を表示
　　　　'
　　　　Me.リスト_該当する梱包材料.RowSource = DBSelect(strSQL)
　　　　'
　　　　'　オプションによって選択する材料を決定
　　　　'
　　　　Me.リスト_該当する梱包材料.Value = Me.選択オプション
　　End If
End Sub

なお、ここではVBAを簡略化するために自作のDBSelect()を用いています。また、その兼ね合いでリストボックスのタイプは”リスト”にしています。

【選択オプションと求める答えとの関係】

ここでは、一応、リストボックスの値が求めるものになるようにしています。そうすることで２番目を求めるというSQL文の作成を回避しています。ユーザーには、キッチリとユトリとの双方は目で確認できるので、それで良いという考えです。

PS、DBSelect()

Public Function DBSelect(ByVal strQuerySQL As String, _
　　　　　　　　　　　　 Optional colDelimita As String = ";", _
　　　　　　　　　　　　 Optional rowDelimita As String = ";") As String
On Error GoTo Err_DBSelect
　　Dim i　　　　　 As Integer
　　Dim J　　　　　 As Integer
　　Dim R　　　　　 As Integer ' 行インデックス
　　Dim N　　　　　 As Integer ' 行総数 - 1
　　Dim rst　　　　 As ADODB.Recordset
　　Dim fld　　　　 As ADODB.Field
　　Dim strList　　 As String　' 全てのデータを区切子で連結して格納

　　Set rst = New ADODB.Recordset
　　With rst
　　　　.Open strQuerySQL, _
　　　　　　　CurrentProject.Connection, _
　　　　　　　adOpenStatic, _
　　　　　　　adLockReadOnly
　　　　If Not .BOF Then
　　　　　　N = .RecordCount - 1
　　　　　　.MoveFirst
　　　　　　For R = 0 To N
　　　　　　　　For Each fld In .Fields
　　　　　　　　　　With fld
　　　　　　　　　　　　strList = strList & .Value & colDelimita
　　　　　　　　　　End With
　　　　　　　　Next fld
　　　　　　　　strList = Mid(strList, 1, Len(strList) - 1) & rowDelimita
　　　　　　　　.MoveNext
　　　　　　Next R
　　　　Else
　　　　　　strList = ""
　　　　End If
　　End With
Exit_DBSelect:
On Error Resume Next
　　rst.Close
　　Set rst = Nothing
　　DBSelect = IIf(Len(strList) > 0, Replace(strList & "[END]", rowDelimita & "[END]", ""), "")
　　Exit Function
Err_DBSelect:
　　MsgBox "SELECT 文の実行時にエラーが発生しました。(DBSelect)" & Chr(13) & Chr(13) & _
　　　　　　"・Err.Description=" & Err.Description & Chr(13) & _
　　　　　　"・SQL Text=" & strQuerySQL, _
　　　　　　vbExclamation, " 関数エラーメッセージ"
　　Resume Exit_DBSelect
End Function

回答No.3

山田 太郎（@f_a_007）

Q、どのようなVBAになるのでしょうか？
A、次のようなSELECT文でOKかも！？

添付図では、入力されたsizeW、sizeD、sizeHを格納できる梱包材をSELECT文にて表示しています。

SELECT TOP 1 * FROM 梱包材台帳
　　WHERE
　　(内寸1>18 AND 内寸2>19 AND 内寸3>20) OR
　　(内寸1>18 AND 内寸2>20 AND 内寸3>19) OR
　　(内寸1>19 AND 内寸2>18 AND 内寸3>20) OR
　　(内寸1>19 AND 内寸2>20 AND 内寸3>18) OR
　　(内寸1>20 AND 内寸2>18 AND 内寸3>19) OR
　　(内寸1>20 AND 内寸2>19 AND 内寸3>18)
　　ORDER BY 重量

１、６個の比較のいずれかをクリアしている梱包材を
２、重量昇順で並び替え
３、その先頭レコードを取得する。

で良いと思うが・・・。

なお、添付図ではTop句を指定しないで合致するレコードを全て表示しています。用意したテストデータは二種類。いずれも成功したので、私の理解に間違いがなければ・・・。多分、提示したSQL文でOK。（多分）

補足

ご回答ありがとうございます。
なるほど、そういう方法もあるのですね。

どういう方法でどういう風に取得したいか私の最初の質問がアバウトすぎましたので少し追加させていただきますと。

テーブル-T商品には梱包材フィールドを持っており、ルックアップでT梱包材マスターのIDが保存されております。

T梱包材マスターのID：1には「オートフィット」という梱包材名のものを登録して、ID：2以降に実際の段ボールのデータを登録してあります。

T商品をソースにしたフォーム-F商品上で商品サイズWHDを入力し、梱包材フィールドのコンボボックスのルックアップの更新後VBAでID：0のオートフィットを選んだ場合にコンボボックスの値を自動で取得した箱のIDで上書きしたいというものです。
この仕様にしたいのは、段ボールを自動で取得した際に、商品によっては頑丈に梱包したいため、最低サイズの一回り大きいサイズの箱に手動で設定したい場合などに対応したいためです。

ちなみにあまりSQL句などを手動で書いたことがなく、上記のようなSQLを書く場合どこに書くものなのでしょうか？クエリを一つ作る必要があるのでしょうか？

チンプンカンプンな再質問ですみません。

回答No.2

naoto0216

＃1です。
失礼しました。
＞入力後に商品サイズのすべての辺より大きく、なおかつ一番軽いの梱包材を取得したいと考えています。

ということなので、ちょっと極端ではありますが
内寸1　51
内寸2　81
内寸3　101
の梱包材があった場合、梱包できるか否かを教えて下さい。

補足

最初のご回答の補足に書かせていただきましたが、
内寸1が必ず一番大きくなります。
ただ内寸1=内寸2など2辺のサイズが同じ箱を今後導入する可能性はあります。

>極端ではありますが

これは1mmオーバーしても無理やりに梱包できるか？
ということでしょうか？
両テーブル内のサイズはどちらも若干余裕を見て入力していますので梱包作業的には梱包できてしまうと思いますが、そこは無視して1mmでも駄目なものはダメという条件で絞り込みたいと思っております。

回答No.1

naoto0216

寸法は以下のように対応してるのでしょうか。
SizeW--内寸1
SizeD--内寸2
SizeH--内寸3

例えば、
SizeW　100
SizeD　　50
SizeH　　80
という商品があった場合、
内寸1　50
内寸2　80
内寸3　100
の梱包材に梱包不可なのでしょうか。
それとも商品を回転する？なりして梱包可能なのでしょうか。

補足

はい。
SizeW--内寸1
SizeD--内寸2
SizeH--内寸3
という比較のみで考えております。

内寸1　50
内寸2　80
内寸3　100

のようにはならないように商品情報のテーブルにはW>D>Hの順で寸法を、梱包材テーブルには内寸1＞2＞3の順でサイズを登録してあります。
単純に
SizeW < 内寸1
SizeD < 内寸2
SizeH < 内寸3
をすべてクリアした段ボールの中で一番軽い箱のIDを取得がしたいです。