如何基于组合框访问VBA中选择的项目对列表框进行排序 [英] How to Sort A Listbox based on Item Selected in combobox Access VBA

问题描述

我想根据我的组合框中的所选项目对列表框项目进行排序。

我使用的是访问vba数据库2007，以下是我的代码

 私有  Sub  QueryExecutives（）
 sqlString =   SELECT Executive Correspondence.Year，Executive Correspondence.OurDate，Executive Correspondence.LogID，Executive Correspondence.Subject， Executive Correspondence.Contact，Executive Correspondence.Director，Executive Correspondence.ADM，Executive Correspondence.DM，Executive Correspondence.Minister _ 
&   FROM Executive Correspondence; 
 结束  Sub  

开我的组合框排序点击这里是我的代码：

 私人 < span class =code-keyword> Sub  cmbSortExecutives_Click（）
  Dim  strSourceExecutives  As  字符串 
 选择 案例 cmbSortExecutives.ListIndex 
 案例  0  
 调用 QueryExecutives 
 orderString =  按年份订购Asc 
 案例  1  
 致电 QueryExecutives 
 orderString =  按照OurDate Asc订购 
 案例  2  
 调用 QueryExecutives 
 orderString =   按LogID Asc排序 
 案例  3  
 调用 QueryExecutives 
 orderString =  按主题Asc排序 
 案例  4  
 调用 QueryExecutives 
 orderString =  通过联系Asc订购 
 案例  5  
 < span class =code-keywor d>调用 QueryExecutives 
 orderString =  由董事Asc订购 
 案例  6  
 致电 QueryExecutives 
 orderString =  订购ADM Asc 
 案例  7  
 致电 QueryExecutives 
 orderString =  按DM Asc排序 
 案例  8  
 调用 QueryExecutives 
 orderString =  由部长Asc订购 
 结束 选择 
 strSourceExecutives = sqlString + orderString 
 我 .lstViewExecutive.RowSource = strSourceExecutives 
  Me  .lstViewExecutive =  Me  .lstViewExecutive.ItemData（ 1 ）
 结束  Sub  

选择每个项目后，列表框为空，我不知道我做得不好



我在模块中声明了orderString和sqlString全球变量

请急需帮助，并提前致谢



Chukse

解决方案

首先，请阅读：变量范围 [ ^ ]。



你不需要任何全局变量和选择案例...结束选择语句。您的查询字符串错误！

 私有  Sub  cmbSortExecutives_Click（）
  Dim  sqlString  As  < span class =code-keyword>字符串，sSort  AS  字符串 
 
 sqlString =   SELECT [Year]，[OurDate]，[LogID]，[Subject]，[Contact ]，[主任]，[ADM]，[DM]，[部长]& vbcr& _ 
   FROM [Executive Correspondence]& vbcr 
 sSort =   ORDER BY& cmbSortExecutives.Value&   ASC; 
 
 我 .lstViewExecutive.RowSource = sqlString 
 结束  Sub  

I want to sort a list box items based on the selected item in my combo box.
I am using access vba database 2007 and below is my code

Private Sub QueryExecutives()
sqlString = "SELECT Executive Correspondence.Year, Executive Correspondence.OurDate, Executive Correspondence.LogID, Executive Correspondence.Subject, Executive Correspondence.Contact, Executive Correspondence.Director, Executive Correspondence.ADM, Executive Correspondence.DM, Executive Correspondence.Minister" _
      &"FROM Executive Correspondence ";
End Sub

On my combo box Sort click here is my code:

Private Sub cmbSortExecutives_Click()
Dim strSourceExecutives As String
Select Case cmbSortExecutives.ListIndex
    Case 0
        Call QueryExecutives
         orderString = "ORDER by Year Asc "
    Case 1
         Call QueryExecutives
         orderString = "ORDER by OurDate Asc "
    Case 2
         Call QueryExecutives
         orderString = "ORDER by LogID Asc "
    Case 3
         Call QueryExecutives
         orderString = "ORDER by Subject Asc "
    Case 4
         Call QueryExecutives
         orderString = "ORDER by Contact Asc "
    Case 5
         Call QueryExecutives
         orderString = "ORDER by Director Asc "
    Case 6
         Call QueryExecutives
         orderString = "ORDER by ADM Asc "
    Case 7
         Call QueryExecutives
         orderString = "ORDER by DM Asc "
    Case 8
         Call QueryExecutives
         orderString = "ORDER by Minister Asc "
End Select
strSourceExecutives = sqlString + orderString
Me.lstViewExecutive.RowSource = strSourceExecutives
Me.lstViewExecutive = Me.lstViewExecutive.ItemData(1)
End Sub

After selecting each item the list box is empty and I don't know what I am not doing fine

I declared orderString and sqlString in my module as Global variables
Please urgent help is needed and thanks in advance

Chukse

解决方案

First of all, please read this: scope of variables[^].

You don't need any global variable and Select Case ... End Select statement. Your query-string is wrong!

Private Sub cmbSortExecutives_Click()
Dim sqlString As String, sSort AS String

sqlString = "SELECT [Year], [OurDate], [LogID], [Subject], [Contact], [Director], [ADM], [DM], [Minister]" & vbcr & _
"FROM [Executive Correspondence]" & vbcr 
sSort = "ORDER BY " & cmbSortExecutives.Value & " ASC;"

Me.lstViewExecutive.RowSource = sqlString
End Sub

这篇关于如何基于组合框访问VBA中选择的项目对列表框进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！