Problem with Keyword search after updateing to 2007

[Redbeard]

I have a keyword search button on my databases footer bar which when clicked opens a pop-up box that you can type a key word into. When the keyword is entered it put it into a query that filters my form to that keyword in several fields. Problem is that since my upgrade to 2007, it only works the first time. If you try to use it again it does not display the pop-up box and just re-filters for the first work you typed. The only way around this is a full shut down and restarts which is getting annoying! I did not write this code and I am not sure how to fix it, any help would be greatly appreciated! Code is below.

Thanks

Private Sub Keyword_Search_Click()
On Error GoTo Err_Keyword_Search_Click
 
    Dim stLinkCriteria As String
 
    'DoCmd.Close acForm, "frmDescription"
    Forms("frmDescription").Refresh
    'DoCmd.Close acForm, Me.Name
    DoCmd.OpenForm "frmDescription", , "qryKeyword", stLinkCriteria
 
 
Exit_Keyword_Search_Click:
    Exit Sub
 
Err_Keyword_Search_Click:
    MsgBox Err.Description
    Resume Exit_Keyword_Search_Click
 
End Sub

[puppydogbuddy]

It sounds like you need to clear your search results before proceeding with a new search. Please post the sql for qrykeyword, and the code relating to the build of stLinkCriteria.

[Redbeard]

Here is the SQL for the Keyword query:

SELECT *
FROM fullqry
WHERE (((fullqry.Description) Like "*" & [Enter keyword: ] & "*")) OR (((fullqry.History) Like "*" & [Enter keyword: ] & "*")) OR (((fullqry.[Object Name]) Like "*" & [Enter keyword: ] & "*")) OR (((fullqry.[Object Type]) Like "*" & [Enter keyword: ] & "*")) OR (((fullqry.[Subject/Image]) Like "*" & [Enter keyword: ] & "*")) OR (((fullqry.[Photo Keywords]) Like "*" & [Enter keyword: ] & "*"))
ORDER BY Mid(Replace(Description.[Accession Number],'X',''),1,4), Mid(Replace(Description.[Accession Number],'D',''),1,4), Mid(Replace(Description.[Accession Number],'DX',''),2,5);

and her is the code relating to the build of stLinkCriteria:

Like "*" & [Enter keyword: ] & "*"

I never had a problem untill I upgraded to 2007?

[NeoPa]

@Redbeard
I don't understand why. The quotes are missing from this. It should read :

Like "'*" & [Enter keyword: ] & "*'"

[Redbeard]

I tried adding the quotes like you suggested NeoPa, but it now does not work at all? I just get 0 records and I still have the same problem.

[puppydogbuddy]

stLinkCriteria looks like duplication of what is in fullquery. Try it without stLinkCriteria.

Change this:

DoCmd.OpenForm "frmDescription", , "qryKeyword", stLinkCriteria 

To this:

DoCmd.OpenForm "frmDescription", , "qryKeyword" 

[NeoPa]

@Redbeard
Am I going mad?

Can anyone else see something I've missed :S

[Stewart Ross]

The SQL as originally posted was correct, NeoPa. It was not a string built in VBA, where the literal would indeed be placed inside single quotes, but SQL direct from the query grid. The syntax used is correct when using a parameter preceded and followed by the wildcard character, and works flawlessly in my tests in A2003.

Given a value for the parameter, the Like clause just becomes something along the lines of

Like "*Some Value*"

Access will happily accept single quotes in place of the double quotes for the same purpose, but the query grid editor places double quotes around string literals by default when they are typed into the criteria part of the grid.

Just a guess, but perhaps RedBeard could substitute single-quotes in place of the double-quotes in the Access 2007 version, to see if the original problem relates to previous versions of Access accepting double quotes when single quotes should be preferred (if 2007 is sticking closer to ANSI SQL standards).

For some back-end databases (such as SQL Server if memory serves) the wild card character is "%" and not "*", but as this problem started with the upgrade to A2007 I doubt that the choice of wildcard is the problem.

-Stewart

[FishVal]

@ Redbeard

Could it be so that when you run DoCmd.OpenForm second time [frmDescription] instance, created by the first call, has not been unloaded?

[Redbeard]

I have changed the quotes to singles like Stewart Ross suggested and got rid of the stLinkCriteria like puppydogbuddy suggested but it was still not letting me enter a Keyword the second time around. So I have modified the code to work but not really happy with how it is doing it. Here is the code now:

Private Sub Keyword_Search_Click()
On Error GoTo Err_Keyword_Search_Click
 
    DoCmd.Close acForm, "frmDescription"
    DoCmd.OpenForm "frmDescription", , "qryKeyword"
 
Exit_Keyword_Search_Click:
    Exit Sub
 
Err_Keyword_Search_Click:
    MsgBox Err.Description
    Resume Exit_Keyword_Search_Click

