Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2003
    Posts
    11

    Unanswered: Listing Fields and Tables

    I am trying to set up two list boxes, box 1 listing all of the tables in the
    database, and box 2 listing all of the fields in the table selected in box
    1.

    I have tried to use 'Field List' as RowSourceType, but I can't get it to do
    what I want.

    Does anyone know how to do it, or is there another way of acheiving this, using code perhaps?

    Thank you

    Nick

  2. #2
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    Hi Nick,

    Create your form, and put two Microsoft Forms 2.0 list boxes on it. The access listbox in the toolbox will not work!

    Name one list box "lstbxTables" and the other "lstbxFields".

    Put this code in the module behind the form:
    Code:
    Option Compare Database
    
    Private Sub Form_Open(Cancel As Integer)
       Dim lngTableIndex As Long
       For lngTableIndex = 0 To CurrentDb.TableDefs.Count - 1
          If CurrentDb.TableDefs(lngTableIndex).Attributes = 0 Then
             lstbxTables.AddItem
             lstbxTables.Column(0, lstbxTables.ListCount - 1) = CurrentDb.TableDefs(lngTableIndex).Name
             lstbxTables.Column(1, lstbxTables.ListCount - 1) = CurrentDb.TableDefs(lngTableIndex).Name
             If lstbxTables.ListCount = 1 Then
                lstbxTables.Value = CurrentDb.TableDefs(lngTableIndex).Name
             End If
          End If
       Next
    End Sub
    
    Private Sub lstbxTables_Updated(Code As Integer)
       Dim lngFieldIndex As Long
       While lstbxFields.ListCount > 0
          lstbxFields.RemoveItem 0
       Wend
       If Not IsNull(lstbxTables.Value) Then
          For lngFieldIndex = 0 To CurrentDb.TableDefs(lstbxTables.Value).Fields.Count - 1
             lstbxFields.AddItem
             lstbxFields.Column(0, lstbxFields.ListCount - 1) = CurrentDb.TableDefs(lstbxTables.Value).Fields(lngFieldIndex).Name
             lstbxFields.Column(1, lstbxFields.ListCount - 1) = CurrentDb.TableDefs(lstbxTables.Value).Fields(lngFieldIndex).Name
          Next
       End If
    End Sub
    When you select a table from the list of tables, the listbox of fields will be populated. A sample .mdb is in the attached zip file. Let me know if it works out.

    JT
    Attached Files Attached Files

  3. #3
    Join Date
    Mar 2003
    Location
    London
    Posts
    40
    Very good code.

    One note though:
    Under Access 2000 CurrentDb is unstable. It is advisable to use dbengine(0)(0) instead.

    Regards

  4. #4
    Join Date
    Mar 2003
    Posts
    11
    JT, Thank you very much for the code.

    yk58301, when you say it is unstable in Access 2000, do you know if Access 2002 suffers as well?

    My app is riddled with code using CurrentDb...

    Thanks

    Nick

  5. #5
    Join Date
    Mar 2003
    Location
    London
    Posts
    40
    Originally posted by Nick Holmes
    JT, Thank you very much for the code.

    yk58301, when you say it is unstable in Access 2000, do you know if Access 2002 suffers as well?

    My app is riddled with code using CurrentDb...

    Thanks

    Nick
    Can't say about 2002 for sure. Use global replace CurrentDb -> DbEngine(0)(0)?

  6. #6
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    Your welcome Nick. And thanks for the compliment yk!

    My code is littered with the CurrentDb method too. Many years ago, I changed from DBEngine(0)(0) to CurrentDb, as recommended by Microsoft at the time. I haven't had any problems with it. (from the MSDN Library)
    The CurrentDb Function Versus DBEngine(0)(0)

    Use the CurrentDb method instead of DBEngine(0)(0) to return a Connection object variable that points to the current database. The CurrentDb function creates another instance of the current database, whereas DBEngine(0)(0) refers to the open copy of the current database. Using DBEngine(0)(0) limits your ability to use more than one variable of type Database that refers to the current database.

    The DBEngine(0)(0) syntax is still supported, so Microsoft Access doesn't change your code during the conversion process. However, you should consider making this modification to your code in order to avoid possible conflicts in a multiuser environment.

  7. #7
    Join Date
    Mar 2003
    Location
    London
    Posts
    40
    Originally posted by JTRockville
    Your welcome Nick. And thanks for the compliment yk!

    My code is littered with the CurrentDb method too. Many years ago, I changed from DBEngine(0)(0) to CurrentDb, as recommended by Microsoft at the time. I haven't had any problems with it. (from the MSDN Library)
    Officially yes, Microsoft encourages using CurrentDb instead of dbengine(0)(0). however, I had numerous reports on this being one of the 97 to 2000 conversion problems.

    Additionally I attach the list of other issues encountered when converting to 2000 (Win NT to Win 2K, Office 97 to Office 2K)
    Attached Files Attached Files

  8. #8
    Join Date
    Aug 2003
    Posts
    1

    Post

    this is some great code...heres an update for access 2000. using the actual 2000 tools. create two combo boxes named "cboTables" & "cboFields".

    Private Sub Form_Open(Cancel As Integer)
    Dim lngTableIndex As Long
    'set row source type
    cboTables.RowSourceType = "Value List"
    cboFields.RowSourceType = "Value List"
    For lngTableIndex = 0 To CurrentDb.TableDefs.Count - 1
    If CurrentDb.TableDefs(lngTableIndex).Attributes = 0 Then
    If tbls = "" Then
    tbls = CurrentDb.TableDefs(lngTableIndex).Name
    'default value
    cboTables.Value = tbls
    Else
    tbls = tbls & "; " & CurrentDb.TableDefs(lngTableIndex).Name
    End If
    End If
    Next
    cboTables.RowSource = tbls
    cboTables_AfterUpdate
    End Sub

    Private Sub cboTables_AfterUpdate()
    Dim lngFieldIndex As Long
    'clear field
    cboFields.RowSource = ""
    cboFields = ""
    If Not IsNull(cboTables.Value) Then
    For lngFieldIndex = 0 To CurrentDb.TableDefs(cboTables.Value).Fields.Count - 1
    If flds = "" Then
    flds = CurrentDb.TableDefs(cboTables.Value).Fields(lngFie ldIndex).Name
    'set default
    cboFields.Value = flds
    Else
    flds = flds & "; " & CurrentDb.TableDefs(cboTables.Value).Fields(lngFie ldIndex).Name
    End If
    Next
    End If
    cboFields.RowSource = flds
    End Sub


    thanks for the help!
    ~t

Posting Permissions

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