access テーブルデータの編集、保存

質問

失礼します。
ACCESSのテーブルのデータをVBAで編集する仕方がわからなくて困っています。

やりたいことは、フォームを作成してそこに「検索」ボタンを作り
テキストボックスに入力した条件をSQLにして検索をかける。
→HITするデータの一覧が表示される。
→修正したいデータを選択する。
→修正画面が表示される。

この時に選択したデータをあらかじめEXCELで作成したフォーマットに
反映して修正できるようにしたいんですが、（フィールドをばらばらに指定したセルに代入するような感じです。）これは可能なんでしょうか？

そもそもレコードセットに格納したデータを視覚的にUSERに認識させてそこからデータを選択するという事が可能なのかもわかりません。
勉強不足で申し訳ありませんがどなたかアドバイスをいただければ幸いです。
一応自分なりに書いてみたんですがいくら調べてもここで止まってしまいます。どこが不備があれば教えてください。
お願いします。

'変数の設定
Dim myDB As DAO.Database
Dim myRS As Recordset
Dim strSQL As String

'検索条件変数の設定
Dim Day As String
Dim Sec As String
Dim shaNO As String
Dim User As String
Dim Sis As String

Day = テキスト3
Sec = テキスト7
shaNO = テキスト9
User = テキスト5
Sis = テキスト1

'検索条件をSQLに代入
strSQL = strSQL & " SELECT"
strSQL = strSQL & ",SM.起票日"
strSQL = strSQL & ",SM.社員番号"
strSQL = strSQL & ",SM.所属部署"
strSQL = strSQL & ",SM.氏名"
strSQL = strSQL & ",SM.対象システム"
strSQL = strSQL & ",SM.内容"
strSQL = strSQL & ",SM.一日当たりの工数"
strSQL = strSQL & ",SM.期待効果"
strSQL = strSQL & ",SM.改修コスト"
strSQL = strSQL & ",SM.改修必要時間"
strSQL = strSQL & ",SM.調査開始日"
strSQL = strSQL & ",SM.受付担当者"
strSQL = strSQL & ",SM.受付日時"
strSQL = strSQL & ",SM.調査終了日"
strSQL = strSQL & ",SM.障害区分"
strSQL = strSQL & ",SM.調査担当者"
strSQL = strSQL & ",SM.代替案"
strSQL = strSQL & ",SM.対応区分"
strSQL = strSQL & ",SM.対応完了日"
strSQL = strSQL & ",SM.難易度"

strSQL = strSQL & " FROM"
strSQL = strSQL & ",T_障害票マスタ SM"

strSQL = strSQL & " WHERE"
strSQL = strSQL & ",SM.起票日 = &Day& "
strSQL = strSQL & ",SM.所属部署 = &Sec& "
strSQL = strSQL & ",SM.社員番号 =&shaNO& "
strSQL = strSQL & ",SM.氏名 = &User& "
strSQL = strSQL & ",SM.対象システム = &Sis& "
strSQL = strSQL & ""

Set myDB = CurrentDb()
Set myRS = myDB.OpenRecordset(strSQL, dbOpenTable)

回答No.3

DexMachina · ベストアンサー

No.2です。

> 【コントロールツール】の式には変数も使用できるんでしょうか？
> 条件入力したものをそのままSQLに渡したいんですが。

『コントロールソース』のことでしたら、残念ながらそこには変数は指定できません。

ただ、ご希望の内容が「フォームのコントロール(テキストボックス等)に対して、ユーザーが
入力した値をSQL文の中に組み込みたい」ということでしたら、別の方法で対応可能です。

この場合、入力値はテーブルに保存する必要がないと思いますので、『コントロールソース』
は空白のままにしておきます(→非連結コントロール)。
その上で、以下のような式を使用して、コントロールの値を参照します。

