Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Searching + Data Mining _ What's The Difference Between Applyfilter & Gotorecord

Posted by: MonteyBurns Jan 25 2019, 04:47 PM

I'm having an issue on trying to get a feature to work.
I have a database that monitors site visits for interns. Each applicant to have an intern has a file number assigned to them for each intern.
I am trying to have a search field on the main form so the end users can type in a file number, hit the search button and the records will be displayed with editing enabled. I have several sub forms on the "main" form so I am guessing that there's something I am missing with the sub forms, but I don't know what.

I have searched for 2 days trying to find a solution to this. I first changed the file number field to short text and this seemed to get rid of the type mismatch error. So I hope that would be the proper thing to do.

I am using the ApplyFilter pointing to the txtBox where the user can type in the file number and keep receiving error 32003. This led me to looking at the GoToRecord function. This seemed to be more complicated with recordsets in all the related tables and I am unsure if I would need to indicate all related table recordsets as well.

I am trying to do this in VBA.

This is the code that generates the error:

Private Sub cbtnFileSearch_Click()

On Error GoTo cbtnFileSearchError

If IsNull("txtFileNoSearch") Then
        MsgBox "Please enter a File Number in the field provided. ", vbOKOnly, "File Number Required ... "

End If
        If IsNumeric("txtFileNoSearch") = False Then
        MsgBox "Please enter numbers only.", vbOKOnly, "Numbers Only ... "
End If

If (IsNull(txtFileNoSearch)) Then
        MsgBox "Please enter a File Number.", vbOKOnly, "File Number Required ... "
End If
        DoCmd.ApplyFilter "", "[TBL_Applicant]![FileNo]  =", "[Forms]![FRM_Applicant]![txtFileSearch]"
If Me.RecordsetClone.RecordCount = 0 Then
        MsgBox "No results found. & vbCr  Please check the file number.", vbOKOnly, "No Results Found ..."
End If
         MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cbtn_FileSearch, line " & Erl & "."

'DoCmd.GoToRecord "Me.txtFileNoSearch", , True, , True

End Sub

So multiple questions;
How do I get this to work as intended - (Users search for the file number and edit related records)
When trying to find the records, which is better? ApplyFilter or GoToRecord.
Is there a difference, which is better, how do you decide which to use?

Any help is appreciated.


Posted by: June7 Jan 25 2019, 05:48 PM

ApplyFilter does just that, filters dataset to only those records that meet criteria. GoTo sets focus on first record that meets criteria.

What is the exact message of error 32003?

I have never used ApplyFilter. I set Filter and FilterOn properties. Review

The GoToRecord syntax you show is just not correct. You have 5 arguments but the command has only 4 and none would have True as a parameter.

Posted by: MonteyBurns Jan 25 2019, 06:01 PM

Thanks for the site, I will review it.
The exact error message is:

Error 32003: (The 'ApplyFilter' action requires a valid control name that corresponds to a subform or subreport.) in procedure cbtn_FileSearch, Line 0.

It looks like it's coming from my on error handling and I'm not sure if I'm missing something in the code to tie in the related records.


Posted by: June7 Jan 25 2019, 07:09 PM

If FileNo is a text field, try:

DoCmd.ApplyFilter , "[FileNo] ='" & [Forms]![FRM_Applicant]![txtFileSearch] & "'"

If it is number field don't use the apostrophe delimiters.

Posted by: tina t Jan 25 2019, 07:28 PM

If IsNull("txtFileNoSearch") Then

the above is meaningless. "txtFileNoSearch" is a string, and of course it's not Null, it has text in it. i've never tried it, but i don't think even a zero-length string (ZLS), which is a pair of quotes that hold no characters or spaces (""), would be read as Null.

If IsNumeric("txtFileNoSearch") = False Then

this check is always going to return False, because a string is not numeric, even if it contains only number characters; and in this case, the text string you're providing is actual alpha characters.

so any string of characters surrounded by quote marks is literal text. the only way to use that to indicate a control is by referring to the form's Controls collection, as


but there's no need to do that in the code you posted. just lose the quote marks, and refer to the control as


also, note that while you intended several checks of the value in control txtFileNoSearch, you're doing nothing to stop the code after a warning msgbox runs. you need to exit the sub, or running the checks is useless, as

MsgBox "Please enter a File Number.", vbOKOnly, "File Number Required ... "
Exit Sub

also, if you're applying a filter to the form's RecordSource to get the specific record you want, you shouldn't need to also use the GoToRecord command.

but more details about your form/subforms setup would be helpful. is the mainform bound to a table or query? are the subforms linked to the mainform using the LinkMasterFields and LinkChildFields properties of the subform container controls in the mainform? some explanation of your tables/relationships setup would also be helpful.


Posted by: MonteyBurns Jan 25 2019, 08:38 PM

Great advice....

I completely forgot about taking care to stop the procedure on the errors.
I just want to double check on this.

If FileNo is a text field, try:

DoCmd.ApplyFilter , "[FileNo] ='" & [Forms]![FRM_Applicant]![txtFileSearch] & "'"

If it is number field don't use the apostrophe delimiters.

I did originally have it in the table as a number field. So should I keep it as a number and use;

DoCmd.ApplyFilter , "[FileNo] ='" & [Forms].[FRM_Applicant].[txtFileSearch] & "'"


