Get Email Address of multiple records (1 Viewer)

[Ksabai]

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

 

[onur_can]

sEmail = DLookup("", "tblstaff", "[SellerID] = [cmbSeller] AND [Duty] = 2")

what to take with space

 

[Ksabai]

sEmail = DLookup("[E-Mail]", "tblstaff", "[SellerID] = [cmbSeller] AND [Duty] = 2")

 

[plog]

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:

Recordset object (DAO)

Office developer client VBA reference documentation

docs.microsoft.com

 

[Ksabai]

can someone help with the code

 

[onur_can]

dear ksabai you can use the following 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

 

[Ksabai]

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)

 

[arnelgp]

you can add the dbSeeChanges as second parameter:

Set rs = db.OpenRecordset("tblStaff", dbOpenDynaset, dbSeeChanges)

 

[Gasman]

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

 

[Ksabai]

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