・参照したいコントロールと、コード記述先が、同じフォームだった場合：
　　Me!コントロール名.Value
　　(なお、「Me!」や「.Value」は省略も可能 ： 「コントロール名」でOk)
・参照したいコントロールが、コード記述先のサブフォームだった場合：
　　Me!サブフォーム名!コントロール名.Value
　　(「Me!」「.Value」は、上と同様に省略が可能)
・参照したいコントロールが、コード記述先以外(別フォームや標準モジュール)の場合：
　　Forms!フォーム名!コントロール名.Value
　　(「.Value」については、上と同様に省略が可能)

以下、コードの一例です：
(フォーム上に「ボタン1」コマンドボタンと「テキスト1」テキストボックスが
　あり、「ボタン1」をクリックすると、「テキスト1」に指定した条件に合う
　レコード(→「Tbl1」テーブルに保存済み)をフォームに表示、の想定)

Private Sub ボタン1_Click()

　　'変数の宣言
　　Dim strCnd As String, strWhr As String, strSQL As String
　　'定数の宣言
　　Const conSQL As String = "Select * From Tbl1"

　　'「テキスト1」テキストボックスの値を確認
　　'(Accessのコントロールの初期値はNullという特殊な値のため、
　　'　Nz関数を使用して、テキストとして扱える空文字に変換)
　　strCnd = Nz(Me!テキスト1, "")　　'←◆コントロールの値を参照◆

　　'「テキスト1」の入力値を元に、Where条件句を作成
　　If strCnd = "" Then　　'条件指定なしの場合は全件表示
　　　　strWhr = ";"
　　Else　　　　　　　　　　　　'条件指定時は曖昧検索
　　　　strWhr = "Where Fld1 Like '*" & strCnd & "*';"
　　End If

　　'SQL文を作成
　　strSQL = conSQL & vbCrLf & strWhr

　　'自身のレコードソースに設定
　　Me.RecordSource = strSQL

End Sub

・・・以上です。

Nullについての参考：
http://oshiete1.goo.ne.jp/qa4850675.html

お礼

遅くなりましてすみません。
教えていただいたとおりに書いたらすんなり出来ました。
本当にありがとうございます。
またなにかありましたらよろしくお願いします。

回答No.2

DexMachina

> レコードセットに格納したデータを視覚的にUSERに認識させてそこから
> データを選択するという事が可能なのか

もしかしたらなのですが、Excelか、VBAではないVB等に慣れた方なのでしょうか。

Excelのユーザーフォームなどとは違って、Accessの『フォーム』には『レコードソース
(RecordSource)』というプロパティがあり、ここにテーブルやクエリ、SQL文を指定する
ことで、レコードセットを簡単に表示できるようになっていますし、追加/編集/削除も
可能です。
(フォームのデザインビューで、プロパティシートを使用して設定もできますし(→GUI)、
　「Forms!フォーム名.RecordSource = "テーブル名"」といった構文による設定も
　できます(→VBA))

通常は、そうして作った「連結フォーム」(=レコードソースに連結したフォーム、の意)を
使用して、入力/編集を行うのが、コーディングが少なくてすむので初心者向きです。
(フォームのプロパティには『既定のビュー(DefaultView)』というものもあり、これによって
　Excelのユーザーフォームのような「単票フォーム」や、複数のレコードを同時に表示
　しつつ、コントロールも自由に配置できる「帳票フォーム」、テーブルと同様の「データ
　シート」(→但し、あくまでフォームなので、テーブルと違い更新時にVBAなどで自動
　で処理を行わせる、といったことも可能)といったように、外観も設定できます)

※なお、データ表示のためには、テキストボックス等のコントロールの追加も必要です。
　また、コントロールについては、「どのフィールドのデータを表示するか」を指定するため
　の『コントロールソース(ControlSource)』プロパティというものがありますので、フォーム
　でのレコードソースと同様、ここにフィールド名(または関数などの式も可)を指定する
　必要があります。

・・・ということで、レコード編集や集計などは、通常はデータベース上で行って、その
後の二次加工をする際に初めてExcelに出力して、というのが一般的な方法かと
思います。

