Filter subform from text box control

[Felix314]

Hello everyone,

I have researched and tried just about all I can on this subject, but I understand my situation is different since I am working on ADP file, using Access on the front end and SQL Server on the back end. The biggest problem is that my query designs are not as sophisticated as they are in normal Access versions. I still think I can make this work however... just need some specific help.

If anyone has a little bit of time on their hands, I would be in your debt if you can help me figure out this request.

I am trying to filter just 1 field on a subform by having the user type a value in a text box control on the main form. My subform has text type fields, and the one I would like to filter is named "Semester".

I realize I must use an Unbound text box which I have named "txtFilterSemester", but I cannot figure out the VBA for this process.
The subform is using a query (view) as its record source and this query (view) is pulling records from 3 tables. The subform is named "AU_subform". The query is named "Agency_University_Results" and the main form itself is named "AU_Graduates". Please Help!!!!

Here is the unsuccesful code I have tried recently:

Private Sub txtFilterSemester_Change()
Dim strWhere As String
With Me.txtFilterSemester
If .Text = vbNullString Then
strWhere = "(False)"
Else
strWhere = "[Semester] Like """ & .Text & "*"""
End If
End With
With Me.[AU_subform].Form
.Filter = strWhere
.FilterOn = True
End With
End Sub

When I try to type in the text box, the subform goes blank and doesn't do anything.

 

[John Big Booty]

The attached sample should give you some pointers. Check the On Change event of the Text Box.

 

[Felix314]

Hello John,

Thank you so much for your help. I looked at the sample db, tried out the method, and it would not work at all. I put in the code for the On Change event to fit my db, and when I tried to type in the text box, it said access can't find the object 'SELECT (All Fields) FROM (Tables in Record Source). This doesn't make any sense to me because the subform is fine.

Here was the code I used:
Private Sub Text4_Change()

Me.Refresh

Dim strFilter As String

strFilter = "[Semester] like '*" & Me.Text4 & "*'"
Forms!Form1!AU_graduates.Form.Filter = strFilter
Forms!Form1!AU_graduates.Form.FilterOn = True

Me.Text4.SelStart = Nz(Len(Me.Text4), 0)

End Sub


So then I tried the method and code you provided in this thread:
http://www.access-programmers.co.uk/forums/showthread.php?t=188663

But that didn't work either. Maybe it would if I could put the Criteria: Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*" in my query design. Each time I do, the query doesn't return any results.

I put this code in for that:
Private Sub txtFilterSemester_Change()
'Create a string (text) variable
Dim vSearchString As String
'Populate the string variable with the text entered in the Text Box txtFilterSemester
vSearchString = txtFilterSemester.Text
'Pass the value contained in the string variable to the hidden text box SrchText,
'that is used as the sear4ch criteria for the Query Agency_University_Graduates
SrchText.Value = vSearchString
'Requery the List Box to show the latest results for the text entered in Text Box txtFilterSemester
Me.SearchResults.Requery

'Tests for a trailing space and exits the sub routine at this point
'so as to preserve the trailing space, which would be lost if focus was shifted from Text Box txtFilterSemester
If Len(Me.SrchText) <> 0 And InStr(Len(SrchText), SrchText, " ", vbTextCompare) Then
'Set the focus on the first item in the list box
Me.SearchResults = Me.SearchResults.ItemData(1)
Me.SearchResults.SetFocus
'Requery the form to refresh the content of any unbound text box that might be feeding off the record source of the List Box
DoCmd.Requery
'Returns the cursor to the the end of the text in Text Box txtFilterSemester,
'and restores trailing space lost when focus is shifted to the list box
Me.txtFilterSemester = vSearchString
Me.txtFilterSemester.SetFocus
Me.txtFilterSemester.SelStart = Me.txtFilterSemester.SelLength

Exit Sub
End If
'Set the focus on the first item in the list box
Me.SearchResults = Me.SearchResults.ItemData(1)
Me.SearchResults.SetFocus
'Requery the form to refresh the content of any unbound text box that might be feeding off the record source of the List Box
DoCmd.Requery
'Returns the cursor to the the end of the text in Text Box txtFilterSemester
Me.txtFilterSemester.SetFocus
If Not IsNull(Len(Me.txtFilterSemester)) Then
Me.txtFilterSemester.SelStart = Len(Me.txtFilterSemester)
End If
End Sub

