How to build a string from a query and use it to append a record to a table

[MvP14]

I'm stuck again.

Here's what i'm trying to do:

I have a table with adresses with multiple fields (ia emailadress and a check box)

I have a table with emailgroups with two fields: groupname and groupmembers. The field groupmembers is basically a string of emailadresses seperated by a semicolon.

In a form with the table with adresses as a source, I'm able to check and uncheck adresses.

Now what I wish to do, is build an email group from the checked adresses and add it as a new record to the table with emailgroups, asking the user for a name for the new group.

I put a button on the form to do that.

Until now, I have this code:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strName As String
Dim strQuery As String
Dim rs2 As DAO.Recordset

Set db = CurrentDb

Set rs2 = db.OpenRecordset("SELECT Name From MSysObjects WHERE Name = 'Query2';")
If rs2.RecordCount = 1 Then
DoCmd.DeleteObject acQuery, "Query2"
End If
rs2.Close

strName = "Query2"
strQuery = "SELECT TableAdress.Checked, TableAdress.EmailAdres FROM Query1 WHERE (((TableAdress.Checked)=Yes) AND ((Table.EmailAdres) Is Not Null));"

Set qdf = db.CreateQueryDef(strName, strQuery)
qdf.Close

This creates a query with the required emailadresses.

Any suggestions on how to proceed from here?

I figure I have to build a string from the records of Query2 and then run some kind of append query, but that is way over my head.

Does anyone have any suggestions?

Thanks in advance!

 

[MvP14]

Did I ask an impossible question?

Does anyone think what I try to do is even possible in Access?

Can I do it some other way?

 

[gecko_1]

Try

strName = "Query2"
strQuery = "SELECT TableAdress.Checked, TableAdress.EmailAdres FROM Query1 WHERE (((TableAdress.Checked)=Yes) AND ((Table.EmailAdres) Is Not Null));"

Set qdf = db.CreateQueryDef(strName, strQuery)
group = inputbox("Enter the group name.")
while not qdf.eof
  sql = "INSERT INTO emailgroups VALUES('" & group & "', '" & qdf.fields("EmailAdres") & "'")
  db.execute sql
  qdf.movenext
wend

qdf.Close

Im not sure how QueryDef works, so if that is posiable or not im not sure.
But you could make it work with a recordset

 

[modest]

of course it's possible... however, instead of adding rows and having yes/no fields, I would make a new table for each group.. this would make it easier to add and delete members in the future... as well as save much more space, should your member count grow.

do it all with

create table, drop table, insert into, and update queries

and of course some DAO

 

[MvP14]

One problem down, Two problems left

Thanks for the replies.

Qecko 1: the inputbox works, so I'm getting closer.
Modest: thanks for the reply but that would not work for me, cause I use the field with the emailstrings to send emails to the group. I have a form with a listbox that allows me to modify the members if minor modifications are required.

Basically I have to problems left:
1. How to build a string containing all the values of a certain field in a query

Eg. Result of query =

Checked EmailAdres

Yes a.b@c.com
Yes d.f@g.com
Yes h.i@j.com


From this I want to make a string that looks like this:
"a.b@c.com; d.f@g.com; h.i@j.com"

2. Once I have the string, I want to define an append query that adds a record to EmailGroupsTable, using the the string from the inputbox as groupname, and the string from the query as group members.


I'm practically a VBA illiterate, so any help is welcome and very much appreciated!

 

[MvP14]

I still haven't been able to figure it out!

Any suggestions are still very much appreciated.

I'll keep you posted once I find a solution.

 

[MvP14]

I'm quite positive there's a more efficient way to do it, but with my limited VBA knowledge, this ended up working for me (Querytwoform is a Form with Query2 as a source; it is invisible when it's opened; MultiEmailForm is a form with the table with emailgroups as source):

Sub MaakQuery2()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strQueryName As String
Dim strQueryDefinition As String
Dim rs2 As DAO.Recordset

Set db = CurrentDb

Set rs2 = db.OpenRecordset("SELECT Name From MSysObjects WHERE Name = 'Query2';")
If rs2.RecordCount = 1 Then
DoCmd.DeleteObject acQuery, "Query2"
End If
rs2.Close

strQueryName = "Query2"
strQueryDefinition = "SELECT Query1.Check, Query1.EmailAdres FROM Query1 WHERE (((Query1.Check)=Yes) AND ((Query1.EmailAdres) Is Not Null));"

Set qdf = db.CreateQueryDef(strQueryName, strQueryDefinition)

qdf.Close

End Sub

Private Sub Command38_Click()
On Error GoTo Err_Command38_Click

Dim f As Form
Dim nRecords As Integer
Dim strGroupName As String
Dim strEmailAdresses As String
Dim i As Integer
Dim result As VbMsgBoxResult


Call MaakQuery2

nRecords = DCount("*", "Query2") - 1

DoCmd.OpenForm "QuerytwoForm", , , , , acWindowNormal
Set f = Forms!QuerytwoForm
For i = 0 To nRecords Step 1
strEmailAdresses = strEmailAdresses & f.EmailAdres.Value & ";"
DoCmd.GoToRecord , , acNext
Next i
DoCmd.Close acForm, "QuerytwoForm", acSaveNo

strGroupName = InputBox("What name should the new Emailgroup get?", "Name new Emailgroup")

DoCmd.OpenForm "MultiEmailForm"
DoCmd.GoToRecord , , acNewRec
Forms!MultiEmailForm.[Name group] = strGroupName
Forms!MultiEmailForm.[List Groupmembers] = strEmailAdresses
Forms!MultiEmailForm.Refresh

result = MsgBox("Would you like to send an email to the new group now?", vbYesNo)
Select Case result
Case vbYes:
DoCmd.SendObject acSendNoObject, , acFormatRTF, Forms!MultiEmailForm.[List Groupmembers], , , , , True
DoCmd.Close
Case vbNo:
DoCmd.Close

Case Else
DoCmd.Close

End Select

Exit_Command38_Click:
Exit Sub

Err_Command38_Click:
MsgBox Err.Description
Resume Exit_Command38_Click

End Sub