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]

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

I never had a problem untill I upgraded to 2007?

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]

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.

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]

Fixed but not Happy

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]

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*"

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]

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

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.