Requery subform with new SQL

  • Thread starter Thread starter RGBrighton
  • Start date Start date
R

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
 
hi,
'Requery
CurrentDb.QueryDefs("qryGeneralStudentList").SQL = sqlString
Forms!frmGeneralStudentList!subcontrolStudentList.Form.Requery
Click to expand...
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 <--
 
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!
 
hi Richard,
PS: Problem solved .... but I am still wondering about the inability to get
requery to work?
Click to expand...
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!
Click to expand...
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!
Click to expand...
You may consider to avoid/drop the query and use only the SQL in the
record source property.


mfG
--> stefan <--
 
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
 
Back
Top