The form is now closing and then asking me for my keyword and then reopening when I entre one. This seems to get rid of the problem as it will let me entre a new keyword each time but the opening and closing of the form is a little time consuming and not visually appealing. Does anyone see a way around this?

Thanks for the help so far.

[Stewart Ross]

I'd suggest using a different approach; there is no need to keep opening and closing the form if what you are doing is to apply a filter based on a keyword entered in response to code running in another form. Use a separate routine to open the form just the once (without a filter applied in the first instance), or just open the form if it is not already loaded. Then, your keyword entry response routine just has to apply a filter to the form - no need to close it and reopen it., This is done using the form's Filter and FilterOn properties to set and apply the keyword filter.

You will need to supply the form's full name if you are applying a filter from outside of the form itself, like this:

with forms![name of your form]
    .filter = strYourFilterString
    .filteron = true
 end with

-Stewart

[NeoPa]

@Stewart Ross Inverness
I was having brain problems that day Stewart. You're absolutely correct of course and it makes perfect sense when I look at it now.

[NeoPa]

@Stewart Ross Inverness
I would suggest looking at ANSI Standards in String Comparisons.

Later versions of Access can be set to use those more recent standard settings also, thus leaving older code to flounder.

[puppydogbuddy]

Assuming that frmDescription is already opened and that qryKeyword is the record source of frmDescrption

try changing this:

DoCmd.Close acForm, "frmDescription" 
DoCmd.OpenForm "frmDescription", , "qryKeyword" 

to this:

Forms!frmDescription.RecordSource = ""
Forms!frmDescription.RecordSource = "qryKeyword"

[Redbeard]

Ok, so I started with puppydogbuddy fix as it seemed the simplest but it causes issues with other code and will not run. Also it is not based on the same query as the main form, tried to change the query to the same one as the main form but same results. I have also looked at Stewart Ross suggestion but I don't know enough about programming to figure out how to use it. I was original hopping that it was just a matter of a simple fix to my code like 2003 use one thing and 2007 uses another, but it seem to be a complete rewrite. So if there is anyone that has a code that will do what I want that would be great. Essentially I have my main form "frmDescription" which is up. I have a Keyword button at the bottom on my footer bar. When I click the button I want to have a pop-up ask me for a keyword that when entered looks for that keyword in 5 different fields on my main form and filters out the appropriate records. As I said above, I am not a programmer and have very limited knowledge about how these things work, so if possible please explain in simplest terms.

Thanks again for all the help.

[NeoPa]

Can you provide meta-data for the form that you are hoping to filter. This may be a query or a table, but we would need to know its name and the fields it contains.

This will work much better if you can post the meta-data (info about the layout / structure) of the table in the same way as I use in my example. Click on the Reply button and you will have access to all the codes I've used. PK & FK stand for Primary Key & Foreign Key respectively. Please don't use any TABs in your formatted layout as this will not display correctly - Spaces only for layout.
Table Name=[tblStudent]

Field           Type        IndexInfo
StudentID       AutoNumber  PK
Family          String      FK
SName           String
University      String      FK
Mark            Numeric
LastAttendance  Date/Time

[Redbeard]

Hi Neopa

The form that I am using is called "frmDescription" and it is run off a query call "fullqry" which is taking info from 10 tables and has over 50 fields. Do you want the meta-data from every field in the Query or Form? And if so how do I get that?

[NeoPa]

@Redbeard

1. Not all no. With that many it would be helpful if you included only the relevant ones. That should cover any that may be used in filtering or sorting.
2. To get the list of fieldnames you can run the query, copy the results and paste into Excel.
   From Excel you can copy just the title row then use Paste Special / Values / Transpose to provide the list in a column.
   This can then be Copy / Pasted as text.

[Redbeard]

These are the feilds in the "fullqry" that I want to filter my Keyword search through:

Feilds                  Type              IndexInfo
Description             Memo
History                 Memo
Object Name             Text
Object Type             Text
Subject/Image           Text 
Photo Keywords          Text

I don't think any of the have IndexInfo as that is what links tables together?

[NeoPa]

Thanks for this and well done (you did a fine job - so many fail to get this right).

I'm just setting up some tests now, but I think I'm right in saying (will confirm later) that filtering is not possible on Memo fields.

FYI. Index Info is useful for linking tables, but is certainly not limited to that single application.

[NeoPa]

Well, that proved a failure (in as much as the Memo field I tried it with worked perfectly).

OK. Let me see if I can put together some SQL to fit this, then we can see if we can determine exactly what is causing your problem. I have some suspicions now, but I suggest we take it step-by-step otherwise we'll get out of synch and confused.

[NeoPa]

Try this and explain where, if anywhere, there are problems. This is actually very similar to the SQL you posted in post #3.

SELECT   *
 
FROM     [FullQry]
 