Does the txtFileSearch identify to access that the field is Text?
So if I use numFileSearch wouldn't it identify it as a number?


 InternDatabaseRelationshipReport.pdf ( 146.17K ): 1 ( 805.38K ): 14

Posted by: June7 Jan 26 2019, 12:43 AM

Changing name of control does nothing about changing data type of field and names tell Access nothing about data type. Data type of field is defined in table design. Access cares nothing about what names you use, as long as they follow a few simple rules. Prefixes like txt and num are for your convenience and mean nothing to Access or VBA. Review:

Even though allowed, do not use spaces nor punctuation/special characters (underscore only exception) in naming convention. Also, do not use reserved words as names for anything. If you do any of these, then object names must be enclosed in [ ] when referenced. You will see Access automatically add those characters in queries.

If txtFileNoSearch is strictly for input of search parameter then it should be an UNBOUND control and therefore it can be Null. Null and ZLS are not same thing.

If FileNo is a number field in table then you want to verify textbox has a number value.

Better, use a combobox where users can only select from items in combobox list.

Do not place reference to controls within quote marks because as TinaT noted, the result is just literal text, not a control reference. And as already stated, if field is number type, do not use the apostrophe delimiters for the parameter.

If IsNull(Me.txtFileNoSearch) Or Not IsNumeric(Me.txtFileNoSearch) Then
            MsgBox "Please enter a valid File Number in the field provided. ", vbOKOnly, "File Number Required ... "
            DoCmd.ApplyFilter , "[FileNo] = " & Me.txtFileNoSearch
End If

Posted by: MonteyBurns Jan 26 2019, 08:51 AM

Thank you for clearing this up.

I have watched a lot of videos and I think it was on a Steve Bishop video where he changed the name of a control and stated, "This tells access it's a text field". So I took that as the name of the control identifies and access uses that as the datatype within the control.
Thank you for setting me straight on that.

I used the "if (IsNull) for that control because if someone clicks the button and there's no text it should pop up a message saying to populate the box with a search string. Again on another video, they stated that this type of procedure should be done because if a user clicks on a button and it does nothing, they think it's broken.

I hope I'm not breaking any rules on this (I don't think I am)

But another question regarding the filtering, is What do experienced DB developers to remove the filter?

I have another database project that I have done that does a macro with search filter.
What I did was create a toggle button to do the initial search. I changed the colour of the toggle button while the filter is on.
To remove the filter, you click the toggle button once more and the colour goes back to the normal state.

No matter how much information I put in and explaining I did, it still is confusing to the end users.
So what is the best practices of removing a filter?
Do you use an event or a button?
I know it depends on the users use of the filtered information as well. In this DB, they would search for a record and might copy and paste information into another window (Word, Excel, etc). However for the Intern DB they would be adding/editing the data.

Thanks again for all your advice and the allenbrowne is a great source of information for beginners such as me.


Posted by: June7 Jan 26 2019, 03:36 PM

As stated, I have never used ApplyFilter and I don't know how to remove filter set with ApplyFilter.

I use code to set Filter and FilterOn properties. FilterOn has True or False parameters and that is how filter is turned on or off. Did you review the Allen Browne tutorial?

There is no 'event or button' - there is always an event involved. The question is 'which event' - button Click or combobox AfterUpdate.

The toggle button arrangement actually sounds reasonable. Users should get accustomed to it rather quickly. As well as changing color, can change caption but that would require VBA. So what is the code that 'unfilters'?

Additional note about use of [ ] - won't always resolve issue when name is a reserved word.

Posted by: MonteyBurns Jan 26 2019, 05:30 PM

Yes, I did review allenbrowne and downloaded the sample database and on the form that he used had filter and un-filter buttons.
And yes, I realize you don't use the ApplyFilter but you are filtering using a different methodology. I posted the question because other experienced users (I'm hoping) will post their methodologies and try to find out what experienced developers use.

In what little experience I have, I can see how you could setup the relationships and use custom filters on reports and queries. At this point within my project and with my current abilities, I don't think I could pull that off.

Also, I find it pretty amazing how you can post one question and get a multitude of different perspectives that I would never think of even looking at.

Thank you very much for information and I will definitely start looking at ways of including filters in queries and reports and turning them on or off within code. Much appreciated.


Posted by: MonteyBurns Jan 28 2019, 08:45 PM

Sorry June7;

The toggle button code is as follows:

Private Sub togSearchOrganizations_Click()
On Error GoTo togSearchOrganizations_Click_Err

        On Error Resume Next
        If (IsNull(txtOrgSearch)) Then
                MsgBox "Please use the text box to the left to type in your search criteria.", vbExclamation, "Enter text is required"
                DoCmd.SetProperty "togSearchOrganizations", , "0"
                Exit Sub
        End If
        If (togSearchOrganizations = -1) Then
                DoCmd.ApplyFilter "", "[Organization] Like ""*"" & [Forms]![FRM_Organizations]![txtOrgSearch] & ""*""", ""
                DoCmd.SetProperty "togSearchOrganizations", acPropertyForeColor, "9445584"
                DoCmd.SetProperty "txtOrgSearch", , ""
        End If

        Exit Sub

        MsgBox Error$
        Resume togSearchOrganizations_Click_Exit

End Sub

I hope you find it useful.