DAO QueryDefs collection - weird result 1

[MakeItSo]

Hi guys,

this is surely a very simple mistake of mine; but I haven't coded in Access for a loong time and don't know why it behaves like this. I've forgotten oh so much...


What I want: populate a combobox with all Queries contained in the current database.

How I tried (and failed) using DAO:

Dim q as QueryDef

For Each q in CurrentDB.QueryDefs
   cbo_select.AddItem q.Name
Next q

Simple, but wrong. I do get my Queries, but I also get duplicates of them including "~queryname" etc.
It seems they are temporary files of the queries, since they occur whenever I access said query.

For example: I open one query, alter values and save it as a new query.
If I execute my code now, I'll get four entries - two for my old query and two for the new one.

Why?

And how do I need to proceed correctly?

Thanks a lot!
MiS

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[dhookom]

You could try the Row Source type of Table/Query and a Row Source of:

SELECT msysobjects.Name
FROM msysobjects
WHERE (((msysobjects.Name) Not Like "~*") AND ((msysobjects.Type)=-32764))
ORDER BY msysobjects.Name;

Duane
Hook'D on Access
MS Access MVP

 

[MakeItSo]

Thanks Duane,

I have added a

If left(q.Name,1)<>"~" Then

to fix that temporary files issue.
Guess where the double entries came from?
==>
...
...
I had the code in Form_Current, so the queries were also added when focus was returned to the form.
I added a quick loop before the one initiating the combo box to remove all entries before adding the query names...

Who got that beautiful sig line here?
=>You can't fix stupid...


[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell