If you are on version 7.5 or later you should be able to do it by adding code to the lkuCustID_LostFocus event in customizer.  I just confirmed that you can pop up a message box so you could also have it write to a text box for display or even better you could have it play a music video you produced to present the information.  :)

It is a reason to upgrade.

If you can wait, one feature that is expected in the next release of 500 was the ability to flag certain Comments to display  during Sales Order Entry. That next release (V.2015) is expected out by the end of this year. If you are going to attend Sage Summit this year in New Orleans, you might get the chance to check with Sage 500 staff directly on this.

Hi John,

We are on Sage 500 2014, which is v7.6 I believe.

Any help you can provide regarding the code to display a popup box reading from a custom table would be helpful!

I just learned I can make a popbox display the custid entered, but I'm not sure how to use that to read from a table.

Thanks!

with session.appdatabase

.SetInParam Form.Controls("txtSONum").text

.SetInParam Session.CompanyID

.SetOutParam CSTR(sErrorMsg)

.SetOutParam CINT(iRetVal)

.ExecuteSP("spsoChecksSOQuality_TOY")

sErrorMsg = .getoutparam(3)

iRetVal = .getoutparam(4)

.releaseParams

end with

IF iRetVal = 0 THEN

MSGBOX "SO Check failed: " + sErrorMsg

END IF

The above code shows how to grab a parameter from a stored procedure (which could be the text you need).  I'm not exactly sure what you need.  If your needs are complicated (like a different memo(s) depending on customer) then using a SP would be great.  If the memo id is always the same for all customers then I'd probably just do read from SQL to return the text.  You may have to play with formatting.

that's great! at first glance i can follow what's going on. i will report back when i get a chance to test it out and configure it for our needs.

Hi John

I've tried to adapt your script to our scenario. The 2nd msgbox comes up blank. I wonder what I am missing?

Sub lkuCustID_LostFocus

   dim custid

   dim terms

   custid = Form.Controls("lkuCustID")    

   msgbox(custid)

   with session.appdatabase

     .SetInParamStr(custid)

     .SetOutParam CSTR(terms)

     .ExecuteSP("spgetcustomerfreightterms_exi")

     terms = .getoutparam(2)

     .releaseParams

   end with

   msgbox(terms)

End Sub

Here is the command in sql server that works..

declare @custid varchar(10)

declare @terms varchar(50)

set @custid = '12141'

exec spGetCustomerFreightTerms_EXI @custid, @terms output

after the dim terms put terms=""

I always initialize my variables and terms is NULL without the init of the variable.

unforunately that didn't do it either. in testing i ended up initializing terms="something" and "something" is what is displayed on the msgbox. it seems it doesn't get overwritten by the stored procedure.

the output column name from the stored procedure is "freightterms". i've tried different combinations of variables and haven't been able to get it work work.

We've implemented this in our system just slightly differently than what you are trying to do.  In our system, our customer service reps know that they have to click a button after they select the Customer and ShipTo address.  According to your original post, you are trying to get the note(s) to display without user interaction.  That said, you might not be interested in the code that we use.  But just in case it might be useful, here is the code behind the "Customer Notes" button on our Sales Order screen.

Sub cmdCustomerNotes_Click

Dim sSql, sSql2

Dim rs, rs2

Dim ShowComment, ShowComment2

sSql = "Select Comment1, Comment2, Comment3, Comment4  from varCustomerComments_XXX"

sSql = sSql & " where CustID = '" & Form.Controls("lkuCustID") & "'"

Set rs = Session.AppDatabase.OpenRecordSet(sSql,1,0)

rs.moveFirst

sSql2 = "Select Comment1, Comment2, Comment3, Comment4  from varCustCommentsOtherAdd_XXX"

sSql2 = sSql2 & " where CustAddrID = '" & Form.Controls("lkuShipTo", 0)& "'"

sSql2 = sSql2 & " and CustID = '" & Form.Controls("lkuCustID") & "'"

Set rs2 = Session.AppDatabase.OpenRecordSet(sSql2,1,0)

rs2.moveFirst

if not rs.iseof then

  ShowComment = rs.field("Comment1") & Chr(10) & Chr(13)

  ShowComment = ShowComment & rs.field("Comment2") & Chr(10) & Chr(13)

  ShowComment = ShowComment & rs.field("Comment3") & Chr(10) & Chr(13)

  ShowComment = ShowComment & rs.field("Comment4") & Chr(10) & Chr(13)

  msgbox ShowComment,, "Primary Address Billing Notes"

end if

if not rs2.iseof then

   ShowComment2 = rs2.field("Comment1") & Chr(10) & Chr(13)

   ShowComment2 = ShowComment2 & rs2.field("Comment2") & Chr(10) & Chr(13)

   ShowComment2 = ShowComment2 & rs2.field("Comment3") & Chr(10) & Chr(13)

   ShowComment2 = ShowComment2 & rs2.field("Comment4") & Chr(10) & Chr(13)

   msgbox ShowComment2,, "Ship To Address Billing Notes"

end if

rs.close

set rs =  nothing

rs2.close

set rs2 = nothing

End Sub

Well I'm not sure why it doesn't work.  If I were you I'd turn this into a learning experience now and hire your reseller/RKL-Joe Noll/Lou Davis to help you get this to work.  If they could see what's happening I imagine it would not be hard to fix and they could show you at the same time what's going on (via WebEx or such).  I would think they could do it in an hour or so and you'd hopefully learn some good tricks.  I'd offer but I don't have webex and no longer work for a reseller.