Referencing fields in recordset

[Dachande]

Hello,

I have created a recordset and am trying to reference fields within it. This works upto a certain number of fields (upto [Email2]) but then it does not recognise anything after that. The code is as follows


rst.Open "SELECT TableTest.[CI Code], TableTest.[Old CI Code], TableTest.Active, TableTest.Name, TableTest.BM, TableTest.Email1, TableTest.Email2, " & _
"TableTest.Email3, TableTest.Email4, TableTest.Email5, TableTest.Combined, TableTest.CombinedFile " & _
"FROM TableTest WHERE (((TableTest.Active)=True));", conn, adOpenForwardOnly

Thanks for any help

Mark

 

[Mile-O]

Why do you have five email addresses for each record?

What's your code for the data access?

 

[Dachande]

Hello,

There are 5 email addresses because there is upto 5 people to email the reports to. The code is as follows.

Dim recip(5) As Variant
recip(0) = rst![Email1]
recip(1) = rst![Email2]
recip(2) = rst![email3]
recip(3) = rst![email4]
recip(4) = rst![email5]

it recognises the first 2 and sends the email as it is supposed to when I take the other 3 out, but when I put them in I get a runtime 7078 error - could not create field.

Thanks for your help

Mark

 

[Mile-O]

Dim recip(0 To 4) As Variant
recip(0) = Nz(rst.Fields("Email1"), vbNullString)
recip(1) = Nz(rst.Fields("Email2"), vbNullString)
recip(2) = Nz(rst.Fields("Email3"), vbNullString)
recip(3) = Nz(rst.Fields("Email4"), vbNullString)
recip(4) = Nz(rst.Fields("Email5"), vbNullString)

 

[Dachande]

I have just tried this and it came back with the same error. It does not see that they exist as when you write "email3" it does not correct this to "Email3" (to reflect what the rst reads).

Thanks

Mark