ホチキス先生の「プログラマーと呼ばれたい」

InfoPath & SQL Server !

Posts Tagged ‘VBA

Access – レコードソースを設定せずテキストボックスに入力した文字列によってSQL文でデータを絞り込みVBAで動的にテーブルのレコードを取得する

leave a comment »

前回、前々回に、アクセスのフォームやレポートでレコードソースをプロパティに設定せず、VBAからSQL文によってレコードソースを指定してデータを取得する方法の概要をまとめてみた。

「Access – レコードソースを設定せずVBAで動的にテーブルのレコードを取得する」
https://matsumotoyoshio.wordpress.com/2011/12/25/access-%e3%83%ac%e3%82%b3%e3%83%bc%e3%83%89%e3%82%bd%e3%83%bc%e3%82%b9%e3%82%92%e8%a8%ad%e5%ae%9a%e3%81%9b%e3%81%9avba%e3%81%a7%e3%83%86%e3%83%bc%e3%83%96%e3%83%ab%e3%81%ae%e3%83%ac%e3%82%b3/

「Access – レコードソースを設定せずSQL文でデータを絞り込みVBAで動的にテーブルのレコードを取得する」
https://matsumotoyoshio.wordpress.com/2011/12/25/access-%e3%83%ac%e3%82%b3%e3%83%bc%e3%83%89%e3%82%bd%e3%83%bc%e3%82%b9%e3%82%92%e8%a8%ad%e5%ae%9a%e3%81%9b%e3%81%9asql%e6%96%87%e3%81%a7%e3%83%87%e3%83%bc%e3%82%bf%e3%82%92%e7%b5%9e%e3%82%8a/

今回はVBAによってフォームのテキストボックスに入力した文字列によってSQL文でデータを絞り込みVBAで動的にテーブルのレコードを取得する方法をまとめてみる。

テーブルのサンプルデータは、テーブル構造は前回までの説明で使ったものと同じだが、少しデータを増やした以下のようなものを使う。

Access_SQL_023

<Fig 1 : 絞り込み検索をするテストデータ>

検索するためのフォームは、新しい空白のフォームを作成し、フォームヘッダーを作成する。フォームヘッダーにはテキストボックスをひとつ、ボタンを2つ作成する。テキストボックスは非連結で、名前を「検索氏名」としておく。ボタンの表題には、それぞれ「全文一致」と「部分一致」とする。

詳細にはデータ表示のためのテキストボックスを3つ作成する。フォームのプロパティにはレコードソースを設定しないが、それぞれのテキストボックスのプロパティでコントロールソースには各フィールド、すなわち「学籍番号」、「氏名」、「性別」を記述しておく。

Access_SQL_024

<Fig 2 : データを検索して表示するフォームをデザインする>

フォームのデザインができたら、ボタンのイベントハンドラにVBAコードを書こう。コードを書くにはボタンを選択してプロパティシートの「イベント」タブで「クリック時」のイベントを「・・・」をクリックする。「ビルダーの選択」ウィンドウが表示されたら「コードビルダー」を選択して「OK」をクリックする。

「全文一致」ボタンのイベントハンドラには次のコードを記述する。

———————————————————————————

Me.RecordSource = "select * from 生徒1 where 氏名 = ‘" & 検索氏名 & "’"

———————————————————————————

ここで記述するSQL文は、select * from 生徒1 where 氏名 = (「検索氏名」テキストボックスのテキスト)、であるが、「検索氏名」テキストボックスの記述方法は、ダブルクォーテーションで囲まれたSQL文の外に「&」演算子で接続する。検索氏名の実態はテキストなのでシングルクォーテーションで囲む。したがって「&」で挟まれる「検索氏名」テキストボックスの記述の前後は、シングルクォーテーションとダブルクォーテーションで挟まれることになる。

次に「部分一致」ボタンのイベントハンドラには次のコードを記述する。

———————————————————————————

Me.RecordSource = "select * from 生徒1 where 氏名 like ‘*" & 検索氏名 & "*’"

———————————————————————————