WHERE  (([Description] Like '*' & [Enter keyword: ] & '*')
   OR   ([History] Like '*' & [Enter keyword: ] & '*')
   OR   ([Object Name] Like '*' & [Enter keyword: ] & '*')
   OR   ([Object Type] Like '*' & [Enter keyword: ] & '*')
   OR   ([Subject/Image] Like '*' & [Enter keyword: ] & '*')
   OR   ([Photo Keywords] Like '*' & [Enter keyword: ] & '*'))

I expect you'll see similar problems to before, but check it carefully anyway if you would.

[Redbeard]

Ok, so I tried it and got the same results. It works the first time but when you try it again there is no pop-up box that asks you to type in a keyword and it automatically re-filters for the keyword you typed the first time. It seems to be that we just need to figure out how to clear the keyword out of the filter? Otherwise it works fine.

[NeoPa]

Right, this is what I was sort of expecting.

What I think is happening is that, just as when you enter a prompt in SQL many times, the SQL engine recognises this and optimises the situation by asking for it only once, A2007 is also (it seems) trying to be clever and optimising out the prompt for the value across multiple runs when it determines that it has a value for that already. trying to be too clever it would seem.

I suggest, to get around this, that you take the form based approach instead.

Write your SQL to refer instead to a TextBox on an open form. That way the operator can change this before the query (report whatever) is run. This can also be set up so that the query is run from a command button on the same form.

Does this sound like a plan?

[Redbeard]

So you are suggesting that I create a form with a text box that is my keyword entre point, that is connected to my main form by a button on the footer bar. When a click the button on the footer bar the new form with text box pops up and askes for a keyword. When I entre one is runs the query that filters my main form. So that each time I get the option to entre a new keyword. Is this right? If so how do I link the text box on my new form to the query that I am running?

[NeoPa]

Almost.

I would consider adding an unbound TextBox onto your main form if that is where you start the process from. That avoids the extra (unnecessary) complexity of creating a new, dedicated, form for the task. Having said that, the separate form could also be made to work should you prefer that approach.

To link the query to the control on the form you have to reference the item as a qualification of Forms('FormName').

[Redbeard]

Ok so I created a new form that just has an unbound text box and a button on it. When I click my keyword button on my main form it opens the new form with the unbound textbox and a button. I type my keyword in and hit the button to run my filter... no problem run fine. But when I go to use it a second time it ignores the new word I typed in and just filter again for the first word, same as before. The only way to clear it is to close the main form and open it. There must be a way around this, it is driving me mad.

[NeoPa]

That's a little bit more complicated than I'd hoped. OK. The hard way (using a separate form).

Is the main form (where the filter should be applied) closed between different attempts?

If not, what is the code behind your command button (Please copy and paste)?

[Redbeard]

Sorry for doing it the hard way but there is really no room left on my form for another text box. No the Main form is not close between attempts... that is the problem. I can make it work if I shut down the main form after every attempt but that take time. I would like to be able to filter the main form without closing it. Here is the code for the button on the new form that activates the filter.

Private Sub Run_Click()
 
DoCmd.OpenForm "frmDescription", , "qryKeyword"
Forms("frmKeyWordSearch").Refresh
DoCmd.Close acForm, Me.Name
 
End Sub

[NeoPa]

I'm rushing now, but try requerying the main form (I'm sorry I'm not sure which that is atm) after setting the new criteria.

I will check back later and give it some deeper thought if you haven't found this sorts your problem out.

[Redbeard]

Problem Solved... thanks Neopa! The requery actually fixes my original code, here is my original code that I posted (A little bit clean up):

Private Sub Keyword_Search_Click() 
On Error GoTo Err_Keyword_Search_Click 
 
    Forms("frmDescription").Refresh 
    DoCmd.OpenForm "frmDescription", , "qryKeyword"  
 
Exit_Keyword_Search_Click: 
    Exit Sub 
 
Err_Keyword_Search_Click: 
    MsgBox Err.Description 
    Resume Exit_Keyword_Search_Click 
 
End Sub 

And here is the new code that solved the problem without using any new forms or text boxes!

Private Sub Keyword_Search_Click()
On Error GoTo Err_Keyword_Search_Click
 
    Forms!frmDescription.Requery
    DoCmd.OpenForm "frmDescription", , "qryKeyword"
 
Exit_Keyword_Search_Click:
    Exit Sub
 
Err_Keyword_Search_Click:
    MsgBox Err.Description
    Resume Exit_Keyword_Search_Click
 
End Sub

Thanks again!

[NeoPa]

Nicely done RedBeard.

It's unfortunate that Refresh simply regrabs the existing records. A Requery is needed to redo the whole query. Refresh neither adds new records, nor removes deleted ones, and certainly doesn't apply any new filter.

Only M$ would know why it's called Refresh :S