Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Mar 2010
    Location
    UK
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Good Morning Loungers,

    Doing a little work, trying to set a lst box (lstColumn) to display the fields of a table, (while this can be done easier, its necessary to do it this way for the next step)

    The list boxes contents will depend on what is selected on a combo box (cmbtable), and this code is on the after update event of that combo box.

    Code:
    Private Sub cmbtable_AfterUpdate()
    Dim db As DAO.Database
    Dim fld As DAO.Field
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset
    
    Set db = CurrentDb
    Set qdf = db.QueryDefs(Me.cmbtable.column(1))
    Set rst = db.OpenRecordset("tblQueryStructure", dbOpenDynaset)
    
    For Each fld In qdf.Fields
            With rst
                .AddNew
                !FieldName = fld.Name
                
                '!FieldType = stdsGetQryFieldType(qdf, fld.Name)
                'commented out as it calls a function we dont have.
                
                If fld.Size = 0 Or fld.Size > 35 Then
                    !FieldSize = 35
                    !UserFieldSize = 35
                Else
                    !FieldSize = fld.Size
                    !UserFieldSize = fld.Size
                End If
        
                If !FieldType = "Number" Then
                    !DataAlignment = "Right"
                    !HeaderAlignment = "Right"
                Else
                    !DataAlignment = "Left"
                    !HeaderAlignment = "Left"
                End If
                
                ![Output] = True
                  End With
                  
        Next fld
        rst.Close
        Set rst = Nothing
        Set dbs = Nothing
        
        Me.lstcolumn.Requery
        
                
    
    End Sub
    The bit that is (hopfully) in yellow, is where i get the runtime error 3265 "Item not found in this collection" I really cant see the problem for the life of me, and i was hoping someone could highlight the obvious.

    thanks.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    It does not think that the value in column(1) (which is really the 2nd column) of Me.cmbtable is the name of an existing query.

    Put in debug.print Me.cmbtable. column(1) before that line to see what is there.
    Regards
    John



  3. #3
    Lounger
    Join Date
    Mar 2010
    Location
    UK
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I forgot that columns and arrays etc start at 0, i altered the code. so its me.cmbtable.column(0) and still no joy, i put in your bit of code, and it didnt seem to do anything. but the toold tip when i hover over me.cmbtable.column(0) it is now actually a value from the list! but it still cant get past that bit of code, which means qdf = nothing.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Debug.print writes to the Immediate Window. To see its output, you need to press CTRL + G in the VBE.
    Another way to see the value is to use msgbox Me.cmbtable. column(1)

    You can't get past that line until you have the name of an existing query.

    Are you sure that the combo has a list of valid query names?
    Regards
    John



  5. #5
    Lounger
    Join Date
    Mar 2010
    Location
    UK
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts
    your last line summed it up John,

    I was using table names, which i wanted. but i didnt even realise there was such a thing as a table def! hours of googling and one smug collegue later, and i have the answer.

    Thank you for your help.

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Glad you sorted it.

    When I looked at your original post, I wondered how a list of queries related to the task said you were doing...I suppose I should have asked at that point.
    Regards
    John



Posting Permissions

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