大変困っています。

質問

大変困っています。
簡単な事なのかもしれませんが、どなたかお教えください。
説明が拙いかも知れませんが、よろしくお願いします。

テーブルが３つあります。
T01・・・顧客ID(主キー)
　　　　会社名

T02・・・顧客ID
　　　　日付
　　　　売上ID(主キー)

T03・・・売上ID
　　　　商品名
　　　　単価
　　　　数量
　　　　合計

この3つのテーブルを使って単票フォーム(F01)を作りました。
ただし、T03は帳票でサブフォーム(F02)です。
各テーブルにある同じ名前のもの同士がリレーションされています。
F01のヘッダーに検索できるテキストボックスとボタンがあり、日付検索を行えます。

ここで困っている問題なのですが・・・
新規登録の時には、各項目を埋めていけばいいわけですが、
検索を行う時に、日付検索を行うと、同じ日付でも売上IDごとにサブフォームのページが変わってしまいます。

例えば
（１つ目の登録）
日付：2011/09/30
会社名：○○商事
売上ID：１
商品名他：ボールペン、2個、200円、合計400円

(2つ目の登録)
日付：2011/10/01
会社名：○○建設
売上ID：2
商品名他：省略

(3つ目の登録)
日付：2011/09/30
会社名：○○商事
売上ID：3
商品名他：ホッチキスの芯、3個、50円、合計150円

1つ目と3つ目は同じ会社で同じ日付なので、
サブフォームには売上IDの１と3を同時に表示させたいのです。
ですが、リレーションの関係か何かわからないのですが、
日付で検索しても売上IDごとにフォームのページが変わってしまいます・・・。

インターネットで検索してみましたが、いまいちなんと検索すればいいのかわからず、
ここで質問させていただきました。

ご面倒おかけしますが、どうかお教えください。

回答No.2

piroin654 · ベストアンサー

どうしても検索用フォームは作りたくない、と言うことなら以下でどうですか。
何をしたいのかわかりませんが。
コード中にメインフォームのレコードソースを変更したり、
元に戻したりします。サブフォームも行ないます。
元のレコードソースはこちらで設定しましたが、
実際に合わせて変更してください。一応メインフォームにも
日付でフィルタをかけるようにしました。
ただし、リンクをはずしているので親を移動させても
子は移動することはことはありません。もっとも
サブフォームにフィルタしたレコードがすべて
表示されています。

ボタンを二つ設定し、日付検索用のテキストボックスを
一つ設定します。それぞれ、
cmd検索設定
cmd売上入力設定
tx日付検索用
という名前にします。

次にクエリを三つ。SQL文中の「Fメイン」はメインフォームの
名前です。実際に合わせて変更してください。

「Q売上入力用」
SELECT T02.顧客ID, T01.会社名, T02.売上ID, T02.日付
FROM T01 INNER JOIN T02 ON T01.顧客ID = T02.顧客ID;

「Qメインフォーム検索用」
SELECT T02.顧客ID, T01.会社名, T02.売上ID, T02.日付
FROM T01 RIGHT JOIN T02 ON T01.顧客ID = T02.顧客ID;

「Q日付検索用」
SELECT T02.日付, T02.売上ID, T03.商品名, T03.単価, T03.数量, T03.合計
FROM T02 INNER JOIN T03 ON T02.売上ID = T03.売上ID
WHERE (((T02.日付)=[Forms]![Fメイン]![tx日付検索用]));

次に、ボタンクリック時のイベントで。
なお、「埋め込み0」はサブフォーム表示コントロール名です。
サブフォームそのもの名前ではありません。実際に合わせて
変更してください。

Private Sub cmd検索設定_Click()
Dim str As String

If IsNull(Me!tx日付検索用) Then
MsgBox "日付が入力されていません"
Exit Sub
End If

If Not IsDate(Me!tx日付検索用) Then
MsgBox "日付を正しく入力してください"
Exit Sub
End If

str = "[日付] = #" & Me!tx日付検索用 & "#"

'メインフォームのフィルタ
'メインフォームのレコードソースの変更も含む
Me.RecordSource = "Qメインフォーム検索用"
Me.Filter = str
Me.Requery

'サブフォームのフィルタ
'リンクフィールドの無効化
Me.埋め込み0.LinkChildFields = ""
Me.埋め込み0.LinkMasterFields = ""
Me.埋め込み0.Form.RecordSource = "Q日付検索用"
Me.Requery
Me.埋め込み0.Form.Filter = str
Me.埋め込み0.Form.FilterOn = True
Me.Requery
End Sub