ここで記述するSQL文は、select * from 生徒1 where 氏名 like *(「検索氏名」テキストボックスのテキスト)*、である。「検索氏名」に入力された文字列の前後を「*」で囲み、文字列を含む指定にする。上と同様に「検索氏名」テキストボックスの記述方法は、ダブルクォーテーションで囲まれたSQL文の外に「&」演算子で接続するので、「&」で挟まれる「検索氏名」テキストボックスの記述の前後は、シングルクォーテーションとアステリスク、そしてダブルクォーテーションで挟まれることになる。

VBAコードの全文は次のようになる。

———————————————————————————

Private Sub コマンド1_Click()
    Me.RecordSource = "select * from 生徒1 where 氏名 = ‘" & 検索氏名 & "’"
End Sub

Private Sub コマンド2_Click()
    Me.RecordSource = "select * from 生徒1 where 氏名 like ‘*" & 検索氏名 & "*’"
End Sub

———————————————————————————

Access_SQL_025
<Fig 3 : ボタンのイベントハンドラにVBAでSQL文を記述する>

コードを記述したらフォームを開いてみよう。フォームが開いた時点ではレコードソースが指定されていないので、データは何も表示されず、テキストボックスは「#Name?」となってエラー表示になる。

「検索氏名」に文字列を入力し、「全文一致」か「部分一致」のボタンをクリックする。たとえば「織田 信長」のようにフルネームを入力し「全文一致」ボタンかまたは「部分一致」ボタンをクリックすると「織田 信長」のレコードだけが表示される。「徳川」のように氏名の一部だけ入力したときは、「全文一致」では何も表示されず、「部分一致」のボタンをクリックしたときに、部分一致したデータが表示される。

Access_SQL_026

<Fig 4 : フォーム「生徒フォーム」を開いてもデータは何も表示されない>

Access_SQL_027

<Fig 5 : 「検索氏名」テキストボックスに「織田 信長」と入力して「全文一致」または「部分一致」ボタンをクリックした>

Access_SQL_028

<Fig 6 : 「検索氏名」テキストボックスに「徳川」と入力して「全文一致」ボタンをクリックしてもデータは表示されない>

Access_SQL_029

<Fig 7 : 「検索氏名」テキストボックスに「徳川」と入力して「部分一致」ボタンをクリックして3件のレコードが表示された>

Written by Yoshio Matsumoto

2011年12月26日 at 11:32 PM

Access – レコードソースを設定せずSQL文でデータを絞り込みVBAで動的にテーブルのレコードを取得する

with 3 comments

Accessでフォームやレポートを作りデータを表示するとき、一般的にはレコードソースをフォームのプロパティに指定するが、レコードソースを設定せずにフォームやレポートを作成し、VBAで動的にレコードソースを指定するとSQL文が使える。SQLに熟練しているなら、この方法で自由にレコードソースを扱うことができ、応用しやすい。

レコードソースを設定せずにフォームを作りVBAでSQL文を書き動的にデータを取得する方法の概要は以下のページを見てほしい。

「Access – レコードソースを設定せずVBAで動的にテーブルのレコードを取得する」
https://matsumotoyoshio.wordpress.com/2011/12/25/access-%e3%83%ac%e3%82%b3%e3%83%bc%e3%83%89%e3%82%bd%e3%83%bc%e3%82%b9%e3%82%92%e8%a8%ad%e5%ae%9a%e3%81%9b%e3%81%9avba%e3%81%a7%e3%83%86%e3%83%bc%e3%83%96%e3%83%ab%e3%81%ae%e3%83%ac%e3%82%b3/

さて、ここで使うSQL文にデータを絞り込むものを使ってみよう。たとえば「男」のデータだけ取得する次のようなSQL文だ。

——————————————————————————–

select 学籍番号,氏名,性別 from 生徒1 where 性別 = 男

——————————————————————————–

まずテーブル「生徒1」に次のようなデータを用意する。

Access_SQL_016

<Fig 1 : テーブル「生徒1」に用意したサンプルデータ>

フォームの作成でレコードソースを指定せずに「生徒フォーム」を作成する。新しいフォームができたらデザインビューにし、「フォームヘッダー/フッター」を作り、フォームの既定のビューを「帳票フォーム」に変更する。フォームヘッダーと詳細の高さを調節する。

