How to call a Module.

S

Steven M. Britton

How do I call a module in VBA code in an onclick event.
Here is the Module:

Option Compare Database
Option Explicit

Sub SendEmails(Cancel As Integer)

Set db = CurrentDb()
Set rec = db.OpenRecordset("tblUPSEmail")

If rec.RecordCount > 0 Then
With rec
.MoveFirst
UPS: Do Until .EOF

DoCmd.SendObject acSendNoObject, , ,
rec.BILLEMAIL, , , "UPS Ground Email", _
"Dear " & rec.FULLNAME & ":" & vbCrLf & "Your order " &
rec.Order_ID & " shipped, ok." _
, False
.MoveNext
Loop
End With
End If

Set rec = db.OpenRecordset("tbl2_3DayEmail")
If rec.RecordCount > 0 Then
With rec
.MoveFirst
Priority: Do Until .EOF

DoCmd.SendObject acSendNoObject, , , rec.BILLEMAIL, , , "2-
3 Day Priority Email", _
"Dear " & rec.FULLNAME & ":" & vbCrLf & "Your order " &
rec.Order_ID & " shipped, ok." _
, False
.MoveNext
Loop
End With
End If
Set db = Nothing
Set rec = Nothing
Exit Sub
End Sub

And I want to put this in at the end of this code:

Private Sub Command231_Click()
On Error GoTo Err_Command231_Click

Dim stDocName, stLinkCriteria, txtToday, str1stEmail,
strUPS, str2_3Day, strInter As String
Dim strUPSTbl, str2_3Tbl, strInterTbl As String
str1stEmail = "qry1stEmail"
strUPS = "qryUPS1stEmailSent"
str2_3Day = "qry2_3Day1stEmailSent"
strInter = "qryInter1stEmailSent"
strUPSTbl = "qryUPSGroundExport"
str2_3Tbl = "qry2_3DayPriorityExport"
strInterTbl = "qryInternationalExport"

DoCmd.Hourglass True
DoCmd.SetWarnings False

' Append new records to Email Table
DoCmd.OpenQuery str1stEmail, acNormal, acEdit

' Makes Table for UPS 1st Email
DoCmd.OpenQuery strUPSTbl, acNormal, acEdit
' Updates the 1st Email as sent
DoCmd.OpenQuery strUPS, acNormal, acEdit

' Makes Table for 2-3 Day Priority 1st Email
DoCmd.OpenQuery str2_3Tbl, acNormal, acEdit
' Updates the 1st Email as sent
DoCmd.OpenQuery str2_3Day, acNormal, acEdit

' Makes Table for International 1st Email
DoCmd.OpenQuery strInterTbl, acNormal, acEdit
' Updates the 1st Email as sent
DoCmd.OpenQuery strInter, acNormal, acEdit

'stDocName = "frmEmails"
'txtToday = Date - 10
'stLinkCriteria = "[OrderDate]=" & "#" & txtToday & "#"
'DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria

DoCmd.Hourglass False
DoCmd.SetWarnings True

Exit_Command231_Click:
Exit Sub

Err_Command231_Click:
MsgBox Err.Description
DoCmd.Hourglass False
DoCmd.SetWarnings True
Resume Exit_Command231_Click

End Sub
 
G

Guest

Hi Steve,

All you have to do is put the name of the function or sub
in the OnClick event.

Public Sub Button1_Click()
SendEmails
'Name of other Functions or Public Subs
End Sub

This will send the code to the sub and run it.

Once that sub is complete, the code will return to the
OnClick event, so if you want to fire another sub, just
put its name after SendEmails.

Brad
-----Original Message-----
How do I call a module in VBA code in an onclick event.
Here is the Module:

Option Compare Database
Option Explicit

Sub SendEmails(Cancel As Integer)

Set db = CurrentDb()
Set rec = db.OpenRecordset("tblUPSEmail")

If rec.RecordCount > 0 Then
With rec
.MoveFirst
UPS: Do Until .EOF

DoCmd.SendObject acSendNoObject, , ,
rec.BILLEMAIL, , , "UPS Ground Email", _
"Dear " & rec.FULLNAME & ":" & vbCrLf & "Your order " &
rec.Order_ID & " shipped, ok." _
, False
.MoveNext
Loop
End With
End If

