Sort a table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How can I sort a table in VBA code on a text field, say [FIELDA] in [Table1]?
This table is not linked. I would like to put this code in a function. Could
someone please post an example with a connection to the table?

Thanks much in advance.
 
If you want to open it up as a record source this is how it would look in DAO
a few posts down is how it would look in a report.

Dim rstc As DAO.Recordset,dbs As Database, sqlstr As String
Set dbs = CurrentDb()
sqlstr = "SELECT
.* FROM
ORDER BY
.[field];"

Set rstc = dbs.OpenRecordset(sqlstr, dbOpenDynaset)
.... (do something like cycle thru records and edit something)
rstc.close
set dbs=nothing
.... (finish up)

HTH
Martin J
 
How can I sort a table in VBA code on a text field, say [FIELDA] in [Table1]?
This table is not linked. I would like to put this code in a function. Could
someone please post an example with a connection to the table?
Click to expand...

You cannot and should not try to sort a Table. Instead, open a Query
based on the table, sorting by the field:

SELECT * FROM Table1
ORDER BY FIELDA;

This Query can be used in any manner that one would use a Table.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Wow. So it is not programmatically possible to sort a table? Even for
appearance to satisfy a user who wants to see the data in a particular
order???

There even happens to be sort buttons that allow you to manually sort a table.
Access is even more limited than I originally thought.

John Vinson said:
How can I sort a table in VBA code on a text field, say [FIELDA] in [Table1]?
This table is not linked. I would like to put this code in a function. Could
someone please post an example with a connection to the table?
Click to expand...

You cannot and should not try to sort a Table. Instead, open a Query
based on the table, sorting by the field:

SELECT * FROM Table1
ORDER BY FIELDA;

This Query can be used in any manner that one would use a Table.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
HOW TO SORT AN ACCESS TABLE:

'Open the table
DoCmd.OpenTable "TableName", acViewNormal, acEdit

'If the sort field is not the first one, tab to the column desired
'SendKeys "{TAB}{TAB}", True

'Execute the 'BUILT-IN' table sort command
Application.CommandBars.FindControl(ID:=210).Execute

'Optionally close and optionally save the design change
DoCmd.Close acTable, "TableName", acSaveYes


quartz said:
Wow. So it is not programmatically possible to sort a table? Even for
appearance to satisfy a user who wants to see the data in a particular
order???

There even happens to be sort buttons that allow you to manually sort a table.
Access is even more limited than I originally thought.

John Vinson said:
How can I sort a table in VBA code on a text field, say [FIELDA] in [Table1]?
This table is not linked. I would like to put this code in a function. Could
someone please post an example with a connection to the table?
Click to expand...

You cannot and should not try to sort a Table. Instead, open a Query
based on the table, sorting by the field:

SELECT * FROM Table1
ORDER BY FIELDA;

This Query can be used in any manner that one would use a Table.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
Click to expand...
 
Wow. So it is not programmatically possible to sort a table? Even for
appearance to satisfy a user who wants to see the data in a particular
order???
Click to expand...

Yes, it is quite possible. It is also A VERY BAD IDEA. The method used
to sort a Table actually constructs a concealed Query; it does NOT
affect the order of records stored on disk.

In general, users should never need to even *see* table datasheets.
Table datasheets are NOT very useful as data presentation or editing
tools; they are best reserved for debugging and development. Give your
users a Form (a Datasheet view form if they like that), based on a
Query, wherein they can control the sorting using the form tools.
There even happens to be sort buttons that allow you to manually sort a table.
Access is even more limited than I originally thought.
Click to expand...

Or perhaps the way you're approaching the use of Access is what's
limited.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Better Method (NOTE: This can only be accomplished in DAO):

ADD THE SORT:

Private Sub TEST_Add_Order_By_Property_For_Table_Sorting_TEST()

'CHANGES THE VIEW OF THE TABLE WHEN OPENED TO APPEAR SORTED;
'RECORDS ARE NOT ACTUALLY RE-ORDERED; SHOULD BE REMOVED IF
'TABLE IS EVER JOINED, OR IF A QUERY OR REPORT IS EVER BUILT ON THE TABLE;

Dim strTableName As String
Dim varSetting As Variant

strTableName = "TEMPTABLE2"
varSetting = "TEMPTABLE2.ACCTNO"
Call TablePropertyAddOrderBy(strTableName, varSetting)

End Sub

Public Function TablePropertyAddOrderBy(argTableName As String, argSetting
As Variant)

'ADD "ORDER BY" PROPERTY TO A TABLE TO MAKE RECORDS APPEAR SORTED WHEN IT IS
OPENED;
Const intNoProperty As Integer = 3270
Dim dbs As Database
Dim tdf As TableDef
Dim objPrp As Object
Dim varSetting As String

On Error GoTo XADDPROPERTY
Set dbs = Application.CurrentDb
Set tdf = dbs.TableDefs(argTableName)
tdf.Properties("OrderBy") = argSetting
Exit Function
XADDPROPERTY:
If Err.Number = intNoProperty Then
Set objPrp = tdf.CreateProperty("OrderBy", dbText, argSetting)
tdf.Properties.Append objPrp
Resume Next
End If

End Function

REMOVE THE SORT:

Private Sub TEST_Remove_Custom_Property_TEST()

'TEST SUB TO REMOVE A PROPERTY FROM A TABLE;
Dim strTableName As String
Dim strPropertyName As String

strTableName = "TEMPTABLE2"
strPropertyName = "ORDERBY"
Call TablePropertyDelete(strTableName, strPropertyName)

End Sub


Public Function TablePropertyDelete(argTableName As String, argPropertyName
As String)

'REMOVES A CUSTOM PROPERTY FROM A TABLE;
Dim db As Database
Dim td As TableDef
Set db = Application.CurrentDb
Set td = db.TableDefs(argTableName)
td.Properties.Delete argPropertyName

End Function


quartz said:
HOW TO SORT AN ACCESS TABLE:

'Open the table
DoCmd.OpenTable "TableName", acViewNormal, acEdit

'If the sort field is not the first one, tab to the column desired
'SendKeys "{TAB}{TAB}", True

'Execute the 'BUILT-IN' table sort command
Application.CommandBars.FindControl(ID:=210).Execute

'Optionally close and optionally save the design change
DoCmd.Close acTable, "TableName", acSaveYes


quartz said:
Wow. So it is not programmatically possible to sort a table? Even for
appearance to satisfy a user who wants to see the data in a particular
order???

There even happens to be sort buttons that allow you to manually sort a table.
Access is even more limited than I originally thought.

John Vinson said:
On Mon, 13 Dec 2004 13:07:13 -0800, quartz

How can I sort a table in VBA code on a text field, say [FIELDA] in [Table1]?
This table is not linked. I would like to put this code in a function. Could
someone please post an example with a connection to the table?

You cannot and should not try to sort a Table. Instead, open a Query
based on the table, sorting by the field:

SELECT * FROM Table1
ORDER BY FIELDA;

This Query can be used in any manner that one would use a Table.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
Click to expand...
Click to expand...
 
Back
Top