Combo box with only select queries

[Sara Sun]

I am trying to create a combo box (in my form) to list the queries in my
access database
i am using the row source as

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

This code worked but this code picks ups all the delete query, update
queries etc.
I am trying to use only select queries & How to restrict this coding to only
the select queries ??

Thanks
Sara Sun

 

[Barry Gilbert]

Sara,
try this:

Dim qryThis As QueryDef
Me.MyCombo.RowSource = ""
Me.MyCombo.RowSourceType = "Value List"
For Each qryThis In CurrentDb.QueryDefs
If Left$(qryThis.SQL, 6) = "SELECT" Then
Me.MyCombo.RowSource = Me.MyCombo.RowSource & qryThis.Name & ";"
End If
Next

Barry

I am trying to create a combo box (in my form) to list the queries in my
access database
i am using the row source as

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

This code worked but this code picks ups all the delete query, update
queries etc.
I am trying to use only select queries & How to restrict this coding to only
the select queries ??

Thanks
Sara Sun

 

[Klatuu]

Very close, but it will also pick up Make Table Queries:

SELECT foobah, putzo, frobish INTO tbl qwirky...

Then, even if you filter out INTO, that string value could possibly turn up
in a real SELECT query.

Sara,
try this:

Dim qryThis As QueryDef
Me.MyCombo.RowSource = ""
Me.MyCombo.RowSourceType = "Value List"
For Each qryThis In CurrentDb.QueryDefs
If Left$(qryThis.SQL, 6) = "SELECT" Then
Me.MyCombo.RowSource = Me.MyCombo.RowSource & qryThis.Name & ";"
End If
Next

Barry

I am trying to create a combo box (in my form) to list the queries in my
access database
i am using the row source as

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

This code worked but this code picks ups all the delete query, update
queries etc.
I am trying to use only select queries & How to restrict this coding to only
the select queries ??

Thanks
Sara Sun

 

[Marshall Barton]

I am trying to create a combo box (in my form) to list the queries in my
access database
i am using the row source as

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

This code worked but this code picks ups all the delete query, update
queries etc.
I am trying to use only select queries & How to restrict this coding to only
the select queries ??

Try this kind of criteria:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE MSysObjects.Type=5 AND Left([Name],1)<>"~"
AND MSysObjects.Flags IN (0,16,128)

 

[Barry Gilbert]

The gauntlet has been thrown. How about this?

Dim qryThis As QueryDef
Dim strFlag As String
Me.MyCombo.RowSource = ""
Me.MyCombo.RowSourceType = "Value List"
For Each qryThis In CurrentDb.QueryDefs
strFlag = DLookup("flags", "msysobjects", "Name = '" & qryThis.Name
& "'")
If Left$(qryThis.SQL, 6) = "SELECT" And strFlag = "80" Then
Me.MyCombo.RowSource = Me.MyCombo.RowSource & qryThis.Name & ";"
End If
Next

Very close, but it will also pick up Make Table Queries:

SELECT foobah, putzo, frobish INTO tbl qwirky...

Then, even if you filter out INTO, that string value could possibly turn up
in a real SELECT query.

Sara,
try this:

Dim qryThis As QueryDef
Me.MyCombo.RowSource = ""
Me.MyCombo.RowSourceType = "Value List"
For Each qryThis In CurrentDb.QueryDefs
If Left$(qryThis.SQL, 6) = "SELECT" Then
Me.MyCombo.RowSource = Me.MyCombo.RowSource & qryThis.Name & ";"
End If
Next

Barry

I am trying to create a combo box (in my form) to list the queries in my
access database
i am using the row source as
SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=5) AND ((Left([Name],1))<>"~"))
This code worked but this code picks ups all the delete query, update
queries etc.
I am trying to use only select queries & How to restrict this coding to only
the select queries ??

Thanks
Sara Sun

 

[Klatuu]

That still did not get it. See Marshall Barton's response. It does it
except that Left([Name],1)<>"~" turns out to be unnecessary, the Flags value
for them is 3.

I learned something today!
(now can I go home?)

The gauntlet has been thrown. How about this?

Dim qryThis As QueryDef
Dim strFlag As String
Me.MyCombo.RowSource = ""
Me.MyCombo.RowSourceType = "Value List"
For Each qryThis In CurrentDb.QueryDefs
strFlag = DLookup("flags", "msysobjects", "Name = '" & qryThis.Name
& "'")
If Left$(qryThis.SQL, 6) = "SELECT" And strFlag = "80" Then
Me.MyCombo.RowSource = Me.MyCombo.RowSource & qryThis.Name & ";"
End If
Next

Very close, but it will also pick up Make Table Queries:

SELECT foobah, putzo, frobish INTO tbl qwirky...

Then, even if you filter out INTO, that string value could possibly turn up
in a real SELECT query.

Sara,
try this:

Dim qryThis As QueryDef
Me.MyCombo.RowSource = ""
Me.MyCombo.RowSourceType = "Value List"
For Each qryThis In CurrentDb.QueryDefs
If Left$(qryThis.SQL, 6) = "SELECT" Then
Me.MyCombo.RowSource = Me.MyCombo.RowSource & qryThis.Name & ";"
End If
Next

Barry


:

I am trying to create a combo box (in my form) to list the queries in my
access database
i am using the row source as
SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=5) AND ((Left([Name],1))<>"~"))
This code worked but this code picks ups all the delete query, update
queries etc.
I am trying to use only select queries & How to restrict this coding to only
the select queries ??

Thanks
Sara Sun