Help with Combo box to Select reports/queries

[Randy]

I have a cmbo box which I want to select both reports and queries. I am
able to select either queries or reports but not both. I would like to
select both from the same combobox. Here is the code for after update, and
row source...Thanks..Randy

Private Sub Combo69_AfterUpdate()
On Error GoTo Err_Combo69_Click
DoCmd.OpenQuery Me!Combo69
Exit_Combo69_Click:
Exit Sub
Err_Combo69_Click:
Resume Exit_Combo69_Click
End Sub

SELECT [msysobjects].[Name] FROM msysobjects WHERE ((([msysobjects].[Name])
Like "IDR:*")) ORDER BY [msysobjects].[Name];

 

[Nikos Yannacopoulos]

Randy,

Start by populating your combo with a query like:

SELECT Name, Type
FROM MSysObjects
WHERE (Type = 5 OR Type = -32764) AND Name Not Like "~sq*"
ORDER BY Name

This will collent both query and report names, along with each object's
type. This will be a two column combo; you can hide the second if you
wish by setting the column widths property to, say, 2;0. The Column
count property should be 2, and the bound column property should be 1.
Now, in your code, you can use the object type returned by the combo's
second column to differentiate between queries and reports, so you open
the appropriate object type, like:

Private Sub Combo69_AfterUpdate()
On Error GoTo Err_Combo69_Click
Select Case Me.Combo69.Column(1)
Case 5
DoCmd.OpenQuery Me!Combo69
Case -32764
DoCmd.OpenReport Me!Combo69, acViewPreview
Case Is Null
MsgBox "No item selected!"
End Select

Exit_Combo69_Click:
Exit Sub
Err_Combo69_Click:
Resume Exit_Combo69_Click
End Sub

HTH,
Nikos

 

[Randy]

Thank you for your help. It worked.

Randy,

Start by populating your combo with a query like:

SELECT Name, Type
FROM MSysObjects
WHERE (Type = 5 OR Type = -32764) AND Name Not Like "~sq*"
ORDER BY Name

This will collent both query and report names, along with each object's
type. This will be a two column combo; you can hide the second if you wish
by setting the column widths property to, say, 2;0. The Column count
property should be 2, and the bound column property should be 1.
Now, in your code, you can use the object type returned by the combo's
second column to differentiate between queries and reports, so you open
the appropriate object type, like:

Private Sub Combo69_AfterUpdate()
On Error GoTo Err_Combo69_Click
Select Case Me.Combo69.Column(1)
Case 5
DoCmd.OpenQuery Me!Combo69
Case -32764
DoCmd.OpenReport Me!Combo69, acViewPreview
Case Is Null
MsgBox "No item selected!"
End Select

Exit_Combo69_Click:
Exit Sub
Err_Combo69_Click:
Resume Exit_Combo69_Click
End Sub

HTH,
Nikos

I have a cmbo box which I want to select both reports and queries. I am
able to select either queries or reports but not both. I would like to
select both from the same combobox. Here is the code for after update,
and row source...Thanks..Randy

Private Sub Combo69_AfterUpdate()
On Error GoTo Err_Combo69_Click
DoCmd.OpenQuery Me!Combo69
Exit_Combo69_Click:
Exit Sub
Err_Combo69_Click:
Resume Exit_Combo69_Click
End Sub

SELECT [msysobjects].[Name] FROM msysobjects WHERE
((([msysobjects].[Name]) Like "IDR:*")) ORDER BY [msysobjects].[Name];