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

    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
    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 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
    Location
    MI
    Posts
    3,713
    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
  •