Full Version: help with subform .Filter.
UtterAccess Forums > Microsoft® Access > Access Forms
KurtW
Hi all:
I have a form that displays basic info from a DB. On that form I have a subform that I want to display related data from another table. The catch is this: the data will not be related 1 to 1 by a key. The purpose of this form is to locate keys in subform B that are CLOSE to keys in form A. The user has a combo box that allows them to select the level of sensitivity for this search.
So, if the user has selected 3 as the level of sensitivity, and the first key to come up on form A is: 'AZ95-1234-567', then I want the subform to show ANY key that is LIKE 'AZ9%'. If the user chose a sensitivity level of 4 then I would search table B for keys LIKE 'AZ95%', and so on.
Rather than run query after query, I thought the best approcah would be to bind subform B to a query that pulls all keys from the second table. Then, as the user clicks through form A, the OnCurrent event would trigger a rs.Filter using the current form A key and a bit of string manipulation to pull out the number of characters they want to search by. (The sensitivity.)
Okay, it pretty much works. When the main form is first opened you do see all keys from the second table in subform B. But, for some reason, as soon as you click to the next record on main form A, the rs.Filter code fires but nothing appears in the subform. The nav buttons on the subform also never register a change in the returned/filtered records. They are grayed out.
What can I be missing here? I return no errors.
Here's the code:
CODE
'-----------------------------------------------------------------------------------
Option Compare Database
Option Explicit
Private Sub Form_Current()
    Call subFindMatch
End Sub
Private Sub cmbSpaces_AfterUpdate()
    Call subFindMatch
End Sub
Private Sub subFindMatch()
    intSpaces = CInt(Me.cmbSpaces)
    
    MsgBox "intSpaces: " & intSpaces & " and filter = '" & Left(Me.Strain, intSpaces) & "%' "
    'MsgBox "rsType: " & Me.sbfrmMatches.Form.RecordsetType
    Me.sbfrmMatches.Form.Filter = "mAccession LIKE '" & Left(Me.Strain, intSpaces) & "%'"
    Me.sbfrmMatches.Form.FilterOn = True
    'Me.sbfrmMatches.Form.Refresh
End Sub
KurtW
It is definitely the application of the filter that causes the issue. If I do no filtering, or if I set FilterOn = False, then the subform continues to display.
KurtW
I have tried moving the .Filter code into the subform module itself. I call that sub from the main form:
!--c1-->
CODE
'--- Main Form ---
Private Sub Form_Load()
    If ("" & Me.txtValue) = "" Then
        'Me.RecordSource.MoveNext
        MsgBox "Found a blank."
    End If
End Sub
Private Sub Form_Current()
    intSpaces = CInt(Me.cmbSpaces)
    strFilter = "mAccession LIKE '" & Left(Me.txtValue, intSpaces) & "%'"
    
    Call Me.sbfrmMatches.Form.subFindMatch(strFilter)
End Sub
Private Sub cmbSpaces_AfterUpdate()
    intSpaces = CInt(Me.cmbSpaces)
    strFilter = "mAccession LIKE '" & Left(Me.txtValue, intSpaces) & "%'"
    
    Call Me.sbfrmMatches.Form.subFindMatch(strFilter)
End Sub
'--- Sub form ---
Public Sub subFindMatch(strFilter As String)
    MsgBox "subform: strFilter: " & strFilter
    'DoCmd.ApplyFilter , strFilter
    
    Me.Filter = strFilter
    Me.FilterOn = True
    MsgBox "Count after filter: " & Me.Recordset.RecordCount
End Sub

