In Create Classes… I had this todo list:
Create the class module rather than copy and paste Name the class module based on the table name Select the database and table from a list on a userform Establish Parent Child relationships between two classes Create functions to fill the class from a recordset
- Create a userform to add/edit/delete records
I need to add
- Create relationships between two child classes
- Automatically set an ADO reference
- Option to make functions instead of subs (for error handling)
- Allow recreation of the classes and fill code
You can Download Payroll.zip. It contains the VBAFramework.xls workbook and a Payroll.mdb Access database.
Here’s where it all starts
Dim axCat As ADOX.Catalog
Dim ufSelect As USelectTable
Dim vaSelected As Variant
Dim axTable As ADOX.Table
Dim i As Long
Dim sChildCode As String, sParentCode As String
Dim sFillCode As String
Set axCat = New ADOX.Catalog
Set axCat.ActiveConnection = gadCon
Set ufSelect = New USelectTable
Set ufSelect.Catalog = axCat
If Not ufSelect.UserCancel Then
vaSelected = ufSelect.SelectedFields
Set axTable = ufSelect.Table
If ActiveWorkbook.Name = ThisWorkbook.Name Then Workbooks.Add
ChildClassCode axTable, vaSelected, sChildCode
MakeChildClass axTable, sChildCode
ParentClassCode axTable, sParentCode
MakeParentClass axTable, sParentCode
FillClassesCode axTable, vaSelected, sFillCode
MakeFillModule axTable, sFillCode
Set axCat = Nothing
Set ufSelect = Nothing
Most of the code is bunch of string concatenation, which is pretty boring. But not as boring as actually typing the code. When you run PickTable, you get
It allows you to select a table and fields to include in your classes. The code creates a class based on the field and a collection class to serve as its parent. Then it creates (or modifies an existing) standard module that holds code to fill the classes.
There’s no customization options (it’s in pre-Beta). But if you happen to use the same conventions as me, it will work perfectly. First, it assumes you name your Access tables as plural and your classes exactly the same way (but singular for the child class). It assumes you have an ID field that is SingularTableNameID, so tblEmployees will have an EmployeeID field. That one is used to uniquely identify the record in the collection class, so it’s important. I should probably not allow the user to deselect that one in the listbox. Oh yeah, it also assumes you prefix your tables with “tbl”. Everyone does that, right?
I imagine I’ll add some customization options as I go so it’s useful to more than just me. So if there’s a convention that’s near and dear to your heart, leave a comment. But I don’t plan on making it fully customizable. I’m going the Ruby on Rails route of “this will work as long as you do it just like me”.
Let me know what you think.