前提・実現したいこと
メインフォームで検索条件を指定し、サブフォームのグラフに反映させたい。
(例:科目区分の検索条件を雑費にすると、各部署の雑費の合計金額のグラフが表示)
-グラフを表示させる為にしていること-
今年の4月~翌年の3月までで納品日が入力されていて、削除日が入力されていないデータを抽出し、金額の合計を部署別で表示させている。
クエリ
VBA
1SELECT a.KANRI_NO, a.BUSHO_CD, a.BUSHO_NAME, a.KAMOKU_KBN, Sum(b.KINGAKU) AS KINGAKUの合計, Format([NOHIN_DATE],"yyyy/mm") AS NOHIN_YM, a.DEL_DATE 2FROM (TRNTBL_KOBAI AS a INNER JOIN TRNTBL_KOBAI_MEISAI AS b ON a.KANRI_NO = b.KANRI_NO) INNER JOIN MSTTBL_BUSHO AS c ON a.BUSHO_CD = c.BUSHO_CD 3WHERE (((a.KANRI_NO)=[b].[KANRI_NO]) AND ((b.NOHIN_DATE) Between CDate(Format(DateAdd("m",-3,Date()),"yyyy") & "/04/01") And CDate(Format(DateAdd("m",-3,Date()),"yyyy")+1 & "/03/31"))) 4GROUP BY a.KANRI_NO, a.BUSHO_CD, a.BUSHO_NAME, a.KAMOKU_KBN, Format([NOHIN_DATE],"yyyy/mm"), a.DEL_DATE 5HAVING (((a.DEL_DATE) Is Null));
グラフの値集合ソース
VBA
1TRANSFORM Format(Sum([KINGAKUの合計]),"\#,##0") AS KINGAKUの合計の合計 2SELECT Format([NOHIN_YM],"yyyy\年mm\月") AS 日付 3FROM que_busho_kingaku 4GROUP BY Format([NOHIN_YM],"yyyy\年mm\月") 5PIVOT que_busho_kingaku.BUSHO_NAME;
試したこと
メインフォームでデータを取得し、検索条件をグラフに反映させようとしたが、グラフへの反映のさせ方が分からない。
VBA
1'////////////////////////////////////////////////////////// 2' 3' フォーム:起動時処理 4' 5'////////////////////////////////////////////////////////// 6Private Sub Form_Load() 7On Error GoTo Err_Proc 8 9 'データ取得 10 If (GetData = False) Then 11 Exit Sub 12 End If 13 14Exit_Proc: 15 Exit Sub 16 17Err_Proc: 18 MsgBox Err.Description 19 MsgBox Err.Number 20 Resume Exit_Proc 21End Sub 22'////////////////////////////////////////////////////////// 23' 24' クリアボタン:クリック時処理 25' 26'////////////////////////////////////////////////////////// 27Private Sub cmd_clear_Click() 28On Error GoTo Err_Proc 29 30 '初期化(検索条件) 31 Me.cmb_KAMOKU_KBN = "" 32 33 'データ取得 34 If (GetData = False) Then 35 Exit Sub 36 End If 37 38Exit_Proc: 39 Exit Sub 40 41Err_Proc: 42 MsgBox Err.Description 43 Resume Exit_Proc 44End Sub 45 46'////////////////////////////////////////////////////////// 47' 48' 検索条件(科目区分):更新後処理 49' 50'////////////////////////////////////////////////////////// 51Private Sub cmb_KAMOKU_KBN_AfterUpdate() 52On Error GoTo Err_Proc 53 54 'データ取得 55 If (GetData = False) Then 56 Exit Sub 57 End If 58 59Exit_Proc: 60 Exit Sub 61 62Err_Proc: 63 MsgBox Err.Description 64 Resume Exit_Proc 65End Sub 66 67'////////////////////////////////////////////////////////// 68' 69' 検索結果データ取得 70' 71'////////////////////////////////////////////////////////// 72Private Function GetData() As Boolean 73On Error GoTo Err_Proc 74 75 Dim strSQL As String 76 Dim strWhere As String 77 Dim rs As Recordset 78 79 '=================================================== 80 ' SQL文生成 81 '=================================================== 82 strSQL = "SELECT " 83 strSQL = strSQL & " a.KANRI_NO," 84 strSQL = strSQL & " b.EDA," 85 strSQL = strSQL & " a.BUSHO_CD," 86 strSQL = strSQL & " a.BUSHO_NAME," 87 strSQL = strSQL & " (SELECT BUNRUI_NAME FROM MSTTBL_BUNRUI AS c" 88 strSQL = strSQL & " WHERE c.BUNRUI_CD = '" & CODE_COM_KAMOKU_KBN & "'" 89 strSQL = strSQL & " AND a.KAMOKU_KBN = c.SEQ" 90 strSQL = strSQL & " ) AS KBN," 91 strSQL = strSQL & " a.DEL_DATE," 92 strSQL = strSQL & " b.KINGAKU," 93 strSQL = strSQL & " b.NOHIN_DATE" 94 strSQL = strSQL & " FROM TRNTBL_KOBAI AS a" 95 strSQL = strSQL & " LEFT JOIN TRNTBL_KOBAI_MEISAI AS b ON a.KANRI_NO = b.KANRI_NO" 96 '=================================================== 97 ' 抽出条件 98 '=================================================== 99 ' 削除日付 100 strWhere = " WHERE a.DEL_DATE IS NULL" 101 102 ' 納品日(開始範囲) 103 If (Nz(Me.txt_NOHIN_DATE_1, "") <> "" Or Me.txt_NOHIN_DATE_1 <> "") Then 104 If (Nz(strWhere, "") = "") Then 105 strWhere = " WHERE b.NOHIN_DATE >= '" & Me.txt_NOHIN_DATE_1 & "'" 106 Else 107 strWhere = strWhere & " AND b.NOHIN_DATE >= '" & Me.txt_NOHIN_DATE_1 & "*'" 108 End If 109 End If 110 111 ' 納品日(終了範囲) 112 If (Nz(Me.txt_NOHIN_DATE_2, "") <> "" Or Me.txt_NOHIN_DATE_2 <> "") Then 113 If (Nz(strWhere, "") = "") Then 114 strWhere = " WHERE b.NOHIN_DATE <= '" & Me.txt_NOHIN_DATE_2 & "'" 115 Else 116 strWhere = strWhere & " AND b.NOHIN_DATE <= '" & Me.txt_NOHIN_DATE_2 & "'" 117 End If 118 End If 119 120 ' 科目区分 121 If (Nz(Me.cmb_KAMOKU_KBN, "") <> "" Or Me.cmb_KAMOKU_KBN <> "") Then 122 If (Nz(strWhere, "") = "") Then 123 strWhere = " WHERE a.KAMOKU_KBN LIKE '*" & Me.cmb_KAMOKU_KBN & "*'" 124 Else 125 strWhere = strWhere & " AND a.KAMOKU_KBN LIKE '*" & Me.cmb_KAMOKU_KBN & "*'" 126 End If 127 End If 128 129 'SQL文結合 130 If (Nz(strWhere, "") <> "") Then 131 strSQL = strSQL & strWhere 132 End If 133 134 '=================================================== 135 ' 並べ替え条件 136 '=================================================== 137 strSQL = strSQL & " ORDER BY CLng(a.KANRI_NO), b.EDA " 138 139 '=================================================== 140 ' データソース設定 141 '=================================================== 142 'サブフォームに反映 143 Me.sub_grf_kobainohin.Form.RecordSource = strSQL 144 145 '戻り値セット 146 GetData = True 147 148Exit_Proc: 149 Exit Function 150 151Err_Proc: 152 MsgBox (Err.Number & ", " & Err.Description) 153 '戻り値セット 154 GetData = False 155 Resume Exit_Proc 156 157End Function 158
2019/6/13 追記
値集合ソースに条件を追加したらうまくいくかと思ったが、グラフ自体が表示されなくなった。指定の仕方が悪いのだろうか?
グラフの値集合ソース
VBA
1TRANSFORM Format(Sum([KINGAKUの合計]),"\#,##0") AS KINGAKUの合計の合計 2SELECT Format([NOHIN_YM],"yyyy\年mm\月") AS 日付 3FROM que_busho_kingaku 4GROUP BY Format([NOHIN_YM],"yyyy\年mm\月") 5PIVOT que_busho_kingaku.BUSHO_NAME; 6TRANSFORM Format(Sum([KINGAKUの合計]),"\#,##0") AS KINGAKUの合計の合計
↓修正
VBA
1TRANSFORM Format(Sum([KINGAKUの合計]),"\#,##0") AS KINGAKUの合計の合計 2SELECT Format([NOHIN_YM],"yyyy\年mm\月") AS 日付 3FROM que_busho_kingaku 4WHERE (((que_busho_kingaku.KAMOKU_KBN) Like "[フォーム名].[cmb_KAMOKU_KBN]")) '修正 5GROUP BY Format([NOHIN_YM],"yyyy\年mm\月") 6PIVOT que_busho_kingaku.BUSHO_NAME;
補足情報(FW/ツールのバージョンなど)
Windows10,ACCESS2016
2019/6/13 追記
VBA参照設定
1.Visual Basic For Applications, 2.Microsoft Access 16.0 Object Library,
3.OLE Automation, 4.Microsoft Office 16.0 Access database engine Object Library, 5.Microsoft Internet Controls, 6.Microsoft ActiveX Date Objects 6.1 Library, 7.Microsoft Office 16.0 Object Library
あなたの回答
tips
プレビュー