My quest to make an object out of a table

[Ruegen]

I am still in the process of making a function that makes an accessible class object that you can use anywhere in the code to load in data

so far I have

Public Sub AddAModule(TableName As String)


    Dim VBAEditor As VBIDE.VBE
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
    Dim CodeMod As VBIDE.CodeModule
    
    Set VBAEditor = Application.VBE
    Set VBProj = VBAEditor.ActiveVBProject
    
    For Each VBComp In VBProj.VBComponents
    
    If VBComp.Name = "mod_" & TableName Then
        VBProj.VBComponents.Remove VBComp
    End If
    
    Next VBComp
    
    Set VBComp = VBProj.VBComponents.Add(vbext_ct_StdModule)
    VBComp.Name = "mod_" & TableName
    
    Set CodeMod = VBComp.CodeModule
    
    Dim i As Integer
    Dim db As DAO.Database
    Dim aTable As DAO.TableDef
    Dim aField As DAO.Field
    Set db = CurrentDb()
    
    i = 1
    With CodeMod
        .DeleteLines i, .CountOfLines
        
        For Each aTable In db.TableDefs
            
            If TableName = aTable.Name Then
                For Each aField In aTable.Fields
                    'create object here with name of field
                    Debug.Print "adding var" & aField.Name
                    .InsertLines i, "Public var" & Replace(aField.Name, " ", "") & " as " & FieldTypeName(aField)
                    i = i + 1
                Next aField
            End If
    
        Next aTable
            
       
        '.InsertLines i, "private Sub EditField()"
        'i = i + 1
 
    End With
    
    
End Sub

