UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Form Or List Box Filtering Based On Few Text Boxes    
 
   
Tiesto_X
post Mar 5 2011, 05:13 AM
Post#1



Posts: 335
Joined: 27-July 10



Hi.
found example of how to filter list box based on one textbox. It works perfect. But, is there way to filter with more than one text box (or combinations textbox, combobox and date picker). For example, I want to filter by name, date and company fields.
Tnx!
Go to the top of the page
 
vtd
post Mar 5 2011, 05:56 AM
Post#2


Retired Moderator
Posts: 19,667
Joined: 14-July 05



You can add similar criteria joined by the Boolean And (or Or) operators...
If you have 3 or more criteria joined by a combination of And and Or operators, make sure you use parentheses to specify exactly the order of operation you want.
Go to the top of the page
 
Peter Hibbs
post Mar 5 2011, 07:40 AM
Post#3


UtterAccess VIP
Posts: 1,682
Joined: 17-June 10
From: Dorset. UK.


Tiesto,
might help if we knew which example you have found but in case you did not find this one - try the attached A2003 demo.
Peter Hibbs.
Attached File(s)
Attached File  Filter_Demo_2003.zip ( 65.29K )Number of downloads: 196
 
Go to the top of the page
 
Tiesto_X
post Mar 5 2011, 08:17 AM
Post#4



Posts: 335
Joined: 27-July 10



Hi guys. Tnx for your answers.
eter, that filter demo is 100% of what I need! I cant believe how simple that is, i wasted 3 hours with bunch of non-sens stuffs.
I just found out how to filter form with few criterias. In case that someone needs, here it is:
CODE
Private Sub CheckFilter()
    Dim strFilter As String, strOldFilter As String
On Error GoTo WrongData
    strOldFilter = Me.Filter
    
     If Me!ComboBoxName.Value > "" Then _
        strFilter = strFilter & _
                    " AND ([FieldToFilterName] Like '" & _
                    Me!ComboBoxName.Value & "*')"
If Me!ComboBox2Name.Value > "" Then _
        strFilter = strFilter & _
                    " AND ([Field2ToFilterName] Like '" & _
                    Me!ComboBox2Name.Value & "*')"
If Me!TextBoxName.Value > "" Then _
        strFilter = strFilter & _
                    " AND ([FieldToFilterName] Like '" & _
                    Me!TextBoxName.Value & "*')"
                    
    Debug.Print ".Filter = '" & strOldFilter & "' - ";
    Debug.Print "strFilter = '" & strFilter & " '"
  
    If strFilter > "" Then strFilter = Mid(strFilter, 6)
    If strFilter <> strOldFilter Then
        Me.Filter = strFilter
        Me.FilterOn = (strFilter > "")
    End If
    Exit Sub
WrongData:
MsgBox "Error", vbInformation, "Try again."
End Sub
Private Sub CommandButtonName_Click()
Call CheckFilter
Forms!FromName.Requery
End Sub

You can add criterias as much as you need.
And once again, very tnx Peter!
Go to the top of the page
 
camp0s
post Mar 14 2011, 04:54 PM
Post#5



Posts: 6
Joined: 14-March 11



Wow!
I'm starting to use access, I've already played with SQL (but not with Access) - can I ask (not the code) what is the idea, the working principle inside this example to have search-as-you-type feature? especially for the 3rd option, sub-form search. I can understand ""programming"", but I have no clue on Access programming.. sorry I know, it's such a noob question :\
The code reside in:
CODE
Private Sub txtCountry_Change()
Me.txtCountryX = Me.txtCountry.Text
Me.frmCustomerList3Sub.Requery
End Sub

and in the subform tables SQL:
CODE

LIKE [forms]![frmCustomerList3]![txtCountryX] & "*"

Correct?
I'm just toying around access, I want to see if I could put togheter a simple, fun and small DB to help my father in the bazar trying to keep track of goods < - I'm reading some books (first attemp), following some courses but nowhere it's mentioned such feature (not yet started VBA tough).
Tnx!
Go to the top of the page
 
Peter Hibbs
post Mar 15 2011, 09:10 AM
Post#6


UtterAccess VIP
Posts: 1,682
Joined: 17-June 10
From: Dorset. UK.


Camp0s,
OK, not quite sure exactly what you are asking for but here is a brief description :-
Referring to the third example (which seems to be the one you are most interested in), the sub-form is bound to a query which returns the relevant fields from the table and displays them on screen. The Like function is used to filter some of the fields in this query based on the equivalent fields on the main form. Using the * character after the field definition (and also before in some fields) matches any text in the field where the text in the main field Text box is the same as that in the field in the query. If the field on the main form is blank, then the query will return every record because the * character (in effect) matches every character in the field. If you enter, say, BL in the Cust ID field, the query will return BLAUS and BLONP because the Like function will match BL*, that is any Cust ID that starts with BL and any other characters. The Like function is a powerful command and has many variations which you should look up in the Help file.
On the main form there are several Text boxes which can be used to filter on several fields in the query. When you enter text into any of these fields, each keypress triggers the control's On Change event which requerys the subform to show the new list of records. Before that though, the contents of the Text box Text property is copied to the equivalent hidden field on the main form (the Text property of a control holds the currently entered text while the Value property holds the text that has been entered after the control loses the focus). To filter on more than one field at once, it is necessary to use this method because the Text property cannot be used in the query once the control has lost the focus and so the contents of the Text property are copied to another control which is then used by the query.
Of course, in a practical application, you would return the unique ID of the selected record to the form which opens the filter form (perhaps in a global variable) and then use that to locate the selected record. I use this system frequently in applications where, for example, you might have a form showing members of an organisation and when I want to allow the user to find one, I give them a button which opens the Filter form, they would choose a membership record using the filter and then return the ID of the member's record to the main membership form which would use the FindRecord command to open the relevant membership record.
Don't know if that helps.
Peter Hibbs.
Go to the top of the page
 
camp0s
post Mar 18 2011, 07:56 AM
Post#7



Posts: 6
Joined: 14-March 11



All right! that was really usefull, I found this example too to be really good: http://www.utteraccess.com/forum/Search-so...t-t1958074.html and I think I should be able to boostrap my single table "db" from it too.
efore digging just into code, which might be seen as "the" solution, I'm still concerned about the DB interface organization, which view/add/edit windows should I use, how to user would interact and so on iconfused.gif
I'll promptly come back here to ask specific question, or open a new thread (in a suitable section of the board) to shape my application as a proceed.
Tnx again Peter!
Go to the top of the page
 
camp0s
post Mar 18 2011, 07:26 PM
Post#8



Posts: 6
Joined: 14-March 11



One more thing, I know it may be slighty off topic, but, if I would have to ask some broad base/reference questions concernig VBA, in which section should I post? (I'm missing the VBA section or..). Tnx.
Go to the top of the page
 
e60_pa
post May 8 2019, 04:46 PM
Post#9



Posts: 2
Joined: 11-May 14



Is it possible to have a criteria for a currency value that the user can enter greater than, less than and there by filtering the list box? Not a pressing thing at all, I just thought it might provide additional value in one of my forms. Thanks!
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    27th May 2019 - 02:14 AM