ReDim an array

[TKnight]

Hi, I've written some code that sends an e-mail to a number of recipients from a recordset. The recordset varies in number and I got some advice on here that I needed to Redim the recipient array so that it could house the right number of addresses each time the code is run.

The code i've got that surrounds the array is:

Set ToRecRst = dbs.OpenRecordset("SELECT tbl_IndividualRecipients.[E-Mail] FROM tbl_IndividualRecipients;")

ToRecRst.MoveLast
ToRecRst.MoveFirst

i = ToRecRst.RecordCount - 1

ReDim strRecTo(i, 0)
n = 1
While Not ToRecRst.EOF
strRecTo(n, 0) = ToRecRst.[E-Mail]
n = n + 1
ToRecRst.MoveNext

Wend


This works in part but it only sends an e-mail to the first person in the list not everyone. Also if there is only one person in the recipient list it fails- i'm assuming it doens't like assigning an array (0,0)

I thought i'd fixed the problem by setting i as follows:

If ToRecRst.RecordCount > 1 Then
i = ToRecRst.RecordCount -1 '## to allow for 0 being 1st array pos
Else:
i = ToRecRst.RecordCount
End If

and that works for single recipients but still only sends the e-mail to the first person if there is more than one recipient in the recordset.

Sorry this is a bit specific but this is the first time i've really come across arrays and i'm finding my feet to say the least, thanks, Tom.

 

[Mile-O]

ReDim strRecTo(i, 0)

When you ReDim, all previous values in that array are lost.
You need to Preserve these so, instead of the line quoted above, use:

ReDim Preserve strRecTo(i, 0)

 

[Kodo]

take a look at this
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthgetrows.asp

it might help in cleaning up a lot of your problems.

 

[TKnight]

Thanks Mile, i want the previous values to be lost though because each time the code is run it's going to a totally different set of people. Originally I hadn't used ReDim, i just set the array to accommodate the maximum number of recipients but when the list of recipients was smaller than the previous list the array still held some addresses and e-mails went to the wrong people.
Then i found out that i needed Redim to reset the array in terms of addresses and size.

If i don't fill every space in the array it gives an "invalid recipients" error (probably because it can't send a mail to no address) and if I try and assign too many addresses I get a subscript out of range error.

For the code to run through ok I must be assigning the right number of addresses for the array, it just only seems to pass the first one to the mailer script.

Thanks Tom.

 

[TKnight]

take a look at this
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthgetrows.asp

it might help in cleaning up a lot of your problems.

Thanks Kodo, do you know if there is a DAO equivalent because i've never used ADO before. (i tried sticking it in anyway and got a type mismatch!) I think I can make it out from the example on-line if I have to though. Are there any implications in using DAO and ADO in the same module?

Thanks, Tom.

 

[Kodo]

I don't think there woud be any problems using DAO and ADO together in the same module.

DAO 3.6 GetRows()
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dao360/html/damthgetrows.asp

 

[TKnight]

Thanks again Kodo, i've gone for ADO but have hit another problem
Here's what I got:

Dim i As Integer
Dim cnx As New ADODB.Connection
Dim ToRecRst As New ADODB.Recordset
Set cnx = CurrentProject.Connection
ToRecRst.Open "SELECT tbl_IndividualRecipients.[E-Mail] FROM tbl_IndividualRecipients;", cnx, adOpenForwardOnly, adLockReadOnly


i = ToRecRst.RecordCount
strRecTo = ToRecRst.GetRows(i)

for some reason ToRecRst.RecordCount gives -1!
when on the same recordset DAO recordcount gives 2
do you know why?

Thanks, Tom

 

[fuzzygeek]

Don't "count" on recordcount necessarily

From the Recordcount in the ACCESS Help file...particularly the last paragraph:

RecordCount Property


Indicates the number of records in a Recordset object.

Return Value

Returns a Long value that indicates the number of records in the Recordset.

Remarks

Use the RecordCount property to find out how many records are in a Recordset object. The property returns -1 when ADO cannot determine the number of records or if the provider or cursor type does not support RecordCount. Reading the RecordCount property on a closed Recordset causes an error.

If the Recordset object supports approximate positioning or bookmarks—that is, Supports (adApproxPosition) or Supports (adBookmark), respectively, return True—this value will be the exact number of records in the Recordset, regardless of whether it has been fully populated. If the Recordset object does not support approximate positioning, this property may be a significant drain on resources because all records will have to be retrieved and counted to return an accurate RecordCount value.

The cursor type of the Recordset object affects whether the number of records can be determined. The RecordCount property will return -1 for a forward-only cursor; the actual count for a static or keyset cursor; and either -1 or the actual count for a dynamic cursor, depending on the data source.