Hello AWF,
I've been trying to implement @MajP 's FAYT continuous form class to filter two subforms from my main form, but I can only get it filter 1 subform and not both at the same time. I'd like to figure this out so that I can reuse this concept.
I've tried initializing the class from each subform load event, passing in the subform names and initializing from the main form, calling a public sub on each subform from the main form load event that calls the FAYT class initialize sub, and also making a 2nd class and changing names to L/R for each sub/class name, but still, only one subform would filter.
This filter only applies to task_list_id field? Why repeat "task:"? I type a number and neither form filters, although I see flickering on both like something is happening.
I've never used custom classes. May offer advantage because you want to filter subforms. So don't know if Allen Browne code can be directly applied http://allenbrowne.com/AppFindAsUType.html
Right now it does only default to task as that is how it is initialized, and I keep getting object variable not set with the way that sample db is with the after update events on the main form for the option controls. I'm struggling to reference the subform/form between code in main form and subform.
The only way that I had all options working previously was with two separate classes, but I don't think that is the solution..
I mentioned in the other thread that I would have thought you would need to set each form as a class. However you said you already tried that?
I will wait to hear from @MajP as it is his code.
Understood.. I wasn't expecting it to work, as it needs to look at +2 forms (subforms) recordsources instead of the 1 main recordsource..
But I was thinking there must be some way to modify it to make it work instead of just hardcoding a form sub/function solution. As I would have liked to be able to reuse the class in other databases.
I was thinking it may have been somewhat simple but I'm not versed with classes at all. I did make my own NW2 error logging to a table and declared public attributes, etc, but this one has been giving me a tough time.
I got it to work but this is kind of putting a square peg in a round hole
1. Normally you would use this type of search on longer text fields. Something like a station name or task id can be searched using a combo or better ui.
2. You are trying to search two different subforms with two vastly different amount of records using the same field at the same time. Unfortunately this code will react when nothing is found so when it is not found in one list and found in the other it causes a problem. I would have to take that code out. So if nothing is found in one recordset the code does not react. I had to make two seperate search boxes.
3. You are searching two completely unrelated field contents. That makes no sense.
Station A7 is never going to be searched with task 8313. So searching both these fields makes not logical sense. You are either searching for a task or searching for a Station but never both. That kind of search makes sense if searching for "Part Failure" in the comments field, notes field, title, or description.
I think you could come up with a better search. My guess when searching for a station you know what station you are looking for. You want to filter A7 not I think it has a 7 in the name or an A in the name, or maybe its a B. But I will know once I see it. Show me all the As and then I will see if it is A5, or A6, or A7. I have to imagine the same with task. I think there is a 3 somewhere in the ID? I doubt that is how your are searching. At worst you know it is 83x. But I assume you know exactly what tasks you are looking for and you want to filter to that tasks. This type of FAYT text search does not really seem how you normally operate with searching IDs.
If you had a wish on how to actually need to search describe that and maybe we can come up with a better UI.
Are your really searching both lists at the same time looking for like records. This can be done.
Ultimately - the reason that I am implementing this is because the db users want to be able to search/filter/find things easier.
The subforms on this form have the same recordsource, but it is an assembly line with a left and right side, so they like to view the work instructions as the assembly line moves from station to station, able to see the left and right side operations at the same time, and the left subform is only LEFT station tasks, and the right subform is RIGHT and all 'other' categories.
The real db tasks are not task 1, task 2, etc, but more like 200 character search fields.. So we assemble a vehicle, maybe they want to search 'frame' and filter all frame related tasks.. or a subset of stations, our left side line would be a1, a3, a5, a7, and right side line would be a2, a4, a6, so maybe they want that zone operations, they can just search 'a' and get that subset of operations..
I initially picked up your filter class, but when demoing with the users, they also wanted the search bar like is present in excel's filter dropdown, but after looking at access, I don't think it is a native capability, so your FAYT function is what I found.. I think the biggest differences between the form and combo is that the form FAYT still allows many records to be viewed at once - it isn't the same as the excel dropdown filter as when you start typing it shows you all records in the search criteria, and then you can select all or choose what ones you want to view, but if i did a FAYT combo - they can see all records from that one field to search through, but can only choose one record from the FAYT combo to view once selecting something. I would rather the subforms be filtered as they type what they are searching for to mimic the Excel filter search bar.
--- not a request, just explaining the journey to where I am with this.
And yes - instead of being able to multi-select search fields, I was going to have this be a one field search. Like you said, no sense in searching station and task in the same search text box. More of drilling down to see all related records on ONE field being searched at a time...
This should do what you want.
1.Look at the subform queries and see the two calculated fields I added
strTask and strStat.
Modify those fields in the CSTR to match whatever fields you are actually filtering for the task and station.
If the fields you are filtering are not numeric then you do not have to convert to string in the query, but then reference the correct field names in the code. Originally, I thought you were using the station_ID and task_ID, but I do not think those are the fields. So you can probably get rid of these two calculated fields in the subform and use the correct names in the code.
2. Here is the basic code. It should always be in the Parent form and not Subforms.
Code:
Dim FAYtRight As New FindAsYouTypeForm
Private Sub Form_Load()
'Rollback means if the text is not found it rollsback one character
FAYTLeft.Initialize Me.frmTaskcont2_sub1.Form, Me.txtFilter, ffrm_FromBeginning, False, False
FAYTLeft.FilterType = GetDirection
SetSearchFields FAYTLeft
FAYtRight.Initialize Me.frmTaskcont2_sub2.Form, Me.txtFilter, ffrm_FromBeginning, False, False
FAYtRight.FilterType = GetDirection
SetSearchFields FAYtRight
End Sub
'Needed when you change the search field or type of search
Public Sub SetSearchFields(FAYT As FindAsYouTypeForm)
FAYT.RemoveSearchField "strTask"
FAYT.RemoveSearchField "strStat"
If Me.frameField = 1 Then
FAYT.AddSearchField "strTask"
FAYT.AddSearchField "strStat"
End If
End Sub
Public Function GetDirection() As FormFilterType
If Me.frameType = 1 Then
GetDirection = ffrm_FromBeginning
Else
GetDirection = ffrm_Anywhereinstring
End If
End Function
3. I had to modify the Class. In the class there is functionality to "rollback". If you type a value and add a character if the filter no longer returns results it rolls back one character.
Example
2
21
213 'Nothing found so rolls textbox and filter back to
21
This will not work if you are filtering two forms at the same time because one might find nothing and the other something. I added a parameter to the class to turn off the rollback if you want.
In my world you filter by task or station, but makes no sense to search both simultaneously.
The subforms on this form have the same recordsource, but it is an assembly line with a left and right side, so they like to view the work instructions as the assembly line moves from station to station, able to see the left and right side operations at the same time, and the left subform is only LEFT station tasks, and the right subform is RIGHT and all 'other' categories.
No they clearly do not. Maybe the same fields but not the same number of records. one has around 760 the other around 1100. Therefore the class had to be modded not to rollback.
The real db tasks are not task 1, task 2, etc, but more like 200 character search fields.. So we assemble a vehicle, maybe they want to search 'frame' and filter all frame related tasks.. or a subset of stations, our left side line would be a1, a3, a5, a7, and right side line would be a2, a4, a6, so maybe they want that zone operations, they can just search 'a' and get that subset of operations..
I get that but searching both Taks and Stations at the same time makes NO sense. They do not have the same type of information.
I initially picked up your filter class, but when demoing with the users, they also wanted the search bar like is present in excel's filter dropdown, but after looking at access, I don't think it is a native capability, so your FAYT function is what I found..
I think the biggest differences between the form and combo is that the form FAYT still allows many records to be viewed at once - it isn't the same as the excel dropdown filter as when you start typing it shows you all records in the search criteria, and then you can select all or choose what ones you want to view, but if i did a FAYT combo - they can see all records from that one field to search through, but can only choose one record from the FAYT combo to view once selecting something. I would rather the subforms be filtered as they type what they are searching for to mimic the Excel filter search bar.
That makes sense, but the example you provided did not. It showed stations IDs and TaskIDs and that just would not be a good fit. If tasks are something more than an ID that makes more sense.
If you do want to search fields at the same time then modify the code in SetSearchFields. My issue was an Option Group where you were forced to pick a single option made more sense.
To see why the class would not work well originally, set the Rollback to True in the initialize. This works great for a single subform/form but would fail in your case where one subform returns no results.
This should do what you want.
1.Look at the subform queries and see the two calculated fields I added
strTask and strStat.
Modify those fields in the CSTR to match whatever fields you are actually filtering for the task and station.
If the fields you are filtering are not numeric then you do not have to convert to string in the query, but then reference the correct field names in the code. Originally, I thought you were using the station_ID and task_ID, but I do not think those are the fields. So you can probably get rid of these two calculated fields in the subform and use the correct names in the code.
2. Here is the basic code. It should always be in the Parent form and not Subforms.
Code:
Dim FAYtRight As New FindAsYouTypeForm
Private Sub Form_Load()
'Rollback means if the text is not found it rollsback one character
FAYTLeft.Initialize Me.frmTaskcont2_sub1.Form, Me.txtFilter, ffrm_FromBeginning, False, False
FAYTLeft.FilterType = GetDirection
SetSearchFields FAYTLeft
FAYtRight.Initialize Me.frmTaskcont2_sub2.Form, Me.txtFilter, ffrm_FromBeginning, False, False
FAYtRight.FilterType = GetDirection
SetSearchFields FAYtRight
End Sub
'Needed when you change the search field or type of search
Public Sub SetSearchFields(FAYT As FindAsYouTypeForm)
FAYT.RemoveSearchField "strTask"
FAYT.RemoveSearchField "strStat"
If Me.frameField = 1 Then
FAYT.AddSearchField "strTask"
FAYT.AddSearchField "strStat"
End If
End Sub
Public Function GetDirection() As FormFilterType
If Me.frameType = 1 Then
GetDirection = ffrm_FromBeginning
Else
GetDirection = ffrm_Anywhereinstring
End If
End Function
3. I had to modify the Class. In the class there is functionality to "rollback". If you type a value and add a character if the filter no longer returns results it rolls back one character.
Example
2
21
213 'Nothing found so rolls textbox and filter back to
21
This will not work if you are filtering two forms at the same time because one might find nothing and the other something. I added a parameter to the class to turn off the rollback if you want.
No they clearly do not. Maybe the same fields but not the same number of records. one has around 760 the other around 1100. Therefore the class had to be modded not to rollback.
I get that but searching both Taks and Stations at the same time makes NO sense. They do not have the same type of information.
That makes sense, but the example you provided did not. It showed stations IDs and TaskIDs and that just would not be a good fit. If tasks are something more than an ID that makes more sense.
If you do want to search fields at the same time then modify the code in SetSearchFields. My issue was an Option Group where you were forced to pick a single option made more sense.
To see why the class would not work well originally, set the Rollback to True in the initialize. This works great for a single subform/form but would fail in your case where one subform returns no results.
@MajP this is perfect! Wow. Thank you for taking the time to set this up and explain it to me. This is really helpful to see how you got this working with my form/subform setup.
What led me to your FAYT solution was the fact that access doesn't seem to have the search box on the filter dropdown menu like excel does.
