access subform requery problem

two forms: main form has an unbound dropdown. form2 has a recordsource that is a query. sometimes we need to sort the subform....all we do is click on the Z->A button. what this does is store a value in the form2 OrderBy property field...

the problem is, when we close out the form and then open it the next time, that value is still stored in the OrderBy property field which causes a conflict with the query that form2 is bound to.

how do i make sure that when the form closes the OrderBy field is cleared out or when it is loaded for the first time the OrderBy field is cleared?

Try this on the forms open event

CurrentDb.QueryDefs("NameOfQuery").SQL = "SELECT * FROM TableName"

or just use the SQL from your query without the Order By if you have selected fields from your table.

lwells

the query used does not have a name - it is the RecordSource of the subform. so i don't have a name to put between the parentheses...that's what confuses me...in the forms properties i have the recordsource setup - the SQL statement - which works great when it is first loaded. once i sort the subform it stores a value in the OrderBy property...the record source stays the same...

Gotcha,

In your main forms load event place the following code:

Dim strSQL As String
strSQL = "SELECT * FROM TableName;" 'Use correct table name here
Me!Form2.Form.RecordSource = strSQL

This would reset the recordsource back to the original format without the ORDER BY

You can also use the original SQL for the subform in place of what is above and it would do the same thing.

lwells

great, that worked!!