Wednesday, June 24, 2009

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

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://dynamicsinnovations.com/blog/LookupExample.zip

Hope that this helps.

Regards,
--
Mohammad R. Daoud
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
CTO
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com

7 comments:

Muhammad Salman said...

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

Mohammad R. Daoud said...

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

Muhammad Salman said...

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

Muhammad Salman said...

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

Mohammad R. Daoud said...

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

Mohammad R. Daoud said...

Here you go:

http://mohdaoud.blogspot.com/2009/07/calling-dynamics-gp-lookup-using-vba.html

Anonymous said...

Is there a way to have users enter SOP user-defined (List 1 with lookup button) on the SOP entry screen using VBA and Modifier? I can get the field to populate correctly, but not the Lookup button to work. Dynamics GP 2015

Related Posts:

Related Posts with Thumbnails