Private Sub cmd売上入力設定_Click()
'メインフォームのレコードソースを基に戻す
Me.RecordSource = "Q売上入力用"
'リンクフィールドを元に戻す
Me.埋め込み0.Form.RecordSource = "T03"
Me.埋め込み0.LinkChildFields = "売上ID"
Me.埋め込み0.LinkMasterFields = "売上ID"
Me.Requery
End Sub

回答で、勘違い、間違い、不明な点があれば補足してください。

回答No.7

piroin654

兼用できるクエリを作ればクエリの数を減らすことができます。
以下の二つのクエリを作ります。

Qメインフォーム日付OR会社名検索用

SELECT T02.顧客ID, T01.会社名, T02.売上ID, T02.日付
FROM T01 INNER JOIN T02 ON T01.顧客ID = T02.顧客ID
WHERE (((T01.会社名)=[Forms]![Fメイン]![会社名入力])) OR (((T02.日付)=[Forms]![Fメイン]![日付入力]));

Q日付OR会社名検索用

SELECT T02.日付, T02.顧客ID, T01.会社名, T02.売上ID, T03.商品名, T03.単価, T03.数量, T03.合計
FROM (T02 INNER JOIN T03 ON T02.売上ID = T03.売上ID) INNER JOIN T01 ON T02.顧客ID = T01.顧客ID
WHERE (((T02.日付)=[Forms]![Fメイン]![日付入力])) OR (((T01.会社名)=[Forms]![Fメイン]![会社名入力]));

「Qメインフォーム日付OR会社名検索用」を
「Qメインフォーム日付検索用」と「Qメインフォーム会社名検索用」
の代わりに。

「Q日付OR会社名検索用」を
「Q日付検索用」と「Q会社名検索用」
の代わりに。

No6のVBAを以下に。クエリ名の入れ替えだけですが。

Private Sub cmd複合検索_Click()

If Not IsNull(Me!日付入力) And IsNull(Me!会社名入力) Then
If IsDate(Me!日付入力) Then
Me.RecordSource = "Qメインフォーム日付OR会社名検索用"
Me.埋め込み0.LinkChildFields = ""
Me.埋め込み0.LinkMasterFields = ""
Me.埋め込み0.Form.RecordSource = "Q日付OR会社名検索用"
Me.Requery
Else
MsgBox "日付を正しく入力してください"
Exit Sub
End If

ElseIf IsNull(Me!日付入力) And Not IsNull(Me!会社名入力) Then
Me.RecordSource = "Qメインフォーム日付OR会社名検索用"
Me.埋め込み0.LinkChildFields = ""
Me.埋め込み0.LinkMasterFields = ""
Me.埋め込み0.Form.RecordSource = "Q日付OR会社名検索用"
Me.Requery

ElseIf Not IsNull(Me!日付入力) And Not IsNull(Me!会社名入力) Then
If IsDate(Me!日付入力) Then
Me.RecordSource = "Qメインフォーム日付会社名検索用"
Me.埋め込み0.LinkChildFields = ""
Me.埋め込み0.LinkMasterFields = ""
Me.埋め込み0.Form.RecordSource = "Q日付会社名検索用"
Me.Requery
Else
MsgBox "日付を正しく入力してください"
Exit Sub
End If
End If

End Sub

使用するクエリは、

Qメインフォーム日付OR会社名検索用
Q日付OR会社名検索用
Qメインフォーム日付会社名検索用
Q日付会社名検索用

です。

お礼

こちらでもきちんとできました！！
ありがとうございました。

回答No.6

piroin654

ついでなので、補足の分も含めて改造します。
No2はいらないものを数々残していたりしていたので
すっきりさせます。No1の検索設定ボタンクリック時のイベント、

Private Sub cmd検索設定_Click()
・
・
End Sub

は残していてもいいですが。一応、新たに設定しなおすので
削除してもかまいません。

(1)
まず、クエリを作成します。

Qメインフォーム会社名検索用

SELECT T02.顧客ID, T01.会社名, T02.売上ID, T02.日付
FROM T01 INNER JOIN T02 ON T01.顧客ID = T02.顧客ID
WHERE (((T01.会社名)=[Forms]![Fメイン]![tx会社名入力]));

Qメインフォーム日付会社名検索用

SELECT T02.顧客ID, T01.会社名, T02.売上ID, T02.日付
FROM T01 INNER JOIN T02 ON T01.顧客ID = T02.顧客ID
WHERE (((T01.会社名)=[Forms]![Fメイン]![tx会社名入力]) AND ((T02.日付)=[Forms]![Fメイン]![tx日付入力]));

