Solved

Form won't filter

Posted on 2011-02-22
29
367 Views
Last Modified: 2013-11-27
I have a split form frmShowBookEDT with a table tbl2011ShowBookEDT as the control source and a search for that is invoked by a button on the main form.  The search form has an unbound combo box cboSearchField an unbound text box txtSearchString   and a 2 option group optWild  like for wildcard and  exact  for exact  searches and code that runs when the find button is pressed  if I click the exact option and enter an exact string it filters but if I select like it shows no rows. This form has worked on other forms in other databases. I copied the form and module form another database and modified it which I have done successfully in the past, not sure what Happened. Any help would be greatly appreciated.  Search Form
Private Sub cmdSearch_Click()
   On Error GoTo cmdSearch_Click_Error

Set db = CurrentDb()

  If Len(Me.cboSearchField & "") = 0 Then
        MsgBox "You must select a field to search."
        Me.cboSearchField.SetFocus
    ElseIf Len(Me.txtSearchString & "") = 0 Then
        MsgBox "You must enter a search string."
        Me.txtSearchString.SetFocus
    Else
        If Me.optWild.Value = 1 Then
          'Generate search criteria
          GCriteria = "[" & Me.cboSearchField & "] LIKE '*" & Me.txtSearchString & "*'"
        Else
          GCriteria = "[" & Me.cboSearchField & "] = '" & Me.txtSearchString & "'"
        End If
        'If no results sets focus back to Search Field
        If DCount("*", "qryEdit", GCriteria) = 0 Then
            MsgBox "No records for the search"
           
            Exit Sub
       End If
       
        'Filter frmCustomers based on search criteria
      Forms!frmShowBookEDT.Filter = GCriteria
      Forms!frmShowBookEDT.FilterOn = True
      If Me.optWild.Value = 1 Then
        Forms!frmShowBookEDT.Caption = "Search Like(" & Me.cboSearchField & " contains '*" & Me.txtSearchString & "*')"
       Else
        Forms!frmShowBookEDT.Caption = "Search Exact (" & Me.cboSearchField & " equals '" & Me.txtSearchString & "')"
      End If

       'Close frmSearch
       DoCmd.Close acForm, "frmSearch"
       
        MsgBox "Your Search Results will be Displayed."
       
    End If
   

   On Error GoTo 0
   Exit Sub

cmdSearch_Click_Error:

    MsgBox "Error " & Err.Number & " (" & Err.DESCRIPTION & ") in procedure cmdSearch_Click of VBA Document Form_frmSearch"
   
End Sub

Open in new window

Module

Option Compare Database
'Global variable used to store search criteria
'Global GCriteria As String
Public GCriteria As String

Open in new window

0
Comment
Question by:skull52
  • 12
  • 8
  • 4
  • +2
29 Comments
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 34956664
There is a bug with Access 2007 split forms, where the Me keyword doesn't always work, and specifically a change or selection made in one portion of the form is not necessarily reflected in the other portion.  The workaround is to declare a Form variable, set to the form name (full syntax), and use that instead of Me in your code.  See if that works.  
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 34956676
Alternatively, instead of using a split form, since you seem to be using the form portion just for creating the search criteria, why not just use a datasheet subform to display the results of the search?  
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 34956690
My Fancy Filters sample database uses a datasheet subform to display filter results.  It can be downloaded from this link:
http://www.helenfeddema.com/Files/accarch129.zip


Here is a screen shot of the form:

Fancy-Filters-Form.jpg
0
 

Author Comment

by:skull52
ID: 34961131
Helen,
The weird thing is that I have used this same form on a couple of previous split forms with no problem, and if I use the exact option it filters. I will try your suggestions. Thanks  
0
 

Author Comment

by:skull52
ID: 34962154
I thought that the issue is with the Like selection in the option group as the exact function worked, so I removed all references to the option group and just tried a simple search but now it throws a debug error see screen shots and code I have tried this as a continuous form and a single form with the same results.  Debug Error Line
Private Sub cmdSearch_Click()

    If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
        MsgBox "You must select a field to search."
        
    ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
        MsgBox "You must enter a search string."
        
    Else
    
        'Generate search criteria
        GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
        'If no results sets focus back to Search Field
        If DCount("*", "qryEdit", GCriteria) = 0 Then
            MsgBox "No records for the search"
            Me.txtSearchString.SetFocus
            Exit Sub
       End If
        'Filter frmShowBookEDT based on search criteria
        Form!frmShowBookEDT2.RecordSource = "select * from qryEdit where " & GCriteria
        Form!frmShowBookEDT2.Caption = "Filtered (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"
        
        'Close frmSearch
        DoCmd.Close acForm, "frmSearch"
        
        MsgBox "Results have been filtered."
        
    End If
    