The sub fires but after the first record in the main form passes (and the .Filter is called in the sub form) the subform no longer shows any records, even when there is data that matches.
Kurt
Jack Cowley
Why not just use the criteria in the query the subform is based on and requery the subform? In the criteria line of the correct column something like this:
Like [Forms]![FormName]![NameOfTextBoxOrWhateverWithAZ9] & "*"
When you change at AZ9 to AZ95 just requery the subform and you should see the results you want.
hth,
Jack
KurtW
Thanks Jack:
That was my original thought, but the user will be scrolling through 5000 records on the main form. That would mean 5000 requeries minimum, plus a requery for each time they tweaked the sensitivity combo.
Oassumed that pulling the entire rs once, then just filtering it each pass would be easier on the DB. Does that make sense?
Thanks
Kurt
Jack Cowley
I guess I don't understand what you are trying to do if the user has to scroll through 5,000 records in the main form. How do they find a record now on the main form, just by using navigation buttons? What detemines when the subform will be required? With each record selected in the main form when they navigate to another record?
ack
KurtW
Sorry, let me explain better:
I have two legacy DBs that should have been different tables in the same DB from the start. Each DB holds different, but related, data. Both use a text field as a their key and, in Utopia, this key should match (PK in one table is FK in the other.)
But, since they have spent years apart, and the users have manually entered the keys, there is a large amount of 'drift' betwen keys. So, in table A a key might be 'AZ95-1234-112'. In table B the user may very well have entered 'AZ951234-112' and these are supposed to match, which they obviously do not.
Now, my first thought was to run string manipulation on all keys to just remove spaces and "-", etc, but there are some keys that were set by different locations of this company and they must maintain their format.
Using even complex string matching and manipulating to automate the task is out of the questions because this is just a small sample of the different formats of keys in both tables:
AZ95-1234-112
AZ1995-1234-112
AZ951234-112
A 01 Test
#34
T13
34-55
You can see my problem. My task is to combine both DBs into 1 multi-table DB. My first step needs to be to find a way to reconcile keys that SHOULD match but don't thanks to user input error.
So I have a form bound to the larger of the two tables. (table A.) As the user scrolls through each pk for A, the subform shows ALL PKS IN TABLE B that match at least part of the table A pk. This is done through a simple Left() function. Once the user sees how many matches there are, he/she can choose to tighten the search to something a bit more practical by upping the number of spaces on which to match the table A pk against table B pk.
So if table A shows a pk of: #34, and the form defaults to 2 match spaces, it searches for all pks in table B that are LIKE '#3%'. If the user ups the sensitivity to 3 spaces, the table B pk will have to match LIKE '#34%', and so on.
This is just a manual winnowing process that will allow the user to make a decision on which pk format is accurate and to change the malformed PK to match.
And I have 90% of that working. I even had the .Filter working in an earlier version, but now as soon as the user moves to the first record of main form (table A), the subform shows no records. Even when I jump to a table A pk that I know has mtches in table B.
Hope that explains it better.
Thanks
The user can also
Jack Cowley
YIKES! I see what you mean.. I think I would try my suggestion of the query. I would have a combo box on the main form to select the basic record I was looking for so if some start with B you can skip to the first record that starts with be. In the On Current event I would use your code to get the first significant character(s) and place them in an unbound (hidden if you like) text box. I would refer to this text box in the criteria line of the query the subform is based on and then I would requery the subform with
Forms]![SubformControlName].Form.Requery
Just for the heck of it why not give it a shot OR you can use the code above in your original filter (instead of "refresh") and see if that jogs your code into working...
Jack
datAdrenaline
This may be late in the game ... but you may want to try to do string manipulation as you originally thought, just manipulate both tables the same way by using a calculated field in an SQL, then "link" table A to table B by the "virtual" key ... I created a function called RemSpecial() that will remove all non-alpha numeric characters from a string. You can also pass a ParamArray of specific characters that should not be considered "special". IE: RemSpecial("MyString#$!","#","!") would return MyString#! where as RemSpecial("MyString#$!") would return "MyString".

