Access 2000 - ComboBox - QueryDef

[tranchemontaigne]

I am trying to convert some MS Access 2002 code to MS Access 2000. With this code I am trying to use the QueryDefs collection of query names to populate the RowSource property of a combo box.

Within Access 2002 this is done with the following code:

Option Compare Database
Option Explicit

Dim db As Database
Dim qd As QueryDef
'DAO 3.6 reference needs to be enabled to make this code run
'------------------------------------------------------------------

Private Sub Form_Load()
Set db = CurrentDb
LoadQueries
Command1_Click
End Sub

'------------------------------------------------------------------
Sub LoadQueries()
Combo1.SetFocus
Dim i As Integer
If Combo1.ListCount > 1 Then
For i = 0 To Combo1.ListCount - 1
Combo1.RemoveItem (0) '// CODE BREAKS HERE
Next i
End If
Set qd = Nothing
Combo1.AddItem "*NEW*"
For Each qd In db.QueryDefs
Combo1.AddItem qd.Name '// CODE BREAKS HERE
Next
End Sub

'------------------------------------------------------------------
Private Sub Command1_Click()
On Error GoTo cmd1_err
Text1.SetFocus
If qd Is Nothing = False Then qd.SQL = Text1.Text
LoadQueries
cmd1_err:
If Err.Description <> "" Then MsgBox Err.Description
End Sub


Unfortunately Access 2000 is unable to process the RemoveItem and AddItem methods due to application limitations documented within the Office 2000 Visual Basic Programmer's Guide (p 136). The work around appears to entail re-writing the procedure with RowSource and RowSourceType property settings to replace the RemoveItem and AddItem methods within the LoadQueries procedure. This type of code should be added to the LoadQueries procedure, but I am stuck with the RowSource line.

With Me!Combo1
.RowSourceType = "Table/Query"
.RowSource = "SELECT ??? FROM ???" '// THIS IS WHERE I AM STUCK //
.BoundColumn = 1
.ColumnCount = 1
.ColumnWidths = "2.5in"
.ColumnHeads = False
.ListRows = 15
End with

I thought that this might be resolvable by creating a record set object that shows the contents of the QueryDefs collection, or possibly referencing the QueryDefs collection directly. Unfortunately I don't know enough about record set objects or DAO to create the object or bind a recordset object to a RowSource property.

Any help would be greatly appreciated.

 

[flyover789]

you can query the MSysObjects Table:

.RowSource = "select t.Name from MSysObjects t " & _
"where t.Type = 5 and t.Flags = 0;"

("t.Flags = 0" makes shure, that only select-queries are selected)

HTH,
fly

Martin Serra Jr.

http://www.mserrasystems.de

 

[PHV]

And what about something like this ?
Sub LoadQueries()
Dim myList As String
Set qd = Nothing
myList = "*NEW*"
For Each qd In db.QueryDefs
myList = myList & ";" & qd.Name
Next
With Me!Combo1
.BoundColumn = 1
.ColumnCount = 1
.ColumnWidths = "2.5in"
.ColumnHeads = False
.ListRows = 15
.RowSourceType = "Value List"
.RowSource = myList
End with
End Sub
Be aware that you're limited to a 2k (2048) characters list

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244