Runtime error 2450 when applying a filter to a form

[omar.norton]

I have created a database with a search form called frmStockSearch
based on Allen Browne's (http://www.allenbrowne.com/ser-62.html)
excellent example, which works absolutely fine. In fact, it worked so
well that I copied the search form into another database, renamed it,
adapted the code and expanded it to make another search form called
frmCustomerSearch. This worked absolutely fine for a long time until
recently when my boss asked me to add another piece of search criteria
to the form. The form still works fine until I enter something into
this new search box when I get runtime error 2450 saying that
frmstocksearch (the old form!!) cannot be found!! I have been through
all the code for the entire project with a fine toothcomb and there is
not one reference to the old search form name. I have checked every
aspect of access I can think of and I cannot find any problems. The
search form still works fine with any other search criteria. Here is
the code for the serch box that brings up the error:



Private Sub cmdFilter_Click()
'Purpose Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Note We tack " AND " on the end of each condition so you
can easily add more
' search boxes; we remove the trailing " AND " at the
end.

Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria
string to append to.


'***********************************************************************
'Look at each search box, and build up the criteria string from
the non-blank ones.

'***********************************************************************

If Not IsNull(Me.cboRegion) Then
strWhere = strWhere & "([Region] = """ & Me.cboRegion & """)
AND "
End If


'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's
Filter.

'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ")
to remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'String is empty
Me.FilterOn = False
Else 'String is not empty
strWhere = Left$(strWhere, lngLen)

'Finally, apply the string as the form's Filter, and store it
in an invisible text box
'for future use
Debug.Print strWhere
Me.Filter = strWhere
txtstrWhere = strWhere
Me.FilterOn = True
End If

Me.txtID.SetFocus
End Sub



Note there are lots of other search criteria that I have not bothered
to put in as these work fine.


Thank you in advance for any help!!!

 

[Allen Browne]

Sounds like something is corrupt in this database. Try this sequence (in
order):

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
In Access 2007, it's:
Office Button | Access Options | Current Database | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact/Repair
or in Access 2007:
Office Button | Manage | Compact/Repair

3. Close Access. Make a backup copy of the file. Decompile the database by
entering something like this at the command prompt while Access is not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access (holding down the Shift key if you have any startup code),
and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

At this point, you should have a database where the name-autocorrect errors
are gone, the indexes are repaired, inconsistencies between the text- and
compiled-versions of the code are fixed, reference ambiguities are resolved,
and the code syntax is compilable.

If it is still a problem, the next step would be to get Access to rebuild
the database for you. Follow the steps for the first symptom in this
article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have created a database with a search form called frmStockSearch
based on Allen Browne's (http://www.allenbrowne.com/ser-62.html)
excellent example, which works absolutely fine. In fact, it worked so
well that I copied the search form into another database, renamed it,
adapted the code and expanded it to make another search form called
frmCustomerSearch. This worked absolutely fine for a long time until
recently when my boss asked me to add another piece of search criteria
to the form. The form still works fine until I enter something into
this new search box when I get runtime error 2450 saying that
frmstocksearch (the old form!!) cannot be found!! I have been through
all the code for the entire project with a fine toothcomb and there is
not one reference to the old search form name. I have checked every
aspect of access I can think of and I cannot find any problems. The
search form still works fine with any other search criteria. Here is
the code for the serch box that brings up the error:



Private Sub cmdFilter_Click()
'Purpose Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Note We tack " AND " on the end of each condition so you
can easily add more
' search boxes; we remove the trailing " AND " at the
end.

Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria
string to append to.


'***********************************************************************
'Look at each search box, and build up the criteria string from
the non-blank ones.

'***********************************************************************

If Not IsNull(Me.cboRegion) Then
strWhere = strWhere & "([Region] = """ & Me.cboRegion & """)
AND "
End If


'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's
Filter.

'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ")
to remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'String is empty
Me.FilterOn = False
Else 'String is not empty
strWhere = Left$(strWhere, lngLen)

'Finally, apply the string as the form's Filter, and store it
in an invisible text box
'for future use
Debug.Print strWhere
Me.Filter = strWhere
txtstrWhere = strWhere
Me.FilterOn = True
End If

Me.txtID.SetFocus
End Sub

Note there are lots of other search criteria that I have not bothered
to put in as these work fine.

 

[omar.norton]

Hi, thanks for your reply. I've tried all the steps you've listed, in
order, but the problem still persists. When I recompiled the database,
there were no errors. I even tried importing the original
frmStockSearch into the database, but the exact same error message
still turned up.

Can anyone think of any other solutions? The error just doesn't make
sense to me, as every other control works just fine and I can't find a
single reference to "frmStockSearch". I'm running Access 2003 on WinXP
SP2.

Thanks for everyone's help!

 

[Allen Browne]

Hmm. I would have expected that the combination of of turning Name
AutoCorrect off and decompiling would have elimintated the non-existent name
"frmStockSearch".

An alternative way to approach this would be:
1. Copy all the code from your module out to Notepad, and save it as a text
file.

2. Set the form's HasModule property to No. Access will question whether you
really want to lose all the code in the form: answer Yes.

3. Save and close the form.

4. Compact the database. Twice.

5. Open the form in design view.
Click the Code button on the toolbar.
Access opens the code window.
Paste the code back in from Notepad.

6. In the code window, choose Compile on the Debug menu.

This performs the same thing as a decompile - eliminating the compiled code,
and forcing Access to recreate it.

 

[omar.norton]

Hmm. I would have expected that the combination of of turning Name
AutoCorrect off and decompiling would have elimintated the non-existent name
"frmStockSearch".

An alternative way to approach this would be:
1. Copy all the code from your module out to Notepad, and save it as a text
file.

2. Set the form's HasModule property to No. Access will question whether you
really want to lose all the code in the form: answer Yes.

3. Save and close the form.

4. Compact the database. Twice.

5. Open the form in design view.
Click the Code button on the toolbar.
Access opens the code window.
Paste the code back in from Notepad.

6. In the code window, choose Compile on the Debug menu.

This performs the same thing as a decompile - eliminating the compiled code,
and forcing Access to recreate it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.








- Show quoted text -

Nope, still coming up with the same problem! I noticed something
strange this time as well - after I had done all the steps you
mentioned in the last post I went back into design view and changed
the control from a combo box to a text box to see if that changed
anything. It didn't so I went back into design view and changed it
back. Then when I selected the control I noticed it was grouped with
some controls at the other end of the form (?!) When I ungrouped them,
it grouped itself with another lot of controls and so I repeated this
about 4 times, each time it would group itself with another control
(it eventually ungrouped after about 3-4 times). I don't know if that
has any link to this problem but just thought i'd throw that one in
there!!

 

[Allen Browne]

I don't have any further suggestions for you.