Recordset returning only first value

[carmenv323]

Hello,



I copied the following from the SQL view of my query
'SELECT EstimateUsers.Email
'From EstimateUsers
'WHERE (((EstimateUsers.Enabled)=True));

This returns all the rows that fit the where clause, but when I adjust it to VBA I'm only getting the first result. What am I doing wrong so that I can see all the rows?

Thank you

        Dim SQL, strReviewers As String
        Dim rstReviewer As DAO.Recordset


        SQL = "SELECT EstimateUsers.Email" & _
            " From EstimateUsers " & _
            " WHERE (((EstimateUsers.Enabled)=True));"


        Set rstReviewer = CurrentDb.OpenRecordset(SQL)
        strReviewers = rstReviewer!Email


        MsgBox strReviewers

Eventually I'll need to add an ";" in between to use in an email

[Micron]

You have to loop over the records in a recordset. FYI - SQL is a variant because you didn't explicitly typecast it. Don't know if you're aware of that.

[VicM]

Hi carmenv323,

The SQL statement in the query reports all the requested results as you've stated.

However in order to achieve the results you want in a VBA subroutine, you have to do a bit more work.

I'll post the code, but I shortened some of the names in your code to make my work go faster. You'll have to insert your particular names in place instead.

       Dim SQL As String, strReviewers As String, mbox As String  'The mbox string will build what eventually
        Dim rstReviewer As DAO.Recordset                           'gets output in the Msgbox
        Dim i As Integer


        SQL = "SELECT EUEmail" & _
            " From EUser " & _
            " WHERE EUEnabled=True;"


        Set rstReviewer = CurrentDb.OpenRecordset(SQL, dbOpenDynaset)
        strReviewers = rstReviewer.Fields("EUEmail")
        rstReviewer.MoveLast    'You have to execute these two statements because the recordset doesn't yet know
        rstReviewer.MoveFirst   'how many records it has.  And you have to return it to the beginning in order for
                                        'the next loop to function properly.

    For i = 1 To rstReviewer.RecordCount        'The previous two statements got us number of records in recordset
        If i <> rstReviewer.RecordCount Then    'We set a loop for that number of records
            mbox = mbox & rstReviewer.Fields(0) & ", " 'There's only one column in your recordset, its index is 0 (zero)
        Else                                                        'We add the comma to delineate the emails
            mbox = mbox & rstReviewer.Fields(0)        'But don't add it after the last record
        End If
        rstReviewer.MoveNext                        'After each iteration we have to move to next record in recordset
    Next i

        MsgBox mbox                                   'And VOILA! this will print out all the emails.

[Welshgasman]

For i = 1 To rstReviewer.RecordCount 'The previous two statements got us number of records in recordset
If i <> rstReviewer.RecordCount Then 'We set a loop for that number of records
mbox = mbox & rstReviewer.Fields(0) & ", " 'There's only one column in your recordset, its index is 0 (zero)
Else 'We add the comma to delineate the emails
mbox = mbox & rstReviewer.Fields(0) 'But don't add it after the last record
End If
rstReviewer.MoveNext 'After each iteration we have to move to next record in recordset Next i

Or you could do what pretty much everyone else does, just walk the recordset until EOF?

[June7]

A MsgBox can display only so much data. Not practical if thousands of records involved.

What do you really want to accomplish beyond simply opening a recordset object? If just want to view data, would be better to open a form or report bound to query.

[Micron]

^^^ what he said. A common approach is to just drop the last separator character with Left(mbox,Len(mbox)-1)

[VicM]

There are obviously several ways to accomplish this. Since I don't do this for a living and just dabble for my own benefit, it's what I came up with. All your other suggestions are very valid.
And as June7 stated, this is an unwieldy way to show those records. The table I created contained only 3 records. So it was basically a proof of concept.
Perhaps carmenv can absorb all the suggestions.

[Welshgasman]

There are obviously several ways to accomplish this. Since I don't do this for a living and just dabble for my own benefit, it's what I came up with. All your other suggestions are very valid.
And as June7 stated, this is an unwieldy way to show those records. The table I created contained only 3 records. So it was basically a proof of concept.
Perhaps carmenv can absorb all the suggestions.

I generally go with the least code as long as it is understandable? No point writing a flash one liner, if it is hard to work out what it is doing.
I would also consider myself a dabbler.

[moke123]

I would also consider myself a dabbler.

Not me. I'm an addict. Sometimes I think I need an intervention, or Access Anonymous, or something.

[Welshgasman]

Not me. I'm an addict. Sometimes I think I need an intervention, or Access Anonymous, or something.

Well I did still contribute when I was on holiday.

[moke123]

Well I did still contribute when I was on holiday.

Tell tale sign of addiction.

[CJ_London]

My tweak is to put the comma first

s=“,” & newvalue & s

then just one function

s=mid(s,2)

many ways, just use one you are comfortable with

[Welshgasman]

I would use ; to start with?
Also Outlook does not complain if the string ends with a ; so no real need to trim the string?

[Micron]

I would use ; to start with?

That would be a Regional Settings thing?

[Welshgasman]

That would be a Regional Settings thing?

OP said they were eventually going to use ; ?

Eventually I'll need to add an ";" in between to use in an email

I must admit I was not aware that it was regional settings issue. I use , to seperate parameters in functions, yet use ; for multiple email addresses?
I thought ; was a standard email address separator, regardless of region.