iT邦幫忙

2017 iT 邦幫忙鐵人賽
DAY 7
0
自我挑戰組

Access VBA的眉眉角角系列 第 7

Access VBA 的眉眉角角Day7: SQL語法於VBA中的應用3

  • 分享至 

  • xImage
  •  

今天要講的是前兩天提到的,變更「下拉方塊」、「清單方塊」、「圖表」資料來源(RowSource)的方法。我們新增一個「表單」來講解,將表單取名為「F_Day7」,裡面放置兩個「指令按鈕」物件,分別名為「cmd_照供應商排序」與「cmd_照銷售額排序」,放置一個「下拉式方塊」物件,命名為「Combo_供應商」,一個「清單方塊」,命名「List_產品資料」,一個「圖表」物件,命名為「Graph_銷售」。其中,圖表物件可由選單中的「插入」->「圖表」方式來新增。
之後依照以下的標籤名稱、按鈕名稱寫入。
http://ithelp.ithome.com.tw/upload/images/20161208/20007221yU4cLUscv7.png

之後於「Form_F_Day7」中貼入以下程式:

Private Sub cmd_照供應商排序_Click()
    Combo_供應商.ColumnCount = 4
    Combo_供應商.ColumnWidths = "0cm;1.501cm;1.503cm;2cm"
    Combo_供應商.ListWidth = "3402"
    
    strSQL = "" & _
    "SELECT 供應商.供應商編號, 供應商.供應商, 供應商.連絡人, 供應商.連絡人職稱 " & vbCrLf & _
    "FROM 供應商 " & vbCrLf & _
    "ORDER BY 供應商.供應商; "
    
    Combo_供應商.RowSource = strSQL
    Combo_供應商.SetFocus
    Combo_供應商.Dropdown
        
End Sub
Private Sub cmd_照銷售額排序_Click()
    Combo_供應商.ColumnCount = 5
    Combo_供應商.ColumnWidths = "0cm;1.501cm;1.503cm;2cm;5"
    Combo_供應商.ListWidth = "4502"

    strSQL = "" & _
    "SELECT 供應商.供應商編號, 供應商.供應商, 供應商.連絡人, 供應商.連絡人職稱, Sum([訂貨明細].[單價]*[訂貨明細].[數量]) AS 銷售額 " & vbCrLf & _
    "FROM (供應商 INNER JOIN 產品資料 ON 供應商.供應商編號 = 產品資料.供應商編號) INNER JOIN 訂貨明細 ON 產品資料.產品編號 = 訂貨明細.產品編號 " & vbCrLf & _
    "GROUP BY 供應商.供應商編號, 供應商.供應商, 供應商.連絡人, 供應商.連絡人職稱 " & vbCrLf & _
    "ORDER BY Sum([訂貨明細].[單價]*[訂貨明細].[數量]) DESC; "

    
    Combo_供應商.RowSource = strSQL
    Combo_供應商.SetFocus
    Combo_供應商.Dropdown

End Sub
Private Sub Combo_供應商_AfterUpdate()

If Combo_供應商 <> "" Then
    
    strSQL = "" & _
    "SELECT 產品資料.產品編號, 產品資料.產品, 產品資料.類別編號 " & vbCrLf & _
    "FROM 產品資料 " & vbCrLf & _
    "WHERE (((產品資料.供應商編號)=" & Combo_供應商 & ")); "

    List_產品資料.RowSource = strSQL
Else
    List_產品資料.RowSource = ""
End If

End Sub
Private Sub List_產品資料_Click()

    If List_產品資料 <> "" Then

        strSQL = "" & _
        "SELECT Format([訂單日期],'e年 第q季') AS 年月, Sum(訂貨明細.數量) AS 數量之總計 " & vbCrLf & _
        "FROM 訂貨主檔 INNER JOIN 訂貨明細 ON 訂貨主檔.訂單號碼 = 訂貨明細.訂單號碼 " & vbCrLf & _
        "GROUP BY Format([訂單日期],'e年 第q季'), 訂貨明細.產品編號 " & vbCrLf & _
        "HAVING (((訂貨明細.產品編號)=" & List_產品資料 & ")); "

        Graph_銷售.RowSource = strSQL
        
    Else
        Graph_銷售.RowSource = ""
    End If
    
End Sub

貼完後程式可能不能直接使用,請於物件內容中新增事件:
以下物件新增「On Click」事件
「cmd_照供應商排序」
「cmd_照銷售額排序」
「List_產品資料」
以下物件新增「After Update」事件
「Combo_供應商」
這樣物件與程式才能成功串連起來,使用檢視時才能正常啟用各物件的程式碼。
點選「照供應商排序」時,會帶入一組SQL語句,依照供應商的名稱進行排序,並調整下拉選單欄位、寬度等資訊,最後使用Dropdown方法點選下拉選單。
http://ithelp.ithome.com.tw/upload/images/20161208/20007221xRiPyz5L3A.png

點選「照銷售額排序」時,則帶入另一組SQL,此語句除了列出供應商外,還列出該供應商於公司經手販售後的銷售額,且依照銷售額由高至低排序。
http://ithelp.ithome.com.tw/upload/images/20161208/20007221epXBxHsW7B.png

進行點選後,下方的商品清單方塊,將會更新為該供應商提供的商品。
http://ithelp.ithome.com.tw/upload/images/20161208/200072212GTcl9GA1m.png

由商品處再點選商品,下方的銷售圖表則會列出每季的銷售數量,切換商品後,也會跟著切換銷售圖表內容
http://ithelp.ithome.com.tw/upload/images/20161208/20007221JFEPlvF279.png

以此方式進行處理,將可於同版面顯示更多有關連的資料,這個部份與一般使用「子表單」處理方式有所不同,所有的過程會更加繁瑣、複雜與手動,但可調整性很高,很容易客製化特殊的查詢需求。

以下再來解說一下,由於Access的「表單」設計,預先是用來新增、編輯與修改資料,因此也是有載入RowSource來處理資料的,所以新增一個表單時,是可以看到下方有「紀錄瀏覽按鈕」、「分隔線」以及旁邊的「資料選取器」 功能。
http://ithelp.ithome.com.tw/upload/images/20161208/20007221LJX0jo1dnp.png

但是,以我們這種模式撰寫的「表單」,並不需要這些功能,可以於「設計檢視」下,點左上角的方格點2下:
http://ithelp.ithome.com.tw/upload/images/20161208/20007221wnl8ohgQBg.png

開啟表單的屬性,並將這「紀錄選取器」、「紀錄瀏覽按鈕」與「分格線」三個屬性改為否
http://ithelp.ithome.com.tw/upload/images/20161208/20007221Oc9gguCnqn.png

這些功能關閉後,整個視窗看起來就清爽多了
http://ithelp.ithome.com.tw/upload/images/20161208/200072217EAb7Ud7IO.png

以上教學,希望對各位有幫助。


上一篇
Access VBA 的眉眉角角Day6: SQL語法於VBA中的應用2
下一篇
Access VBA 的眉眉角角Day8: 於VBA中的設定值存取方法
系列文
Access VBA的眉眉角角30
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言