G
Guest
I have some code that opens a couple of make-table querys, and then executes
some more code. I would like to insert the line 'DoCmd.DeleteObject acTable,
"Table to Delete"' to delete those tables that are created, but it doesn't
work. The tables don't delete. I'm assuming it has something to do with the
loop, but I can't figure out where to put the deleteobject code to make it
work. Here's the code that I have now:
Private Sub btnEmailVolunteers_Click()
DoCmd.OpenQuery "Community Project1"
DoCmd.Close acQuery, "Community Project1"
Dim strDocName As String
strDocName = "queemailtable"
DoCmd.OpenQuery strDocName
On Error GoTo ErrHandle
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strAddress As String 'this creates the Email addresses
Dim strTo As String 'this is needed to populate the TO block on
the EMail form
Dim strCC As String 'this is needed to populate the CC block on
the EMail form
Dim strBCC As String 'this is needed to populate the BCC block on
the EMail form
Dim strSubj As String 'this is needed to populate the SUBJ block on
the EMail form
Dim strText As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblemailtable", dbOpenSnapshot)
If rst.BOF = True And rst.EOF = True Then
MsgBox "There must be an error in the query as there are no EMail
Addresses listed!"
GoTo ErrExit
End If
With rst
.MoveFirst 'go to the first record
strAddress = .Fields("EmailName").Value
strBCC = strAddress
.MoveNext
Do While .EOF = False
strAddress = .Fields("EmailName").Value
strBCC = strBCC & "; " & strAddress
.MoveNext
Loop
End With
strTo = Nz(DLookup("[EmailAddr]", "[tblEmailSetup]"), "")
strCC = Nz(DLookup("[AltEmailAddr]", "[tblEmailSetup]"), "")
strBCC = strBCC
strSubj = "Volunteer Opportunities"
strText = Chr$(13) & Chr$(13) & Nz(DLookup("[EmailSig]",
"[tblEmailSetup]"), "")
DoCmd.SendObject acSendNoObject, , , strTo, strCC, strBCC, strSubj,
strText, True
DoCmd.DeleteObject acTable, "Volunteers"
DoCmd.DeleteObject acTable, "tblemailtable"
ErrExit:
Exit Sub
ErrHandle:
MsgBox Err.Description
Resume ErrExit
Resume
End Sub
some more code. I would like to insert the line 'DoCmd.DeleteObject acTable,
"Table to Delete"' to delete those tables that are created, but it doesn't
work. The tables don't delete. I'm assuming it has something to do with the
loop, but I can't figure out where to put the deleteobject code to make it
work. Here's the code that I have now:
Private Sub btnEmailVolunteers_Click()
DoCmd.OpenQuery "Community Project1"
DoCmd.Close acQuery, "Community Project1"
Dim strDocName As String
strDocName = "queemailtable"
DoCmd.OpenQuery strDocName
On Error GoTo ErrHandle
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strAddress As String 'this creates the Email addresses
Dim strTo As String 'this is needed to populate the TO block on
the EMail form
Dim strCC As String 'this is needed to populate the CC block on
the EMail form
Dim strBCC As String 'this is needed to populate the BCC block on
the EMail form
Dim strSubj As String 'this is needed to populate the SUBJ block on
the EMail form
Dim strText As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblemailtable", dbOpenSnapshot)
If rst.BOF = True And rst.EOF = True Then
MsgBox "There must be an error in the query as there are no EMail
Addresses listed!"
GoTo ErrExit
End If
With rst
.MoveFirst 'go to the first record
strAddress = .Fields("EmailName").Value
strBCC = strAddress
.MoveNext
Do While .EOF = False
strAddress = .Fields("EmailName").Value
strBCC = strBCC & "; " & strAddress
.MoveNext
Loop
End With
strTo = Nz(DLookup("[EmailAddr]", "[tblEmailSetup]"), "")
strCC = Nz(DLookup("[AltEmailAddr]", "[tblEmailSetup]"), "")
strBCC = strBCC
strSubj = "Volunteer Opportunities"
strText = Chr$(13) & Chr$(13) & Nz(DLookup("[EmailSig]",
"[tblEmailSetup]"), "")
DoCmd.SendObject acSendNoObject, , , strTo, strCC, strBCC, strSubj,
strText, True
DoCmd.DeleteObject acTable, "Volunteers"
DoCmd.DeleteObject acTable, "tblemailtable"
ErrExit:
Exit Sub
ErrHandle:
MsgBox Err.Description
Resume ErrExit
Resume
End Sub