Help with defining a variable in a class and declaring it in a module

**templeowls** · 12-27-2022, 11:50 AM

I'm trying to define a variable in a class module and then declare in a module with a lookup function.

In the class module (titled CMemail, I have the following:

Code:

Public CMDocumentation As String

and then in the module, I have:

Code:

Dim strDocumentation As New CMailItemEvents

Code:

Set sDocumentation.CMDocumentation = Nz(DLookup("Documentation", "tblTemplates", sTemplate), "")

I'm getting a "compile error: invalid use of property" message on the set line. I'm a novice in VBA and so I was hoping somone could assist. Thanks

**ranman256** · 12-27-2022, 11:55 AM

PUBLIC in a module will make the variable GLOBAL to all forms/modules/classes.

dim will only allow the var to exist in the 1 sub it's in.

PRIVATE will only allow the var to exist anywhere in the 1 module/class

you ONLY use SET for objects: controls or CREATING A CLASS
not for use on variables like strings, numbers.

so DONT use set on: sDocumentation.CMDocumentation = NZ...
you are assigning a value.
its acutally a LET sDocumentation.CMDocumentation =NZ...

Your variable prefix should match the type of variable:
strVal = STRING

set oDocumentation As New CMailItemEvents
or clsDocumentation as New class...

vMyVar as VARIANT
iMyNum as INTEGER

**templeowls** · 12-27-2022, 12:53 PM

THank you! So I made the changes you suggested and the error is gone, but I'm not actually getting the update to work.

I'm trying to have the class module update a form field.

In the class module I have:

Code:

Public strDocumentation As String

and

Code:

Forms!frmComplaints.FollowUp = Date & ": " & strDocumentation

and then in the module I have:

Code:

Dim oDocumentation As New CMailItemEvents

and

Code:

Let oDocumentation.strDocumentation = DLookup("Documentation", "tblTemplates", sTemplate)

When i run it, only the date is added into the field. Not strDocumentation as well. I've triple checked the DLookup and its correct.

Any suggestions?

**CJ_London** · 12-27-2022, 01:19 PM

Dlookup returns a string

little bits of code are meaningless for this sort of question but my best guess is you have not used the property let

**CJ_London** · 12-27-2022, 01:22 PM

little bits of code are meaningless for this sort of question but my best guess is you have not used the property let statement

https://learn.microsoft.com/en-us/of...-let-statement

**templeowls** · 12-27-2022, 03:41 PM

Originally Posted by CJ_London

little bits of code are meaningless for this sort of question but my best guess is you have not used the property let statement

https://learn.microsoft.com/en-us/of...-let-statement

Sorry. Here's the full code.

Macro:

Code:

Option Compare DatabaseOption Explicit


Dim OutApp As Outlook.Application
Dim itmevt As New CMailItemEvents
Dim oDocumentation As New CMailItemEvents


Public Sub EmailTemplate(sTemplate As String, sTo As String, sCC As String, sBCC As String)
        
        Dim OutMail As Outlook.MailItem
        On Error Resume Next
        
        Set OutApp = GetObject(, "Outlook.Application")
        
        Set OutApp = New Outlook.Application
        'If OutApp Is Nothing Then Set OutApp = New Outlook.Application
    
        Set OutMail = OutApp.CreateItem(olMailItem)
        Set itmevt.itm = OutMail
        Let oDocumentation.strDocumentation = Nz(DLookup("Documentation", "tblTemplates", sTemplate), "")
        
        With OutMail
            .BodyFormat = olFormatHTML
            .To = sTo
            .CC = sCC
            .BCC = sBCC
            .Subject = "Email Report"
            .HTMLBody = ""
            .Display
        End With
        
        Set OutMail = Nothing
    
End Sub

Class Module:

Code:

Option Compare DatabaseOption Explicit


Public WithEvents itm As Outlook.MailItem
Public strDocumentation As String


Private Sub itm_Close(Cancel As Boolean)


    Dim blnSent As Boolean
    
    On Error Resume Next
    blnSent = itm.Sent
    
    If Err.Number = 0 Then
        
    Else
  
    Forms!frmComplaints.FollowUp = Date & ": " & strDocumentation
        
    End If
    
End Sub

I can provide the code for where I call the macro in my form but I didn't think that was needed

**orange** · 12-27-2022, 04:38 PM

You may get some insight from this "Handling Events" article and sample from Philipp Stiefel

**CJ_London** · 12-27-2022, 04:48 PM

So take a look at the link I provided - you should have a property let statement in your class module. And a property get to return the value if required

Code:

Option Compare Database
Option Explicit


Public WithEvents itm As Outlook.MailItem
private strDoc As String

property let strdocumentation(s as string)

    strDoc=s

end property

Private Sub itm_Close(Cancel As Boolean)


    Dim blnSent As Boolean
    
    On Error Resume Next
    blnSent = itm.Sent
    
    If Err.Number = 0 Then
        
    Else
  
    Forms!frmComplaints.FollowUp = Date & ": " & strDocumentation
        
    End If
    
End Sub

then to assign the value you just have

oDocumentation.strDocumentation = Nz(DLookup("Documentation", "tblTemplates", sTemplate), "")

However I don't see what you are trying to accomplish by using a class object, at the moment there is nothing there that says you need a class object, all can be achieved in a standard module. If you are trying to learn how to use class objects, recommend you read up on the basics first.

**templeowls** · 12-27-2022, 06:05 PM

Originally Posted by CJ_London

So take a look at the link I provided - you should have a property let statement in your class module. And a property get to return the value if required

Code:

Option Compare Database
Option Explicit


Public WithEvents itm As Outlook.MailItem
private strDoc As String

property let strdocumentation(s as string)

    strDoc=s

end property

Private Sub itm_Close(Cancel As Boolean)


    Dim blnSent As Boolean
    
    On Error Resume Next
    blnSent = itm.Sent
    
    If Err.Number = 0 Then
        
    Else
  
    Forms!frmComplaints.FollowUp = Date & ": " & strDocumentation
        
    End If
    
End Sub

then to assign the value you just have

oDocumentation.strDocumentation = Nz(DLookup("Documentation", "tblTemplates", sTemplate), "")

However I don't see what you are trying to accomplish by using a class object, at the moment there is nothing there that says you need a class object, all can be achieved in a standard module. If you are trying to learn how to use class objects, recommend you read up on the basics first.

Okay I followed your instructions and it is still not populating the DLookUp. Perhaps I misinterepted though. Below are below codes with changes in red

Class Module:

Code:

Public WithEvents itm As Outlook.MailItem
Private strDoc As String


Property Let strdocumentation(s As String)


    strDoc = s


End Property


Private Sub itm_Close(Cancel As Boolean)


    Dim blnSent As Boolean
    
    On Error Resume Next
    blnSent = itm.Sent
    
    If Err.Number = 0 Then
        
    Else
        
        Forms!frmComplaints.FollowUp = Date & ": " & strDocumentation


    End If
    
End Sub

Module:

Code:

Dim OutApp As Outlook.ApplicationDim itmevt As New CMailItemEvents
Dim oDocumentation As New CMailItemEvents




Public Sub EmailTemplate(sTemplate As String, sTo As String, sCC As String, sBCC As String)
        
        Dim OutMail As Outlook.MailItem
        On Error Resume Next
        
        Set OutApp = GetObject(, "Outlook.Application")
        
        Set OutApp = New Outlook.Application
        'If OutApp Is Nothing Then Set OutApp = New Outlook.Application
    
        Set OutMail = OutApp.CreateItem(olMailItem)
        Set itmevt.itm = OutMail
        oDocumentation.strDocumentation = Nz(DLookup("Documentation", "tblTemplates", sTemplate), "")
        
        With OutMail
            .BodyFormat = olFormatHTML
            .To = sTo
            .CC = sCC
            .BCC = sBCC
            .Subject = "Email Report"
            .HTMLBody = ""
            .Display
        End With
        
        Set OutMail = Nothing
    
End Sub

**CJ_London** · 12-27-2022, 07:05 PM

Okay I followed your instructions and it is still not populating the DLookUp.

it won't populate the dlookup, the dlookup is assigned to your class variable

suggest you step through the code to see what it is actually doing. I'm not going to spend time trying to guess what it is intended to do

**templeowls** · 12-27-2022, 09:08 PM

Originally Posted by CJ_London

it won't populate the dlookup, the dlookup is assigned to your class variable

suggest you step through the code to see what it is actually doing. I'm not going to spend time trying to guess what it is intended to do

…the DLookUp looks up a value in a table and then that’s what populates the form field. Appreciate the help. I’ll try something else.

**Gicu** · 12-28-2022, 12:35 PM

Seems like a cross-post with answers here:
https://www.access-programmers.co.uk...-class.326088/

Cheers,

**Micron** · 12-28-2022, 12:44 PM

I suspect that what has been referred to as a class module is really just a standard module. I see nothing in that code that resembles a class.

Help with defining a variable in a class and declaring it in a module

Thread Tools

Help with defining a variable in a class and declaring it in a module

Similar Threads

declaring a Global variable

Defining Variables, Which Variable Type and When?

Declaring a Variable Constant (Sort Of)

Trouble defining variables - Access Module

class module vs regular module

Posting Permissions