Set rec = db.OpenRecordset("tbl2_3DayEmail")
If rec.RecordCount > 0 Then
With rec
.MoveFirst
Priority: Do Until .EOF

DoCmd.SendObject acSendNoObject, , , rec.BILLEMAIL, , , "2-
3 Day Priority Email", _
"Dear " & rec.FULLNAME & ":" & vbCrLf & "Your order " &
rec.Order_ID & " shipped, ok." _
, False
.MoveNext
Loop
End With
End If
Set db = Nothing
Set rec = Nothing
Exit Sub
End Sub

And I want to put this in at the end of this code:

Private Sub Command231_Click()
On Error GoTo Err_Command231_Click

Dim stDocName, stLinkCriteria, txtToday, str1stEmail,
strUPS, str2_3Day, strInter As String
Dim strUPSTbl, str2_3Tbl, strInterTbl As String
str1stEmail = "qry1stEmail"
strUPS = "qryUPS1stEmailSent"
str2_3Day = "qry2_3Day1stEmailSent"
strInter = "qryInter1stEmailSent"
strUPSTbl = "qryUPSGroundExport"
str2_3Tbl = "qry2_3DayPriorityExport"
strInterTbl = "qryInternationalExport"

DoCmd.Hourglass True
DoCmd.SetWarnings False

' Append new records to Email Table
DoCmd.OpenQuery str1stEmail, acNormal, acEdit

' Makes Table for UPS 1st Email
DoCmd.OpenQuery strUPSTbl, acNormal, acEdit
' Updates the 1st Email as sent
DoCmd.OpenQuery strUPS, acNormal, acEdit

' Makes Table for 2-3 Day Priority 1st Email
DoCmd.OpenQuery str2_3Tbl, acNormal, acEdit
' Updates the 1st Email as sent
DoCmd.OpenQuery str2_3Day, acNormal, acEdit

' Makes Table for International 1st Email
DoCmd.OpenQuery strInterTbl, acNormal, acEdit
' Updates the 1st Email as sent
DoCmd.OpenQuery strInter, acNormal, acEdit

'stDocName = "frmEmails"
'txtToday = Date - 10
'stLinkCriteria = "[OrderDate]=" & "#" & txtToday & "#"
'DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria

DoCmd.Hourglass False
DoCmd.SetWarnings True

Exit_Command231_Click:
Exit Sub

Err_Command231_Click:
MsgBox Err.Description
DoCmd.Hourglass False
DoCmd.SetWarnings True
Resume Exit_Command231_Click

End Sub

.
Click to expand...
 
S

Steven M. Britton

I did just as was instructed and I get "Compile Error:
Expected variable or procedure, not module"

What is missing? - 1st the buttons code - 2nd the Module
code:

Public Sub Command231_Click()
On Error GoTo Err_Command231_Click

Dim stDocName, stLinkCriteria, txtToday, str1stEmail,
strUPS, str2_3Day, strInter As String
Dim strUPSTbl, str2_3Tbl, strInterTbl As String
str1stEmail = "qry1stEmail"
strUPS = "qryUPS1stEmailSent"
str2_3Day = "qry2_3Day1stEmailSent"
strInter = "qryInter1stEmailSent"
strUPSTbl = "qryUPSGroundExport"
str2_3Tbl = "qry2_3DayPriorityExport"
strInterTbl = "qryInternationalExport"

DoCmd.Hourglass True
DoCmd.SetWarnings False

' Append new records to Email Table
DoCmd.OpenQuery str1stEmail, acNormal, acEdit

' Makes Table for UPS 1st Email
DoCmd.OpenQuery strUPSTbl, acNormal, acEdit
' Updates the 1st Email as sent
DoCmd.OpenQuery strUPS, acNormal, acEdit

' Makes Table for 2-3 Day Priority 1st Email
DoCmd.OpenQuery str2_3Tbl, acNormal, acEdit
' Updates the 1st Email as sent
DoCmd.OpenQuery str2_3Day, acNormal, acEdit

' Makes Table for International 1st Email
DoCmd.OpenQuery strInterTbl, acNormal, acEdit
' Updates the 1st Email as sent
DoCmd.OpenQuery strInter, acNormal, acEdit

'stDocName = "frmEmails"
'txtToday = Date - 10
'stLinkCriteria = "[OrderDate]=" & "#" & txtToday & "#"
'DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria

SendEmails

DoCmd.Hourglass False
DoCmd.SetWarnings True

Exit_Command231_Click:
Exit Sub

Err_Command231_Click:
MsgBox Err.Description
DoCmd.Hourglass False
DoCmd.SetWarnings True
Resume Exit_Command231_Click

End Sub

Option Compare Database
Option Explicit

Sub SendEmails(Cancel As Integer)

Set db = CurrentDb()
Set rec = db.OpenRecordset("tblUPSEmail")

If rec.RecordCount > 0 Then
With rec
.MoveFirst
UPS: Do Until .EOF

DoCmd.SendObject acSendNoObject, , ,
rec.BILLEMAIL, , , "UPS Ground Email", _
"Dear " & rec.FULLNAME & ":" & vbCrLf & "Your order " &
rec.Order_ID & " shipped, ok." _
, False
.MoveNext
Loop
End With
End If

Set rec = db.OpenRecordset("tbl2_3DayEmail")
If rec.RecordCount > 0 Then
With rec
.MoveFirst
Priority: Do Until .EOF

DoCmd.SendObject acSendNoObject, , , rec.BILLEMAIL, , , "2-
3 Day Priority Email", _
"Dear " & rec.FULLNAME & ":" & vbCrLf & "Your order " &
rec.Order_ID & " shipped, ok." _
, False
.MoveNext
Loop
End With
End If
Set db = Nothing
Set rec = Nothing
Exit Sub
End Sub


-----Original Message-----
Hi Steve,

All you have to do is put the name of the function or sub
in the OnClick event.

Public Sub Button1_Click()
SendEmails
'Name of other Functions or Public Subs
End Sub

This will send the code to the sub and run it.

Once that sub is complete, the code will return to the
OnClick event, so if you want to fire another sub, just
put its name after SendEmails.

Brad
-----Original Message-----
How do I call a module in VBA code in an onclick event.
Here is the Module:

Option Compare Database
Option Explicit

Sub SendEmails(Cancel As Integer)

Set db = CurrentDb()
Set rec = db.OpenRecordset("tblUPSEmail")

If rec.RecordCount > 0 Then
With rec
.MoveFirst
UPS: Do Until .EOF

DoCmd.SendObject acSendNoObject, , ,
rec.BILLEMAIL, , , "UPS Ground Email", _
"Dear " & rec.FULLNAME & ":" & vbCrLf & "Your order " &
rec.Order_ID & " shipped, ok." _
, False
.MoveNext
Loop
End With
End If

Set rec = db.OpenRecordset("tbl2_3DayEmail")
If rec.RecordCount > 0 Then
With rec
.MoveFirst
Priority: Do Until .EOF

DoCmd.SendObject acSendNoObject, , , rec.BILLEMAIL, , , "2-
3 Day Priority Email", _
"Dear " & rec.FULLNAME & ":" & vbCrLf & "Your order " &
rec.Order_ID & " shipped, ok." _
, False
.MoveNext
Loop
End With
End If
Set db = Nothing
Set rec = Nothing
Exit Sub
End Sub

And I want to put this in at the end of this code:

Private Sub Command231_Click()
On Error GoTo Err_Command231_Click

Dim stDocName, stLinkCriteria, txtToday, str1stEmail,
strUPS, str2_3Day, strInter As String
Dim strUPSTbl, str2_3Tbl, strInterTbl As String
str1stEmail = "qry1stEmail"
strUPS = "qryUPS1stEmailSent"
str2_3Day = "qry2_3Day1stEmailSent"
strInter = "qryInter1stEmailSent"
strUPSTbl = "qryUPSGroundExport"
str2_3Tbl = "qry2_3DayPriorityExport"
strInterTbl = "qryInternationalExport"

DoCmd.Hourglass True
DoCmd.SetWarnings False

' Append new records to Email Table
DoCmd.OpenQuery str1stEmail, acNormal, acEdit

' Makes Table for UPS 1st Email
DoCmd.OpenQuery strUPSTbl, acNormal, acEdit
' Updates the 1st Email as sent
DoCmd.OpenQuery strUPS, acNormal, acEdit

