How to Call GP Lookup using VBA??

Guys,

Allot of question comes to our minds when customizing GP on how to open GP lookup form while we are using VBA, here in this post, I will explain a simple workaround in details.

To get this done easily, we need to take the customer card as example, I will add a new lookup button and a new text field, the lookup button will call the lookup form and the value (Customer ID) will be placed in the text.

A. Customer Card Work:

1. Modify customer card form and add two objects, a lookup button and a string text box.

clip_image002

2. Add the “Modified” customer card form to VBA, and then add our lookup button with the text to VBA.

3. Now we need to add a Global Boolean variable to determine wither the lookup is called from this customized button or not.

4. On the button “Changed” event we need to call the customer lookup form, and this will require using Dynamics Continuum API, so you need first to add your reference to the API by following steps below:

  a. On your VBA window go to Tools >> References

  b. Check on “Dynamics Continuum Integration Library”

 

Then write the following code in the customer maintenance form:

Public bolFireLookup As Boolean
Private Sub btnCustomerLookup_Changed()
bolFireLookup = True
Dim GPApp As New Dynamics.Application
Dim ErrMsg As String
GPApp.CurrentProductID = 1493
GPApp.ExecuteSanscript “open form ‘Customer_Lookup’;”, ErrMsg
GPApp.ExecuteSanscript “run script ‘PB_Refresh_Scrolling_Window’ of window ‘Customer_Lookup’ of form ‘Customer_Lookup’;”, ErrMsg
End Sub

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

B. Customer Lookup Work:

1. Add the customer lookup form to VBA, and then add the Customer ID field and the select button.

2. As the Smart-list is a separate module that GP and has its own dictionary, you will need to reference the Dynamics_GP project by following steps below:

  a. Go to Tools >> References

  b. Check “Microsoft_Dynamics_GP”

3. Paste the following code:

Private Sub Select_Changed()
‘Validate that the Customer Card is open
If Microsoft_Dynamics_GP.CustomerMaintenance.IsLoaded Then
‘Validate that the lookup is called from customized lookup button 
If Microsoft_Dynamics_GP.CustomerMaintenance.bolFireLookup Then 
Microsoft_Dynamics_GP.CustomerMaintenance.txtCustomerID.Value = CustomersandProspectsDetail.CustomerNumber.Value Microsoft_Dynamics_GP.CustomerMaintenance.bolFireLookup = False 
End If
End If
End Sub

The complete sample package could be downloaded using the following link:

http://blog.greatpbs.com/LookupExample.zip

Hope that this helps.

Regards,

Mohammad R. Daoud
MVP, MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 – 79 – 999 65 85
Great Package For Business Solutions
daoudm@greatpbs.com
http://www.greatpbs.com

Advertisements

About Mohammad R. Daoud
Mohammad R. Daoud has been working as a Microsoft Dynamics GP consultant since 2004. His career path started with version 7.5; he studied every single tip of the application technicalities and did allot of successful implementations that includes functional consultations, analysis, and custom development projects. He holds a graduate degree in Computer Science and currently he is pursuing MBA degree in Accounting. In January 2007 he was nominated to the Microsoft Most Valuable Professional (MVP) certificate and was certified in April 2008 due to his online contributions in the Dynamics Community (Dynamics GP Newsgroups, Forums, User Groups and his blog: http://mohdaoud.blogspot.com). Worth to mention that he was also listed as one of the Microsoft Dynamics Top 100 Most Influential People in 2009 by DynamicsWorld. And more... • Demonstrated Passion for MS Products through participating in beta testing for some product versions, an effective member of Microsoft Connect. • Talented in securing strong high quality relationships with premier customers, partners and sales teams passionately and profitably. • Dedicated to meeting the expectations and requirements of internal and/or external customers • Problem Solver – Uses systematic approach to uncover true problem(s) and practical solution and can marshal resources to solve problem! • Proven record of effective account management, including Account Planning, Opportunity Management, and Business Management Excellence and working as part of a larger virtual team • Successful in delivering high quality technical engagements in the enterprise Dynamics GP implementations area according to customer requirements. • Dedicated, Committed, Self Motivated, Creative and highly focused with excellent communication, Negotiation, presentation, Active Listening and Objection Handling skills. • Experienced in Profitable researches and studies including white papers and technical presentations.

6 Responses to How to Call GP Lookup using VBA??

  1. Its Great Example, i like it.

    I want to open the Account_Lookup in CreditorAccountMaintenence Window, i fellow your steps ..
    Account_Lookup is open but have not any record in this lookup.
    Please help me to solve this problem….thanks

  2. Muhammad,

    By default calling the lookup form as is will not load startup scripts and objects, so I have included the code below that calls “Refresh” button of the form:

    Dim GPApp As New Dynamics.Application
    Dim ErrMsg As String
    GPApp.CurrentProductID = 1493
    GPApp.ExecuteSanscript “open form ‘Customer_Lookup’;”, ErrMsg
    GPApp.ExecuteSanscript “run script ‘PB_Refresh_Scrolling_Window’ of window ‘Customer_Lookup’ of form ‘Customer_Lookup’;”, ErrMsg

    Following steps as described in the blog will get this to work.

    Regards,

    Mohammad

  3. Mohammad R. Daoud,

    i follow all the steps that you mention in blog

    Dim GPApp As New Dynamics.Application
    Dim ErrMsg As String
    GPApp.CurrentProductID = 1493
    GPApp.ExecuteSanscript “open form ‘Account_Lookup’;”, ErrMsg
    GPApp.ExecuteSanscript “run script ‘PB_Refresh_Scrolling_Window’ of window ‘Account_Lookup’ of form ‘Account_Lookup’;”, ErrMsg

    “Account_Lookup” window opens but have not any data in the window. I also clicks on the refreshing button at that window but still window shows empty data.

    Please brother help me to fix this problem, i will be very kind of you. thanks.

    Best Regards,

    Muhammad Salman Malik

  4. i find out the reason why records are not showing in the Accounts lookup.

    that is when we go

    Tools -> Resource Descriptions -> Windows
    from Find “Accounts”

    we will see that at auto linked table column = (No auto linked table)

    that is the reason the records are not showing in the accounts lookup window

    please help me…..out this problem thanks

    Best Regards
    Muhammad Salman Malik
    salman.malik@m-consultants.com

  5. Muhammad,

    It seems you are right, the account lookup is not working that way, let me find alternative way to show this lookup.

    Regards,

    Mohammad

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: