unbound list box selection/filtering

[miken5678]

no worries.. I greatly appreciate the walkthrough.. I am going to modify a couple items and post the final view.. I am going off the premise of altering the main strsql query to exclude the completed items from the beginning and then have the button bring up on those that are complete.

 

[rainman89]

You can can write alittle code for the checkboxes to make it so that if one is selected the others aren't

 

[miken5678]

i ended up excluding 5's from the view to begin with and then once the button was selected it views only those that are 5s however once deselected it showed everything as i forgot to modify the click code. I have yet to try multiple buttons and greying out one button if another is selected.

Option Compare Database
Option Explicit
Private Function basOrderby(col As String, xorder As String) As Integer
Dim strSql As String
'Clear captions from command buttons
ClearCaptions
'Set row source for list box
strSql = "SELECT DISTINCTROW Project_Name, Project_Creator, Project_Status "
strSql = strSql & "FROM tblPrimaryData "
strSql = strSql & "ORDER BY " & col & " " & xorder
Me!lstSearch.RowSource = strSql
Me!lstSearch.Requery
End Function
Sub ClearCaptions()
'Clear captions of asc and desc symbols
Me!cmdOrderProjectNameDesc.Caption = "Order by Project Name"
Me!cmdOrderProjectName.Caption = "Order by Project Name"
Me!cmdOrderProjectCreatorDesc.Caption = "Order by Project Creator"
Me!cmdOrderProjectCreator.Caption = "Order by Project Creator"
Me!cmdOrderProjectStatusDesc.Caption = "Order by Project Status"
Me!cmdOrderProjectStatus.Caption = "Order by Project Status"
End Sub
Private Sub Check11_Click()
Dim strSql As String
If Me.Check11 = True Then
strSql = "SELECT tblPrimaryData.Project_Name, tblPrimaryData.Project_Creator, tblPrimaryData.Project_Status FROM tblPrimaryData WHERE (((tblPrimaryData.Project_Status) = '5')) ORDER BY tblPrimaryData.Project_Name;"
Else
strSql = "SELECT tblPrimaryData.Project_Name, tblPrimaryData.Project_Creator, tblPrimaryData.Project_Status FROM tblPrimaryData WHERE (((tblPrimaryData.Project_Status) <> '5')) ORDER BY tblPrimaryData.Project_Name;"
End If
lstSearch.RowSource = strSql
End Sub

 

[miken5678]

thank you tons for the help on this.. i was pulling my hair out because there was no table linked to the form and access macros and buttons wouldnt filter it correctly

again much thanks.. a lifesaver

 

[miken5678]

out of curiosity your one thread you pointed to a while back talked about requerying... as there could be in theory several users is it as simple as adding the requery to the button itself?

 

[miken5678]

one issue that showed up is that if i can sort based on the three top buttons perfectly. However, when i select my view completed projects button only the 5 shows as intenede but once i hit the sort buttons all records come back so it isnt sorting the five.. but the button is still selected. any thoughts

 

[miken5678]

http://www.access-programmers.co.uk/forums/showthread.php?t=77694

is it possible to set my global variable to an if function?


where

Private Function basOrderby(col As String, xorder As String) As Integer
Dim strSql As String
'Clear captions from command buttons
ClearCaptions
'Set row source for list box
strSql = "SELECT DISTINCTROW Project_Name, Project_Creator, Project_Status "
strSql = strSql & "FROM tblPrimaryData "
strSql = strSql & "WHERE Project_Status <> '5'"
strSql = strSql & "ORDER BY " & col & " " & xorder
Me!lstSearch.RowSource = strSql
Me!lstSearch.Requery
End Function

can be turned into an if statement based on the button selection?

what would need to be done to change the actual listbox query property itself on the form?

 

[miken5678]

i tried this but with no luck

Private Function basOrderby(col As String, xorder As String) As Integer
Dim strSql As String
'Clear captions from command buttons
ClearCaptions
'Set row source for list box
If Me.Check25 = -1 Then
strSql = "SELECT DISTINCTROW Project_Name, Project_Creator, Project_Status "
strSql = strSql & "FROM tblPrimaryData "
strSql = strSql & "WHERE Project_Status <> '5'"
strSql = strSql & "ORDER BY " & col & " " & xorder
ElseIf Me.Check25 = 0 Then
strSql = "SELECT DISTINCTROW Project_Name, Project_Creator, Project_Status "
strSql = strSql & "FROM tblPrimaryData "
strSql = strSql & "ORDER BY " & col & " " & xorder

End If

Me!lstSearch.RowSource = strSql
Me!lstSearch.Requery
End Function

 

[boblarson]

A couple of problems:

strSql = strSql & "WHERE Project_Status <> '5'"
strSql = strSql & "ORDER BY " & col & " " & xorder


Should be:

strSql = strSql & "WHERE Project_Status <> '5' "
strSql = strSql & "ORDER BY " & col & ", " & xorder

(a space is needed after the red part in the first line) and you really should normalize your data structure.

 

[miken5678]

when you say normalize you are in reference to? The limited data that is being captured fits on one table without anything that needs to be setup in another linked table. Or so I thought.

Your suggested edits didnt result in any problems but i still get no action based on the actual button activiation... still a bit puzzled and stuck

Private Function basOrderby(col As String, xorder As String) As Integer
Dim strSql As String
'Clear captions from command buttons
ClearCaptions
'Set row source for list box
If Me.Check25 = -1 Then
strSql = "SELECT DISTINCTROW Project_Name, Project_Creator, Project_Status "
strSql = strSql & "FROM tblPrimaryData "
strSql = strSql & "WHERE Project_Status <> '5' "
strSql = strSql & "ORDER BY " & col & ", " & xorder
ElseIf Me.Check25 = 0 Then
strSql = "SELECT DISTINCTROW Project_Name, Project_Creator, Project_Status "
strSql = strSql & "FROM tblPrimaryData "
strSql = strSql & "ORDER BY " & col & ", " & xorder

End If

Me!lstSearch.RowSource = strSql
Me!lstSearch.Requery
End Function

 

[boblarson]

1. What version of Access are you using?

2. By normalization, I mean your data structure is properly designed where you have tables designed to not have repeating fields (things like Research_&_Analysis_Original, Research_&_Analysis_2, Research_&_Analysis_3 etc.) and more.

See here for a thorough explanation of normalization.

 

[miken5678]

using 03..

thanks for the link.. i actually found that lastnight after reading your post to learn about it.. i thought i was ok as those duplicate fields are for different completion dates should the original start date be exceeded or an issue comes up. I could split them up however Last time I tried putting in multiple tables to the properties of a form I had issues. I guess subforms would work but I was trying to do something rather quick and simple and see if could add extra filtering.. seems part of my idea works but the other doesnt.. i think it is the button click that is over riding the chekbutton for adding the fives so it reverts to the original query

probably sounds like garble.. just need to figure out a way when the button is selected to show 5's that when you hit the sort buttons up top the 5's stay