Sort order in dynamic combo box 4

[LHWC]

I have a combo box that lists all tables on form load. How do I have the tables listed alphabetically?

 

[mph1]

If your combo box is based on a query then, in the design view of the query make the Sort Order Ascending.


HTH

An investment in knowledge always pays the best dividends.
by Benjamin Franklin
Autonumber Description - FAQ702-5106

 

[LHWC]

The combo box is built using this code in the form's Form_Load event:

Public Sub Form_Load()

Dim db As Database
Dim tbl As TableDef
Dim rpt As AccessObject
Dim tda As VbFileAttribute
Dim objCP As Object
Dim strTables As String
Dim strReports As String

Set db = CurrentDb()
Set objCP = Application.CurrentProject

For Each tbl In db.TableDefs
tbl.Attributes = vbNormal
strTables = strTables & tbl.Name & ";"
Next tbl

cbxJobs.RowSourceType = "Value List"
cbxJobs.RowSource = strTables

For Each rpt In objCP.AllReports
strReports = strReports & rpt.Name & ";"
Next rpt

cbxCutSheet.RowSourceType = "Value List"
cbxCutSheet.RowSource = strReports
End Sub

 

[RoyVidar]

Oups - to sort that, you'd probably have to load them into an array and sort, but, couldn't you just use queries on MSysObjects as rowsource of the combos?

For the tables:
[tt]Select Name FROM msysObjects WHERE (Type = 1 or Type = 6) and left(name,3)<>"msy" order by Name[/tt]

type=1 native tables
type=6 linked tables

For the reports:
[tt]Select Name FROM msysObjects WHERE Type = -32764 order by Name[/tt]

- change the rowsource of the combos to table/query - or perhaps open recordset based upon them and add the items

Roy-Vidar

 

[scriverb]

RoyVidar: That is going to be something useful to many here at TT. Have a Star!!

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[RoyVidar]

Thank you very much, scriverb!

To deserve it, some more Types:
[tt]
-32768 Forms
-32766 Macro
-32761 Modules
4 Linked tables (MSDE - same as SQL server?)
5 Queries (check for left$(name,1)<>"~"[/tt]

Roy-Vidar

 

[TheAceMan1]

Roy! . . . . .

[blue]Beautiful![/blue] . . . . . [purple]Absolutely Beautiful![/purple] . . . . . [blue]Extraordinary Technique![/blue]

Thats certainly one for the books here. I never would have realized. I hope others pick this up!

I wish I could give ya more than one star!

See Ya! . . . . . .

 

[mph1]

Hi Roy,

What you have spelled out in detail is what I was alluding to. However, what you have indicated is excellent. Two other points to mention:

1. The numbers indicated are located in the field Type

2. If MySysObjects does not appear, which could frustrate some if they don't see the table, you can go to:

Tools > Options. In the View tab under Show put a check mark in the System Objects check box. This will also show some other hidden tables.

Since it was pointed out that what you have indicated can be " That is going to be something useful to many here at TT." What I have suggested is available in Access 2000, but am not sure of other versions.

Hope this can be of some help to others.

Maurie

An investment in knowledge always pays the best dividends.
by Benjamin Franklin
Autonumber Description - FAQ702-5106

 

[randy700]

I agree that this is valuable information and I'll have many uses for it in the future. However, I copied and ran LHWC's code and it works perfectly as written -- my tables get put into the combo box in alphabetical order. Of course, the system tables are included. This slight modification provides the desired list...

Change this...
For Each tbl In db.TableDefs
tbl.Attributes = vbNormal
strTables = strTables & tbl.Name & ";"
Next tbl

to this...
For Each tbl in db.TableDefs
If Left(tbl.Name,3) <> "msys" Then
tbl.Attributes = vbNormal
strTables = strTables & tbl.name & ";"
End If
Next tbl

Randy

 

[LHWC]

Thanks to all!
I inserted the If statement (change the 3 to a 4) suggested by Randy, and it does eliminate system tables, but a temp table still shows in the list. I thought the "tbl.Attributes = vbNormal" line would prevent that, but no.
I also added:
If (Left(tbl.Name,4) <> "msys" Or (Left(tbl.Name,1) <> "~")Then
to no avail. Anyone have an idea to remove the temp tables from the list?

 

[RoyVidar]

Hi again!

Must confess to not having the foggiest on DAO (except I do a little form recordset manipulation from time to time), but, in the table loop, you try to set the attributes of the table to vbNormal (0), shouldn't you rather check for vbNormal?

[tt]For Each tbl In db.TableDefs
If tbl.Attributes = vbNormal Then
strTables = strTables & tbl.Name & ";"
End If
Next tbl[/tt]

On a very simple test here, the attribute values for native tables where 0, linked tables 1073741824, linked to MSDE were 536870912, the systables -2147483648 and MSysAccessobjects was 2.

Don't know about the temptable, is that some temptable you've created, is it system created... Try using a

[tt]Debug.Print tbl.name, tbl.attributes[/tt]

within the loop to check if there's something you can use as condition to avoid having it entering the list. BTW - did the query on MSysObjects also show the temptable?

Thanx mph1 for pointing out how to find the system tables. It seems the types are consistent 97 thru xp, which are the versions I'm able to test at the moment.

Roy-Vidar

 

[scriverb]

The following SQL details Roy's postings above in a single query for reference purposes. All of the possibilities are included. This code could be modified at anytime to select just a specific object type. The rows are sorted in the following order: tables, linked tables, MSDE linked tables, queries, forms, reports, and modules.

SELECT msysObjects.Name, Switch([Type]=1,"Table",[Type]=5,"Query",[Type]=4,"MSDE Linked Table",[Type]=-32764,"Report",[Type]=-32768,"Form",[Type]=-32761,"Module",[Type]=6,"Linked Table") AS Description, msysObjects.Type
FROM msysObjects
WHERE (((msysObjects.Type)=-32764 Or (msysObjects.Type)=-32768 Or (msysObjects.Type)=-32761 Or (msysObjects.Type)=4)) OR (((msysObjects.Type)=5) AND ((Left$([name],1))<>"~")) OR (((msysObjects.Type)=1 Or (msysObjects.Type)=6) AND ((Left([name],3))<>"msy"))
ORDER BY Switch([Type]=1,1,[Type]=5,4,[Type]=4,3,[Type]=-32764,6,[Type]=-32768,5,[Type]=-32761,7,[Type]=6,2), msysObjects.Name;

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[LHWC]

Hi,
I've been away for a week, but here is where I am currently.
Public Sub Form_Load()

Dim db As Database
Dim tbl As TableDef
Dim rpt As AccessObject
Dim objCP As Object
Dim strTables As String
Dim strReports As String

Set db = CurrentDb()
Set objCP = Application.CurrentProject
strDefault = "Unit Master"

For Each tbl In db.TableDefs
If Left(tbl.Name, 4) <> "MSys" Then
If Left(tbl.Name, 4) <> "~TMP" Then
strTables = strTables & tbl.Name & ";"
End If
End If
Next tbl

cbxJobs.RowSourceType = "Value List"
cbxJobs.RowSource = strTables

For Each rpt In objCP.AllReports
strReports = strReports & rpt.Name & ";"
Next rpt

cbxCutSheet.RowSourceType = "Value List"
cbxCutSheet.RowSource = strReports
End Sub

For the tables: cbx lists all tables, except system and temp, alphabetically. Tried Or in the If statement. Didn't work.
For the reports: All are listed, but not alphabetically. They seem to be in the order created.
Is there a line of code I can add to accomplish alphabetical sort order?
Thanks to all.