How do I filter a subform as the user enters a surname? 1

[pmp1]

I have found a thread [thread702-1209629] that seems to answer my question, however, that answer is not working for me. I would appreciate some assistance in identifying where I am going wrong, to which I'm sure it is to with referencing and have looked up many threads on this but I have still not been able to resolve my current problem.

Using Access 2003, I have a form "frmPatientBrowser" which includes an unbound text box "txtSurname" and a subform "ctrlPatientBrowser". The subform "sfrPatientBrowser" links to a table "tblPatients" which includes a field "surname" and is displayed as a datagrid. Fields displayed in the datagrid are (surname; firstname; address; suburb).

My plan is for the user to enter a Patients surname into the text box "txtSurname" and as they enter each character of the surname the subform (datagrid) is filtered using the text being typed with a "*" appended to the end of it. This should reduce the list of patients being displayed making it quicker for the user to select the appropriate patient.

My problem is that the subform is not being filtered. I have overcome my previous problems of the application crashing due to incorrect referencing.

The code I have tried is as follows:

Option Compare Database
Option Explicit
Private Sub txtSurname_Change()
    Dim strFilterValue As String
    Dim strPatient As String
        
    strFilterValue = txtSurname.Text
    MsgBox ("before " & strFilterValue) 'just checking the progress - all OK so far!
    'The following line of code (commented out) does not work yet the one after does
    'Happy to be advised why??
    'strPatient = Me.Parent![ctrlPatientBrowser].Form![txtSurname].Text (does NOT work)
    strPatient = Forms![frmpatientBrowser]![ctrlPatientBrowser].Form![txtSurname].Text 'WORKS
    MsgBox ("surname = " & strPatient) ' just checking my referencing - all OK so far

    'The following line of code from thread702-1209629 is used as a guide:
    '[Forms]![FrmBrowser]![FrmBrowser subform].Form.Filter = "Dealership='" & Criteria & "'"

    'PROBLEM - I believe the following line of code is my issue and have used the filter code
    'from another example - see below:
    'rsSubMain.Filter = "LName LIKE '" & txtNameFilter.Text & "*'"
    'Me.Filter = "Country = 'USA'"
    
    Forms![frmpatientBrowser]![ctrlPatientBrowser].Form.Filter = "surname LIKE '" & strFilterValue & "*'"

End Sub

I look forward to hearing where I have made my error(s).

Kind regards

Peter

 

[JBinQLD]

G'day fella,

Try:

Option Compare Database
Option Explicit
Private Sub txtSurname_Change()
    Dim strFilterValue As String
    Dim strPatient As String
        
    strFilterValue = txtSurname.Text
    'The following line of code (commented out) does not work yet the one after does
    'Happy to be advised why??
    'strPatient = Me[red].Parent[/red]![ctrlPatientBrowser].Form![txtSurname].Text 
    '[red]Line above was designed to be called from the subfrm, hence reference to parent.  Remove that and it effectively becomes same as below :) [/red]
    strPatient = [red]Forms![frmpatientBrowser][/red]![ctrlPatientBrowser].Form![txtSurname].Text
    The red above is same as Me, so both lines are identical if you make the changes....
    MsgBox ("surname = " & strPatient) ' just checking my referencing - all OK so far

   [red]'all of above can be removed as strPatient is only used for that msgbox?
   
    Forms![frmpatientBrowser]![ctrlPatientBrowser].Form.Filter = "surname LIKE '" & strFilterValue & "*'"

End Sub

Now ensure you have the allow filters property set to true - thats the most common oversight

Hope this helps,

JB

 

[JBinQLD]

PS. The other reason the sub aint filtering could be that you've not got a requery. Which brings me onto the other thing I wanted to mention - requerying on every key press is probably going to make this very slow and flickery. I'd suggest you have a button that does the above but only on click. You can make the button a default so it triggers when the user presses enter.

You could even add a line to set the focus back to the search box after applying the filter incase the characters yped weren't restrictive enough then the user can keep typing more of the name before hitting enter again.

 

[pmp1]

JB,

Thankyou for explaining the use of Me and parent.

However, I would realy appreciate knowing why the following bit of code (i.e. the filtering) doesn't work?

Forms![frmpatientBrowser]![ctrlPatientBrowser].Form.Filter = "surname LIKE '" & strFilterValue & "*'"

Thanking you

Peter

 

[Remou]

I do not see:

Forms![frmpatientBrowser]![ctrlPatientBrowser].Form.FilterOn=True

Is this a typo?

 

[pmp1]

JB,

Sorry for my last post, you must have written to me whilst I was typing.

I will try your solution and let you kmnow the outcome.

Thanks

Peter

 

[pmp1]

Remou,

Thankyou. Adding the FilterOn=True line of code has resolved my problem.

I had tried JB's requery suggestion without siccess.

As mentioned by JB I thought simply "Allow Filters = yes" in the properties was sufficient, but obviosly not.

Thanks again to both of you for your help and this request can now be closed.

Kind regards

Peter