' Makes Table for 2-3 Day Priority 1st Email
DoCmd.OpenQuery str2_3Tbl, acNormal, acEdit
' Updates the 1st Email as sent
DoCmd.OpenQuery str2_3Day, acNormal, acEdit

' Makes Table for International 1st Email
DoCmd.OpenQuery strInterTbl, acNormal, acEdit
' Updates the 1st Email as sent
DoCmd.OpenQuery strInter, acNormal, acEdit

'stDocName = "frmEmails"
'txtToday = Date - 10
'stLinkCriteria = "[OrderDate]=" & "#" & txtToday & "#"
'DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria

DoCmd.Hourglass False
DoCmd.SetWarnings True

Exit_Command231_Click:
Exit Sub

Err_Command231_Click:
MsgBox Err.Description
DoCmd.Hourglass False
DoCmd.SetWarnings True
Resume Exit_Command231_Click

End Sub

.
Click to expand...
.
Click to expand...
 
D

Dirk Goldgar

Steven M. Britton said:
I did just as was instructed and I get "Compile Error:
Expected variable or procedure, not module"

What is missing? - 1st the buttons code - 2nd the Module
code:

Public Sub Command231_Click()
On Error GoTo Err_Command231_Click

Dim stDocName, stLinkCriteria, txtToday, str1stEmail,
strUPS, str2_3Day, strInter As String
Dim strUPSTbl, str2_3Tbl, strInterTbl As String
str1stEmail = "qry1stEmail"
strUPS = "qryUPS1stEmailSent"
str2_3Day = "qry2_3Day1stEmailSent"
strInter = "qryInter1stEmailSent"
strUPSTbl = "qryUPSGroundExport"
str2_3Tbl = "qry2_3DayPriorityExport"
strInterTbl = "qryInternationalExport"

DoCmd.Hourglass True
DoCmd.SetWarnings False

' Append new records to Email Table
DoCmd.OpenQuery str1stEmail, acNormal, acEdit

' Makes Table for UPS 1st Email
DoCmd.OpenQuery strUPSTbl, acNormal, acEdit
' Updates the 1st Email as sent
DoCmd.OpenQuery strUPS, acNormal, acEdit

' Makes Table for 2-3 Day Priority 1st Email
DoCmd.OpenQuery str2_3Tbl, acNormal, acEdit
' Updates the 1st Email as sent
DoCmd.OpenQuery str2_3Day, acNormal, acEdit

' Makes Table for International 1st Email
DoCmd.OpenQuery strInterTbl, acNormal, acEdit
' Updates the 1st Email as sent
DoCmd.OpenQuery strInter, acNormal, acEdit

'stDocName = "frmEmails"
'txtToday = Date - 10
'stLinkCriteria = "[OrderDate]=" & "#" & txtToday & "#"
'DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria

SendEmails

DoCmd.Hourglass False
DoCmd.SetWarnings True

Exit_Command231_Click:
Exit Sub

Err_Command231_Click:
MsgBox Err.Description
DoCmd.Hourglass False
DoCmd.SetWarnings True
Resume Exit_Command231_Click

End Sub

Option Compare Database
Option Explicit

Sub SendEmails(Cancel As Integer)

Set db = CurrentDb()
Set rec = db.OpenRecordset("tblUPSEmail")

If rec.RecordCount > 0 Then
With rec
.MoveFirst
UPS: Do Until .EOF

DoCmd.SendObject acSendNoObject, , ,
rec.BILLEMAIL, , , "UPS Ground Email", _
"Dear " & rec.FULLNAME & ":" & vbCrLf & "Your order " &
rec.Order_ID & " shipped, ok." _
, False
.MoveNext
Loop
End With
End If

Set rec = db.OpenRecordset("tbl2_3DayEmail")
If rec.RecordCount > 0 Then
With rec
.MoveFirst
Priority: Do Until .EOF

DoCmd.SendObject acSendNoObject, , , rec.BILLEMAIL, , , "2-
3 Day Priority Email", _
"Dear " & rec.FULLNAME & ":" & vbCrLf & "Your order " &
rec.Order_ID & " shipped, ok." _
, False
.MoveNext
Loop
End With
End If
Set db = Nothing
Set rec = Nothing
Exit Sub
End Sub


-----Original Message-----
Hi Steve,

