得意先を絞り込んでDM用の宛名ラベルを印刷するお手本
図5-8-1 受注商品から得意先を絞り込んで、売上高順にDM用の宛名ラベルを印刷する例
このサンプルデータベースは、受注商品から得意先を絞り込んで、リストボックスに売上高順の得意先リストを表示します。リストボックスに表示された得意先は、DM用の宛名ラベルとして印刷することができます。DM用ラベル印刷のコマンドボタンは、フィルタ実行ボタンをクリックしたとき、自動的に使用可能状態になります。また、フィルタ解除ボタンをクリックしたとき、自動的に使用不可状態になります。
サンプルデータベース（CH5-8.mdb）では、以下のノウハウを習得することができます。
◆ 受注商品から得意先を絞り込む方法
◆ 得意先の売上高上位ｎ件またはｎ％を表示する方法
◆ リストボックスに表示されている得意先の宛名ラベルを印刷する方法
◆ フォームに独自のプロパティを追加する方法
◆ レポートのレコードソースを実行時、ダイナミックに書き替える方法
● サンプルデータベースで使用するテーブル/クエリ/フォームを準備するには
１ Access を起動したら作業フォルダに、新規データベース CH5-8.mdbを作成します。
２ メニューから外部データの取り込み、インポートをクリックします。インポートのダイアログが表示されたら、CH2-3.mdbを選択してインポートボタンをクリックします。オブジェクトのインポートダイアログが表示されたら、テーブルタブをクリックしてすべて選択のボタンをクリックします。同様の手順で、クエリタブ、フォームタブそしてモジュールタブをクリックしてすべて選択のボタンをクリックします。最後にOKボタンをクリックしてインポートを完了させます。
３ データベースウィンドウのテーブル、クエリ、フォームそしてモジュールをクリックして全てのオブジェクトがインポートされているか確認します。
４ データベースウィンドウからクエリ
をクリックして、一覧からデザインビューでクエリを作成するをダブルクリックします。テーブルのダイアログが表示されたら、閉じるのボタンをクリックします。メニューからSQLのアイコン
をクリックして、SQLビューを表示させます。SQLビューには、SELECT;が表示されていますので、削除してからリスト5-8-1のqryFilteringCustomersByProductsのSQLを入力します。（SQLを入力する代わりに、CH5-8.mdbからqryFilteringCustomersByProductsをインポートすることもできます。）クエリをデザインビューに切り替えると、図5-8-2のように表示されます。クエリをqryFilteringCustomersByProductsの名称で保存して閉じます。
リスト5-8-1 qryFiltringCustomersByProductsのSQL
|
SELECT 得意先.得意先コード, 得意先.得意先名, 得意先.部署, 得意先.担当者名, 得意先.郵便番号, 得意先.都道府県, 得意先.住所1, 得意先.住所2, Sum(CCur([受注明細].[単価]*[数量])) AS 売上額 FROM 商品 INNER JOIN (得意先 INNER JOIN (受注 INNER JOIN 受注明細 ON 受注.受注コード = 受注明細.受注コード) ON 得意先.得意先コード = 受注.得意先コード) ON 商品.商品コード = 受注明細.商品コード GROUP BY 得意先.得意先コード, 得意先.得意先名, 得意先.部署, 得意先.担当者名, 得意先.郵便番号, 得意先.都道府県, 得意先.住所1, 得意先.住所2 HAVING (((Sum(CCur([受注明細].[単価]*[数量])))>0)) ORDER BY Sum(CCur([受注明細].[単価]*[数量])) DESC; |
図5-8-2 得意先別に売上高を集計するクエリ
５ テーブル、クエリ、フォームの準備ができたら、Accessを終了させます。
● 宛名ラベルを印刷するレポートを作成するには
１ Accessを起動したら作業フォルダに作成した CH5-8.mdbを開きます。
２ データベースウィンドウからレポート
をクリックしたら、新規作成ボタンをクリックします。レポートの新規作成ダイアログが表示されたら、リストボックスから宛名ラベルウィザードを選択します。基になるテーブル/クエリのコンボボックスからは、qryFilteringCustomersByProductsを選択してOKボタンをクリックします。ウィザードの指示に従ってA4縦に２列の宛名ラベルを印刷するレポートを作成します。
図5-8-3 A4縦に２列の宛名ラベルを印刷するレポートを作成した例
３ メニューからコードのアイコン
をクリックしてレポートモジュールを表示させます。CH5-8.mdbを開いたら、rptFilteringCustomersのレポートモジュールをコピーして貼り付けます。レポートモジュールのソースコードは、リスト5-8-2を参照してください。
４ レポートをrptFilteringCustomersの名称で保存して、Access を終了させます。
Sub Report_Open()イベントの処理
このイベントは、レポートが開かれるときに実行されます。このイベントでは、呼び出し元のフォームが開かれているか調べて、レポートのレコードソースを再設定します。再設定されたレコードソースには、フォームから入力したフィルタ条件が設定されています。レポートのレコードソースは、フォーム独自のプロパティRecordSource_FSメソッドを使用して取得します。
If IsOpen(conFormName) Then
Me.RecordSource =
Forms(conFormName).RecordSource_FS()
End If
Function IsOpen()関数
この関数は、引数で指定されたオブジェクトが開かれているか調べて、True/Falseを返します。引数で指定するオブジェクトのデフォルトは、フォームです。
リスト5-8-2 rptFilteringCustomersのレポートモジュール
|
Option Compare Database Option Explicit Const conFormName = "frmMultiSelectListBox" Private Sub Report_Open(Cancel As Integer) If IsOpen(conFormName) Then Me.RecordSource = Forms(conFormName).RecordSource_FS() End If End Sub Private Function IsOpen(strName As String, _ Optional intObjectType As AcObjectType = acForm) IsOpen = (SysCmd(acSysCmdGetObjectState, intObjectType, strName) <> 0) End Function |
● フォームに宛名ラベルを印刷するコマンドボタンを作成するには
１ Access を起動したら作業フォルダに作成した CH5-8.mdbを開きます。
２ データベースウィンドウからフォーム
をクリックしたら、frmMultiSelectListBoxをデザインモードで開きます。ツールボックスからコマンドボタンのアイコン
をクリックしたら、フォームの上位中央に配置します。コマンドボタンのプロパティを表示させたら、標題に”DM用ラベル印刷”を設定して、名前を”cmdPrint”に書き替えます。また、前景色を”255”の赤色、使用可能を”いいえ”に設定します。
ツールボックスからラベルのアイコン
をクリックしたら、ラベル印刷コマンドボタンの左側に配置します。ラベルのプロパティを表示させたら、標題に項番”⑧”を設定します。
図5-8-4 フォームに宛名ラベル印刷のコマンドボタンとラベル⑧を作成した例
３ メニューからコードのアイコンをクリックしてフォームモジュールを表示させます。CH5-8.mdbを開いたら、frmMultiSelectListBoxのフォームモジュールからSub cmdPrint_Click()とProperty Get RecordSource_FS()をコピーして貼り付けます。Sub cmdFilter_Click()に、cmdPrintのEnabledプロパティをTrueに設定するコードを追加します。Sub cmdReset_Click()には、cmdPrintのEnabledプロパティをFalseに設定するコードを追加します。frmMultiSelectListBoxに新たに追加するソースコードについては、リスト5-8-3を参照してください。
４ フォームをビューモードに切り替えたら、受注日、都道府県、商品アイテム等のフィルタ条件を入力してフィルタ実行ボタンをクリックします。リストボックスに得意先リストが表示されたら、DM用ラベル印刷ボタンをクリックして、ラベルをプレビュー印刷します。
図5-8-5 受注日、都道府県、商品アイテム（果樹１００％オレンジ）のフィルタ条件を入力して、得意先を絞り込んだ例（リストボックスには、売上高の降順に得意先が８件表示されます）
図5-8-6 絞り込んだ得意先を売上高の降順に宛名ラベルにプレビュー印刷した例
５ 宛名ラベルが正常に印刷されることを確認したらフォームを保存して、Access を終了させます。
Sub cmdPrint_Click()イベントの処理
このイベントは、宛名ラベル印刷のコマンドボタンをクリックしたときに実行されます。このイベントでは、宛名ラベル用のSQLを生成してモジュールレベルのメモリ変数(mstrSQL)に格納します。このメモリ変数は、RecordSource_FS()プロパティで参照します。宛名ラベル印刷用のSQLには、受注日、都道府県、商品アイテムと、レコード件数を制約するTOP ｎのオプションを指定することができます。
Sub cmdFilter_Click()イベントの処理
このイベントは、フィルタ実行ボタンがクリックされたときに実行されます。このイベントでは、cmdPrintのEnabedプロパティをTreuに設定して使用可能状態にします。
Sub cmdReset_Click()イベントの処理
このイベントは、フィルタ解除ボタンがクリックされたときに実行されます。このイベントでは、cmdPrintのEnabledプロパティをFalseに設定して使用不可の状態にします。
Property Get RecordSource_FS()プロパティの処理
このプロパティは、宛名ラベルを印刷するためのレコードソースを取得します。レコードソースは、cmdPrint_Clickイベントにて生成して、メモリ変数mstrSQLに格納しておきます。
リスト5-8-3 frmMultiSelectListBoxに新たに追加するコード
|
Private Sub cmdPrint_Click() Dim intI As Integer Dim intItems As Integer Dim varSelected As Variant Dim strSQL As String Dim strTop As String Dim strWhere As String intItems = mMS.AvailableCount If intItems = 0 Then
' Select All varSelected =
Null Else varSelected =
mMS.SelectedItems(0)
' PrimaryKey End If With Me If Nz(.txtTop) <>
"" Then
strTop = " TOP " & !txtTop
' TOP n
If .grpTop = 2 Then
strTop = strTop & " PERCENT " ' TOP n Percent
End If End If strSQL = "SELECT
" & strTop _
& " 得意先.得意先コード, 得意先.得意先名, 得意先.部署, 得意先.担当者名, 得意先.郵便番号,” _ & “ 得意先.都道府県, 得意先.住所1, 得意先.住所2, “ _ & “ Sum(CCur([受注明細].[単価]*[数量])) AS 売上額" _
& " FROM 商品 INNER JOIN (得意先 INNER JOIN” _ & “ (受注 INNER JOIN 受注明細 ON 受注.受注コード = 受注明細.受注コード) “ _ & “ ON 得意先.得意先コード = 受注.得意先コード) ON 商品.商品コード = 受注明細.商品コード" strWhere = "
WHERE ((受注.受注日) Between #" _
& .txtFromDate & "# And #" & .txtToDate & "#) AND" If .cboKen <> "(全国)" Then
strWhere = strWhere & " ((得意先.都道府県)='" & .cboKen & "')
AND" End If If
IsArray(varSelected) Then
strWhere = strWhere & " ("
For intI = LBound(varSelected) To UBound(varSelected)
strWhere = strWhere & "受注明細.商品コード=" &
varSelected(intI) & " OR "
Next intI
strSQL = strSQL & Left(strWhere, Len(strWhere) - 4) & ")"
' strip last OR Else
strSQL = strSQL & Left(strWhere, Len(strWhere) - 4)
' strip last AND End If strSQL = strSQL _
& " GROUP BY 得意先.得意先コード, 得意先.得意先名, 得意先.部署, 得意先.担当者名, “ _ & “ 得意先.郵便番号, 得意先.都道府県, 得意先.住所1, 得意先.住所2" _
& " HAVING (((Sum(CCur([受注明細].[単価] * [数量]))) > 0))" _
& " ORDER BY Sum(CCur([受注明細].[単価]*[数量])) DESC;" End With mstrSQL = strSQL On Error Resume Next DoCmd.OpenReport
conReportName, acViewPreview End Sub Public Property Get RecordSource_FS() As String RecordSource_FS = mstrSQL End Property Private Sub cmdFilter_Click() With Me ' Enable
.lstCustomer.Enabled = True
.txtCustomerInfo.Value = vbNullString
.txtCustomerInfo.Enabled = True
.cmdReset.Enabled = True
.cmdPrint.Enabled = True End With End Sub Private Sub cmdReset_Click()
With Me ' Disable
.txtCustomerInfo.Enabled = False
.cmdReset.Enabled = False
.cmdPrint.Enabled = False End With End Sub |