With that you would have a main form with 2 sub forms. The main is unbound to a record source. The 2 subs will be bound to a SQL statement that has a "virtual" key, which is the string manipulated key. 1 sub form would be subTableA the other subTableB. subTableB would have the Link Child as vKey (virtual key) and Link Master Field set to subTableA.Form!vKey, then set the OnCurrent of subTableA to requery the subTableB form

.... much time has passed as sample app is developed ...

This is best explained by the posted example. I hope it makes sense ... if not let me know if it did not ...




Edited by: datAdrenaline on Wed Apr 20 0:27:22 EDT 2005.
KurtW
Thanks to you both, Brent and Jack:
reat answers, all. I am going to study both and see which one (or combo of both) will work best for me. But I do have some followup questions:
Can either of you spot why my original idea of just filtering the subform rs didn't work? I am still curious about that.
Also, had my idea worked, did my initial logic make sense? About not wanting to requery because filtering would require less resources? Or, since you used requery in your solution Jack, does it just not matter all that much on the desktop?
Thanks.
I'll post back in a bit with the new code.
Kurt
KurtW
Actually, I do have another question for either of you:
ack, you posted:
"...In the criteria line of the correct column something like this:
Like [Forms]![FormName]![NameOfTextBoxOrWhateverWithAZ9] & "*" "
Oassume you assumed (ok, bad English) that I built the subform on a query using the query design tool and/or the wizard. I actually built the query in VBA and bound the subform using Set Me.subform.Recordset = rs syntax.
And that's where my question comes in. Is there any practical, behind-the-scenes differences in how Access will treat an underlying recordset for a form built in VBA vs one built using the Access UI? I don't know why, exactly, but I keep getting a gut feeling that Access prefers to have the rs's built through its UI.
And If I use ADO in VBA to build the rs, does that mean that the underlying rs should now be manipulable by all ADO commands and retain all ADO properties? Again, I can't point to anything specific, but it seems that Access cuts out some ADO functionality when you do this.
I am having some difficulty reconciling the ADO recordset to an actual Access object. I have searched on this, and read some books, but this seems to be a topic that just isn't delved into, or is so obvious that they assume folks are smarter than me and just get it.
Any insight is appreciated.
Thanks
Kurt
KurtW
Jack & Brent:
raham at AccessMonster solved it! I was (stupidly) using % as my wildcard delimiter instead of *. Now my original code works.
But I am still looking over your suggestions and would still appreciate any insight on my previous question.
Thanks
Kurt
datAdrenaline
Glad to here a solution was found! ... Ya know I saw the '%' but since I use SQL back ends and passthru queries a lot I did not think anything about it!! ... Good job Graham! ...

You can set the Recordset property of a form to an ADO recordset, however, the default Recordset type is a DAO recordset and seems to work more "smoothly" ... at least for me!! ... If you are using an Access Project, however, the default recordset type is ADO.

What I frequently do is just modify the forms .RecordSource (as I did in the posted example) property to the correct SQL statement, then I avoid some of the recordset issues. I figure I am already creating the SQL statement to build the underlying recordset, whether I use ADO or DAO ... I still build the SQL in VBA ... So I just plug the SQL text into the RecordSource property. When that property gets set, Access automatically requeries the form.

One item to remember, is that when you do this if you refer to the forms Recordset object, it will be of a recordset type that is the forms default ... so

'Set rst = Me.RecordsetClone' as well as 'Set rst = Me.Recordset' ==> will (by default) return a DAO recordset for .MDB's

'Set rst = Me.RecordsetClone' as well as 'Set rst = Me.Recordset' ==> will (by default) return an ADO recordset for .ADP's

So ... keep all that in mind when manipulating the recordset object behind a form ...

Summary ... Know which type of rst your form is using ... ADO or DAO ... that will definately make a difference in what commands you can apply to it.

Also, a note on Filters ... (Please forgive me if this is not an 'exact' definition ... this explanation is derived from a combination of experience and a variety of readings that has formed this 'opinion') ... Filters do not actually modify your recordset ... they actually just 'hide' records that don't match the filter. Where as a SQL with a WHERE clause will actually limit the number of records that make it to the recordset.

