Query parameters not getting refreshed

[Katerine]

Hi,
I have a rather strange problem that I've been racking my brain on for about 3 hours now. I've checked the knowledgebase, and didn't return anything helpful.

I have a client with an Access 2000 database that's connected to a remote SQL server on the other side of the country. Because of the slowness of getting the data, several of the data forms open with a filter dialog form. I've made several of these, but one of them (just one) is having this problem.

This form (WebLinksSubform) opens with a dialog form (DialogFilterWebLinks), which has 6 controls:
txtTitleWord1
txtTitleWord2
txtTitleWord3
txtSearchWord1
txtSearchWord2
cboSelpReviewer
All of these are text controls, except cboSelpReviewer, which contains the IndividualID # of the reviewer.

Each time the dialog form opens, it resets all fields to Null in the code.

The main form is based on the following query:
SELECT WebLinks.*
FROM WebLinks LEFT JOIN Groups ON WebLinks.fkGroupID = Groups.pkGroupID
WHERE (((WebLinks.Title) Like "*" & [Forms]![DialogFilterWebLinks]![txtTitleWord1] & "*" And (WebLinks.Title) Like "*" & [Forms]![DialogFilterWebLinks]![txtTitleWord2] & "*" And (WebLinks.Title) Like "*" & [Forms]![DialogFilterWebLinks]![txtTitleWord3] & "*&quot AND ((Groups.GroupName) Like "*" & [Forms]![DialogFilterWebLinks]![txtSearchWord1] & "*" And (Groups.GroupName) Like "*" & [Forms]![DialogFilterWebLinks]![txtSearchWord2] & "*&quot AND ((WebLinks.fkIndividualID)=[Forms]![DialogFilterWebLinks]![cboSelpReviewer]) AND (([Forms]![DialogFilterWebLinks]![cboSelpReviewer]) Is Not Null)) OR (((WebLinks.Title) Like "*" & [Forms]![DialogFilterWebLinks]![txtTitleWord1] & "*" And (WebLinks.Title) Like "*" & [Forms]![DialogFilterWebLinks]![txtTitleWord2] & "*" And (WebLinks.Title) Like "*" & [Forms]![DialogFilterWebLinks]![txtTitleWord3] & "*&quot AND ((WebLinks.fkIndividualID)=[Forms]![DialogFilterWebLinks]![cboSelpReviewer]) AND (([Forms]![DialogFilterWebLinks]![cboSelpReviewer]) Is Not Null) AND ((Groups.Acronym) Like "*" & [Forms]![DialogFilterWebLinks]![txtSearchWord1] & "*" And (Groups.Acronym) Like "*" & [Forms]![DialogFilterWebLinks]![txtSearchWord2] & "*&quot) OR (((WebLinks.Title) Like "*" & [Forms]![DialogFilterWebLinks]![txtTitleWord1] & "*" And (WebLinks.Title) Like "*" & [Forms]![DialogFilterWebLinks]![txtTitleWord2] & "*" And (WebLinks.Title) Like "*" & [Forms]![DialogFilterWebLinks]![txtTitleWord3] & "*&quot AND ((Groups.GroupName) Like "*" & [Forms]![DialogFilterWebLinks]![txtSearchWord1] & "*" And (Groups.GroupName) Like "*" & [Forms]![DialogFilterWebLinks]![txtSearchWord2] & "*&quot AND (([Forms]![DialogFilterWebLinks]![cboSelpReviewer]) Is Null)) OR (((WebLinks.Title) Like "*" & [Forms]![DialogFilterWebLinks]![txtTitleWord1] & "*" And (WebLinks.Title) Like "*" & [Forms]![DialogFilterWebLinks]![txtTitleWord2] & "*" And (WebLinks.Title) Like "*" & [Forms]![DialogFilterWebLinks]![txtTitleWord3] & "*&quot AND (([Forms]![DialogFilterWebLinks]![cboSelpReviewer]) Is Null) AND ((Groups.Acronym) Like "*" & [Forms]![DialogFilterWebLinks]![txtSearchWord1] & "*" And (Groups.Acronym) Like "*" & [Forms]![DialogFilterWebLinks]![txtSearchWord2] & "*&quot);

Translation:
Get all WebLinks records where:
( Title contains txtTitleWord1, txtTitleWord2, and txtTitleWord3 (if any) )
AND
(( GroupName contains txtSearchWord1 and txtSearchWord2 (if any) ) OR ( GroupAcronym contains txtSearchWord1 and txtSearchWord2 (if any) ))
AND
( IndividualID equals cboSelpReviewer OR cboSelpReviewer is Null )

The filtering works just fine -- the first time. But each subsequent attempt to filter the recordset query returns a recordset that's limited not only by the new filter criteria, but the old filter criteria as well. I've tried running just this query (without the dialog form, so I'm prompted for each of the six field values), and left all parameters blank, which should return the complete recordset - but instead, it's returning just the one record that was returned when I first ran the filter today.

(Hopefully, this made sense to somebody, who will be able to tell me how to fix this ) Please let me know if there's anything that needs clarifying. Katie

 

[vbajock]

Are you using ADO?

 

[Katerine]

I'm not sure what you mean by using ADO (so I'll interpret the question every possible way I can think of).

I'm using Access 2000, professional edition.

The main form (the one with the data) is bound to a saved query (that's the SQL statement I posted above - the saved query). The dialog form is, of course, unbound.

There are no ADO objects created or referenced in the dialog form. In the main form, however, the Form_Current event calls a global (public) function which uses an ADO connection and recordset to look up an ID field in another table and return an array (this other table contains directory information.. the function iterates up the directory path until it gets to the root, and returns an array of the entire directory path). This function does close the recordset and connection objects and set them to Nothing at the end. This is the only code in the main form that uses ADO objects.

The problem is not in the main form. The problem, as far as I can tell, is in the query.

This is what happens:
1. I open the main form, which opens the dialog form in it's Form_Open event.
2. The dialog form sets all possible filter criteria initially to Null. I leave them at Null to return everything, and hit the Search button on the form. The dialog form is hidden, and the main form opens, displaying all 42 records, exactly as it should.
3. I click a search button on the main form to open up the dialog form again. The dialog form's Open event fires again (I've checked this, using breakpoints), resetting all items to Null. I enter "Env" in the txtTitleWord1 textbox, and hit the search button on the dialog form again. The dialog form, again, is hidden, and the main form shows with one record, exactly as it should.
4. I click the search button on the main form again. Again, the Open event fires, resetting all fields to Null. I leave them at Null, just as I did in step 2, but this time, when I click the Search button, it just returns the one record that it returned in step 3.
5. I close the main form and open it again. Again, I just get the one record, when I should get them all.
6. I close the main form (which, incidentally, also closes the hidden dialog form in it's Close event handler) and open the saved query. Since the dialog form is closed, I'm asked for each of the parameter values. I leave all parameter values blank to get all records, but the query still just returns the one record from step 3.

I've tried explicitly declaring the parameters in the query. I've tried using the Nz function in the query, replacing null strings with blank strings. Neither makes any difference in the outcome.

Help! :-( Katie

 

[GDGarth]

I had this problem, too. I was able to fix it by adding "FilterOn = False" as the first step in the routine. Then I apply the new filter, and it works. Here's the code I use to show only records after a user-selected date (I know it's not pretty, but it seems to work):

On Error GoTo ErrHandler
FilterOn = False
Forms!schedule2.Requery
Dim FilterDate As Date
Dim DateString As String
Dim FilterString As String
SearchDate.SetFocus
FilterDate = SearchDate.Text
DateString = Str(FilterDate)
FilterString = "[builddate] >= " + "#" + Str(FilterDate)+ "#"
DoCmd.ApplyFilter , FilterString
[build date].SetFocus

DoCmd.FindRecord FilterDate
Exit Sub

ErrHandler:
Exit Sub

 

[blakezx1]

Hi Katie, I am not really clear on a couple of things.
1. Are you are using the huge SQL statement as actual query criteria, or are you selecting all records and then applying a filter that contains the huge SQL statement?

2. It seems that huge SQL statement is forcing your SQL server to perform all the work. Have you considered grabbing the values from your text boxes, storing those in variables and then constructing the necessary SQL statement at runtime in VBA? This would minimize the work on your SQL server and put more of the "filtering" on you user's machine (improving speed I would suspect).

Its seems like you have an enormous amount of time already invested so I am hesitant to ask that second question. It would require quite a bit of coding.

 

[Katerine]

Thanks for all the offers to help Actually, the problem seems to have somehow gone away on its own (to be replaced by others caused by bad data, but I know how to resolve those).

To answer your questions, blakezx1, the SQL statement I posted above is the entire contents of a saved query. The saved query is on an Access database.

Oh, how I wish I could just make the entire thing unbound. I prefer unbound forms so much. But the clients don't want that.. Katie