Assistance in Module making

**Thompyt** · 04-13-2016, 03:23 PM

I am starting up in building Modules to lessen the amount of text in VBA. I use many instances of Format(Date, "dd mmm yyyy")

Module.StrTod

Option Compare Database
Public Const StrTod As Variant = Format(Date, "dd mmm yyyy")

Fails on Format, I have tried String vs Variant. I get "Constant expression is required" is this due to the Date() function?

Thanks
Wayne

**orange** · 04-13-2016, 04:05 PM

In plain English What are you trying to do?

Are you trying to build a user defined function? If so, then this article by Martin Green may help.

**Thompyt** · 04-13-2016, 04:32 PM

orange,
I am being lazy but working harder to simplify. I have @ 140 instances of Format(Date,"dd mmm yyy") and I want to make one instance to point to. A public module for formatted todays date. I could use it in a table and reference it with a dlookup, but I think it is cumbersome.

I'll take a look at your reference.
Thanks
Wayne

**Thompyt** · 04-13-2016, 04:49 PM

I tried it as a function and can get it to compile. When I use this in an Email subject line .Subject = "Weekly Summation - " & StrTod, I get Type mismatch error.

I believe I need to use a Public Const strTod = or something similar

**orange** · 04-13-2016, 05:05 PM

Here is a function and a test routine. I am assuming it's always today's Date that you are dealing with.

Function

Code:

Function ThomsDate() As String
  ThomsDate = Format(Date, "dd mmm yyyy")
End Function

test routine

Code:

Sub testtom()
  Debug.Print ThomsDate
End Sub

Result of test
13 Apr 2016

Good luck

**Thompyt** · 04-13-2016, 05:30 PM

Orange,
I am using it in this manner:
With objOutlookMsg
.Subject = "Weekly Summation - " & StrTod

I get a Type mismatch error on the &

**orange** · 04-13-2016, 07:08 PM

I don't think you can use an intrinsic Access/vba function eg Format in defining a constant.

Have you tried the function I posted? I think if you put this in a standard module, it should work (best guess).
You can change ThomsDate to strTOD if that's important.

**Thompyt** · 04-14-2016, 08:38 AM

Would building a function like this bea reasonable thing just to cut down on the amount of code? How would I then call out this function in the form?
Add a Public Sub StrTod - or whatever name
Dim StrTod
Set StrTod as String
StrTod
End Sub

Then I would have to call this out in each code set right? That would end up being the same amount of characters or more than if I left this alone. I was simply trying to make a module or find a means to decrease the amount of characters in a code by only having to reference one spot.

Its a start where I want to set up code for emails Dim/set and even making certain codes (ie Dlookup)stay resident and public.

Call

**orange** · 04-14-2016, 08:56 AM

If you define the function in a standard module in your database, you can refer to it by name

So if the function is called strTod, you should be able to refer to it as StrTod any where in your database.

You should try it in your test database, and see what works and what doesn't, then post any issues.

You do not need any Set. Look at my post 5, the function(change the name to strTod) goes into a standard module.
Any code in your database should just use strTod.

**Thompyt** · 04-14-2016, 09:28 AM

Placed this in the form:
Sub StrTod()
StrTod
End Sub

Then put
.Subject = "Today's AORB/TEWG/CCB outcome - " & StrTod 'Format(Date, "dd mmm yyyy")

Dim StrTod as variant
Email output Subject line = Today's AORB/TEWG/CCB outcome -

Without StrTod in the code Dimmed as variant: Expected function or variable

Run the code and get "Expected Function or variable"

**orange** · 04-14-2016, 09:42 AM

Put this function into a standard module.

create a new module --standard, then insert this function.

Code:

Function strTOD() As String
  strTOD = Format(Date, "dd mmm yyyy")
End Function

in your form

.Subject = "Today's AORB/TEWG/CCB outcome - " & StrTod

you don't need to DIM

Dim StrTod as variant

and you don't need

Sub StrTod()
StrTod
End Sub

**Thompyt** · 04-14-2016, 10:05 AM

Module:

Code:

Option Compare Database
Function strTod() As String
   strTod = Format(Datye, "dd mmm yyy")
End Function

Email:

Code:

