Using VBA to open a window and add a value 1

[bustercoder]

Hello,

I've been trying to handle this on the back end (see post "Duplicate Key Error in SOP10106"). Because I need to always have an initial insert, a VBA solution seems to be what's needed. Basically, I just need to to have the User Defined window open and have a default (static) value entered in one of the user defined fields, which would enable my update trigger solution. As I write this, now I'm thinking I could probably handle the whole thing in VBA, but at this point I just need the most straightforward and simplest, quickest solution. If someone could show me or point me to a VBA sample to do what I described I would really appreciate it. I've written some smaller VBA modules for Excel and Access, so would it be the same?

Thanks,
Buster

 

[RobertC22]

Hello Buster,

I tried simply to get some VBA code for you, so that when the SAVE button is pressed would populated the USERDEF1 field. However the code I expected should work did not. The User Defined window opened (invisibly) but out of context.

I have some existing code on the SOP entry window that opens the Sales Entry Detail window that I will attempt to modify to satisfy your purpose.

In the meantime refer to the Modifier/VBA online manual. More examples can be found in Customersource - search for "VBA playground"

https://mbs.microsoft.com/customers...cles/modifiervbasamples80.htm?printpage=false

https://mbs.microsoft.com/customers...cles/modifiervbasamples90.htm?printpage=false

------
Robert

 

[RobertC22]

Buster,
I took a different approach and got it working. And it resulted in less code but you will get a 'flashing' effect when the user hits the SAVE button.

Here is the code you need to add to the SalesTransactionEntry (window) within the DynamicsGP vba project.

Private Sub Save_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)

    UserDefined.Value = 1   ' press the button to open window
    If SalesUserDefinedFieldsEntr.UserDefined1 = "" Then
        SalesUserDefinedFieldsEntr.UserDefined1 = "SEND PENDING"
    End If
    SalesUserDefinedFieldsEntr.OK.Value = 1
    

End Sub

You will need to add the Sales Entry window and Sales User Define windows to the DynamicsGP project.
From the Sales Entry window, you will need to add the following fields
* [SAVE] button,
* [User-Defined] button
From Sales User Defined window, add the fields
* User Defined 1
* [OK] button

If you haven't added windows and fields to the DynamicsGP product before, look under Tools --> Customise --> xxxx
This does assume you are licensed for Modifier.

------
Robert

 

[bustercoder]

Thanks Robert!
I'll give it a try and let you know. Much obliged.
Buster

 

[bustercoder]

Hey Robert!

Almost there. It works, except the only thing left is for me to grab the Payment Terms from the Sales Customer Detail Entry window and assign that value to the SalesUserDefinedFieldsEntr.UserDefinedTable1.Value. I added that window and the appropriate fields but the SalesCustomerDetailEntry.PaymentTerms value is returning empty string(when I cursor over I get = ""):

Private Sub Save_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)

UserDefined.Value = 1 ' press the button to open window
If SalesUserDefinedFieldsEntr.UserDefined1 = "" Then
'SalesUserDefinedFieldsEntr.UserDefinedTable1.Value = SalesCustomerDetailEntry.PaymentTerms
SalesUserDefinedFieldsEntr.UserDefined1 = "TBZ SEND PENDING"
End If
SalesUserDefinedFieldsEntr.OK.Value = 1

End Sub

I'm so close to getting this to work. Is it just a syntax error? I made sure I added the windows and fields I needed.

Thanks,
Buster

 

[RobertC22]

Buster,

Since the payment terms ID is on another window, you will need to open that window to be able to extract the value.

Add the button on the main SOP Entry window that opens the Sales Customer Detail Entry.
Also the button on the Sales Customer Detail Entry that closes that window. The field names listed below may not be exactly the same for you when added to your VBA project.

Private Sub Save_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)

    Dim sPayment as String
    CustomerZoom.Value = 1 ' press the button to open Sales Customer Detail
    sPayment = SalesCustomerDetailEntry.PaymentTerms
    SalesCustomerDetailEntry.OK.Value = 1 ' press to close the window

    UserDefined.Value = 1   ' press the button to open window
    If SalesUserDefinedFieldsEntr.UserDefined1 = "" Then
        'SalesUserDefinedFieldsEntr.UserDefinedTable1.Value = SalesCustomerDetailEntry.PaymentTerms
        SalesUserDefinedFieldsEntr.UserDefined1 = "TBZ SEND PENDING"
    End If
    SalesUserDefinedFieldsEntr.OK.Value = 1

End Sub

------
Robert

 

[bustercoder]

Thanks Robert. you know, I realized after I replied to your last response that the update trigger on the SOP10106 should be updating this. I can see the payment terms in the order record in the SOP10100 table:

CREATE trigger dbo.trSOP10106_UpdateUDF on SOP10106
FOR INSERT, UPDATE
AS
begin
update udf
set USRTAB01 = H.PYMTRMID
, USERDEF1 = 'TEST SEND PENDING'
, CMMTTEXT = COMMNTID
from SOP10106 udf
inner join inserted ins on ins.SOPTYPE = udf.SOPTYPE and ins.SOPNUMBE = udf.SOPNUMBE
inner join SOP10100 H on ins.SOPTYPE = H.SOPTYPE and ins.SOPNUMBE = H.SOPNUMBE
where ins.SOPTYPE = 2
end


In any case, I'll handle it with the VBA. I'll try it and let you know.

Thanks again,
Buster

 

[bustercoder]

Hey Robert!

Ok, I got it working. had to change this:
CustomerZoom.Value = 1 ' press the button to open Sales Customer Detail

to:

SalesTransactionEntry.ExpansionButton4.Value = 1 ' press the button to open Sales Customer Detail

but it's working. Funny how when I insert a record via trigger, GP complains down the line when trying to transfer from a back order, etc. but if GP initially creates the insert, no problems down the line. Seems that trying to circumvent the logic of the black box isn't working in this case.

Anyway, if you're ever in So Calif let me know and lunch (or dinner) is on me! Thanks so much, you really helped me a lot!

Buster

 

[RobertC22]

Buster,

I am sure that after a couple of flights equally 16 hours, I sure would be hungry, next time I get to the USofA!

Glad you've finally got to the outcome you desire.

------
Robert
Perth, Western Australia

 

[winthropdc]

Hi Rob, Buster

If you want to get rid of the screen flashing you can do that as well. This will make the VBA customisation invisible to the user.

What you need to do is create a public boolean variable on the user defined entry form. Then on the BeforeWindowOpen you can check the variable and see if it is set. If it is set you can change the Hidden flag to prevent the window from being seen. On the AfterWindowClose event, clear the boolean variable.

Then all you need to do is before you "click" the expansion button, set the boolean variable to true and it will open the window in hidden mode.

If the window is opened manually by the user it will stay visible.

Have fun.

PS: Buster, why don't you come to Perth and then you can visit me and Rob.

David Musgrave [MSFT]
Senior Development Consultant
Escalation Engineer - Great Plains
Microsoft Dynamics Support - Asia Pacific

Microsoft Dynamics (formerly Microsoft Business Solutions)

http://www.microsoft.com/Dynamics

Any views contained within are my personal views and
not necessarily Microsoft Business Solutions policy.
This posting is provided "AS IS" with no warranties,
and confers no rights.

 

[bustercoder]

Hi Rob and David,

After my last vacation in Europe I was on a tour with a great couple from Brisbane and another couple from (I think it was Hamilton) New Zealand (funny how the Aussies called them "Kiwis"). Anyway, after hearing all about Austrailia and New Zealand it is one of the top goals in my life to spend at least three weeks travelling througout Austrailia and New Zealand. I know it's a long flight but I don't care, I really want to go. So, maybe I'll be the one going out there for dinner (but I'll still buy).

David, any chance you could show me a code sample of what you describe in suppressing the flashing window? Sorry, I'm not exactly sure. I suppose I should just spend some time on it on my own.

Hope I can get as good as you guys someday.

Thanks,
Buster

 

[RobertC22]

Buster,

At the top of the SalesTransactionEntry window add

Dim bProgControl as boolean

into the existing code on SalesTransactionEntry insert

Private Sub Save_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)

    Dim sPayment as String
    bProgControl = true

    'existing code ...

    bProgControl = false
End Sub

In both of the SalesCustomerDetailEntry & SalesUserDefinedFieldsEntr add the following code

Private Sub Window_BeforeOpen(OpenVisible As Boolean)

    OpenVisible = (SalesTransactionEntry.bProgControl <> True)
    
End Sub

------
Robert

 

[bustercoder]

This is great Robert, thanks. By 'clear the boolean variable', does that mean to set it to false?
Buster

 

[bustercoder]

Sorry guys. Now i'm getting Method or Data member not found on this (bProgControl):

Private Sub Window_BeforeOpen(OpenVisible As Boolean)
OpenVisible = (SalesTransactionEntry.bProgControl <> True)
'this will prevent the user from seeing the flashing window
End Sub

 

[RobertC22]

Change the

Dim bProgControl as boolean

to

Public bProgControl as boolean

 

[bustercoder]

Robert, I thought it might be a scope issue. Funny, I tried doing this:

Public Dim bProgControl as boolean

But obviously that doesn't work. You're awesome, thank you SOOO much! Wish I could help you out with something sometime.

Buster