Hi Gicu
I couldnt get that working but I did some searching and found some code from 2000 which when adapted works well and does what I was looking for.
I posted it in case anyone else wants it. I am sure there is a way to just use a lable but I ended up using buttons as ran into a object not supported with lable.
Also had t make 6 buttons and hide them.... but it has been tested and works well.
Thanks to all, will mark solved in less someone has a better method and can educate me!
Code:
Private Function basOrderby(col As String, xorder As String) As Integer
Dim strSQL As String
'ClearCaptions
'Set row source for list box
strSQL = "SELECT DISTINCT BusinessID, BusinessName, CategoryName, SubCategoryName, CategoryID, SubCategoryID "
strSQL = strSQL & "FROM qryBusinessSearch "
strSQL = strSQL & "ORDER BY " & col & " " & xorder
Me!LstBusinessSearch.RowSource = strSQL
Me!LstBusinessSearch.Requery
End Function
Private Sub ResetSortCmdColor()
'Set reset color and capptions
CmdBusinessNameSort.ForeColor = RGB(217, 240, 255)
CmdBusinessNameSortDesc.ForeColor = RGB(217, 240, 255)
CmdIndustrySort.ForeColor = RGB(217, 240, 255)
CmdIndustrySortDesc.ForeColor = RGB(217, 240, 255)
CmdCategorySort.ForeColor = RGB(217, 240, 255)
CmdCategorySortDesc.ForeColor = RGB(217, 240, 255)
Me!CmdBusinessNameSortDesc.Caption = "Business Name"
Me!CmdBusinessNameSort.Caption = "Business Name"
Me!CmdIndustrySortDesc.Caption = "Industry"
Me!CmdIndustrySort.Caption = "Industry"
Me!CmdCategorySortDesc.Caption = "Category"
Me!CmdCategorySort.Caption = "Category"
End Sub
Private Sub CmdBusinessNameSort_Click()
'Set BusinessName order in ascending order and apply captions
ResetSortCmdColor
Dim response As Integer
response = basOrderby("BusinessName", "ASC")
Me!CmdBusinessNameSortDesc.ForeColor = RGB(100, 255, 255) ' blue
Me!CmdBusinessNameSortDesc.Visible = True
Me!CmdBusinessNameSortDesc.Caption = "Business Name (ASC)"
Me!CmdBusinessNameSortDesc.SetFocus
Me!CmdBusinessNameSort.Visible = False
Me!LstBusinessSearch.SetFocus
End Sub
Private Sub CmdBusinessNameSortDesc_Click()
'Set BusinessName order in descending order and apply captions
ResetSortCmdColor
Dim response As Integer
response = basOrderby("BusinessName", "DESC")
Me!CmdBusinessNameSort.ForeColor = RGB(75, 255, 79) ' Green
Me!CmdBusinessNameSort.Visible = True
Me!CmdBusinessNameSort.Caption = "Business Name (DESC)"
Me!CmdBusinessNameSort.SetFocus
Me!CmdBusinessNameSortDesc.Visible = False
Me!LstBusinessSearch.SetFocus
End Sub
Private Sub CmdCategorySort_Click()
'Set Category order in ascending order and apply captions
ResetSortCmdColor
Dim response As Integer
response = basOrderby("SubCategoryName", "ASC")
Me!CmdCategorySortDesc.ForeColor = RGB(100, 255, 255) ' blue
Me!CmdCategorySortDesc.Visible = True
Me!CmdCategorySortDesc.Caption = "Category (ASC)"
Me!CmdCategorySortDesc.SetFocus
Me!CmdCategorySort.Visible = False
Me!LstBusinessSearch.SetFocus
End Sub
Private Sub CmdCategorySortDesc_Click()
'Set Category order in descending order and apply captions
ResetSortCmdColor
Dim response As Integer
response = basOrderby("SubCategoryName", "DESC")
Me!CmdCategorySort.ForeColor = RGB(75, 255, 79) ' Green
Me!CmdCategorySort.Visible = True
Me!CmdCategorySort.Caption = "Category (DESC)"
Me!CmdCategorySort.SetFocus
Me!CmdCategorySortDesc.Visible = False
Me!LstBusinessSearch.SetFocus
End Sub
Private Sub CmdIndustrySort_Click()
'Set Industry order in ascending order and apply captions
ResetSortCmdColor
Dim response As Integer
response = basOrderby("CategoryName", "ASC")
Me!CmdIndustrySortDesc.ForeColor = RGB(100, 255, 255) ' blue
Me!CmdIndustrySortDesc.Visible = True
Me!CmdIndustrySortDesc.Caption = "Industry (ASC)"
Me!CmdIndustrySortDesc.SetFocus
Me!CmdIndustrySort.Visible = False
Me!LstBusinessSearch.SetFocus
End Sub
Private Sub CmdIndustrySortDesc_Click()
'Set Industry order in descending order and apply captions
ResetSortCmdColor
Dim response As Integer
response = basOrderby("CategoryName", "DESC")
Me!CmdIndustrySort.ForeColor = RGB(75, 255, 79) ' Green
Me!CmdIndustrySort.Visible = True
Me!CmdIndustrySort.Caption = "Industry (DESC)"
Me!CmdIndustrySort.SetFocus
Me!CmdIndustrySortDesc.Visible = False
Me!LstBusinessSearch.SetFocus
End Sub