List tables in a form

[RobJDB]

Is it possible to populate a drop-down list on a form with the names of all the tables in the current database?
(Access 2000)
Basically, I want a user to choose a table, and then I'm going to run a load of code on that table to compress it to half the number of records with twice the number of fields.
I am new to Access, having previously done all my data manipulation via ASP, so any help would be appreciated. I'm finding Access quite monstrous, to be honest.
Thanks, Rob

 

[PaulF]

probably the easiest way is to query the MsysObjects Table, use this as the Row Source for the ComboBox

Select [name] From msysobjects where mid([name],2,3)<>'sys' and type = 1 or mid([name],2,3)<>'sys' and type = 6


Type 1 = Local tables, Type 6 = Linked tables
eliminating those with &quot;sys&quot; as the 2,3,4 letters of the table name will prevent displaying Access System Tables in the ComboBox

PaulF

 

[RobJDB]

It worked immediately! Thanks for that.
What about listing the fields within a given table in another combo box that updates itself when a table is selected from the first combo box?

Rob

 

[bkclaw113]

Public Sub test()
Dim dbs As Database
Dim i As Integer

' Return reference to current database.
Set dbs = CurrentDb

For i = 1 To dbs.TableDefs.Count
MsgBox dbs.TableDefs(i - 1).Name
Next i

End Sub

This shows you how to get the names of the tables, additional information about each table is contained in each TableDefs(i). If you just need the name use the routine to get the names then create a semi colon delimited list, or populate the names into a table. Once you have the names in the list or table you can reference that as the control source for the drop down list box.

 

[RobJDB]

Thanks for that.
As I understand it now, my user will select a table from a combo box.
This combo box will have an 'onChange' or similar behaviour attached that will call the above sub from within a module.
This sub (somewhat modified to list field names) will populate a table or a list, which will then feed the second combo box.
Could you please expand on how to create this semicolon delimited list? Is it just a string built up in a loop, like:
'loop start
strList = strList & &quot;;&quot; & strFldName(i)
'next
If so, that's no problem.
But then how does a combo box on a form read that string and use it to fill itself up?

 

[PaulF]

You set the Row Source Type for the ComboBox that will display the Fields to Field List, then in the AfterUpdate Event for the ComboBox with the Table Names you add code to change the Row Source for the Field List Combo Box to the Table Selected.
This code uses names cboTable for the ComboBox with Table Names, and cboFields for the ComboBox with the Field List

Private Sub cboTable_AfterUpdate()
If cboTable <> &quot;&quot; And cboTable <> &quot; &quot; Then
cboFields.RowSource = cboTable
Me.Refresh
End If
End Sub

PaulF

 

[RobJDB]

Excellent! That's that implemented already. Access doesn't seem so bad now.
Getting a few tips like this quickly gives insight into how the program works.
I should be okay for a little while now, but I'm sure I'll be back...
Thanks for your help guys.