Public Sub Send_Daily_Click()
 On Error GoTo ErrorMsgs
 Dim rs As Dao.Recordset
 Dim objOutlook As Outlook.Application
 Dim objOutlookMsg As Outlook.MailItem
 Dim objOutlookMsg1 As Outlook.MailItem
 Dim objOutlookMsg2 As Outlook.MailItem
 Dim objOutlookAttach As Outlook.Attachment
 Dim strBody, strAddresses, strSubject, strMsg As String
 Dim CRNUM, DateTypes, FINAL As Variant
  Set rs = CurrentDb.OpenRecordset("SELECT Status,CR_Numbers,[Change Requested]FROM Daily_Actions_Email ORDER BY Status,CR_ID ASC")
  Set objOutlook = CreateObject("Outlook.Application")
  Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
  Set objOutlookMsg1 = objOutlook.CreateItem(olMailItem)
  Set objOutlookMsg2 = objOutlook.CreateItem(olMailItem)
CRNUM = DLookup("[CR_Numbers]", "[Daily_Actions_Email]")
DateTypes = DLookup("[DateType]", "[Settings_Qry]")
FINAL = DLookup("[Final_Vote]", "[Daily_Actions_Email]")
With DoCmd
     .SetWarnings False
     .OpenQuery "Daily Update"
     .SetWarnings True
End With
If IsNull(CRNUM) Then
With objOutlookMsg
    .Subject = "There were no actioned CR's - " & Format(Date, "dd mmm yyyy")
    .Body = "The email addressing is a living entity.  If there are corrections, additions, or deletions, please notify the sender." & vbCrLf & vbCrLf & _
            "There were no actioned Change Requests for " & Format(Date, "dd mmm yyyy") & "." & strSigBlock
    .To = "CCB Results"
    .Display
    DoCmd.Close acReport, "Daily Actions"
Exit Sub
End With
Else
End If
rs.MoveFirst
While Not rs.EOF
 strMsg = strMsg & rs!Status & vbCrLf & Chr(9) & "CR  " & rs!CR_Numbers & " - " & rs![Change Requested] & vbCrLf
 rs.MoveNext
Wend
 rs.Close
With objOutlookMsg1
   .Subject = "Today's AORB/TEWG/CCB outcome - " & strTod ' Format(Date, "dd mmm yyyy")

When I run this combination I get: Type Mismatch on the >.Subject line on "&"

**orange** · 04-14-2016, 10:23 AM

Originally Posted by Thompyt

Module:

Code:

Option Compare Database
Function strTod() As String
   strTod = Format(Datye, "dd mmm yyy")
End Function

Email:

Code:

Public Sub Send_Daily_Click()
 On Error GoTo ErrorMsgs
 Dim rs As Dao.Recordset
 Dim objOutlook As Outlook.Application
 Dim objOutlookMsg As Outlook.MailItem
 Dim objOutlookMsg1 As Outlook.MailItem
 Dim objOutlookMsg2 As Outlook.MailItem
 Dim objOutlookAttach As Outlook.Attachment
 Dim strBody, strAddresses, strSubject, strMsg As String
 Dim CRNUM, DateTypes, FINAL As Variant
  Set rs = CurrentDb.OpenRecordset("SELECT Status,CR_Numbers,[Change Requested]FROM Daily_Actions_Email ORDER BY Status,CR_ID ASC")
  Set objOutlook = CreateObject("Outlook.Application")
  Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
  Set objOutlookMsg1 = objOutlook.CreateItem(olMailItem)
  Set objOutlookMsg2 = objOutlook.CreateItem(olMailItem)
CRNUM = DLookup("[CR_Numbers]", "[Daily_Actions_Email]")
DateTypes = DLookup("[DateType]", "[Settings_Qry]")
FINAL = DLookup("[Final_Vote]", "[Daily_Actions_Email]")
With DoCmd
     .SetWarnings False
     .OpenQuery "Daily Update"
     .SetWarnings True
End With
If IsNull(CRNUM) Then
With objOutlookMsg
    .Subject = "There were no actioned CR's - " & Format(Date, "dd mmm yyyy")
    .Body = "The email addressing is a living entity.  If there are corrections, additions, or deletions, please notify the sender." & vbCrLf & vbCrLf & _
            "There were no actioned Change Requests for " & Format(Date, "dd mmm yyyy") & "." & strSigBlock
    .To = "CCB Results"
    .Display
    DoCmd.Close acReport, "Daily Actions"
