Newbie Problem - Requery/Refresh Main Form after 'SearchForm' completed

[RSIboy]

Hi - I have frmCompany which is the main form.

There is a button on this form that opens the frmSearch form which is basically a 'Company Name' field (to allow the user to enter the first few letters of a company name), and then a 'Search' button (which builds a SQL string that modifys the querydef.SQL for the query on which frmCompany is based).

The code behind the 'Search' button should a)modify the qryCompany, b)Refresh/Requery the frmCompany, c)close the Search form

I am having trouble with the refresh/requery bit - searched the forums but I'm doing what everyone is suggesting. I have tried the lines:

Forms!frmCompany.ReQuery
Forms!frmCompany.Refresh

but it does not properly refresh/requery the form (The one thing that does work, is my 'Record x of y' counter updates!). However, if I close the main form, and open it again, the new information is correctly displayed! So at the moment I am using the lines:

DoCmd.Close acForm, ("frmCompany")
DoCmd.OpenForm "frmCompany"

- but this is messy, and look bad on-screen.... surely there must be a neater way?

Any help much appreciated

Dale
Manchester, UK

 

[WayneRyan]

Dale,

Try: Forms!frmCompany.RecordSource = "TheQueryThatYouBuilt"

Wayne

 

[RSIboy]

Fantastic! Thanks very much for your help!

(I was worried no-one was going to respond...) ;-)

Cheers

Dale
Manchester, UK

 

[Brianwarnock]

Hi Dale
Glad one of the experts solved the problem after my original botched attempt

but I don't understand why what you were doing didn't work.

Brian

 

[WayneRyan]

Brian,

Probably can't explain this too well, but if you base a form on a query, then
change the query, you must start all over again and "reapply" the query
to the form. Otherwise, Access continues to use the old one.

Wayne

 

[Brianwarnock]

That is very clear thanks Wayne, but ACCESS help states:-

The Requery method does one of the following:

Reruns the query on which the form or control is based
Iguess that there is some subtle point that I am missing and some more study is needed.

Brian

 

[WayneRyan]

Brian,

To put it another way:

When access opens a form "it stores the SQL string" for the query. Any
.Requery will just run the same old SQL string. To get the new query to
"take hold", you have to re-assign the RecordSource and make Access
start from square one.

Wayne