I borrowed and modified this listbox to be able to view and select records and bring them up in the main form. However projects should be defined 1-5 5 being complete. I am trying to filter the listbox view of the 5s if there were any by a selection of a button so that when selected all 5s are omitted and when not selected they are shown in the view? different approaches have resulted in one issue stating that the form itself isnt bound or linked to a field/table. Is there a way to do this inside the vba by simply selecting a button?
I was going to consider a macro as well but it wont work either.. I am open to ideas or suggestions
the only other thing i can think of doing that is cheesy is making a switchboard and copy the code twice and alter the query for one of the list boxes to exclude the 5s in a view..
SELECT tblPrimaryData.Project_Name, tblPrimaryData.Project_Creator, tblPrimaryData.Project_Status FROM tblPrimaryData ORDER BY tblPrimaryData.Project_Name;
this is what I have setup for the row source of the list box
this is the vba that is setup
rivate 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
i guess i can revise my question to try to make it easier for me. I would like to filter by project status when a check box is selected. When the selection is made I would like to omit all fives from view so the filter would be <>5. Is there a way I can amend the query to have a where project_status=[put the button name here] so when the button is pressed it would pass the value over?
Well, if you define StrSQL as a public variable you should be able to add a bit of code into a check box, that adds a WHERE condition into the variable to restrict the list to 5s only. Then requery the form/subform, it should show the correct result.
I came up with this, put it behind a button and see if it does what you want
Code:
Private Sub FilterFives_Click()
On Error GoTo Err_FilterFives_Click
Dim strSql As String
strSql = "SELECT tblPrimaryData.Project_Name, tblPrimaryData.Project_Creator, tblPrimaryData.Project_Status FROM tblPrimaryData WHERE (((tblPrimaryData.Project_Status) <> '5')) ORDER BY tblPrimaryData.Project_Name;"
lstSearch.RowSource = strSql
Exit_FilterFives_Click:
Exit Sub
Err_FilterFives_Click:
Resume Next
Resume Exit_FilterFives_Click
End Sub
while i get no error code i set the below code you provided for the on click however it does not filter anything.. no error is especially nice though.. resulting code for the list box is as follows
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 FilterFives_Click()
On Error GoTo Err_FilterFives_Click
Dim strSql As String
strSql = "SELECT tblPrimaryData.Project_Name, tblPrimaryData.Project_Creator, tblPrimaryData.Project_Status FROM tblPrimaryData WHERE (((tblPrimaryData.Project_Status) <> '5')) ORDER BY tblPrimaryData.Project_Name;"
lstSearch.RowSource = strSql
Exit_FilterFives_Click:
Exit Sub
Err_FilterFives_Click:
Resume Next
Resume Exit_FilterFives_Click
End Sub
Private Sub cmdOrderProjectName_Click()
'Set First Name order in ascending order and apply captions
Dim response As Integer
response = basOrderby("Project_Name", "asc")
Me!cmdOrderProjectNameDesc.Visible = True
Me!cmdOrderProjectNameDesc.Caption = "v Order by Project Name v"
Me!cmdOrderProjectNameDesc.SetFocus
Me!cmdOrderProjectName.Visible = False
Me!lstSearch.SetFocus
End Sub
Private Sub cmdOrderProjectNameDesc_Click()
'Set First Name order in descending order and apply captions
Dim response As Integer
response = basOrderby("Project_Name", "DESC")
Me!cmdOrderProjectName.Visible = True
Me!cmdOrderProjectName.Caption = "^ Order by Project Name ^"
Me!cmdOrderProjectName.SetFocus
Me!cmdOrderProjectNameDesc.Visible = False
Me!lstSearch.SetFocus
End Sub
Private Sub cmdOrderProjectCreator_Click()
'Set Last Name order in ascending order and apply captions
Dim response As Integer
response = basOrderby("Project_Creator", "asc")
Me!cmdOrderProjectCreatorDesc.Visible = True
Me!cmdOrderProjectCreatorDesc.Caption = "v Order by Project Creator v"
Me!cmdOrderProjectCreatorDesc.SetFocus
Me!cmdOrderProjectCreator.Visible = False
Me!lstSearch.SetFocus
End Sub
Private Sub cmdOrderProjectCreatorDesc_Click()
'Set Last Name order in descending order and apply captions
Dim response As Integer
response = basOrderby("Project_Creator", "DESC")
Me!cmdOrderProjectCreator.Visible = True
Me!cmdOrderProjectCreator.Caption = "^ Order by Project Creator ^"
Me!cmdOrderProjectCreator.SetFocus
Me!cmdOrderProjectCreatorDesc.Visible = False
Me!lstSearch.SetFocus
End Sub
Private Sub cmdOrderProjectStatus_Click()
'Set Region order in ascending order and apply captions
Dim response As Integer
response = basOrderby("Project_Status", "asc")
Me!cmdOrderProjectStatusDesc.Visible = True
Me!cmdOrderProjectStatusDesc.Caption = "v Order by Project Status v"
Me!cmdOrderProjectStatusDesc.SetFocus
Me!cmdOrderProjectStatus.Visible = False
Me!lstSearch.SetFocus
End Sub
Private Sub cmdOrderProjectStatusDesc_Click()
'Set Region order in descending order and apply captions
Dim response As Integer
response = basOrderby("Project_Status", "DESC")
Me!cmdOrderProjectStatus.Visible = True
Me!cmdOrderProjectStatus.Caption = "^ Order by Project Status ^"
Me!cmdOrderProjectStatus.SetFocus
Me!cmdOrderProjectStatusDesc.Visible = False
Me!lstSearch.SetFocus
End Sub
Private Sub lstSearch_AfterUpdate()
'Once a record is selected in the list, enable the showRecord button
ShowRecord.Enabled = True
End Sub
Private Sub lstSearch_DblClick(Cancel As Integer)
'If the user double-clicks in the list, act as though
'the ShowRecord button was clicked
If Not IsNull(lstSearch) Then
ShowRecord_Click
End If
End Sub
Private Sub ShowRecord_Click()
'Find a selected record, then close the search dialog box
DoCmd.OpenForm "frmMain", , , _
"[tblPrimaryData.Project_Name]=" & "'" & Me.lstSearch.Column(0) & "'"
'Close the dialog box
DoCmd.Close acForm, "frmListBoxSearch"
End Sub
Private Sub Cancel_Click()
On Error GoTo Err_Cancel_Click
'Cancel and close the form
DoCmd.Close
Exit_Cancel_Click:
Exit Sub
Err_Cancel_Click:
MsgBox Err.Description
Resume Exit_Cancel_Click
I came up with this, put it behind a button and see if it does what you want
Code:
Private Sub FilterFives_Click()
On Error GoTo Err_FilterFives_Click
Dim strSql As String
strSql = "SELECT tblPrimaryData.Project_Name, tblPrimaryData.Project_Creator, tblPrimaryData.Project_Status FROM tblPrimaryData WHERE (((tblPrimaryData.Project_Status) <> '5')) ORDER BY tblPrimaryData.Project_Name;"
lstSearch.RowSource = strSql
Exit_FilterFives_Click:
Exit Sub
Err_FilterFives_Click:
Resume Next
Resume Exit_FilterFives_Click
End Sub
Take a look at what I did based on the sample you gave me and see if you can figure it out. Does your data have anything with a 5 in it? I didnt see any when you gave it to me so I had to put one in there.
no i had to actually add a 5 to it as none of my dummy records were setup with that value. Taking a look as we speak. I was looking at the other thread you referenced and wondering if the if statement is the way to go.
one thing to note is that it states there is abroken reference to the file acedao.dll when i open the file you attached. Looking at it now and trying to figure out if there is a way to put a requery in there once the button is deselected. This is quite interesting.. is this the preferred way or would it be better to attempt an if/else statement to the strsql variable
K im not sure whats going on there, but put this code behind your checkbox
Code:
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 ORDER BY tblPrimaryData.Project_Name;"
End If
lstSearch.RowSource = strSql
if i follow your same logic on this and add a second button to view only those that equal five am I going to run into an issue if both buttons are pressed? In essence how would access know which button has priority in terms of the modified where clause?
if i follow your same logic on this and add a second button to view only those that equal five am I going to run into an issue if both buttons are pressed? In essence how would access know which button has priority in terms of the modified where clause?
I am seeing how an option group works with the following options so that only one button can be selected.
remove completed projects
view completed projects
view all records (default)
I thought it would be easy to go in and rename the toggle button to the check11 button however there is no onclick area to assign an event to each button.
i assumed using the code for each button modified so that you would have <> 5, =5 and then no filter. What is the different approach that I am looking at when going from check boxes to option groups? Is it driven by the actual value of the selection vs the click?