Excelへの出力も、テーブルやクエリの単純なエクスポートでは、Excel上のA1セルから
上詰め・左詰めにしかなりませんが、逆に、Access上に「出力用テーブル」のようなもの
を作り、クエリを使用して、一旦ここに必要なデータを全てコピーし(→Excel上のヘッダや、
余白となるセルも含めて再現：但し、並び順を規定するフィールドを作成する必要あり)、
これをご希望のExcelフォーマットに出力してやる、という方法もあります。

AccessのテーブルデータをExcelに書き出してユーザーが編集し、それを再びAccessの
テーブルに反映させる、というのは、かなり面倒ですので、よければご検討下さい。

お礼

いつも詳しい説明ありがとうございます。
とても参考になりました。
ACCESSの基本的な機能も勉強せずに、複雑なシステムを作ろうとしていました。
あとひとつお伺いしたいんですが、【コントロールツール】の式には変数も使用できるんでしょうか？
条件入力したものをそのままSQLに渡したいんですが。
よろしくお願いします。

回答No.1

imogasi

初心者らしいが、ＶＢＡで相当複雑なことをやろうとしているようだが、これでは、判らないからと、課題の丸投げでは無いか。要は長い時間をかけて勉強が必要なのに、課題が欲張りすぎるのだと思う。
コードを書いてくださいといったことになる。
もっと現実の複雑な問題から一遍に取り掛かるのでなく、易しい例で
、一部から取り掛かることがやり方では。
アクセスでエクセルを出力結果の表示用に使うのは難しいのでは。
Ｓｐｒｅｄｓｈｅｅｔのようなコントロールが無いと難しいが普通では無い。
ーー
参考に
フォ－ム１０に
テキスト1
テキスト3
リスト5
コマンドボタン
を設ける。
ーーー
テキスト1,
テキスト3
に条件文字列を入れてクリック。
コマンドボタンのクリックイベント
Private Sub コマンド0_Click()
MsgBox テキスト1
MsgBox テキスト3
Dim strSQL As String
strSQL = "SELECT 氏名,学校,県,生年月日,性別 from 生徒1"
'strSQL = strSQL & "where 氏名 = '" & テキスト1
strSQL = strSQL & " where 学校 ='" & テキスト3
strSQL = strSQL & "';"
MsgBox strSQL
Set mydb = CurrentDb()
Set rs = mydb.OpenRecordset(strSQL, dbOpenDynaset)
Me.リスト5.RowSource = strSQL
Me.リスト5.ColumnCount = 4
Do Until rs.EOF
MsgBox rs!性別 & " , " & rs!氏名 & " , " & _
rs!学校 & " ," & rs!生年月日
rs.MoveNext
Loop

rs.Close: Set rs = Nothing
mydb.Close: Set qry = Nothing

End Sub
ーーー
リストボックスのクリックイベント（選択時）
Private Sub リスト5_Click()

With Me.リスト5
'MsgBox .ListCount
For Cnt = 0 To リスト5.ListCount - 1
'MsgBox リスト5.Selected(Cnt)
If リスト5.Selected(Cnt) = True Then
For j = 0 To 4
MsgBox リスト5.Column(j)
Next j
End If
Next Cnt
End With
End Sub
ーーー
テーブル例
ID 生徒番号 氏名 県 学校 学年 身長 生年月日 生年文字 年号暦 性別
11 北村 北小学校 女
2 1 山田 埼玉 北小学校 2 154 1964/11/01 1954 H14 女
3 2 木下 埼玉 北小学校 1 145 1990/12/03 1991 H10 女
4 3 北野 千葉 中央小学校 3 160 1994/05/24 1994 H18 男
6 4 竹田 東京 南小学校 4 140 1998/02/23 1998 男
7 5 山 下 東京 南小学校 3 129 1991/01/23 1988 男
10 6 近藤 山梨 本町小学校 6 139 男
これでも参考にして拡充してみては。

お礼

いつもご回答いただきありがとうございます。
記述していただいたものを参考にがんばってみます。
これからもよろしくお願いします。