I've received yet another feature request for my main DB. A while back, I was trying to make a search form to filter the records in my main form. A kind fellow suggested I use the right-click menu and tell others to use the filter option in there. It's worked, but people would still prefer not to right click on a field to go to a filtering box. They're very "left-click-oriented".
So:
Question #1
Is it possible to make a button that will bring up the same "Text Filters -> Equals..." box as would come up if someone used the right click menu?
And/Or
Question #2
Is it possible to filter a bunch of records in a form by typing into a field? (I'm not finding the right words right now... here's an example)
[Field1data]-----------------------[Field2data]
[type here to filter field1]--------[type here to filter field2]
Am I explaining that clearly? Is either possible? Does diet pepsi taste just as good as regular pepsi?
are the records being shown in a listbox? maybe put a textbox(if you cant anticipate what they might filter on) or combo box (if you can) on the form with a button. when the button is clicked youll take the text from that control and build a sql statement. now set the listbox's data source to that sql statment. if you need specifics on how to do this, let me know
are the records being shown in a listbox? maybe put a textbox(if you cant anticipate what they might filter on) or combo box (if you can) on the form with a button. when the button is clicked youll take the text from that control and build a sql statement. now set the listbox's data source to that sql statment. if you need specifics on how to do this, let me know
The records fields of the records by which I would like to filter are in a listbox/combobox, yes. I cannot anticipate by which values of the two fields my users would like to filter.
I understand, conceptually, making a field and a button to permute data put into that field, but everything you mention after that is beyond me. I'm no programmer and am using access's UI-based commands and options a million times more than I use straight SQL.
first, you should no im no expert and im sure my code and process may not be the best way to deal with the problem. also, if i read your question right you have this scenario: on a form you have a list box with a bunch of values. you want the user to be able to type a value in2 a textbox, hit a button, and filter the listbox according to the textbox. if not, what im about to show you will still work with some tweeking
that being said:
the first thing youll need to do is add the controls (buttons, fields, etc) to your form.
1. open the form in design view. (ill assume you know how to do this and some other basics)
2. add these controls: button, textfield. when you add the button, a wizard will try to start. just hit cancel. we're doing this alone
3. now, double click the button to bring up the buttons properties box. 4. open the event tab
5. select the onclick event, and click the button with three dots
6. select code builder. this will open up the vba edit page.
from here we start coding.
1. first, add some error checking code. this is so if your app. causes an error, it wont crash the program
Code:
Private Sub Command2_Click()
On Error GoTo errorhandler
errorhandler:
If Err.Number = 0 Then
Else
MsgBox (Err.Description)
end if
End Sub
2. now we build an sql string from the users input. youll need to add two variables (just like in math class, a variable X holds some value). add this between the error handler top and bottom half
Code:
Dim GetFilterValue As String
Dim SqlString As String
3. now assign the variables a value. add this after declaring your variables. now, because i dont know what your filtering, youll have to come up with your own sql code. ill put an example in
Code:
Me.Text0.SetFocus 'before refferencing a control, you must set focus to it
GetFilterValue = Me.Text0.Text
SqlString = "Select Username from tblClientInfo where FirstName = '" & GetFilterValue & "'"
' this sql statement is saying, select the column titled "Username"
' from the table "tblClientInfo" where the column FirstName is equal to
' the value of GetFilterValue. everything between double quotes is a
' string. the & joins the string with our variable making one complete
' sql string. to view it uncomment below
' msgbox(SqlString)
4. now you must assign this this sql string to the listbox rowsource. i assume you have the listbox bound to some table. so, programatically, we need to change that, and add our own string. add this bellow the sqlstring
Code:
Me.List3.SetFocus 'set focus
Me.List3.ControlSource = "" 'removes binding to table
Me.List3.RowSourceType = "table/query"
'sets the rowsource type to a query
Me.List3.RowSource = SqlString 'sets rowsource to the sqlstring
Me.List3.Requery 'refreshes the listbox
that should do it. now, im not sure how you set up the initial form, so there might still be errors. im assuming this form is databound to some table and you created it with a wizard (if you dont know coding, id imagine this is how youd do it.) your complete code will look like this:
Code:
Private Sub Command8_Click()
On Error GoTo errorhandler
Dim GetFilterValue As String
Dim SqlString As String
Me.Text0.SetFocus 'before refferencing a control, you must set focus to it
GetFilterValue = Me.Text0.Text
MsgBox (GetFilterValue)
SqlString = "Select * from table1 where s = '" & GetFilterValue & "'"
' this sql statement is saying, select the column titled "Username"
' from the table "tblClientInfo" where the column FirstName is equal to
' the value of GetFilterValue. everything between double quotes is a
' string. the & joins the string with our variable making one complete
' sql string. to view it uncomment below
MsgBox (SqlString)
Me.List3.SetFocus 'set focus
Me.List3.ControlSource = "" 'removes binding to table
Me.List3.RowSourceType = "Table/Query"
'sets the rowsource type to a query
Me.List3.RowSource = SqlString 'sets rowsource to the sqlstring
Me.List3.Requery 'refreshes the listbox
MsgBox (Me.List9.RowSourceType)
errorhandler:
If Err.Number = 0 Then
Else
MsgBox (Err.Description)
End If
End Sub