次に「詳細」エリアにテキストボックスを3つ作る。各テキストボックスは、プロパティの「データ」タブ「コントロールソース」に、それぞれ表示させたいフィールド名「学籍番号」、「氏名」、「性別」を記述する。通常レコードソースを設定したフォームの編集では、ここでテーブルのレコードソースが表示されドロップダウンリストから選択できるが、ここではレコードソースが設定していないのでリスト表示はされない。キーボードから文字入力でフィールドを設定しておく。

そしてフォームヘッダーには3つボタンを作成する。ボタンの「表題」はそれぞれ「全部」、「男だけ」、「女だけ」としておこう。

Access_SQL_017

<Fig 2 : データを取得するフォームをデザインする>

そしてボタンのクリックイベントに以下のVBAコードを記述する。このときSQL文では、スタティックな文字列はシングルクォーテーション「’」で囲む。

———————————————————————————

Private Sub コマンド1_Click()
    Me.RecordSource = "select 学籍番号,氏名,性別 from 生徒1"
End Sub

Private Sub コマンド2_Click()
    Me.RecordSource = "select 学籍番号,氏名,性別 from 生徒1 where 性別 = ‘男’"
End Sub

Private Sub コマンド3_Click()
    Me.RecordSource = "select 学籍番号,氏名,性別 from 生徒1 where 性別 = ‘女’"
End Sub

———————————————————————————

Access_SQL_018

<Fig 3 : ボタンのクリックイベントにVBAでSQL文を書く>

このフォームを実行すると、フォームを開いたときにはレコードソースが設定されていないのでテキストボックスにはエラーが表示され、「全部」のボタンをクリックすると全てのデータが、「男だけ」では男のデータだけ、「女だけ」では女のデータだけが表示される。

Access_SQL_019

<Fig 4 : フォームを開いたところ>

Access_SQL_020

<Fig 5 : 「全部」ボタンをクリックして全件データを取得した>

Access_SQL_021

<Fig 6 : 「男だけ」ボタンをクリックして男のデータだけを取得した>

Access_SQL_022

<Fig 7 : 「女だけ」ボタンをクリックして女のデータだけを取得した>

Written by Yoshio Matsumoto

2011年12月25日 at 3:25 PM

Access – レコードソースを設定せずVBAで動的にテーブルのレコードを取得する

with 4 comments

アクセスのフォームやレポートは通常レコードソースをプロパティ値に設定してデータを表示する。しかしあらかじめレコードソースを設定しなくても、VBAを使って動的にレコードソースを設定し、データを表示することができる。このときレコードソースの記述がSQL文でできるので、SQLを使ったレコード操作に慣れた人には、このVBAを使った処理はわかりやすく、応用しやすいだろう。たとえばこんなかんじだ。

まずAccessデータベース上に2つのテーブル「生徒1」と「生徒2」を作ってみる。テーブルのフィールドはどちらも同じ構造とし、「学籍番号」、「氏名」、「性別」があるとする。しかしデータの内容は違う。

Access_SQL_001

<Fig 1 : 「生徒1」テーブルを作成する>

Access_SQL_002

<Fig 2 : 「生徒2」テーブルを作成する>

テーブルを作成し適当なデータを作れば、次にフォームを作成する。Access2003の場合は「挿入」-「フォーム」で「新しいフォーム」ウィンドウを開き「基になるテーブルまたはクエリの選択」を空白にしたまま「デザインビュー」をクリックする。Access2007とAccess2010の場合は「作成」-「空白のフォーム」をクリックする。

新しいフォームができたらデザインビューにし、「フォームヘッダー/フッター」を作り、フォームの既定のビューを「帳票フォーム」に変更する。フォームヘッダーと詳細の高さを調節する。フォームの名前は「生徒フォーム」として保存しておこう。

Access_SQL_003

<Fig 3 : レコードソースを指定しないフォームを作る>

ここでいちどフォームを「フォームビュー」にして表示してみよう。レコードソースが指定されておらず、テキストボックスもなにもないので、単に真っ白な画面が表示されるだけである。

