Get Email Address of multiple records

Ksabai

Registered User.
Local time
Yesterday, 17:13
Joined
Jul 31, 2017
Messages
104
Iam trying to get the Email Address of 2 Staffs is "tblStaff" with 2 Conditions.
one condition is the sellerid and second condition is duty=2. following is the code iam using.

If DCount("[Duty]", "tblstaff", "[SellerID] = [cmbSeller] AND [Duty] = 2") >= 1 Then
For i = 1 To DCount("[Duty]", "tblstaff", "[SellerID] = [cmbSeller] AND [Duty] = 2")
sEmail = DLookup("", "tblstaff", "[SellerID] = [cmbSeller] AND [Duty] = 2")
Next i
sTo = sTo & ";" & DLookup("[Staff]", "tblstaff", "[SellerID] = [cmbSeller] AND [Duty] = 2") & " <" & sEmail & ">"

with the above code its struck on the the first row and not moving to the next row with the condition

Can someone help
 
Code:
sEmail = DLookup("", "tblstaff", "[SellerID] = [cmbSeller] AND [Duty] = 2")

what to take with space
 
sEmail = DLookup("[E-Mail]", "tblstaff", "[SellerID] = [cmbSeller] AND [Duty] = 2")
 
Code:
DLookup("[E-Mail]", "tblstaff", "[SellerID] = [cmbSeller] AND [Duty] = 2")

There's nothing to loop thru in a dlookup. It's going to return the same value no matter how many times you run it. Instead you Should loop thru a recordset:

 
dear ksabai you can use the following code.

Code:
Private Sub cmbSeller_AfterUpdate()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sEMail As String
Dim countrec As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("tblStaff")

countrec = 0
rs.MoveFirst

Do While Not rs.EOF
    If rs![duty] = 2 And rs![SellerID] = Me.cmbSeller Then
        sEMail = sEMail & ";" & rs![E-Mail]
        countrec = countrec + 1
        
            If countrec > 1 Then
                Exit Do
            End If
    Else
    End If
    
rs.MoveNext
Loop

sEMail = Mid(sEMail, 2)
MsgBox sEMail

Set db = Nothing
Set rs = Nothing

End Sub
 
i get the following error at
Set rs = db.OpenRecordset("tblStaff")

You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column. (Error 3622)
 
you can add the dbSeeChanges as second parameter:

Set rs = db.OpenRecordset("tblStaff", dbOpenDynaset, dbSeeChanges)
 
If it is always just two, I'd just use two specific DLookups?, otherwise I'd limit the records with a query and open that.?

As for dbopenrecordset, just add the paramter as you have been advised by the error message?

dbopenrecordset

HTH
 
Thanks Onur_Can and arnelgp, it worked fine. Stay safe and healthy everyone
 

Users who are viewing this thread

Back
Top Bottom