Hi all. Been search a few days for a loop search through a table for displaying emails in a list format:

TABLE_A: with the following fields
NovellLoginName
EmailAddies
GroupA <- Either with a string "YES" or blank
GroupB <- Either with a string "YES" or blank
GroupC <- Either with a string "YES" or blank

FORM_A: with the following controls
ComboBox_GroupList <- Diplaying a selection for displaying GroupA, B, or C

* Using a test button to test things out for the time being
Button_TEST


VBA using so far:

Function TESTINGVBA()

Dim str_Emails as Long
Dim snp As DAO.Recordset
Set dbs = CurrentDb
Set snp = dbs.OpenRecordset("TABLE_A", dbOpenSnapshot)

'Creating a string of emails to display:

If ComboBox_GroupList = "GroupA" Then

snp.MoveFirst

Do Until snp.EOF

snp = DLookup("EmailAddies", "TABLE_A", "GroupA='YES'")
str_Emails = snp
snp.MoveNext
str_Emails = str_Emails & ", " & DLookup("EmailAddies", "TABLE_A", "GroupA='YES'")

Loop

End If

MsgBox snp 'Hopefully a string of emails will be displayed in a msgbox..but nope...


snp.Close
dbs.Close

End Function


Sorry about the miserable approach to the loop search and display method. Hope you can help. Thanks in advance.

Here's the update..playing with the VBA and simplifying it down to where it works..but still have a bit of a problem:


Function TESTINGVBA()

Dim str_Email As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("TABLE_A", dbOpenSnapshot)

If ComboBox_GroupList = "GroupA" Then

rst.FindFirst ("GroupA='YES'")
str_Email = rst![EmailAddies]

Do While Not rst.EOF

If rst.NoMatch Then
GoTo Display
Else
rst.FindNext ("GroupA='YES'")
str_Email = str_Email & ", " & rst![EmailAddies]
End If

Loop

End If


Display:
MsgBox str_Email
rst.Close
dbs.Close

Exit Function

End Function


PROBLEM:

The emails will be displayed properly..BUT, the first email that was found, it got displayed twice on the list. Example, the first NovellLoginName is "StaffA", the second is "StaffB", etc. When the msgbox appears with the emails in the string, it displays as:

StaffC@here.com, StaffB@here.com, StaffA@here.com, StaffA@here.com

2 counts of StaffA@here.com. Anyone has any clue to why it displays like that?