Don't use a UDF, just have the script write the number to the Fax Number field and change the label

Hi Justin -- Below is a button script I have that looks up the contact's email address and slaps it in the email field. I don't know how button scripts differ from the scripting you are doing, but maybe this will help. This script does throw an error if the contact name includes an apostrophe.

BTW ... This script happens to be a modification of one that BigLouie once posted.



Dim sEmail, sContact

if SO_SalesOrder_bus_ConfirmTo <> "" and not isnull(SO_SalesOrder_bus_ConfirmTo) then
sContact = SO_SalesOrder_bus_ConfirmTo
else
sContact = "Not on file"
end if

strConnectionString = "DSN=SOTAMAS90;UID=zzz|ABC;PWD=xxx"
Set objCN = CreateObject("ADODB.Connection")
objCN.Open strConnectionString
Set objRS = CreateObject("ADODB.Recordset")

strSQLQuery = "SELECT * FROM AR_CustomerContact WHERE  ARDivisionNo = '" & SO_SalesOrder_bus_ARDivisionNo _
& "' AND CustomerNo = '" & SO_SalesOrder_bus_CustomerNo _
& "' AND ContactName = '" & sContact & "'"

Set objRS = objCN.Execute(strSQLQuery)

Do While Not objRS.EOF
sEmail = objRS.Fields("EmailAddress")
objRS.MoveNext
Loop

if sEmail <> "" and not isnull(sEmail) then
SO_SalesOrder_bus_EmailAddress = sEmail

else
SO_SalesOrder_bus_EmailAddress = ""

end if

objRS.Close
objCN.close
Set objRS = nothing
Set objCN = nothing

Thanks!

What is the UID and PWD in this line referring to? :

Helium said:

strConnectionString = "DSN=SOTAMAS90;UID=zzz|ABC;PWD=xxx"

User ID and password

zzz is the user ID, ABC is your company code, xxx is zzz's password

Thanks again Helium. I have pasted the code in and set my UID/PWD etc to the correct prameters, but it ins't working. I have the code firing after a confirm to person has been selected, and i see the "confirm to" field change briefly to the contact code, then back to the name, but the email isn't populating- it remains blank. I am going to look more at it tonight. I am quite new to scripting so i don't know i will see the error.

this is the code that i use on post-validate of the Confirm To field:

Dim strConnectionString, objCN
Dim oContact, sConfirmTo, sCCode, sDiv, sCustCode, sEmail, company, username, password

company = oSession.CompanyCode
username = "xxx"
password = "yyy"

sConfirmTo = ""
sDiv = ""
sCustCode=""
sCCode = ""
sEmail = ""

retval = oBusObj.GetValue("ARDivisionNo$", sDiv)
retval = oBusObj.GetValue("CustomerNo$", sCustCode)
retval = oBusObj.GetValue("ConfirmTo$", sConfirmTo)

If sConfirmTo <>"" then

strConnectionString = "DSN=SOTAMAS90;UID="+username+"|"+company+";PWD="+password+""

Set objCN = CreateObject("ADODB.Connection")
objCN.Open strConnectionString

Set objRS = CreateObject("ADODB.Recordset")
strSQLQuery = "SELECT * FROM AR_CustomerContact WHERE ARDivisionNo = '" & sDiv _
& "' AND CustomerNo = '" & sCustCode _
& "' AND ContactName = '" & sConfirmTo & "'"
Set objRS = objCN.Execute(strSQLQuery)

Do While Not objRS.EOF
sEmail = objRS.Fields("EmailAddress")
objRS.MoveNext
Loop

if sEmail <> "" and not isnull(sEmail) then
retval = oBusObj.SetValue("EmailAddress$", sEmail)
end if

objRS.Close
objCN.close

Set objRS = nothing
Set objCN = nothing
end if

Thank you so much!!!! This works great!!!!!!