Access_SQL_004

<Fig 4 : レコードソースを指定しないフォームを表示した>

次に「詳細」エリアにテキストボックスを3つ作る。各テキストボックスは、プロパティの「データ」タブ「コントロールソース」に、それぞれ表示させたいフィールド名を記述する。通常レコードソースを設定したフォームの編集では、ここでテーブルのレコードソースが表示されドロップダウンリストから選択できるが、ここではレコードソースが設定していないのでリスト表示はされない。キーボードから文字入力でフィールドを設定する。

Access_SQL_005

<Fig 5 : 「学籍番号」、「氏名」、「性別」のテキストボックスを作成する>

テキストボックスを作成したら、もういちどフォームを開いてみよう。もちろんレコードソースが指定されていないので、データは何も表示されない。各フィールドには「#Name?」というエラーが表示されている。

Access_SQL_006

<Fig 6 : データソースが指定されていないフォームを開いたので「#Name?」エラーが表示される>

次にデータソースをVBAで記述するコードを書くが、このコードをボタンのクリックイベントで実行させることにする。そのためにフォームのフォームヘッダーに2つボタンを作成する。ボタンの「表題」はそれぞれ「生徒1データ」、「生徒2データ」としておこう。

Access_SQL_007

<Fig 7 : フォームヘッダーにボタンを2つ作り表題を設定した>

次にVBAコードを書くため、「生徒1データ」の表題を書いたボタンを選択し、プロパティの「イベント」タブから「クリック時」の横の「・・・」をクリックする。すると「ビルダーの選択」ウィンドウが表示されるので、「コードビルダー」を選択して「OK」をクリックする。

Access_SQL_008

<Fig 8 : ボタンのクリック時イベントをVBAで記述するためにコードビルダーを開く>

コードビルダーが開いたら、クリックイベントに対応するイベントプロシージャができている。ここに次のコードを書く。

——————————————————————————–

Me.RecordSource = "select 学籍番号,氏名,性別 from 生徒1"

——————————————————————————–

ダブルクオーテーションで挟まれた部分は単純なSQL文である。

Access_SQL_009

<Fig 9 : コードビルダーでイベントプロシージャを表示する>

Access_SQL_010

<Fig 10 : コードビルダーにレコードソースを指定するコードを書く>

コードが書けたらフォームを開いてみよう。フォームを開いた時点ではレコードソースが指定されていないので、各テキストボックスには「#Name?」のエラーが表示されているが、「生徒1データ」ボタンをクリックすると「生徒1」テーブルを指定したクエリが実行され、データが表示される。

Access_SQL_011

<Fig 11 : レコードソースを指定しないフォームを開く>

Access_SQL_012

<Fig 12 : 「生徒1データ」ボタンのクリックでレコードソースを指定し「生徒1」テーブルのデータを表示した>

では次に「生徒2データ」ボタンのクリックイベントで「生徒2」テーブルからデータを取得するレコードソースを記述しよう。SQL文のfrom句でテーブル名を変更するだけだ。

Access_SQL_013

<Fig 13 : 「生徒2データ」ボタンのイベントプロシージャにレコードソースを指定するコードを書く>

これで「生徒1データ」ボタンをクリックすると「生徒1」テーブルのデータを表示し、「生徒2データ」ボタンをクリックすると「生徒2」テーブルのデータを表示するフォームができた。

Access_SQL_014

<Fig 14 : 完成したフォームを開き「生徒2データ」ボタンをクリックすると「生徒2」テーブルのデータが表示される>

Access_SQL_015

<Fig 15 : 完成したフォームを開き「生徒1データ」ボタンをクリックすると「生徒1」テーブルのデータが表示される>

このようにフォームやレポートをデータソースの指定なしに作成し、ボタンクリックなどで動的にレコードソースを指定してデータを表示させることができる。ここで重要なことは、レコードソースの記述が一般的なSQL文で記述できることである。SQL文に習熟したものなら、自由にレコードセットを記述してフォームやレポートに動的に表示させることができる。とてもわかりやすい。