Qメインフォーム日付検索用

SELECT T02.顧客ID, T01.会社名, T02.売上ID, T02.日付
FROM T01 INNER JOIN T02 ON T01.顧客ID = T02.顧客ID
WHERE (((T02.日付)=[Forms]![Fメイン]![tx日付入力]));

Q会社名検索用

SELECT T02.日付, T02.顧客ID, T01.会社名, T02.売上ID, T03.商品名, T03.単価, T03.数量, T03.合計
FROM (T02 INNER JOIN T03 ON T02.売上ID = T03.売上ID) INNER JOIN T01 ON T02.顧客ID = T01.顧客ID
WHERE (((T01.会社名)=[Forms]![Fメイン]![tx会社名入力]));

Q日付会社名検索用

SELECT T02.日付, T02.顧客ID, T01.会社名, T02.売上ID, T03.商品名, T03.単価, T03.数量, T03.合計
FROM (T02 INNER JOIN T03 ON T02.売上ID = T03.売上ID) INNER JOIN T01 ON T02.顧客ID = T01.顧客ID
WHERE (((T02.日付)=[Forms]![Fメイン]![tx日付入力]) AND ((T01.会社名)=[Forms]![Fメイン]![tx会社名入力]));

Q日付検索用

SELECT T02.日付, T02.売上ID, T03.商品名, T03.単価, T03.数量, T03.合計
FROM T02 INNER JOIN T03 ON T02.売上ID = T03.売上ID
WHERE (((T02.日付)=[Forms]![Fメイン]![tx日付入力]));

(2)
フォームにボタン一つと、テキストボックスを一つ
設定します。

cmd複合検索設定
tx会社名検索用

複合検索のボタンクリック時のイベントに、以下を設定します。

Private Sub cmd複合検索設定_Click()

If Not IsNull(Me!日付入力) And IsNull(Me!会社名入力) Then
If IsDate(Me!日付入力) Then
Me.RecordSource = "Qメインフォーム日付検索用"
Me.埋め込み0.LinkChildFields = ""
Me.埋め込み0.LinkMasterFields = ""
Me.埋め込み0.Form.RecordSource = "Q日付検索用"
Me.Requery
Else
MsgBox "日付を正しく入力してください"
Exit Sub
End If

ElseIf IsNull(Me!日付入力) And Not IsNull(Me!会社名入力) Then
Me.RecordSource = "Qメインフォーム会社名検索用"
Me.埋め込み0.LinkChildFields = ""
Me.埋め込み0.LinkMasterFields = ""
Me.埋め込み0.Form.RecordSource = "Q会社名検索用"
Me.Requery

ElseIf Not IsNull(Me!日付入力) And Not IsNull(Me!会社名入力) Then
If IsDate(Me!日付入力) Then
Me.RecordSource = "Qメインフォーム日付会社名検索用"
Me.埋め込み0.LinkChildFields = ""
Me.埋め込み0.LinkMasterFields = ""
Me.埋め込み0.Form.RecordSource = "Q日付会社名検索用"
Me.Requery
Else
MsgBox "日付を正しく入力してください"
Exit Sub
End If
End If

End Sub

以上です。使い方は、
(1) 日付入力のみデータが入っている場合は
　　日付で検索
(2) 会社名のみデータが入っている場合は
　　会社名で検索
(3)日付入力、会社名入力の両方にデータが入っている場合は
　　日付と会社名で検索

補足

ありがとうございます！！！
できました！！！

ただ、テキストボックスは２つにしてモジュールの
Me!の後ろをそれぞれテキストボックスに合う名前に変更したらできました！

もう十分の回答をいただいたのに、さらに回答していただいて
本当にありがとうございます。
もう一つも作ってみます！

この度は大変ありがとうございました。
もしまたわからないことがあって、自分ではどうにもならず
質問する機会がありましたらまたご助力頂けると助かります(^^)
ありがとうございました！

回答No.5

piroin654

サブフォーム表示コントロール名

というのは、メインフォームにサブフォームを
設定するときに最初にツールボックスから
サブフォーム/サブレポート　というコントロールを
貼り付け、そのコントロールのソースオブジェクトに
サブフォームを設定します。
このときの　「サブフォーム/サブレポート」　というコントロール
が「サブフォーム表示コントロール名」という表現を
しています。
普通は、初期値は「埋め込み0」というような名前に
なっています。

ウイザードで作るとサブフォーム名がそのまま
コントロールの名前になっているかもしれません。
デザインビューでサブフォームの端っこで右クリックして
プロパティで確認してみてください。

