Problem reading a null with a loop 1

[InkyRich]

Hello you smart people out there,
I wonder if you can help me with a problem I am having. I have created code to 'look' at a table with E-mail addresses in, to loop through the records and list the e-mail addresses within a certain criteria. I have had this working ok but not all of the records are shown in the table so when the ID sequence that the loop is looking at is broken such as RecordsID 1, 2, 3, 4, 7, 8 the loop stops and it shows an error. This is the code that I have:

Dim GetEMailAddresses As New ADODB.Recordset
    Dim conn As ADODB.Connection
    Set conn = CurrentProject.Connection
    Dim EMailAddressResult As String
    Dim MailID As Integer
    Dim x As Integer

    For x = 1 To 60
    
    MailID = x

    GetEMailAddresses.Open "SELECT MailingEMailAddress FROM TBLProclaimEMailList WHERE MemberID = " & MailID & "", conn
    
    EMailAddressResult = GetEMailAddresses("MailingEMailAddress").Value
    
    GetEMailAddresses.Close
    
    
Debug.Print EMailAddressResult
    
    Next x

Can anyone see how to modify this code to enable the search to include or skip the non-existant record?

Hope you can help

Regards

InkyRich





A fool and his money are soon parted - so you might as well send it to me!

 

[PWise]

you could change to

if not GetEMailAddresses.eof then
EMailAddressResult = GetEMailAddresses("MailingEMailAddress").Value
    
    GetEMailAddresses.Close
end if

or

 

[KenReay]

Hi

It would have helped if you had given exact error message, but try:

GetEMailAddresses.Open "SELECT MailingEMailAddress FROM TBLProclaimEMailList WHERE MemberID = " & MailID & "", conn

if GetEMailAddresses.recordcount > 0 Then
EMailAddressResult = GetEMailAddresses("MailingEMailAddress").Value
end if

GetEMailAddresses.Close

but why not simplify to:

Dim GetEMailAddresses As New ADODB.Recordset
Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection
Dim EMailAddressResult As String
Dim MailID As Integer
Dim x As Integer


GetEMailAddresses.Open "SELECT MailingEMailAddress FROM TBLProclaimEMailList;", conn
Do until GetEMAilAddress.EOF
EMailAddressResult = GetEMailAddresses("MailingEMailAddress").Value




Debug.Print EMailAddressResult

Loop

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PWise]

try this

Dim GetEMailAddresses As New ADODB.Recordset
Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection
GetEMailAddresses.Open "SELECT MailingEMailAddress FROM TBLProclaimEMailList , conn"
Do While Not GetEMailAddresses.EOF
    Debug.Print GetEMailAddresses!MailingEMailAddress
    GetEMailAddresses.MoveNext
Loop
GetEMailAddresses.Close

 

[InkyRich]

Hello all,
Yes some very good suggestions there. I see that you have all done away with the need to look at the ID, so that should eliminate the problem. I will try them out.
Thanks,
Please keep the suggestions coming.
InkyRich

A fool and his money are soon parted - so you might as well send it to me!

 

[InkyRich]

Hello,
Thanks for your help. The suggestion you came up with pWise was the one I used and seemed to work without much adaptation. Many thanks again
InkyRich

A fool and his money are soon parted - so you might as well send it to me!