I need help creating Helper Class

[HiBoo]

I'm not certain this can be done so I'm posting the question to see if anyone can respond with a possible solution. Since describing what I want to do is somewhat confusing I'll try to define it with an example.

Here is what I want my code to look like when using the Helper Class:

Dim clsHelper As clsHelperClass

public sub ShowMeStuff

   Set clsHelper = new clsHelperClass
   debug.print "There are " & clsHelper.Tables.Employees.RecordCount & " records in " & clsHelper.Tables.Employees.Name & "."
   set clsHelper = nothing

End Sub

Assuming the current database has a table named Employees and there are 3 records in the table, the result of this code block would be:
There are 3 records in Employees.

Assuming there is another table in the database, one called Sales for example, I could also have written clsHelper.Tables.Sales.RecordCount to determine how many records were in the Sales table.

Once I get an idea of how to set up this scenerio, I plan on extending the features and usage of this class for things a little bit more useful.


So that being said, the helper class needs to extract the user defined tables in the current database through class methods. I know I can create this functionality by creating a Employees or Sales class with a RecordCount method but I don't want to do that. I want to know if this can be done dynamically? The reason for this is to be able to use the helper class in any database and have it do what it's coded to do.

There isn't any specific reason I have to do this other than to see if it can be done. Any help or leads would be greatly appreciated so I can put this personal bug of mine to rest.

Thanks.

 

[MajP]

Although I am a big fan of custom classes, not sure what this would give you. I can1 not really visualize multiple instances of this class. Seems it would simply be a standard module with a few public methods. But putting it in a class is good practice.

All of these properties are already available to you through native classes. Look at
currentproject
queryDefs
tabledefs
dao

Simple example

public sub getRecordCount(tblName as string) as long
dim rs as dao.recordset
set rs = currentdb.openrecordset(tblName)
getRecordCount = rs.recordcount
end sub

public sub showmestuff()
dim tdf as tabledefs
for each tdf in currentdb.tabledefs
msgbox tdf.name & " has " & getRecordCount(tdf.name) & records
next tdf
end sub

 

[HiBoo]

Hi Majp, thanks for the reply.

I know this isn't really a practical or typical thing to do. I'm just trying to see what can be done with classes. The thing I'm trying to get at is the drop lists from the object... or intellisense. I want to see the tables in the database appear as a drop list item when I code clsHelper.Table.TableName.MethodName for example.

 

[MajP]

The only way you could do that would be to define the tables as properties of the class. But that is kind of self defeating.

 

[HiBoo]

Exactly... that's why I'm trying to see if there is a more dynamic way to do this. As a developer I know I can navigate the tableDefs collection and pull out the data I want from the tabledef objects in that collection. But let's assume I'm not a developer... what makes more sense; declaring an instance of clsHelper and coding clsHelper.Tables.Employees.RecordCount or figuring out how to code and implement tabledefs properties? This is a very simplistic demo of course. I'd just like to prove one way or the other if it's possible to do using VBA code.

Looking ahead, I'd maybe like to simplify the implemention of code in said helper class so that non developers could use it by navigating the helper class object and it's drop list items. Really I'm just playing around with the possibility... you're right it's probably not practical or necessary! I'm just curious and looking outside of the box to see what can be done.

Thanks again for your insight.

 

[MajP]

This is the part that you cannot make dynamic
Tables.Employees

But in my example it would be something like

public hc as new helperClass
msgbox hc.getRecordcount("Employees")