what this will do is build an access module that plants in all the field names of the table as variables (I use a modified function from Allen's FieldTypeName to get the datatype for vba)

What I want is to make a "Class" object. I want to be able to put the same code into a class an not a module - so I don't quite know how to do that yet.



Ideally I want to make an object where I can pull (if "tblSchools" is my table name parameter)

school.name

or school.phonenumber

this way I can edit a records details in code however it would load at startup and when you add a field the code would auto add the field to the class object.

if anyone knows the code to make it a class rather than adding a module please let me know!

Update: vbext_ct_ClassModule gives me a class module - working!

[Ruegen]

now I have a form with the same recordsource as the table and for a record at a time I can load this on form open - and change it on record.

Private Sub Form_Load()


Dim School As tblSchools


Set School = New tblSchools


School.varSchoolName = Me.SchoolName
MsgBox School.varSchoolName




End Sub

[ItsMe]

...What I want is to make a "Class" object. I want to be able to put the same code into a class an not a module...

To make a Class Module, select Class Module form the Ribbon. It is that easy. The hard part is instantiating your class as an object within a form or another module.


Actually, as soon as you add VBA to a form, you are creating a Class. You are creating a Private Class. All of the procedures within the Form's Module are private to the form and are not available at the Global level. On the other hand, when you create a Class Module within Access, it is a Public Class. The code within the public class is available from anywhere within the app. All you have to do is instantiate the Class as an Object. The main difference between a Standard Module and a Class Module, within Access, is that the Standard Module is, kinda, instantiated at startup and the Class needs to be instantiated before it uses the Client machine's resources.

If I created a Class Module named MyPublicClass I would instantiate it from within a form's module like this.

Dim MyPublicClass as New MyPublicClass

Then, I can access the Public Functions located within the Class. For instance, if there is a Public Function within the Class Object named fGoodStuff, you will see it available within intellisense.

MyPublicClass.fGoodStuff()

[Ruegen]

To make a Class Module, select Class Module form the Ribbon. It is that easy. The hard part is instantiating your class as an object within a form or another module.


Actually, as soon as you add VBA to a form, you are creating a Class. You are creating a Private Class. All of the procedures within the Form's Module are private to the form and are not available at the Global level. On the other hand, when you create a Class Module within Access, it is a Public Class. The code within the public class is available from anywhere within the app. All you have to do is instantiate the Class as an Object. The main difference between a Standard Module and a Class Module, within Access, is that the Standard Module is, kinda, instantiated at startup and the Class needs to be instantiated before it uses the Client machine's resources.

If I created a Class Module named MyPublicClass I would instantiate it from within a form's module like this.

Dim MyPublicClass as New MyPublicClass

Then, I can access the Public Functions located within the Class. For instance, if there is a Public Function within the Class Object named fGoodStuff, you will see it available within intellisense.

MyPublicClass.fGoodStuff()

yeah that is from the form level - I am making a public one (like you said) that works with any table - so you could just put in the table name and the table would then show up in your code to utilise. I now want to load the matching fields from a recordset into the fields of the object.

Since I have created the object at load, now I want to loop thorugh on a form - find a matching field name and then load the value into the object.

This way I work with the object etc on a form without looking up the name of the field.

so far I have

Public Function loadClassUp(FormRecordset As DAO.Recordset, classObject As Variant)


Dim rs As DAO.Recordset
Dim rsField As DAO.Field
Dim i As Variant


Set rs = FormRecordset
For Each rsField In rs.Fields


    For Each i In classObject.Name <-- I want to refer to the variables in the class (not the name of the class)
    
    Next i


Next rsField

so I want to loop through the records available fields. Any matching field names will get their values from the recrodset - this happens on current event

then I have an object that works across forms if I want to do something with it..... I have a particular universal method with the class (yet to make)

this above is a work in progress so I know the code is wrong

[Ruegen]

To clear things up

I want the element in the class to be matched with the field name in the recordset, then the element to receive the value of the field

Public Function loadClassUp(FormRecordset As DAO.Recordset, classObject As Variant)


Dim rs As DAO.Recordset
Dim rsField As DAO.Field
Dim i As Variant


Set rs = FormRecordset
For Each rsField In rs.Fields
    
For Each i In classObject.elements '<-- need to find out how to refer to the variables


If classObject.element.Name = rsField.Name Then
    classObject.element.value = rsField.value
End If


Next i


Next rsField

and in case you are wondering how I load up the class

Private Sub Form_Load()


Dim school As tblSchools


Set school = New tblSchools


school.varSchoolName = Me.SchoolName
MsgBox school.varSchoolName


Call loadClassUp(Me.Form.Recordset, school)


End Sub

[ItsMe]

My previous response is directed at your desire to create a Class Module. Aside from that, I am having a hard time understand the mechanics of your code.

Perhaps a public function is beneficial. If it is, the type of "Class" could be a Standard Module or a Class module. I described the difference earlier.

As for using table defs, creating tables, etc. I do not know that I would create a public function to accomplish this. I would likely, place everything behind a form. My thoughts are this, if my code is only going to be called from one or two forms, keep the code private to the form and place all of the code within the form's module.

Before I would entertain the idea of creating a class module to create tables, list field names, add fields to tables, whatever, I would need to call the code from various forms throughout the app. This does not sound probable.

[Ruegen]

My previous response is directed at your desire to create a Class Module. Aside from that, I am having a hard time understand the mechanics of your code.

Perhaps a public function is beneficial. If it is, the type of "Class" could be a Standard Module or a Class module. I described the difference earlier.

As for using table defs, creating tables, etc. I do not know that I would create a public function to accomplish this. I would likely, place everything behind a form. My thoughts are this, if my code is only going to be called from one or two forms, keep the code private to the form and place all of the code within the form's module.

Before I would entertain the idea of creating a class module to create tables, list field names, add fields to tables, whatever, I would need to call the code from various forms throughout the app. This does not sound probable.

I've done it! - check the code (post 1)

The only thing I haven't given is allen's modified function - which I will give later.

As soon as the front end loads up it builds a new class with the name of the table and loads elements with the same name as the fields (I add "var" in front so it doesn't confuse).

If I make the class object public it sits there. It is empty now but I want to load it up with the same details as the record

I can load details up manually in code say...

school.schoolname = me.schoolname

however this would mean typing each one out to load it where a loop could do the same thing.

update: think of it this way too - you load the database which has say 10 tables... each table instantly gets built as a class. you close the database front end, open the back end and add a new field. Then you open the front end again and it rebuilds the class module to bring in the new field for referencing.

[Ruegen]

since my tables are called tblSomething all the classes get the same name, tblSchools, tblStates etc

e.g. my code in post 5 is making an object using the newly built class




Dim school As tblSchools


Set school = New tblSchools

[Ruegen]

also you don't need to use allens function - you could just name every class element as a variant - it would still hold the same value I guess

[ItsMe]

...
update: think of it this way too - you load the database which has say 10 tables... each table instantly gets built as a class. you close the database front end, open the back end and add a new field. Then you open the front end again and it rebuilds the class module to bring in the new field for referencing...

I will have to take a closer look then. Still not understanding the real world application for such a convenience.

[Ruegen]

I will have to take a closer look then. Still not understanding the real world application for such a convenience.

think of currently logged in user

you could ask for

user.name

user.id

user.password

user.accessGrant (bool)

anywhere in access because it is as public object

that way less code, easier, etc etc

like you said - convenience.