Written by Yoshio Matsumoto

2011年12月25日 at 2:08 PM

エクセルのマクロで図形を操作する(4) – オートシェイプを消去する

leave a comment »

ではエクセルのシートに作ったオートシェイプを消去するにはどうすればいいだろうか。オートシェイプを削除するには、deleteメソッドが使えるはずだ。試しに次の2つのマクロを作ってみよう。

————————————————————————

Sub smaile()
ActiveSheet.Shapes.AddShape msoShapeSmileyFace, 10, 20, 100, 100
ActiveSheet.Shapes.AddShape msoShapeSmileyFace, 120, 20, 100, 100
End Sub

————————————————————————

Sub delete()
Sheet1.Shapes(1).delete
Sheet1.Shapes(2).delete
End Sub

————————————————————————

最初のマクロでは、スマイルのオートシェイプを2つ作っている。これら2つのオートシェイプはそれぞれShapes(1)とShapes(2)という番号で管理されている。そして次のマクロでは、そのShapes(1)とShapes(2)に対して消去のDeleteメソッドを実行している。だが残念ながらこのマクロは、消去するときにエラーになる。ひとつめのシェイプを消去した後「指定したコレクションに対するインデックスが境界を越えています。」というメッセージが出るはずだ。なぜかといえば、Shapes(1)を消去したときに、自動的にShapes(2)がShapes(1)になってしまうからだ。そこで消去する順番は、まず数字の大きいShapes(2)を消去し、その次にShapes(1)を消去する、というようにすればいい。次のマクロは正しく動くだろう。

————————————————————————

Sub smaile()
ActiveSheet.Shapes.AddShape msoShapeSmileyFace, 10, 20, 100, 100
ActiveSheet.Shapes.AddShape msoShapeSmileyFace, 120, 20, 100, 100
End Sub

————————————————————————

Sub delete()
Sheet1.Shapes(2).delete
Sheet1.Shapes(1).delete
End Sub

————————————————————————

あるいは次のように、Shapes(1)を二回消去する、というようにしてもうまく消去できる。

————————————————————————

Sub smaile()
ActiveSheet.Shapes.AddShape msoShapeSmileyFace, 10, 20, 100, 100
ActiveSheet.Shapes.AddShape msoShapeSmileyFace, 120, 20, 100, 100
End Sub

————————————————————————

Sub delete()
Sheet1.Shapes(1).delete
Sheet1.Shapes(1).delete
End Sub

————————————————————————

Written by Yoshio Matsumoto

2010年12月25日 at 7:01 PM

エクセルのマクロで図形を操作する(3) – オートシェイプに名前をつける

with one comment

エクセルのシート上に作成したオートシェイプは番号がついているが、番号ではなく、オートシェイプに名前を付けて区別することもできる。例えばこんな具合だ。

Sub smaile()
ActiveSheet.Shapes.AddShape msoShapeSmileyFace, 10, 20, 100, 100
ActiveSheet.Shapes.AddShape msoShapeSmileyFace, 120, 20, 100, 100
ActiveSheet.Shapes(1).Name = “taro”
ActiveSheet.Shapes(2).Name = “hanako”
Sheet1.Shapes(“taro”).IncrementRotation (-20)
Sheet1.Shapes(“hanako”).IncrementRotation (20)
Sheet1.Shapes(“taro”).Adjustments.Item(1) = 0.5
Sheet1.Shapes(“hanako”).Width = 160
Sheet1.Shapes(“hanako”).Height = 160
End Sub

excel_macro_002 
(Fig.1 オートシェイプを2つ作成し名前を付けて角度や変形、大きさを変えた)

ここでは作成した2つのオートシェイプに「taro」と「hanako」という名前をつけている。「taro」に操作をするときはShapes(“taro”)、「hanako」に操作をするときはShapes(“hanako”)と記述して区別できる。ここでは「taro」を左に20度(つまり右に-20度)回転、変形を0.5、「hanako」は右に20度回転、幅と高さを160に拡大している。

Written by Yoshio Matsumoto

2010年7月19日 at 9:14 AM

