Combo Box to return all queries in database.

[Grace]

I developed a query that returns all queries in my database.- eg.



SELECT Name, Type

FROM MSysObjects

WHERE (Type = 5 = -32764)

ORDER BY Name



What I want to do is create a combobox in a form that populates all queries
in my database based on above query. I created code in After Update Events
of Combo69 as noted below. Everything compiles OK; but when going to the
combo box on the form, it doesn't work. The combo field remains blank.

What am I doing wrong? Any assistance is most appreciated.



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

 

[Douglas J. Steele]

That should be

WHERE (Type = 5)

You're comparing 5 to -32764, which obviously isn't true, so you're ending
up with Type = 0

 

[Bob Hairgrove]

I developed a query that returns all queries in my database.- eg.

SELECT Name, Type
FROM MSysObjects
WHERE (Type = 5 = -32764)
ORDER BY Name

Both words "Name" and "Type" are reserved words ... you should put them inside
brackets in order to use them as object names, i.e.:

SELECT [Name], [Type]
FROM MSysObjects
'etc.

WHERE (Type = 5 = -32764)

What is this supposed to mean? There are two ways that the query interpreter
could see this (brackets added where needed):

(1) WHERE ([Type] = (5 = -32764))
- or -
(2) WHERE (([Type] = 5) = -32764)

The first criteria can be reduced to the following:

(1) WHERE ([Type] = False)

Since "False" is a constant in Access which evaluates to 0, this WHERE clause
would only find rows where the [Type] field contains 0 (presumably, thereare
none)...

The second criteria evaluation would always be false, because for rows where
[Type]=5, the innermost expression would evaluate to True which is equivalent to
the value -1 in Access. If [Type]<>5, it evaluates to 0 (i.e. False). Since both
(-1 = -32764) and (0 = -32764) evaluate to False, that parsing of theWHERE
criteria will return no rows.

Therefore, it is not surprising that your query returns no rows.

What I want to do is create a combobox in a form that populates all queries
in my database based on above query. I created code in After Update Events
of Combo69 as noted below. Everything compiles OK; but when going to the
combo box on the form, it doesn't work. The combo field remains blank.

You need to set the RowSource property of the combo to your query. But first,
you need to fix the query.

The AfterUpdate event fires only after a selection in the combo has been made.
You probably don't need any code in the AfterUpdate event at all.

 

[Grace]

Thanks all for most helpful comments. All is running smoothly now.