List of Reports on a combo box(sorted)

[jlocke]

I have used this code and it works perfect, but i have one issue. I want to be able to sort the reports, for some reason this code randomly sorts and ideas?

Private Sub Form_Load()

Dim objAO As AccessObject
Dim objCP As Object
Dim strValues As String

Set objCP = Application.CurrentProject

For Each objAO In objCP.AllReports
strValues = strValues & objAO.Name & ";"
Next objAO

cmbReportNames.RowSourceType = "Value List"
cmbReportNames.RowSource = strValues
End Sub

 

[ghudson]

Use SQL as your record source and "query" the MSysObjects system table. You can then add criteria to the SQL to limit the objects based on a custom prefix. Never try to alter a System table!

This code will display a list of objects in you database in a List box or Combo box:

ALL FORMS:
SELECT [MSysObjects].[Name]
FROM MSysObjects
WHERE (Left([Name],1)<>"~") And ([MSysObjects].[Type])=-32768
ORDER BY [MSysObjects].[Name];

ALL REPORTS:
SELECT [MSysObjects].[Name]
FROM MSysObjects
WHERE (((Left([Name],1))<>"~") AND ((MSysObjects.Type)=-32764))
ORDER BY MSysObjects.Name;

ALL QUERIES:
SELECT [MSysObjects].[Name]
FROM MSysObjects
WHERE (((Left([Name],1))<>"~") AND ((MSysObjects.Type)=5))
ORDER BY MSysObjects.Name;

ALL TABLES [1 = System & Access, 4 = ODBC & 6 = Access]:
SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((Left([Name],1))<>"~") AND ((MSysObjects.Type)=1 Or (MSysObjects.Type)=4 Or (MSysObjects.Type)=6))
ORDER BY MSysObjects.Name;

LINKED TABLES [4 = ODBC, 6 = Access]:
SELECT [MSysObjects].[Name]
FROM MSysObjects
WHERE Left([Name],1) <> "~" AND (MSysObjects.Type = 4 OR MSysObjects.Type = 6)
ORDER BY MSysObjects.Name;

ALL MODULES [forms, macros, modules]:
SELECT [MSysObjects].[Name]
FROM MSysObjects
WHERE (((Left([Name],1))<>"~") AND ((MSysObjects.Type)=-32761 Or (MSysObjects.Type)=3))
ORDER BY MSysObjects.Name;

PUBLIC MODULES
SELECT [MSysObjects].[Name]
FROM MSysObjects
WHERE (((Left([Name],1))<>"~") AND ((MSysObjects.Type)=-32761))
ORDER BY MSysObjects.Name;

 

[RoyVidar]

Here's the Microsoft reply, with a sorting solution (it's also demonstrating usage of callback function)

http://support.microsoft.com/default.aspx?scid=kb;en-us;275563

There are different views on usage of the system tables, see the warning by Microsoft under "Method 2", but most seem to ignore the warning, and use the system tables anyway. Note however that it will probably not work if you switch to ADP.

 

[jlocke]

Thank you both

For the time being i will use the easiest solution just to get this done. but will work on the better solution offered by RoyVidar. This Access forum is awesome!

 

[modest]

...will work on the better solution offered by RoyVidar...

I wouldn't necessarily call it better. I believe resorting an array is actually slower than running a select query. I'm not positive on this, but I would prefer the query (especially since that is what I do).

 

[Mile-O]

ALL TABLES [1 = System & Access, 4 = ODBC & 6 = Access]:
SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((Left([Name],1))<>"~") AND ((MSysObjects.Type)=1 Or (MSysObjects.Type)=4 Or (MSysObjects.Type)=6))
ORDER BY MSysObjects.Name;

A slight change.

ALL TABLES [1 = System & Access, 4 = ODBC & 6 = Access]:
SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((Left([Name]),1)<>"~")) AND ((MSysObjects.Type) In (1, 4, 6)))

 

[ghudson]

A slight change.

ALL TABLES [1 = System & Access, 4 = ODBC & 6 = Access]:
SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((Left([Name]),1)<>"~")) AND ((MSysObjects.Type) In (1, 4, 6)))

This works using your IN() but your example had a misplaced parenthesis. Thanks!

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((Left([Name],1))<>"~") AND ((MSysObjects.Type) In (1,4,6)))
ORDER BY MSysObjects.Name;

 

[Mile-O]

This works using your IN() but your example had a misplaced parenthesis.

I knew it!

I don't like to use excess brackets but thought I would for that post.

 

[gpurger]

Just found your thread and it seems to do what I want BUT I do not understand it.
I want to list all queries in my database into a combobox then select one to be the record sourse for a form.

Tried the following but got the 'Object does not support this property of method' error
Private Sub Form_Load()

Dim objAO As AccessObject
Dim objCP As Object
Dim strValues As String


Set objCP = Application.CurrentProject

For Each objAO In objCP.AllQueries
strValues = strValues & objAO.Name & ";"
Next objAO

Combo0.RowSourceType = "Value List"
Combo0.RowSource = strValues
End Sub

The above I understand a little but the SQL code Aghh!
Cheers.

 

[Mile-O]

The above I understand a little but the SQL code Aghh!

Forget all that code. Just go to your combo's RowSourceType property and set it to Table/Query. And then copy the following, as ghudson posted, verbatim into the RowSource property:

SELECT [MSysObjects].[Name]
FROM MSysObjects
WHERE (((Left([Name],1))<>"~") AND ((MSysObjects.Type)=5))
ORDER BY MSysObjects.Name;

I don't know why you would want to set the RecordSource of a form from a selection though... Bad design, maybe?

 

[gpurger]

Bad Design I agree, and not mine but it is too large to change and I have been given a short time to try to solve a problem. The original designer is still with use but too busy.
Thanks for your help.
Gordon