Reload bound controls after underlying query change?

[Guest]

I have an application with a search form, and a subform for the results of that search (there may be multiple results so the subform is a datasheet view). If the individual changes the search parameters, I use an SQL statement to programatically re-create the underlying query for the results subform. Is there a way to update the sub-form data with the new query results? Currently, the only way I can get it to work is to unload and reload the form (not very clean). I have tried several variations of the requery method, but can't seem to get it to work with the subform. The primary form is called FrmSelection and the subform is called SubFrmResults in the "SubFrmResultsCtl" control. Any help on the requery method or other ideas would be greatly appreciated.
Thanks

 

[Erez]

hi there
first, did you requery the subform? or just the main form
to requry the subform in SubFrmResultsCtl you
write "SubFrmResultsCtl.Form.Requery"
second, you can set the sql statement directly as the
record-source of the subform (instead of creating a saved
query and setting the record source to it)
so if it happens from the main form, you write:
SubFrmResults.form.recordsource="Select ... Where...."
SubFrmResults.form.requery
hope it helps,
Erez.

-----Original Message-----
I have an application with a search form, and a subform

for the results of that search (there may be multiple
results so the subform is a datasheet view). If the
individual changes the search parameters, I use an SQL
statement to programatically re-create the underlying
query for the results subform. Is there a way to update
the sub-form data with the new query results? Currently,
the only way I can get it to work is to unload and reload
the form (not very clean). I have tried several
variations of the requery method, but can't seem to get it
to work with the subform. The primary form is called
FrmSelection and the subform is called SubFrmResults in
the "SubFrmResultsCtl" control. Any help on the requery
method or other ideas would be greatly appreciated.

 

[Guest]

Thanks - I tried that and it still didn't seem to work - here are the variations I have tried

SubFrmResultsCtl.Form.Requer

Forms!FrmSelection!SubFrmResultsCtl.Form.Requer

Me.SubFrmResultsCtl.Requer

Forms!FrmSelection!SubFrmResults.Form.Requery (gives an error - "can't find the field 'SubFrmResults' "

Dim ctrl As Contro
Set ctrl = Forms!FrmSelection!SubFrmResultsCt
ctrl.Requer

Except for the one I noted that gives an error, the rest just don't do anything - the underlying query is updated, but the subform is not changed. One possible contributing factor (but I don't think it affects it) is that the subform is actually on a different "page" of a tab control. I have tried doing the above both before and after giving the focus to the associated tab.

 

[Guest]

One thing I just thought of - is it possible that my problem is due to some property setting of the subform that prevents it from re-querying? The subform is for display only (datasheet view) - I don't allow edits, data entry, deletions, additions, etc. Could one of these, or some other property setting be causing this? I have tried changing some of them, both on the main form and the sub form, so far with no success - but there are a lot of combinations, so trial and error could be a pretty exhausting effort.

 

[John S]

Hi,

I've had a bitch of a problem with this too (and still have) - requering the
sub forms (in my case, to clear it after a record insert).

Possibilities:

Play around with the refresh method.

Move to a continuous form, instead of a datasheet, but this may have no
effect.

bind the datasheet to a temporary table, and manipulating the temporary
table. However, As my newsgroup friends have informed me, this is rather
defeating the point of MS Access, whose strength is bound forms.

John S
Aylmer, PQ

Thanks - I tried that and it still didn't seem to work - here are the variations I have tried:

SubFrmResultsCtl.Form.Requery

Forms!FrmSelection!SubFrmResultsCtl.Form.Requery

Me.SubFrmResultsCtl.Requery

Forms!FrmSelection!SubFrmResults.Form.Requery (gives an error - "can't

find the field 'SubFrmResults' ")

Dim ctrl As Control
Set ctrl = Forms!FrmSelection!SubFrmResultsCtl
ctrl.Requery

Except for the one I noted that gives an error, the rest just don't do

anything - the underlying query is updated, but the subform is not changed.
One possible contributing factor (but I don't think it affects it) is that
the subform is actually on a different "page" of a tab control. I have
tried doing the above both before and after giving the focus to the
associated tab.

 

[TC]

Are you saying that the subform is based on a stored query, & you re-write
the SQL of that query?

If so, maybe you could base the subform on a SELECT statement. Putting a new
SELECT statement into the subform's recordsource (programatically) would
certainly cause it to requery in the way you expect.

HTH,
TC

I have an application with a search form, and a subform for the results of

that search (there may be multiple results so the subform is a datasheet
view). If the individual changes the search parameters, I use an SQL
statement to programatically re-create the underlying query for the results
subform. Is there a way to update the sub-form data with the new query
results? Currently, the only way I can get it to work is to unload and
reload the form (not very clean). I have tried several variations of the
requery method, but can't seem to get it to work with the subform. The
primary form is called FrmSelection and the subform is called SubFrmResults
in the "SubFrmResultsCtl" control. Any help on the requery method or other
ideas would be greatly appreciated.