End Sub

Open in new window

0
 

Author Comment

by:skull52
ID: 34963114
it seems that no filter works, I tried Allen Browne's type as you go function and it can't anything typed in the value field is as though it cant read the field to filter.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 34969735
If this form worked in other databases but not here, then there must be something different. Maybe language-related, for example, or it does not like the delimiter.

Try to do the same filtering "manually", not within the code: try to apply an advanced filter from Access menu, like the one that does not work. Will it work that way?
Capture-02-24-00001.png
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 34971144
try this syntax and see if it works:
      GCriteria = "[" & Me.cboSearchField & "] LIKE '*' & Me.txtSearchString & '*'"
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 34971447
also try the syntax both of the ways shown:
GCriteria = "[" & Me.cboSearchField & "] LIKE '*'" & Me.txtSearchString & '*'


GCriteria = "[" & Me.cboSearchField & "] LIKE " & chr(42) & Me.txtSearchString & chr(42)
0
 
LVL 30

Expert Comment

by:hnasr
ID: 34974733
Attach a database, with istructions to recreate the problem.
0
 

Author Comment

by:skull52
ID: 34999560
Vadimrapp1,
I created a query on the table as you suggested and entered the search criteria LIKE "ALN*"  access switched the LIKE to ALIKE and displayed no results but if I replaced the *  wildcard with the % which is the SQL wildcard it worked. Query Search Quear Search 2
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 35000490
So that's probably your answer then - since you now see what it likes, use that.

This article sheds a like at the mystery:

Using the Right Wildcard Characters in SQL Statements
0
 

Author Comment

by:skull52
ID: 35000752
Puppy I tried your suggestion but it throws an error.   Error
Private Sub cmdSearch_Click()
   On Error GoTo cmdSearch_Click_Error

Set db = CurrentDb()

  If Len(Me.cboSearchField & "") = 0 Then
        MsgBox "You must select a field to search."
        Me.cboSearchField.SetFocus
    ElseIf Len(Me.txtSearchString & "") = 0 Then
        MsgBox "You must enter a search string."
        Me.txtSearchString.SetFocus
    Else
        If Me.optWild.Value = 1 Then
          'Generate search criteria
          GCriteria = "[" & Me.cboSearchField & "] LIKE " & Chr(42) & Me.txtSearchString & Chr(42)
        Else
          GCriteria = "[" & Me.cboSearchField & "] = '" & Me.txtSearchString & "'"
        End If
        'If no results sets focus back to Search Field
        If DCount("*", "2011AmcharShowBookEdit", GCriteria) = 0 Then
            MsgBox "No records for the search"
           
            Exit Sub
       End If
       
        'Filter frmCustomers based on search criteria
      Forms!frm2011AmcharShowBookEdit.Filter = GCriteria
      Forms!frm2011AmcharShowBookEdit.FilterOn = True
      If Me.optWild.Value = 1 Then
        Forms!frm2011AmcharShowBookEdit.Caption = "Like(" & Me.cboSearchField & " contains '%" & Me.txtSearchString & "%')"
       Else
        Forms!frm2011AmcharShowBookEdit.Caption = "Exact (" & Me.cboSearchField & " equals '" & Me.txtSearchString & "')"
      End If

       'Close frmSearch
       DoCmd.Close acForm, "frmSearch"
       
        MsgBox "Your Search Results will be Displayed."
       
    End If
   

   On Error GoTo 0
   Exit Sub

cmdSearch_Click_Error:

    MsgBox "Error " & Err.Number & " (" & Err.DESCRIPTION & ") in procedure cmdSearch_Click of VBA Document Form_frmSearch"
   
End Sub

Open in new window