For example:

rst.Open "Select * From tblAreas WHERE LocationID = 6"

will only pull in the records that match the WHERE clause

However

rst.Open "Select * From tblAreas" 'will pull in EVERY record from tblAreas
rst.Filter = "your filter here" 'Will HIDE records in the recordset that do not match the filter ... but the records are still there.

So filtering can be very taxing on a system since you are actually using the whole rst. Filters can be used effectively, but I tend toward only applying filters only AFTER I have a based the rst on a SQL with an overall limiting WHERE clause. For example I may create a rst from a SQL that gets 100 records from a 10,000 record table, and then use a filter to pull out similar records from that initial group of 100.

Note: The FILTER property on forms is the same way ... it HIDES records from your base recordset. This is evidenced by the fact that on a form, you can set a filter based on the value of a control on the form that does not share its name with a field in the recordset...ie: Filter ==> [ControlNameOnForm]=6

Hope that helps ..
KurtW
Brent:

That makes a lot of sense. I didn't actually know that the Access default rs type was DAO. That's important stuff, glad you mentioned it.

You said something that sparked another question:
"'Set rst = Me.RecordsetClone' as well as 'Set rst = Me.Recordset' ==> will (by default) return a DAO recordset for .MDB's"

So if I base a form on a recordSOURCE query, then the rs implicitly created by Access is a DAO rs, correct? But once I declare, in code, the rs as an ADO rs, it remains an ADO rs throughout.

Now, I know that if I create an ADO rs (and I assume a DAO rs) explicitly in code, I can then manipulate that rs via code. But I have seen the syntax you mentioned before: 'Set rst = Me.Recordset' , and I'm not sure how and when I would use that.

Does that syntax imply that if I want to manipulate an underlying form rs that was NOT created explicitly via code, but was created implicitly by Acces from a recordSource sql, I have to first create a local rs and set it = the form's underlying rs, then manipulate the local rs?

And if that's true, then I could implicitly create a DAO rs by basing a form on a recordSource query, but then create a local ADO rs and set it = to the underlying DAO rs then manipulate it with ADO?

Does any of this ring true?

Also:

I am having an issue with Access nav buttons, events and the filter.

On the same form I call a subroutine to update an unbound field to display some details of what is about to happen. Works great 99% of the time.

But if the particular filtering happens to return no records in the subform, then the unbound txt field is not updated. It shows the data from the last time the filtering had records returned.

Let's say the user then clicks 'next record' on the main form 3 more times without the subform filter returning any records. On the 4th click the subform returns records but the txt field doesn't update. It will update as you click to the next record, but it seems to hesitate on the first record that returns a filtered record after a record that returns no filtered records.

So it seems to me that the txt field update is somehow being tied to the success (or return of data) from the subform filter. I don't know why that should be since I call them individually.

I even went so far as to build a separate cmdButton to do the 'Next Record' nav and added the update script to its click event, but it performs the same way. And, I should mention that the txt field update code is executed (or should be) BEFORE the filter, so I would have thought the filter's success would have nothing to do with it.

Any thoughts?


Thanks
Edited by: KurtW on Wed Apr 20 13:15:11 EDT 2005.
datAdrenaline
Kurt,
This is correct ...
Yes you can declare a VB based recordset as and ADO type and manipulated it as you wish, with ADO.
Example: Possible code behind a Code Based Form (Form with code attached to it)
CODE
Private Sub Form_Load()
    Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblTableA", dbOpenDynaset)
    Set Me.Recordset = rst
End Sub
[code]
HOWEVER ... By Default this will generate an error ... (in all honesty I have NEVER set an .MDB form to an ADODB recordset ... I know its possible, but I have not done it)
[code]
Private Sub Form_Load()
    Dim rst As New ADODB.Recordset
    rst.Open "SELECT * FROM tblTableA", CurrentProject.Connection, adOpenDynamic, adLockPessimistic
    Set Me.Recordset = rst  'This line will raise the error due to incompatible recordset types
