Results 1 to 2 of 2
  1. #1
    Join Date
    May 2004

    Unanswered: QueryDef - Combo Box Row Source

    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 that exist within the database and use that collection 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
    End Sub

    Sub LoadQueries()
    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
    End Sub

    Private Sub Command1_Click()
    On Error GoTo cmd1_err
    If qd Is Nothing = False Then qd.SQL = Text1.Text
    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 possibley 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.

  2. #2
    Join Date
    Sep 2003
    The RemoveItem method is not supported under Access 2000 (as you found out). To clear just reset the RowSource to "" ...

    Since this looks like a Value List combo just concat the query names together with a separating semi-colon ...

    P.S. - AddItem is not supported either ...
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts