Loop through records to create an e-mailing list

[Islwyn]

I have a form which is used as the basis of a query to choose which people a user wants to e-mail.

Once a user has selected various options on the form (used as the basis of the query) I want them to click a button which makes the first form invisible and opens a new form containing a text box box with all the relevant e-mail addresses in. (I then want a copy button so they can copy all those e-mail addresses and paste into the BCC recipients box of whatever e-mail program they are using)

I have written the below code which I hoped would work but does not. I get an error saying there are "Too few parameters. Expected 8" referring to the "Set EmailsList = Db.OpenRecordset("AddressMailList")" Line. Yet the name of the query referred to ("AddressMailList") is definitely correct and everything I read suggests that the number of parameters is correct.

I would be grateful for any suggestions as to where I am going wrong. Also, I have no idea whether the code lower works as it does not get that far.

Many thanks in advance for any help.


Private Sub Form_Open(Cancel As Integer)
Dim Db As DAO.Database
Dim EmailsList As DAO.Recordset
Set Db = CurrentDb()
' "AddressMailList" is the name of the query I want to loop through
Set EmailsList = Db.OpenRecordset("AddressMailList")

Dim Emails As String

' Now loop through values in the query and add e-mail addresses to the
' string "Emails" separated by semi-colons:

Do While Not EmailsList.EOF
Emails = Emails + EmailsList![EmailAddress]
Emails = Emails + "; "

EmailsList.MoveNext
Loop

' Now enter the string "Emails" into the textbox [EmailList1]
[EmailList1] = Emails

End Sub

 

[carl6885]

Hi

I guess you have declared - AddressMailList as I can not see it above....

Also have you tried shortening the variable name? to something like sqlAddMail.

I also can not see how you want to open the recordset which is important. The most common is dbOpenDynaset as below.

I have had a problem like this before and shortening the variable name to pass into CurrentDb.OpenRecordset(sql,dbOpendynaset) worked!

Worth a try maybe....

 

[Islwyn]

Thanks, I'll give it a try.

I have tried dbOpenDynaset but get the same problem. I see that others have had the same problem when there is a mistake in the SQL - for example when it referrs to a record which does not exists - but when I view the data in Query view it is fine and I cannot see a problem with the SQL - unless the method is not appropriate for using SQL which refers to data contained in an open form?

Does the loop procedure lower down make sense?

Thanks

 

[carl6885]

Ok. The loop looks good to me I would personally use Do Until but I guess its personal preference.

I just noticed you have " " around AddressMailList, is this how it is in your code? You wouldn't need it if that is a variable.

Thanks

Carl

 

[carl6885]

I've just thought is the query a saved query. In design view and your referencing that??

I'm not 100% certain but not sure if that only works with querydef cmd. I had an issue so I declared a string and assigned to it the SQL statement and passed that to record set which then worked....

 

[Islwyn]

It's definitely a problem with the select query I was basing the code on so you may be right.

I've got the general approach to work now by creating a temporary table then using the query to insert its values into the table and referring to the table in the above code, rather than basing the code on the query itself. A bit long-winded but at least it now works, and it may work better in the long run as I probably need a few different queries to run based upon the form.

I also changed the relevant code to the below as the null values were causing a problem:

Do While Not EmailsList.EOF
If Not IsNull(EmailsList!EmailAddress.Value) Or _
EmailsList!EmailAddress.Value <> "" Then
Emails = Emails + EmailsList!EmailAddress.Value
Emails = Emails + "; "
End If
EmailsList.MoveNext

Loop
[EmailList1] = Emails

Thanks again for your help.