End Sub

THere is a possible use of Me.RecordsetClone:
CODE
Private Sub Navigation()
    Dim rst As DAO.Recordset
    Set rst = Me.RecordsetClone  'Me.RecordsetClone returns a DAO recordset by default
    rst.FindFirst "Key Like '*'"
    If rst.NoMatch Then  'the "NoMatch" is for DAO only
        MsgBox "Key Not Found"
    Else
        Me.Bookmark = rst.Bookmark  'This will cause the form to go to the
                                    'record that was just found
    End If
End Sub

Otypically SET Me.Recordset NOT use Me.Recordset to set a rst ... but both are possible
CODE
Private Sub ApplyNewCriteria(strWhere as string)
    Dim rst As DAO.Recordset
    Set rst = CurrentDB.OpenRecordset("Select * FROM tblTableA WHERE " & strWhere)
    Me.Recordset = rst
End sub

Other notes:
- You can not set an ADO recordset with a DOA recordset .... that is like setting an interger to a string, they are not the same thing .... note: DAO is optimized for JET databases and is the recommended method for accessing data in a form (read the link I have included at the end).
- The link below will link you to an UA post which in turn links to artical from MS which details how to set a form to a ADO recordset.
I know I did not answere all you questions specifially ... but I need to go to bed!!! ... Maybe I will comment more tommorrow!!
http://www.utteraccess.com/forums/access/access51284.html
datAdrenaline
Sorry ... somewhere in my last post I did not close the CODE block ... so here is that link again:
ADO in Access 2002
KurtW
Brent:
any thanks for all that info. Yeah, I can see that DAO is quite the Acces standard, but since I come from a web-development background (where everything is ADO) I have chosen to use ADO in Access. (honestly, I believe in the right tool for the job, I just don't have the time right now to learn DAO, though I'm starting to see I might have to.)
I'm still not clear, from your example of a rs clone, why I would even create the clone rather than perfrom the FindFirst operation gainst the Me.rs?
CODE
Private Sub Navigation()    
     Dim rst As DAO.Recordset    
     Set rst = Me.RecordsetClone  'Me.RecordsetClone returns a DAO recordset by default    
     rst.FindFirst "Key Like '*'"    
     If rst.NoMatch Then  'the "NoMatch" is for DAO only        
          MsgBox "Key Not Found"    
     Else        
          Me.Bookmark = rst.Bookmark  'This will cause the form to go to the                                    
          'record that was just found  
      End If
End Sub

Thanks
Kurt
KurtW
Brent:
That ADO white paper was great, thanks. I hade nebver heard of the AccessConnection before. Think I'll give it a try.
Kurt
datAdrenaline
Me.Recordset is a PROPERTY of a form that allows the user to set or return the recordset object of a form, it does not actually work like a recordset object itself.
o ... Me.Recordset.Findfirst ... does not work
you first have to create a recordset object in code so you can manipulate the recordset which feeds the form ...
set rst = Me.RecordsetClone ..OR.. set rst = Me.Recordset
Otypically use Me.RecordsetClone for navigational purposes (although you can do navigation with me.Recordset too) and Me.Recordset if I do calculations against the recordset as a whole that feeds the form (ie: a summation of a "cost" field that is on all records of the recordset that feeds the form)
KurtW
Brent:
Me.Recordset is a PROPERTY of a form that allows the user to set or return the recordset object of a form, it does not actually work like a recordset object itself."
Ah - that is EXACTLY what I was missing and needed to know. You aren't manipulating Me.recordset itself (that's just a pointer.) You must manipulate the actual rs.
Thanks again. Over the weeklend I was looking through my book "Serious ADO: Universal Data Access with Visual Basic" and the author also gave a good example of why and when you'd want to use a rs clone.
Kurt
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.