Private Sub btnListContractorEmail_Click()
Dim strSQL As String
Dim rst As New ADODB.Recordset
On Error GoTo Err_btnListContractorEmail_Click
strSQL = "SELECT DISTINCT ConfirmationOrder.IdContractor, Contractor.CompanyEmail, " & vbCrLf & _
"ContractorContact.CCEmail AS ContractorEmail, ConfirmationOrder.IdConsignor, " & vbCrLf & _
"Consignor.CompanyCity, ConfirmationOrder.IdConsignee, Consignee.CompanyCity " & vbCrLf & _
"FROM (((ConfirmationOrder LEFT JOIN Company AS Consignor ON " & vbCrLf & _
"ConfirmationOrder.IdConsignor = Consignor.CompanyCode) " & vbCrLf & _
"LEFT JOIN Company AS Consignee ON ConfirmationOrder.IdConsignee = Consignee.CompanyCode) " & vbCrLf & _
"LEFT JOIN Company AS Contractor ON ConfirmationOrder.IdContractor = Contractor.CompanyCode) " & vbCrLf & _
"LEFT JOIN CompanyContact AS ContractorContact ON Contractor.CompanyId = ContractorContact.CompanyId " & vbCrLf & _
"WHERE (((Consignor.CompanyCity)='" & [Forms]![frmMail]![VilleEntree] & "') AND " & vbCrLf & _
"((Consignee.CompanyCity)= '" & [Forms]![frmMail]![VilleSortant] & "') AND " & vbCrLf & _
"((ContractorContact.CCType)='Contractor'));"
clsCnx.Ouverture
Set rst = clsCnx.ExecQuery(strSQL)
While Not rst.EOF
rst.MoveNext
Wend
rst.Close
Set rst = Nothing
clsCnx.Fermeture
Exit_btnListContractorEmail_Click:
Exit Sub
Err_btnListContractorEmail_Click:
MsgBox Err.Description
Resume Exit_btnListContractorEmail_Click
End Sub
Partager