LIST TABLES N DATABASE(COMBO BOX) 7

villica · Mar 1, 2000

I have a list of Logs tables on my database. They only contain date, actvity and what a person did on that certain date and the time spent. I would like to have a form that list the log tables on a list box or combo box. I would like the user to pick the table they want and then generate a cross tab report from. I have not figure out the report yet but I am hopping at least to be able to display the tables. I am not sure what is the best way of doing this, but any suggestions would be appreciated. 
Thank You 
Villica <a href=mailto:villica67@hotmail.com>villica67@hotmail.com</a> <a href= > </a>

elizabeth · Mar 1, 2000

Here's one approach: If your log tables have a certain string in common, such as tblLog*, you could create a combo box with a source query that looks for that string in the MSysObjects table (Name column). Then add a button to the form that uses the value in the combo box as a parameter in the DoCmd.Open command. Use the button wizard to create the code for you, then modify it as needed.

DougP · Mar 1, 2000

Here is a good staring point 
this code will debug.print all of the tables out 
-------------------------- 
Public Sub TableDefs() 
On Error GoTo Err_TableDefs 
Dim dbs As Database 
Dim tdfNew As TableDef 
Dim tdfLoop As TableDef 
Dim prpLoop As Property 
 
Set dbs = CurrentDb 
' Create new TableDef object, append Field objects 
' to its Fields collection, and append TableDef 
' object to the TableDefs collection of the 
' Database object. 
Set tdfNew = dbs.CreateTableDef("NewTableDef&quot

tdfNew.Fields.Append tdfNew.CreateField("Date", dbDate) 
 
dbs.TableDefs.Append tdfNew 
 
With dbs 
Debug.Print .TableDefs.Count & _ 
" TableDefs in " & .Name 
 
' Enumerate TableDefs collection. 
For Each tdfLoop In .TableDefs 
 
If Left(tdfLoop.Name, 4) = "MSys" Or Left(tdfLoop.Name, 1) = "~" Then 
'leave it out of the list 
Else 
'print out tables 
Debug.Print " " & tdfLoop.Name 
 
End If 
Next tdfLoop 
 
.Close 
End With 
 
Exit_TableDefs: 
Exit Sub 
 
Err_TableDefs: 
Select Case Err.Number 
Case 3010 
'Table already exists 
DoCmd.DeleteObject acTable, "NewTableDef" 
Resume Next 
Case Else 
MsgBox "Error # " & Err.Number & " " & Err.Description, vbInformation, "In sub TableDefs" 
Resume Exit_TableDefs 
End Select 
End Sub 
---------------------------------- DougP <a href=mailto: dposton@universal1.com> dposton@universal1.com</a> <a href= > </a>

MikeAngelastro · Mar 2, 2000

Hi, 
When querying MSysObjects table remember that the type code for tables is "1" - last field of table. 
Mike

villica · Mar 3, 2000

I have a problem. I am very new to Access so the above reply I was not able to figure out in its entirety. 
Elizabeth all my logs starts with: logmmm00(EG LOGMAY99) 
I am not quite sure what do you mean by a source query using mSysObject. I looked in the help but I was not able to find anythig. 
 
Doug If I were to use some of the code you gave me. Do I put it under the combo box or do I create a button. 
 
Thank you all for your help. 
 
Villica <a href=mailto:villica67@hotmail.com>villica67@hotmail.com</a> <a href= > </a>

DougP · Mar 3, 2000

OK here it is ready to go, tested and debugged... 
 
------------------------ Put this in your On_load event of the form 
Private Sub Form_Current() 
On Error GoTo Err_TableDefs 
Dim dbs As Database 
Dim tdfNew As TableDef 
Dim tdfLoop As TableDef 
Dim prpLoop As Property 
Dim ComboRow As String 
Set dbs = CurrentDb 
' Create new TableDef object, append Field objects 
' to its Fields collection, and append TableDef 
' object to the TableDefs collection of the 
' Database object. 
Set tdfNew = dbs.CreateTableDef("NewTableDef&quot

tdfNew.Fields.Append tdfNew.CreateField("Date", dbDate) 
 
dbs.TableDefs.Append tdfNew 
 
With dbs 
Debug.Print .TableDefs.Count & _ 
" TableDefs in " & .Name 
ComboRow = "" 
' Enumerate TableDefs collection. 
For Each tdfLoop In .TableDefs 
 
If Left(tdfLoop.Name, 3) = "LOG" Then 
'Add to combo box 
Debug.Print " " & tdfLoop.Name 
ComboRow = ComboRow & Chr$(34) & tdfLoop.Name & """;" 
End If 
 
Next tdfLoop 
Me!Combo2.RowSource = Left(ComboRow, Len(ComboRow) - 1) 
.Close 
End With 
 
Exit_TableDefs: 
Exit Sub 
 
Err_TableDefs: 
Select Case Err.Number 
Case 3010 
'Table already exists 
DoCmd.DeleteObject acTable, "NewTableDef" 
Resume Next 
Case Else 
MsgBox "Error # " & Err.Number & " " & Err.Description, vbInformation, "In sub TableDefs" 
Resume Exit_TableDefs 
End Select 
 
End Sub 
 
 
 DougP <a href=mailto: dposton@universal1.com> dposton@universal1.com</a> <a href= > </a>

JimHorton · Mar 3, 2000

villica, 
Put this in your combobox row source: 
 
 
Select Name from MsysObjects where Name like "log*" And Type in(4,6,1) 
4 = ODBC tables 
6 = ISAM attached tables 
1 = Local access tables 
 
--Jim Horton

DougP · Mar 4, 2000

Jim, 
I can't get that to work in Access '97 
But it sure is 500% shorter than my Ruug Goldberg Approach. DougP <a href=mailto: dposton@universal1.com> dposton@universal1.com</a> <a href= > </a>

JimHorton · Mar 4, 2000

Doug 
I just pasted directly from the post, (but I took out the Like log* clause-- that was for villica's case), and it worked. (obviously don't include the 3 lines explaining what number goes to what type of table). 
--Jim

DougP · Mar 5, 2000

Well I created 3 tables - LOGjun99,LOGjul99 and LOGaug99 just for testing and it still does not work. 
The odd thing is when I click the combo box it will drop down with 3 items cause I have 3 tables but all 3 items are blank. Nothing shows in them. 
This is a brand new install of Office '97 Pro. 
Do I need to check on something. 
This is a brand new computer 3 days old. 
I just built my self a new Windows 2000 Pro machine from scratch. 
 DougP <a href=mailto: dposton@universal1.com> dposton@universal1.com</a> <a href= > </a>

DougP · Mar 5, 2000

I found the problem 
I originally set up the combo box using the "Wizard". 
I picked two fields out of a random table 
the Column widths were set to 0";1" by the wizard. 
So the LOG* info was in column 0 which had no width and did not show. 
Nothing was in column 1 so nothing showed there either. 
Even though I saw three blank items when I clicked the down arrow. 
I set both columns to 1" and 
It works now... 
 
I voted your code a star by the way. 
 DougP <a href=mailto: dposton@universal1.com> dposton@universal1.com</a> <a href= > </a>

villica · Mar 5, 2000

Hi Doug thank you for the code but I coul not get it to work, I basically just copy and paste. You told me to put it under the load form event but your code starts with form_current, was this a typo?. I still would like to try it out if you think I put it under the wrong event.. 
 
Jim your code works perfect and it works great. Thank you so very much both of you for your wonderfull help. I also gave you a start. 
 
 
Thank you again 
 
Villica <a href=mailto:villica67@hotmail.com>villica67@hotmail.com</a> <a href= > </a>

DougP · Mar 6, 2000

Yes and NO 
I said under Form Load but though about it while I was creating it. 
It will work under either. 
If you put it under the Form load event it will work only the first time you open the form. (thus On_load) 
So If you move to the next record and some new table is added, it will not update the data in the combo box 
The best place to put it is the On-Current event. 
The ON-current event is tripped when the form opens and also moving around in the recordset. 
I'm not sure why it does not work. 
Are getting an error 
Frankly I would'nt worry about it. 
You have a much better solution in Jim's 
 DougP <a href=mailto: dposton@universal1.com> dposton@universal1.com</a> <a href= > </a>

elizabeth · Mar 6, 2000

If you click on Tools/Objects/View and check "System Objects", you will see some system tables that are normally hidden. One of these, MSysObjects, lists all the objects in the Database, including the tables. The names are listed in the Names columns, and as MikeAngelastro noted, in case you have other objects with similar names, you can also chaeck that the object Type in the Type column is "1" for table. You can use table as a source for a query.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

LIST TABLES N DATABASE(COMBO BOX) 7

villica

Programmer

elizabeth

IS-IT--Management

DougP

MIS

MikeAngelastro

Programmer

villica

Programmer

DougP

MIS

JimHorton

Programmer

DougP

MIS

JimHorton

Programmer

DougP

MIS

DougP

MIS

villica

Programmer

DougP

MIS

elizabeth

IS-IT--Management

Similar threads

Part and Inventory Search

Sponsor