Form records don't follow change in underlying query

[grnzbra]

I have a form whose date source is a query.

Using QueryDef, in a module (at the moment I am just testing), I delete the old query and replace it with a duplicate that has a WHERE clause.

The OnFocus event has a Me.Refresh statement.

I have also tried Me.Requery.

I have also tried these in the OnFocus event of one of the controls and then tabbed into that control.

The records remain the same as before the module was run.

If I go into design mode and open the query from the datasource property, it reflects the records that match the change.

When I go back to normal mode for the form, the records are correct for the new query.

How can I get the records displayed on the form to match the new query without going through the process of closing and reopening the form?

 

[PHV]

I delete the old query and replace it with a duplicate that has a WHERE clause
Why not simply play with the Fileter and FilterOn properties of the Form object ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

OOps, sorry for the typo: Filter and FilterOn

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[grnzbra]

The form has a subform which lists Topics (main form is Books)

The driving query consists of fields from the books table plus TopicID from the BookTopics table which is joined on the BookID fields of both tables.

This generates a record for each book topic. (I need the topic id to show up in the main form so that the filter can filter on it).

I tried adding a loop in the WHERE generating module which would loop through the records of the BookTopics table and create a "WHERE BookID IN(BookID1,BookID2, etc)" clause. This worked very nicely because now I was searching for BookID so I didn't need the TopicID to show up in the DataSource for the form. However there are so many books with the topic "Art" that I got an error message saying that the filter string was too big.

The only other thing I could think of was to change the driver query to take the TopicID as a criteria field but not a display field. The QueryDef method is the only way I could find to manipulate the WHERE clause of a query.

(I was thinking of trying use a SQL statement for the

 

[grnzbra]

Record source property of the form, but ran out of lunchtime so I couldn't try it.