I think I should give up... this sucks

 

[Felix314]

When I try the version from the sample db, it keeps pulling up the Run-time error '7874' and when i hit Debug, it highlights "Me.Refresh" with a definition that "Identifier under cursor is not recognized".

Thought this would help.

I probably should give up though.

 

[John Big Booty]

Firstly when you post code could you please use the Code Tags.

Have you changes all the references, in the code you have used to match the reality of your DB?

 

[Felix314]

Sorry, I will use the code tag from now on.

Yes, I changed all the references when I put it in my DB. Something must be wrong with the subform itself. I'm not sure what, because it's pretty simple. It's based on 3 tables, with the bulk of the data coming from a foreign key table. For some reason, the code isn't working because it can't find the subform object. I'll post it again:

Private Sub txtFilter_Change()
Me.Refresh
    
Dim strFilter As String
    
strFilter = "Semester like '*" & Me.txtFilter & "*'"
Forms!Form1!AU_graduates.Form.Filter = strFilter
Forms!Form1!AU_graduates.Form.FilterOn = True
    
    Me.txtFilter.SelStart = Nz(Len(Me.txtFilter), 0)
End Sub

The subform, "AU_graduates", has a field I am trying to filter called "Semester". The main form is named "Form1". The unbound text box is called "txtFilter".

 

[John Big Booty]

Are you able to post a copy of your DB, as the code looks fine.

 

[Felix314]

I would post a copy, but it is an ADP file, as the db is connected to a SQL Server backend. It wouldn't work for you since all the tables are stored on my work server database.

I believe the problem is simply that I am working with an ADP project. The query process is just not the same as it is for an ACCDB file. Also, since it's an ADP, I am using Access 2002-2003. I have tried the criteria expression like in the sample query, but it doesn't work. The query won't execute results. Maybe it's because the language has changed or it's not compatible with ADP projects.

I wish I understood more about stored procedures... do you know anything about ADP projects?

 

[Felix314]

Hello again,

So I actually got the code to work for filtering a subform using the text box. I went back to the code I posted and realized that for ADP files, you must use % instead of *. When I did that it worked. Here's the updated code:

Option Compare Database
Private Sub Form_Filter_Change()

    Dim strFilter As String
    
    Me.Refresh
    
    strFilter = "semester like '%" & Me.Form_Filter & "%'"
    Forms!Agency_University_MailMerge!AU_graduates_subform.Form.Filter = strFilter
    Forms!Agency_University_MailMerge!AU_graduates_subform.Form.FilterOn = True
    
    Me.Form_Filter.SelStart = Nz(Len(Me.Form_Filter), 0)

End Sub

Just have one slight problem that I would really appreciate some help on. When I am typing in the text box, it starts working as a filter, however I am not able to use the spacebar. For example, I would like to write a semester value such as "Fall 2012". This is the format of my field values I am trying to filter. Instead of having to type _ in between Fall and 2012, is there a way to modify the code to enable using the spacebar?

Thank you dearly

 

[Felix314]

Hi John,

I'm sorry, please ignore what I asked just recently. All I am left to figure out is if there any way to then export the filtered results to an excel sheet?

I will trust your genius.

 

[John Big Booty]

I think this article should help you.

 

[Felix314]

Thanks for the link John. However, I am already familiar for doing basic exports of Access objects to Excel.

This situation is bit more specific though since it is a filtered subform, which is dependend on what is typed in the text box control.

I am hoping for users to type in the box to filter the subform first, then click on a command button to send the filtered result to an excel file. I have a code for a button now, but it sends all the records:

Private Sub Command16_Click()
DoCmd.OutputTo acOutputForm, "AU_graduates_subform", acFormatXLS, strPath & "\Mail_Merge_Report.xls", AutoStart:=-1
End Sub

I've been doing a lot of research on this topic, and I have found a few recommendations saying things like "do a Query, and then write the SQL to the TransferSpreadsheet routine"
Unfortunately, it doesn't seem possible with my current setup/ADP file.

Any ideas?

 

[John Big Booty]

Sorry your outside my area of knowledge