エクセルのマクロで図形を操作する(2) – 作成したオートシェイプには番号がついている

leave a comment »

エクセルのシート上に作成されたオートシェイプは連番で管理されている。たとえば次のようなマクロを作って実行する。結果はこんな具合だ。

Sub smaile()
ActiveSheet.Shapes.AddShape msoShapeSmileyFace, 10, 20, 100, 100
ActiveSheet.Shapes.AddShape msoShapeSmileyFace, 120, 20, 100, 100
Sheet1.Shapes(1).IncrementRotation (-10)
Sheet1.Shapes(2).IncrementRotation (10)
Sheet1.Shapes(1).Adjustments.Item(1) = 0.5
End Sub

excel_macro_001 
(Fig.1 マクロによって作成した2つのオートシェイプ)

ここでは2つのオートシェイプを作っているが、それぞれ作成された順番でShapes(1)、Shapes(2)という連番で管理されている。最初に作成したオートシェイプは図の左側のもので、こちらはShapes(1)、次に作成したオートシェイプが右側で、Shapes(2)である。Shapes(1)を左に10度(つまりマイナス10度)回転させ、Shapes(2)を右に10度回転させる、そして右側のShapes(1)については、Adjustment.Itemの値を変えて変形している。

Written by Yoshio Matsumoto

2010年7月18日 at 9:16 AM

エクセルのマクロで図形を操作する(1) – マクロでオートシェイプを作成する

leave a comment »

あることを思い立ち、エクセルのマクロで作ってみたものがある。そのため、図形を操作するマクロについて調べている。

エクセルのマクロは習得が簡単だ。その理由のひとつに「マクロの記録」がある。マクロで自動化したい操作を「マクロの記録」を実行してマウスやキーボードで操作すると、その操作がマクロとなって記録される。これはいわばユーザーの操作を記録しているわけで、このマクロを分析し加工すれば思いのままに操作を自動化できる。

例えばMicrosoft Excel 2003でオートシェイプを作り、形を変化させ、回転する、という操作をマクロに記録すると、次のようなマクロができる。

—————————————————————————————————————————————–

Sub Macro1()

‘ Macro1 Macro
‘ マクロ記録日 : 7/19/2010  ユーザー名 :


    ActiveSheet.Shapes.AddShape(msoShapeRoundedRectangle, 124.5, 87.75, 126.75, 53.25).Select
    Selection.ShapeRange.Adjustments.Item(1) = 0.5
    Selection.ShapeRange.IncrementRotation 35.28
End Sub

—————————————————————————————————————————————–

ActiveSheet.Shapes.AddShape()によってシートにオートシェイプが作られる。()の中には5つの引数があり、最初のmsoShapeRoundedRectangleがオートシェイプの種類、続く4つの数字はオートシェイプの大きさと位置を決めている。これらの数値を変更したマクロを作り実行すると、引数は「オートシェイプの種類、横の位置、縦の位置、横の大きさ、縦の大きさ」であることがわかる。

Selection.ShapeRange.Adjustments.Item()はオートシェイプの変形だ。オートシェイプの中には黄色の菱形アンカーポイントを持つものがあり、変形することができる。

Selection.ShapeRange.IncrementRotationはオートシェイプの回転であり、右回りの回転角度で指定する。右回りに90度回転させるなら値は「90」だ。

ここで自動的に作成されたマクロでオートシェイプを作ることができるが、より実際的にオートシェイプを操作するためには、次のように書くこともできる。

●オートシェイプを作成する

ActiveSheet.Shapes.AddShape msoShapeRoundedRectangle, 10, 20, 50, 60

●作成したオートシェイプを右に5度回転させる

Sheet1.Shapes(1).IncrementRotation (5)

●作成したオートシェイプの横の大きさを100に変える

Sheet1.Shapes(1).Width = 100

●作成したオートシェイプの塗りを半透明化する

Sheet1.Shapes(1).Fill.Transparency = 0.5

●作成したオートシェイプの変形を0.5にする

Sheet1.Shapes(4).Adjustments.Item(1) = 0.5

Written by Yoshio Matsumoto

2010年7月17日 at 9:15 AM