Populating combo boxes 1

[JR2913]

I have two combo boxes on a form. The first box I want to populate with names of the tables in the database. The second combo box I want to populate with names of fields within the selected table.

I know how to generate the list of tables, and the fields within them, but how do I get these into the combo box as the data source?

Many thanks

John R

 

[scriverb]

The combobox RowSource is the property where you put either a table, saved query name, SQL, List etc to populate the combobox. In addtion the properties Bound Column, Number of Columns, column widths must be appropriatly entered to get the desired effect.

Post back if you need more assistance beyond this information.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[afryer]

Hello,

Try this....

dim l_counter as integer
dim l_str_sql as string
dim l_table_name as string
dim l_field_count as integer
dim l_sub_loop as integer
dim l_field_name as string

for l_counter = 1 to currentdb.tabledefs.count
l_table_name = currentdb.tabledefs (l_counter).name
combo_table_names.additem (l_table_name)
l_field_count = currentdb.tabledefs (l_counter).fields.count
for l_sub_loop = 1 to l_field_count
l_field_name = currentdb.tabledefs(l_counter).fields(l_sub_loop).name
combo_fields.additem (l_field_name)
next l_sub_loop

next l_counter


Syntax may not be 100% as I have not tested it, but this will work!

Andrew

 

[JR2913]

Thanks Guys for the help. I used Andrew's basic structure to solve the problem, but the second combo box content was dependent on the selection made in the first, so devised two procedures, as follows:

Private Sub Form_Open(Cancel As Integer)

      Dim counter As Integer
      Dim table As String

      For counter = 0 To CurrentDb.TableDefs.count - 1
          table = CurrentDb.TableDefs(counter).Name
          Me!cmbTables.AddItem (table)
      Next

End Sub

Private Sub cmbTables_AfterUpdate()
      Dim tablename As String
      Dim counter As Integer
      Dim fieldname As String
      Dim fcount As Integer
    
  
      tablename = CurrentDb.TableDefs(Me!cmbTables.Value).Name
      fcount = CurrentDb.TableDefs(Me!cmbTables.Value).Fields.count
      Me!cmbFields.ListRows = fcount
      For counter = 0 To fcount - 1
           fieldname = CurrentDb.TableDefs(tablename).Fields(counter).Name
           Me!cmbFields.AddItem (fieldname)
      Next
End Sub

Interestingly, if I set the counter to 1 each case, it ignored the first table or field in the set, and if I didn't include the -1 whilst setting the counter it fell over on the last table or field name. Any ideas?

The whole purpose for doing this was to allow users to search for duplicate entries in any field of over 600 tables. This has generated another interesting problem in terms of SQL, but I'll post this elsewhere.

John R.

 

[Halfgreek]

I'm also dealing with a similar problem. Any help would be appreciated.