Exit Sub
End With
Else
End If
rs.MoveFirst
While Not rs.EOF
 strMsg = strMsg & rs!Status & vbCrLf & Chr(9) & "CR  " & rs!CR_Numbers & " - " & rs![Change Requested] & vbCrLf
 rs.MoveNext
Wend
 rs.Close
With objOutlookMsg1
   .Subject = "Today's AORB/TEWG/CCB outcome - " & strTod ' Format(Date, "dd mmm yyyy")

When I run this combination I get: Type Mismatch on the >.Subject line on "&"

I do not have outlook on my machine.
I created a function in a module, and I can refer to that function by name anywhere in my database.

Incidently, this is NOT doing what you think.

Dim strBody, strAddresses, strSubject, strMsg As String

In Access, you must explicitly Dim your variables
eg
Dim strBody as string, strAddresses as string , strSubject as string, strMsg As String

otherwise variables become Variants.

**orange** · 04-14-2016, 10:52 AM

Originally Posted by Thompyt

Module:

Code:

Option Compare Database
Function strTod() As String
   strTod = Format(Datye, "dd mmm yyy") <-----Error
End Function

Email:

Code:

Public Sub Send_Daily_Click()
 On Error GoTo ErrorMsgs
 Dim rs As Dao.Recordset
 Dim objOutlook As Outlook.Application
 Dim objOutlookMsg As Outlook.MailItem
 Dim objOutlookMsg1 As Outlook.MailItem
 Dim objOutlookMsg2 As Outlook.MailItem
 Dim objOutlookAttach As Outlook.Attachment
 Dim strBody, strAddresses, strSubject, strMsg As String
 Dim CRNUM, DateTypes, FINAL As Variant
  Set rs = CurrentDb.OpenRecordset("SELECT Status,CR_Numbers,[Change Requested]FROM Daily_Actions_Email ORDER BY Status,CR_ID ASC")
  Set objOutlook = CreateObject("Outlook.Application")
  Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
  Set objOutlookMsg1 = objOutlook.CreateItem(olMailItem)
  Set objOutlookMsg2 = objOutlook.CreateItem(olMailItem)
CRNUM = DLookup("[CR_Numbers]", "[Daily_Actions_Email]")
DateTypes = DLookup("[DateType]", "[Settings_Qry]")
FINAL = DLookup("[Final_Vote]", "[Daily_Actions_Email]")
With DoCmd
     .SetWarnings False
     .OpenQuery "Daily Update"
     .SetWarnings True
End With
If IsNull(CRNUM) Then
With objOutlookMsg
    .Subject = "There were no actioned CR's - " & Format(Date, "dd mmm yyyy")
    .Body = "The email addressing is a living entity.  If there are corrections, additions, or deletions, please notify the sender." & vbCrLf & vbCrLf & _
            "There were no actioned Change Requests for " & Format(Date, "dd mmm yyyy") & "." & strSigBlock
    .To = "CCB Results"
    .Display
    DoCmd.Close acReport, "Daily Actions"
Exit Sub
End With
Else
End If
rs.MoveFirst
While Not rs.EOF
 strMsg = strMsg & rs!Status & vbCrLf & Chr(9) & "CR  " & rs!CR_Numbers & " - " & rs![Change Requested] & vbCrLf
 rs.MoveNext
Wend
 rs.Close
With objOutlookMsg1
   .Subject = "Today's AORB/TEWG/CCB outcome - " & strTod ' Format(Date, "dd mmm yyyy")

When I run this combination I get: Type Mismatch on the >.Subject line on "&"

I do not have outlook on my machine.
I created a function in a module, and I can refer to that function by name anywhere in my database.

I recommend you use 4 digit year eg YYYY

Incidently, this is NOT doing what you think.

Dim strBody, strAddresses, strSubject, strMsg As String

In Access, you must explicitly Dim your variables
eg
Dim strBody as string, strAddresses as string , strSubject as string, strMsg As String

otherwise variables become Variants.

**Thompyt** · 04-14-2016, 10:52 AM

Thanks,
I figured it out.

I had Date spelled wring in the function and I added Public to the front of function so it read Public Function StrTod(). Works now.

Appreciate it.

Assistance in Module making

Thread Tools

Assistance in Module making

Similar Threads

VBA assistance

Qry Assistance Please

class module vs regular module

Referencing and Changing A Form Module Variable From A Module

Need Assistance with Forms

Posting Permissions