Putting Select Query Results into an Access Table

[Carol W.]

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.

[Carol W.]

Here is the original code that has worked for years:

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

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.

Thanks

[HansV]

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.

[Carol W.]

Hans,

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 "*@*"));

I've also attached 2 screenshots of qry-Group email addresses in design mode.

query design 1.jpg

query design 2.jpg

Thanks.

[HansV]

Add the following declarations at the beginning of the procedure:

Code: Select all

  Dim strGroup As String
  Dim strSQL As String

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:

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

(db is the variable already defined in your code.)

[Carol W.]

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