補足

できました！
涙がでるほど嬉しいです！！
ありがとうございます！

返答も早く、詳しい説明でとってもわかりやすかったです！
日付で検索できるだけなく、元の状態にも戻すことができるなんて
piroin654さんの聡明さだけでなく、アクセスの奥深さというか
アクセスでできる可能性の広さに感動いたしました。
本当にありがとうございます。

重ねがさね申し訳ないのですが、よろしければもうひとつお教えください。

この検索システムに、会社名のテキストボックスを増やし、
会社名と日付と両方で検索することも可能ですか？

お手が空いていて、しょうがないなと思われましたよろしくお願いいたします。

回答No.4

piroin654

訂正です。
No2の「Qメインフォーム検索用」のSQL文の張り間違いです。
以下にしてください。

「Qメインフォーム検索用」

SELECT T02.顧客ID, T01.会社名, T02.売上ID, T02.日付
FROM T01 INNER JOIN T02 ON T01.顧客ID = T02.顧客ID
WHERE (((T02.日付)=[Forms]![Fメイン]![tx日付検索用]));

補足

詳しい説明ありがとうございます！
お返事が遅くなってしまった大変もうしわけありません…

今、NO2でご回答いただいたものを急いで作ってみていますので、
また困るか、解決するかしましたら、すぐにお返事いたします！

>「埋め込み0」はサブフォーム表示コントロール名です。

というところが、よくわかりません…
表示コントロール名というのは、どこの設定？なのでしょうか？
それともサブフォーム全体の[プログラム]の名前か何かなのでしょうか？

せっかくご説明いただいているのに、わからないことだらけで大変恐縮に思います。
すみませんが、どうかよろしくお願いいたします。

回答No.3

piroin654

補足の中の、
＞おそらく、T03にも日付レコードを作成し
ならば、もう少し簡素になるかもしれませんが。
こちらもしてみますか？

回答No.1

piroin654

>新規登録の時には、各項目を埋めていけばいいわけですが、

つまり、売上が発生したときに売上伝票を新規に
起こし、売上を入力していくことができる、
というのは正常処理できている、ということですね。
それならば、メインフォームとサブフォーム表示コントロールは
売上IDでリンクされているのでしょうから、

検索を行う時に、日付検索を行うと、同じ日付でも
売上IDごとにサブフォームのページが変わってしまいます。

というのは、至極当然だと思います。
もし、日付で検索して複数の売上IDを抽出して
サブフォームにその売上明細の売上IDにかかわらず
日付の範囲のデータを表示したいと思えば、検索用の
フォームを新たに作ったほうが早いです。
そのときは、メインフォームとサブフォーム表示コントロール
にリンクフィールドを設定せずにクエリ、あるいは
VBAで抽出のロジックを組むようになります。

補足

さっそくのご回答ありがとうございます！！

>検索を行う時に、日付検索を行うと、同じ日付でも
>売上IDごとにサブフォームのページが変わってしまいます。
>
>というのは、至極当然だと思います。

ですよね…笑

検索用のフォームを作ることも考えたのですが、
できるだけ窓を移動することを避けたいというのと、
検索したら反映するという形にしたかったため、検索フォームを別では作りませんでした。

クエリからの検索も作ってみたのですが、
一度検索を行うと二度目の検索からは一度目の検索結果レコードの下から
検索してしまうという壁にぶつかりました…。(わかりにくい説明ですみません)

ということで、今は↓のHPさんから参考にさせていただいた検索方法を使用しています。

http://hatenachips.blog34.fc2.com/

VBAは↓です。
Private Sub 検索_Click()
Dim strFilter As String, strExp As String, aryOpe As Variant
If Not IsNull(Me.会社名入力) Then
strFilter = strFilter & " AND " & BuildCriteria("会社名", dbText, _
"*" & Replace(Me.会社名入力, " ", "* And *") & "*")
End If
If Not IsNull(Me.日付入力) Then
strFilter = strFilter & " AND " & BuildCriteria("日付", dbText, _
"*" & Replace(Me.日付入力, " ", "* And *") & "*")★
End If

Me.Filter = Mid(strFilter, 6)
If strFilter = "" Then
Me.FilterOn = False
Else
Me.FilterOn = True
End If
End Sub

おそらく、T03にも日付レコードを作成し、
★を入れたIF文章のところをT03の日付を指定するようにしたら
良いのではないかと思っているのですが…
どういう文章にしたら良いのかもわからず、上手くいきません。