Requery subform with new SQL

[RGBrighton]

I have an unbound subform for which I wish to make choices on the parent form
which will lead to redefinition of the SQL for the query that is the
rowsource of the subform in the subcontrol of the main form.

The SQL redefinition works fine and the SQL comes out correct but the
subform doesn't refresh. I have read the many helpful posts and answers
about requerying subforms on this forum but still no success. Strangely, (to
me) the subform updates if I click the form into design view and then back
again.

The main form has a button to refresh the data which contains this code:

'set basic SQL clauses
sqlSelect = "SELECT q.* FROM qryBaseStudentList AS q "
sqlOrder = "ORDER BY q.Surname;"

'Set WHERE clause
If Len(ActiveClause & CourseClause) = 0 Then
sqlWhere = " "
Else
sqlWhere = "WHERE " & ActiveClause & CourseClause & " "
End If
sqlString = sqlSelect & sqlWhere & sqlOrder

'Requery
CurrentDb.QueryDefs("qryGeneralStudentList").SQL = sqlString
Forms!frmGeneralStudentList!subcontrolStudentList.Form.Requery

Thanks for reading.

Richard

Access 2003 in XP

 

[Stefan Hoffmann]

hi,

'Requery
CurrentDb.QueryDefs("qryGeneralStudentList").SQL = sqlString
Forms!frmGeneralStudentList!subcontrolStudentList.Form.Requery

Maybe a CurrentDb.QueryDefs.Refresh works before calling the form's requery.

Otherwise you can set the SQL for the form directly:

Forms!frmGeneralStudentList!subcontrolStudentList.Form.RecordSource = _
sqlString


mfG
--> stefan <--

 

[RGBrighton]

Thank you Stefan,
Setting the rowsource to the sql works fine.
Your help is much appreciated.

Regards
Richard

PS: Problem solved .... but I am still wondering about the inability to get
requery to work?
And with the solution of setting the rowsource to the sql statement I get
another strange anomaly ... I can see the sql in the rowsource property of
the form while it is open; but when I close and save the form then the
rowsource property is not saved and it reverts to the original query name!
Maybe it only saves the parent form properties and not those of the subform?
But this is not something I need to worry about too much!

 

[Stefan Hoffmann]

hi Richard,

PS: Problem solved .... but I am still wondering about the inability to get
requery to work?

The form requery uses the already loaded query and executes it. It does
not reload the query object and its definitions, thus it doesn't load
the modified SQL statement.

And with the solution of setting the rowsource to the sql statement I get
another strange anomaly ... I can see the sql in the rowsource property of
the form while it is open; but when I close and save the form then the
rowsource property is not saved and it reverts to the original query name!

Normally changes on a form or sub form are not saved during runtime.

But this is not something I need to worry about too much!

You may consider to avoid/drop the query and use only the SQL in the
record source property.


mfG
--> stefan <--

 

[RGBrighton]

Hi Stefan,
Yes, I have now changed the form rowsource property to a default sql string
so the query is redundant!

Once again, Many Thanks for your help.

Regards
Richard