Refer to a Queries Collection using code

  • Thread starter Thread starter John
  • Start date Start date
J

John

I am able to refer to Forms And Reports using the Containers collection
but I can't refer to a query

strObjectType = "Forms"
Set ctr = dbs.Containers(strObjectType)

The following gives me an error
strObjectType = "Queries"
Set ctr = dbs.Containers(strObjectType)

How can I refer to the Queries?
For that matter, how about macros and modules?
I find the help file on this matter rather confusing.
Any help?
Thanks
 
John said:
I am able to refer to Forms And Reports using the Containers
collection but I can't refer to a query

strObjectType = "Forms"
Set ctr = dbs.Containers(strObjectType)

The following gives me an error
strObjectType = "Queries"
Set ctr = dbs.Containers(strObjectType)

How can I refer to the Queries?
For that matter, how about macros and modules?
I find the help file on this matter rather confusing.
Any help?
Thanks
Click to expand...

Queries and Tables live in the special database collections, QueryDefs
and TableDefs. These aren't containers, and you can't get at them via
the Containers collection. You'd refer to them like this:

Dim qdf As DAO.QueryDef

Set qdf = dbs.QueryDefs("MyQuery")

Macros and Modules *are* stored in containers: macros in the Scripts
container, modules in the Modules container.
 
Thanks Dirk That helps,
But now that I can refer to a query in my database window
how do I refer to it's "Description" property and/or set the description
if it doesn't exist. I've figured out how to do it for Forms & Reports
but...
Again the help file seems rather limited on this issue or
I am not able to understand it. Probably the latter.

Thanks for any help you can offer
 
John said:
Thanks Dirk That helps,
But now that I can refer to a query in my database window
how do I refer to it's "Description" property and/or set the
description if it doesn't exist. I've figured out how to do it for
Forms & Reports but...
Again the help file seems rather limited on this issue or
I am not able to understand it. Probably the latter.
Click to expand...

"Description" is a special property for the QueryDef that may or may not
exist, depending on whether it has been created by Access (or by you)
yet. It can be accessed by name through the querydef's Properties
collection, but unfortunately, if you try that when the Description
property hasn't been created, an error will be raised. You might use
code something like this to deal with that:

'---- start of code ----
Sub DemoReadQueryDefDescription()

On Error GoTo Err_Handler

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strDescription

Set db = CurrentDb
Set qdf = db.QueryDefs("MyQueryName")

strDescription = qdf.Properties("Description")

Debug.Print strDescription
' ... or do whatever you want ...

' ...

Exit_Point:
Exit Sub

Err_Handler:
If Err.Number = 3270 Then
' property not found; don't worry about it
Resume Next
Else
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point
End If

End Sub

'---- end of code ----

If you want to set the property, again you have to trap the error if the
property doesn't exist, but instead of simply ignoring the fact you have
to create the property so that you can go back and set its value.

'---- start of code ----
Sub DemoSetQueryDefDescription()

On Error GoTo Err_Handler

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strDescription

Set db = CurrentDb
Set qdf = db.QueryDefs("MyQueryName")

qdf.Properties("Description") = "This is a querydef I'm testing."

' ...

Exit_Point:
Exit Sub

Err_Handler:
If Err.Number = 3270 Then
' property not found; create it.
qdf.Properties.Append _
qdf.CreateProperty("Description", dbText, " ")
Resume ' Go back and set the desired value.
Else
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point
End If

End Sub

'---- end of code ----

Note: the above is all air code, but between that and the examples in
the help you should be able to do what you want.
 
Thanks so much Dirk
That really helped clarify a few things for me
Obviously I'm still not familiar with all the nuances of
how to refer to different Objects and their properties,
and what constitues a collection, container etc...
And the variances in syntax drive me up a wall sometime.
The most frustrating part is when I don't quite understand a
concept fully, I find the Help file so difficult to decipher.
But I'm learning as I go....

Again, Thanks very much for your help!
 
John said:
Thanks so much Dirk
That really helped clarify a few things for me
Obviously I'm still not familiar with all the nuances of
how to refer to different Objects and their properties,
and what constitues a collection, container etc...
And the variances in syntax drive me up a wall sometime.
Click to expand...

I can't blame you at all. Rest assured, it does start to make sense
after a while.
The most frustrating part is when I don't quite understand a
concept fully, I find the Help file so difficult to decipher.
Click to expand...

And I blame you even less for that!
But I'm learning as I go....
Click to expand...

It seems to me you're doing fine. You'll find these newsgroups a great
resource.
Again, Thanks very much for your help!
Click to expand...

You're very welcome.
 
Back
Top