Solved Form: Objects Navigation (see speakers_86)

[vhung]

Ive been using different approach for objects navigation,
but this one on current is unique.

I made little modification for (open/close, max/min),
looks like something i missed.

My idea is to count the number of tables on_click "Tables" button,
as table names shown on the list,
i wish to have the steps for that purpose.

But for now I could already navigate the objects as i need to,
I made additional coding whenever some changes occur depending to what object be opened.

Unsolved: ?=count the tables, query, reports, modules, macro

 

[arnelgp]

see this link on how to get the Total count of each object.
MS Access - Listing of Database Objects (Tables, Queries, Forms, Reports, ...) | DEVelopers HUT (devhut.net)

 

[vhung]

Something wrong with this code for query, total does not match the actual number of Queries

Dim qdf As DAO.QueryDef
Dim obj As Object
Dim tdf As DAO.TableDef
Dim i As Long

i = 0
Debug.Print CurrentDb.TableDefs.Count
For Each tdf In CurrentDb.TableDefs
If Not Left(tdf.Name, 5) = "MSys" Then
i = i + 1
End If
Next tdf

'Determine number of queries
Debug.Print "Number of Queries: " & CurrentDb.QueryDefs.Count
MsgBox "Number of Queries: " & i

 

[arnelgp]

i think you mean Left(tdf.Name, 4) = "Msys"

 

[moke123]

If Not Left(tdf.Name, 5) = "MSys"

5?

 

[vhung]

i think you mean Left(tdf.Name, 4) = "Msys"

Not exactly, because the result of Left(tdf.Name, 4) is the count of Local tables and Linked Tables minus "Msys"

 

[vhung]

5?

If Not Left(tdf.Name, 5) = "MSys", is the count of Local tables and Linked Tables plus "Msys"
Not the query counts-total

 

[arnelgp]

Dim i as integer, j as integer
i = 0: j = 0
Debug.Print CurrentDb.TableDefs.Count
For Each tdf In CurrentDb.TableDefs
If Not (tdf.Name, Like "MSys*") Then
If Len(Trim$(tdf.Connect & vbnullstring)) = 0 'table
i = i + 1
Else 'linked table
j = j + 1
End If
Next tdf

 

[moke123]

If Not Left(tdf.Name, 5) = "MSys", is the count of Local tables and Linked Tables plus "Msys"
Not the query counts-total

Unless my math skills have completely disappeared "MSys" is 4 letters.
The syntax of Left() is

Left ( text, number_of_characters )

 

[moke123]

Debug.Print CurrentDb.TableDefs.Count will return # of tabledefs

For Each tdf In CurrentDb.TableDefs
If Not Left(tdf.Name, 5) = "MSys" Then
i = i + 1
End If
Next tdf

This will return # of all tabledefs including the MSys tables because MSys is only 4 letters

MsgBox "Number of Queries: " & i

is returning # of all tables because that is what your iterating through to get i not the querydefs.

 

[vhung]

You allow me to keep going, thanks much to your guidance.

i try this way;

Dim i As Long
i = 0
If Left(aob.Name, 5) = "MSys" And Not Me.chkSystem Then GoTo skip
i = i + 1
Debug.Print "Number of Queries: " & i
skip:
Next
MsgBox "Number of Queries: " & i


It works, table and query count is done,
next would be acForm and ...

 

[vhung]

Form, Module, Macro and Report count is done aleady,
some code Syntax vary as obejct type changes.

Thanks for the help: "arnelgp" and "moke123", good hints.

 

[MajP]

Why are you looping? Just do a query to get the count on al objects.

 

[isladogs]

For a working example, see Get Stats About VBA Project | Access World Forums (access-programmers.co.uk).

It includes the numbers of all database objects as well as additional info about the number of procedures, relationships etc

 

[vhung]

Why are you looping? Just do a query to get the count on al objects.

How you do it, on your way?
Thanks.

 

[vhung]

For a working example, see Get Stats About VBA Project | Access World Forums (access-programmers.co.uk).

It includes the numbers of all database objects as well as additional info about the number of procedures, relationships etc

More time to spent, more analysis to do, to reach the peak.
This is big enough to work on.
Thanks.