VBA

(0) Share

Report

Posted on by Romryan

Dear All,

I am writing some vba codes to alert the user that number he/she is putting on Debtor PO number field in Sales Transaction Entry window have been used or not.

Am using sop10100 and sop30200. But my first test only include sop10100.

Unfortunately my codes are not responding to my expectation.

Below is my code

Private Sub DebtorPONumber_AfterGotFocus()
Dim objRec
Dim objConn
Dim cmdString

Set objRec = CreateObject("ADODB.Recordset")
Set objConn = CreateObject("ADODB.Connection")

objConn.ConnectionString = "Provider=MSDASQL;DSN=GP;DataSource=.;Initial Catalog=TWO;User Id=sa;Password=allowme@1"

objConn.Open

'cmdString = "select cstponbr from (select cstponbr from SOP10100 union select cstponbr from SOP30200)a where (a.cstponbr='" + CUSTPONMBR + "')"

Do While Not objRec.EOF

MsgBox "the po" & " " & CSTPONBR & " " & "has been used"

objRec.MoveNext

Loop

objConn.Close

'End If
End Sub

Kindly assists on where am going wrong.

RonRyan

*This post is locked for comments

I have the same question (0)

All responses (3)

Answers (0)

Suggested answer

Almas Mahfooz 11,009 User Group Leader on at

Like (0)

Report

RE: VBA

You are not executing your command also customer po number is not passing.

Also change your event to CustomerPONumber_BeforeUserChanged.

Was this reply helpful? Yes No
Selva D 65 on at

Like (0)

Report

RE: VBA

1. You are not assigning your result to record set.

2. In loop please add to verify your condition and display the message.

Was this reply helpful? Yes No
Suggested answer

L Vail 65,271 on at

Like (0)

Report

RE: VBA

Have you tried using the SOP PO Number Check module of the Professional Services Tools module?

Kind regards,

Leslie

Was this reply helpful? Yes No