All you have to do is put the name of the function or sub
in the OnClick event.

Public Sub Button1_Click()
SendEmails
'Name of other Functions or Public Subs
End Sub

This will send the code to the sub and run it.

Once that sub is complete, the code will return to the
OnClick event, so if you want to fire another sub, just
put its name after SendEmails.

Brad
-----Original Message-----
How do I call a module in VBA code in an onclick event.
Here is the Module:

Option Compare Database
Option Explicit

Sub SendEmails(Cancel As Integer)

Set db = CurrentDb()
Set rec = db.OpenRecordset("tblUPSEmail")

If rec.RecordCount > 0 Then
With rec
.MoveFirst
UPS: Do Until .EOF

DoCmd.SendObject acSendNoObject, , ,
rec.BILLEMAIL, , , "UPS Ground Email", _
"Dear " & rec.FULLNAME & ":" & vbCrLf & "Your order " &
rec.Order_ID & " shipped, ok." _
, False
.MoveNext
Loop
End With
End If

Set rec = db.OpenRecordset("tbl2_3DayEmail")
If rec.RecordCount > 0 Then
With rec
.MoveFirst
Priority: Do Until .EOF

DoCmd.SendObject acSendNoObject, , , rec.BILLEMAIL, , , "2-
3 Day Priority Email", _
"Dear " & rec.FULLNAME & ":" & vbCrLf & "Your order " &
rec.Order_ID & " shipped, ok." _
, False
.MoveNext
Loop
End With
End If
Set db = Nothing
Set rec = Nothing
Exit Sub
End Sub

And I want to put this in at the end of this code:

Private Sub Command231_Click()
On Error GoTo Err_Command231_Click

Dim stDocName, stLinkCriteria, txtToday, str1stEmail,
strUPS, str2_3Day, strInter As String
Dim strUPSTbl, str2_3Tbl, strInterTbl As String
str1stEmail = "qry1stEmail"
strUPS = "qryUPS1stEmailSent"
str2_3Day = "qry2_3Day1stEmailSent"
strInter = "qryInter1stEmailSent"
strUPSTbl = "qryUPSGroundExport"
str2_3Tbl = "qry2_3DayPriorityExport"
strInterTbl = "qryInternationalExport"

DoCmd.Hourglass True
DoCmd.SetWarnings False

' Append new records to Email Table
DoCmd.OpenQuery str1stEmail, acNormal, acEdit

' Makes Table for UPS 1st Email
DoCmd.OpenQuery strUPSTbl, acNormal, acEdit
' Updates the 1st Email as sent
DoCmd.OpenQuery strUPS, acNormal, acEdit

' Makes Table for 2-3 Day Priority 1st Email
DoCmd.OpenQuery str2_3Tbl, acNormal, acEdit
' Updates the 1st Email as sent
DoCmd.OpenQuery str2_3Day, acNormal, acEdit

' Makes Table for International 1st Email
DoCmd.OpenQuery strInterTbl, acNormal, acEdit
' Updates the 1st Email as sent
DoCmd.OpenQuery strInter, acNormal, acEdit

'stDocName = "frmEmails"
'txtToday = Date - 10
'stLinkCriteria = "[OrderDate]=" & "#" & txtToday & "#"
'DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria

DoCmd.Hourglass False
DoCmd.SetWarnings True

Exit_Command231_Click:
Exit Sub

Err_Command231_Click:
MsgBox Err.Description
DoCmd.Hourglass False
DoCmd.SetWarnings True
Resume Exit_Command231_Click

End Sub
Click to expand...
Click to expand...
Click to expand...

The module itself, which is just the container for the SendEmail
procedure, must not be named "SendEmails". If you've named it that,
change it to "modSendEmails", or "basSendEmails", or some other name.

Also, your SubSendEmails appears to have been defined to require a
Cancel argument:
Sub SendEmails(Cancel As Integer)
Click to expand...

But your call to the sub doesn't specify that argument. My guess is
that this argument doesn't really serve a purpose; if so, change the
sub's definition to

Sub SendEmails()
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Garbage in a string variable loaded from a field 2
how to call a value from an opened query 3
Word MailMerge to E-Mail 0
running a query based on a record's ID from a form 4
Closing Excel files from Access and/or quitting Excel from Access 1

Top