0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:skull52
ID: 35000903
vadimrapp1,
 I added the ALIKE statement but it fails I am not sure if I entered it right. If I enter an exact value into the Search Text Field and select the Exact option it returns a value, however if I only enter a partial value and select the like option it returns no results and fires the "No records for the search" msgbox. it is somewhere in this statement.

       If Me.optWild.Value = 1 Then
          'Generate search criteria
          GCriteria = "[" & Me.cboSearchField & "] ALIKE '%" & Me.txtSearchString & "*'"
        Else
          GCriteria = "[" & Me.cboSearchField & "] = '" & Me.txtSearchString & "'"
        End If
        'If no results sets focus back to Search Field
        If DCount("*", "2011AmcharShowBookEdit", GCriteria) = 0 Then
            MsgBox "No records for the search"
           
            Exit Sub
       End If
       
        'Filter frmCustomers based on search criteria
      Forms!frm2011AmcharShowBookEdit.Filter = GCriteria
      Forms!frm2011AmcharShowBookEdit.FilterOn = True
      If Me.optWild.Value = 1 Then
        Forms!frm2011AmcharShowBookEdit.Caption = "Like(" & Me.cboSearchField & " contains '%" & Me.txtSearchString & "%')"
       Else
        Forms!frm2011AmcharShowBookEdit.Caption = "Exact (" & Me.cboSearchField & " equals '" & Me.txtSearchString & "')"
      End If
0
 

Author Comment

by:skull52
ID: 35000933
Also this search form is working  in previous databases, why all of a sudden would it require the ALIKE  operator over the LIKE
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 35001038
I think you need to find the answer to the last question, and that will explain the before-the last one. What is different in this database compared to previous ones? something in Options, in Advanced Options, in Properties in Access? then, what about the system libraries - language, regional settings? installed version of Access? of Jet engine? of anything else related you can think of? of the operating system?
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 35001084
...also: are the tables involved in the filter internal in this same database? or they are linked? if the latter, then also look at how they are linked. Maybe they are somehow linked as general ADO source - this would explain changing LIKE to ALIKE, according to the article I quoted above.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 35001112
It may be helpful to create filter string variables and inspect them in the Immediate Window.

Also, what is the ALIKE operator?  I have never come across it before.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 35001129
Helen, the article I found in http:#35000490 explains ALIKE. Undocumented feature.
0
 

Author Comment

by:skull52
ID: 35001242
Well folks I found the problem, SQL Server Compatibility Syntax (ANSI 92) was checked in Access Options>Object Designers and would only respond to that wild card code set. Not sure how that got checked as I am pretty sure I did not.  So all is well now, thanks for all the help.
0
 

Author Comment

by:skull52
ID: 35001768
Helen,
the ALIKE operator is used with the ANSI  92 code set used by SQL server, and if were to guess  the A in ALIKE stands for ANSI. and my issues was that my code was looking for the non ANSI 92 but the compatibility syntax was checked in the options.
0
 

Author Closing Comment

by:skull52
ID: 35017233
SQL Server Compatibility Syntax (ANSI 92) was the cause of the issue.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 35017525
I don't want to appear greedy or something, but it looks to me that the resolution was provided only in the comment http:#35001084, which prompted you to carefully look at the Options, where you have found the setting that was the root of the problem; while all the comments that you have accepted as answers, including mine, were missing the target. Now this accepted question goes into KB, and someone who will find it there, will think that the green comments are the resolution - while this is exactly the opposite. Accepting comments is not only about rewarding experts' effort, it's showing the right solution to the future visitors. Do you really think that accepted comments resolve this problem?
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 35017533
correction: http:#35001038
0
 

Accepted Solution

by:
skull52 earned 0 total points
ID: 35018533
Actually I found the resolution through a Google search by putting in "access 2007 only allows alike for like searches"  http://www.accessmonster.com/Uwe/Forum.aspx/access/113217/Access-2007-and-Alike     Prior to your http:#35001038 comment.  The link you refereed me to did explain the differences between the 2 however did not resolve my problem. I thought to be fair I would share the points as each of you offered viable solutions but did not work because of the ANSI setting. also my comment http:#35001242 explained the problem and resolution.  My intent was not to confuse or mislead but to reward for the help, and yes I may have miss-checked the box for your comment, for that I apologize but I believe the necessary information is in place for someone to follow for a resolution. Can the thread be reopened and rectified.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 35018687
===========
http://www.experts-exchange.com/help.jsp#hs=29&hi=404

How can I change a grade?

If you feel you have graded a solution incorrectly, click the Request Attention button and the Moderators will change it for you. If another member disputes the grade you have given, you will be asked to explain your reasoning; please respond to those requests.
===========

If you feel that you found the solution yourself, you can accept your own comment as well. As I said, the most important for an accepted answer is having the accepted solution indeed resolving the problem.
0
 

Author Closing Comment

by:skull52
ID: 35067652
Comment http:#35001242 explained the problem and resolution
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now