Form sort indication

  • Thread starter Thread starter Michael Iannantuoni
  • Start date Start date
M

Michael Iannantuoni

Does anyone know if there is a way in a Form to show, in a text box for
example, which field is being used to sort the data and in which order?

TIA

Michael
 
Michael Iannantuoni said:
Does anyone know if there is a way in a Form to show, in a text box for
example, which field is being used to sort the data and in which order?

TIA

Michael
Click to expand...

Well one way would be to parse it out of the sql behind the form's
recordsource:

With CurrentDb.QueryDefs(Me.RecordSource)
Me.MyTextBox = Mid(.SQL, Instr(1, .SQL, " ORDER BY ") + 11)
End If

Or, if your form's recordsource contains an SQL statement instead of the
name of a Query, then:

Dim rsc As String

rsc = Me.RecordSource
Me.MyTextBox = Mid(rsc, Instr(1, rsc, " ORDER BY ") + 11)

This will display the field or fields (there can be more than one,
remember), followed by DESC if the order is descending. If no DESC, then the
order is Ascending.
 
I'm a relative newcomer to Access so I must be doing something wrong as
I can't get this to work. Can you spell it out for me please?

Thanks
 
Michael Iannantuoni said:
I'm a relative newcomer to Access so I must be doing something wrong as I
can't get this to work. Can you spell it out for me please?

Thanks
Click to expand...

Ok, open your form in design view, open the property sheet if it isn't
showing (Menu option View -> Properties), click on the 'Data' tab and look
at the property 'RowSource'. It should either contain the name of a saved
Query or an SQL statement (eg SELECT * FROM TableName). If it's the name of
a Query, the code you need is the first I suggested, otherwise it's the
second. Paste that code into the Form's OnLoad event procedure. To do this,
click on the property sheet's 'Events' tab, find the property 'On Load' and
click the button on the RHS (with ... as it's caption). Access will open the
Visual Basic editor, open the form's module, and create an event procedure
for you. It'll look like this:

Private Sub Form_Load()

End Sub

Paste the required code in between those lines. Select the menu option
'Debug -> Compile' to make sure all is hunky dory and if so, close the form
and answer 'Yes' to the prompt to save it. Try the form out.

Hope that clears things up.
 
Many Thanks.

Stuart said:
Ok, open your form in design view, open the property sheet if it isn't
showing (Menu option View -> Properties), click on the 'Data' tab and look
at the property 'RowSource'. It should either contain the name of a saved
Query or an SQL statement (eg SELECT * FROM TableName). If it's the name of
a Query, the code you need is the first I suggested, otherwise it's the
second. Paste that code into the Form's OnLoad event procedure. To do this,
click on the property sheet's 'Events' tab, find the property 'On Load' and
click the button on the RHS (with ... as it's caption). Access will open the
Visual Basic editor, open the form's module, and create an event procedure
for you. It'll look like this:

Private Sub Form_Load()

End Sub

Paste the required code in between those lines. Select the menu option
'Debug -> Compile' to make sure all is hunky dory and if so, close the form
and answer 'Yes' to the prompt to save it. Try the form out.

Hope that clears things up.
Click to expand...
 
Back
Top