I have a query called qry-Group email addresses that contains a text field parameter called strGroup. This query runs perfectly. The parameter comes from a user selection from a List Box on a form. This all runs well and has run well for several years.
My task, now, is simply to convert the results of that query into a table for use in a mass mailing program we purchased. I tried copying the qry-Group email addresses query and converting the copy into a make table query but I can't get the selection from the List Box into the make table query as a parameter.
What would be the best way to simply get the results of qry-Group email addresses into a table?
Thanks, in advance.
Putting Select Query Results into an Access Table
-
- 3StarLounger
- Posts: 390
- Joined: 26 Jan 2010, 16:02
- Location: Las Vegas, NV
-
- 3StarLounger
- Posts: 390
- Joined: 26 Jan 2010, 16:02
- Location: Las Vegas, NV
Re: Putting Select Query Results into an Access Table
Here is the original code that has worked for years:
Thanks
I'll be removing all the Outlook code and just closing the mdb and opening the mass mailing program once I can get my issue resolved.Public Sub SendGrpMessage()
Dim strBcc As String
Dim strmsg As String
Dim dbsMember As Database
Dim rstMembers As Recordset
'Set dbsmember = CurrentDb()
'Set rstmembers = _
' dbsmember.OpenRecordset("qry-Group email addresses", dbOpenDynaset)
Set db = CurrentDb
Set qdf = db.QueryDefs("qry-Group email addresses")
qdf(0) = [Form_frmEntryScreen]!List18.Column(1)
Forms![frmEntryScreen].strerrmsg = ""
Set rs = qdf.OpenRecordset(dbOpenDynaset)
On Error GoTo err1
strBcc = ""
rs.MoveFirst
Do While Not rs.EOF
With rs
If Not IsNull(!strE_MAIL) Then
strBcc = strBcc & ![strE_MAIL] & ";"
End If
End With
rs.MoveNext
Loop
Set rs = Nothing
'strmsg = "<p><font face=""Arial"" size=""3"">The message below has been sent by the ALLV Broadcast system. <br><b>Please do not reply to this message.</b> Thank you. <br>______________________________________________________________________</font></p>"
strmsg = "<p><font face=""Arial"" size=""3"">The message below has been sent by the ALLV Broadcast system. <br><b>Please do not reply to this message. Replies to this message will be sent back to the ALLV address and will not reach the intended recipient. <u>To contact the original sender, forward this message to him/her and add your reply to that message.</u></b><br> <br>Thank you. <br>______________________________________________________________________</font></p>"
'DoCmd.SendObject acSendNoObject, , , , , strbcc, "Enter Message Subject Here", strmsg, yes
'DoCmd.SendObject acSendNoObject, , htm, , , strbcc, "Enter subject here", , True, "C:Documents and Settings\Administrator\Desktop\email template.htm"
Dim strEmail, strSubject As String, strBody As String
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
Set objOutlook = CreateObject("Outlook.application", "localhost")
Set objEmail = objOutlook.CreateItem(olMailItem)
'strEmail = " a @ b.c"
'strBody = "Make this <B>bold</B> and <BR>add a line."
'strSubject = "Subject"
With objEmail
.BCC = strBcc
.Subject = "Enter Message Subject here"
.HTMLBody = strmsg
'.Send 'Will cause warning message
.Display
End With
Set objEmail = Nothing
Exit Sub
err1:
Forms![frmEntryScreen].strerrmsg = "No members found in this group. No email message will be generated."
Exit Sub
End Sub
Thanks
Carol W.
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Putting Select Query Results into an Access Table
You could assemble the SQL string for the make-table query in code, then execute it.
If you want help with that, we'll need to know details of qry-Group email addresses, for example its SQL string.
If you want help with that, we'll need to know details of qry-Group email addresses, for example its SQL string.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 390
- Joined: 26 Jan 2010, 16:02
- Location: Las Vegas, NV
Re: Putting Select Query Results into an Access Table
Hans,
I'll take you up on your offer. Here is the SQL for qry-Group email addresses:
I've also attached 2 screenshots of qry-Group email addresses in design mode.
Thanks.
I'll take you up on your offer. Here is the SQL for qry-Group email addresses:
Code: Select all
PARAMETERS strGroup Text ( 255 );
SELECT tblMembers.strClassification, tblMembers.ysnOSB, [strGroup] AS Expr1, tblMembers.ysnTS, tblMembers.ysnCommOutreac, tblMembers.ysnChairmen, tblMembers.ysnBD, tblMembers.strFirstName, tblMembers.strLastName, tblMembers.strE_MAIL
FROM tblMembers
WHERE (((tblMembers.strClassification)<>"Resigned" And (tblMembers.strClassification)<>"Deceased" And (tblMembers.strClassification) Not Like "*Dropped" And (tblMembers.strClassification)<>"Dues Not Paid") AND ((tblMembers.ysnOSB)=True) AND (([strGroup])="OSB") AND ((tblMembers.strE_MAIL) Is Not Null And (tblMembers.strE_MAIL) Like "*@*")) OR (((tblMembers.strClassification)<>"Resigned" And (tblMembers.strClassification)<>"Deceased" And (tblMembers.strClassification) Not Like "*Dropped" And (tblMembers.strClassification)<>"Dues Not Paid") AND (([strGroup])="TS") AND ((tblMembers.ysnTS)=True) AND ((tblMembers.strE_MAIL) Is Not Null And (tblMembers.strE_MAIL) Like "*@*")) OR (((tblMembers.strClassification)<>"Resigned" And (tblMembers.strClassification)<>"Deceased" And (tblMembers.strClassification) Not Like "*Dropped" And (tblMembers.strClassification)<>"Dues Not Paid") AND (([strGroup])="CO") AND ((tblMembers.ysnCommOutreac)=True) AND ((tblMembers.strE_MAIL) Is Not Null And (tblMembers.strE_MAIL) Like "*@*")) OR (((tblMembers.strClassification)<>"Resigned" And (tblMembers.strClassification)<>"Deceased" And (tblMembers.strClassification) Not Like "*Dropped" And (tblMembers.strClassification)<>"Dues Not Paid") AND (([strGroup])="Chair") AND ((tblMembers.ysnChairmen)=True) AND ((tblMembers.strE_MAIL) Is Not Null And (tblMembers.strE_MAIL) Like "*@*")) OR (((tblMembers.strClassification)<>"Resigned" And (tblMembers.strClassification)<>"Deceased" And (tblMembers.strClassification) Not Like "*Dropped" And (tblMembers.strClassification)<>"Dues Not Paid") AND (([strGroup])="BD") AND ((tblMembers.ysnBD)=True) AND ((tblMembers.strE_MAIL) Is Not Null And (tblMembers.strE_MAIL) Like "*@*"));
You do not have the required permissions to view the files attached to this post.
Carol W.
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Putting Select Query Results into an Access Table
Add the following declarations at the beginning of the procedure:
Here is the code that assembles the SQL string and executes it. The resulting table is named tblTemp; you can change this name in the code if you like:
(db is the variable already defined in your code.)
Code: Select all
Dim strGroup As String
Dim strSQL As String
Code: Select all
strGroup = Forms!frmEntryScreen!List18.Column(1)
strSQL = "SELECT strClassification, ysnOSB, '" & strGroup & "' AS Expr1, ysnTS, " & _
"ysnCommOutreac, ysnChairmen, ysnBD, strFirstName, strLastName, strE_MAIL INTO tblTemp " & _
"FROM tblMembers WHERE strClassification<>'Resigned' And strClassification<>'Deceased' And " & _
"strClassification Not Like '*Dropped' And strClassification<>'Dues Not Paid' AND " & _
"strE_MAIL Like '*@*' AND ('" & strGroup & "'='OSB' AND ysnOSB=True OR '" & strGroup & _
"'='TS' AND ysnTS=True OR '" & strGroup & "'='CO' AND ysnCommOutreac=True OR '" & strGroup & _
"'='Chair' AND ysnChairmen=True OR '" & strGroup & "'='BD' AND ysnBD=True)"
db.Execute strSQL, dbFailOnError
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 390
- Joined: 26 Jan 2010, 16:02
- Location: Las Vegas, NV
Re: Putting Select Query Results into an Access Table
Perfect! All I had to do was add a delete table statement. Here is the final code:
Code: Select all
Public Sub SendGrpMessage()
Dim strGroup As String
Dim strSQL As String
Dim db As Database
Set db = CurrentDb
DoCmd.DeleteObject acTable, "tblgrpemails"
strGroup = Forms!frmEntryScreen!List18.Column(1)
strSQL = "SELECT strClassification, ysnOSB, '" & strGroup & "' AS Expr1, ysnTS, " & _
"ysnCommOutreac, ysnChairmen, ysnBD, strFirstName, strLastName, strE_MAIL INTO tblgrpemails " & _
"FROM tblMembers WHERE strClassification<>'Resigned' And strClassification<>'Deceased' And " & _
"strClassification Not Like '*Dropped' And strClassification<>'Dues Not Paid' AND " & _
"strE_MAIL Like '*@*' AND ('" & strGroup & "'='OSB' AND ysnOSB=True OR '" & strGroup & _
"'='TS' AND ysnTS=True OR '" & strGroup & "'='CO' AND ysnCommOutreac=True OR '" & strGroup & _
"'='Chair' AND ysnChairmen=True OR '" & strGroup & "'='BD' AND ysnBD=True)"
db.Execute strSQL, dbFailOnError
MsgBox "This function is now being performed in GroupMail. All information has been prepared for GroupMail. The Membership database will now close and GroupMail will open automatically."
Shell "C:\Program Files\GroupMail 5\GMMain.exe", vbMaximizedFocus
Quit
End Sub
Carol W.