Microsoft Dynamics Forums Homepage

Forum Home Forum Home > Microsoft Dynamics SL (Solomon) > SL - Customization and Integration
  New Posts New Posts RSS Feed - Solomon Tools for VBA
  FAQ FAQ  Forum Search   Register Register  Login Login


Solomon Tools for VBA

 Post Reply Post Reply
Author
Message
david View Drop Down
MicrosoftDynamicsForums.com Member
MicrosoftDynamicsForums.com Member


Joined: July 11 2009
Location: Melbourne
Status: Offline
Points: 48
Post Options Post Options   Thanks (0) Thanks(0)   Quote david Quote  Post ReplyReply Direct Link To This Post Topic: Solomon Tools for VBA
    Posted: January 08 2012 at 2:04pm
We're trying to do some customisations that appear to be outside the limits of what the Customisation Manager for VBA can do - essentially, adding tables to a form that aren't currently there in Standard.

Question - is this possible using the the Solomon Tools for VBA, and if so, where would I get the suite from? Or are the tools something that are only available to Vendors ??

Thanks.
Back to Top
Sponsored Links


Back to Top
hieuvt View Drop Down
MicrosoftDynamicsForums.com Member
MicrosoftDynamicsForums.com Member


Joined: January 17 2012
Location: Vietnam
Status: Offline
Points: 9
Post Options Post Options   Thanks (0) Thanks(0)   Quote hieuvt Quote  Post ReplyReply Direct Link To This Post Posted: January 17 2012 at 1:05am

I think you can adding tables to a form in Dynamics SL easily. There are some functions in Customisation manager for VBA. You can do this by select Customization Mode in any SL screen and in ALL User level.

Example Code:

// New table

Type NVVendor
    VendId As String * 15
    Descr As String * 250
End Type
Public bNVVendor As NVVendor, nNVVendor As NVVendor

Public CSR_NVVendor As Integer

Private Sub cvendid_Chk(ChkStrg As String, retval As Integer)
    Dim Sqlstr As String

    bNVVendor = nNVVendor
    Sqlstr = "select * from nvvendor where vendid = " & SParm(ChkStrg)
    serr1 = SqlFetch1(CSR_NVVendor, Sqlstr, bNVVendor, LenB(bNVVendor))

    Call DispFields("form1", "")
End Sub


Private Sub Form1_Load()
    Call VBA_SetAddr("bNVVendor", bNVVendor, nNVVendor, LenB(bNVVendor))

    Call SqlCursorEx(CSR_NVVendor, NOLEVEL, "CSR_NVVendor", "NVVendor", "NVVendor")
End Sub

Private Sub Update1_OnUpdate(Level As Integer, InsertFlg As Integer, retval As Integer)
    Dim Sqlstr As String
    Dim bNVVendor_Copy As NVVendor
    Dim VendId As String

    If Level = LEVEL0 Then
        Call GetBufferValue("bvendor.vendid", VendId)

        bNVVendor_Copy = bNVVendor
        Sqlstr = "select * from nvvendor where vendid = " & SParm(VendId)
        serr1 = SqlFetch1(CSR_NVVendor, Sqlstr, bNVVendor, LenB(bNVVendor))
        bNVVendor = bNVVendor_Copy

    If serr1 = NOTFOUND Then
        bNVVendor.VendId = VendId

        Call SInsert1(CSR_NVVendor, "NVVendor", bNVVendor, LenB(bNVVendor))
    Else
        Call SUpdate1(CSR_NVVendor, "NVVendor", bNVVendor, LenB(bNVVendor))
    End If
    End If
End Sub

I hope this can help you.



Back to Top
david View Drop Down
MicrosoftDynamicsForums.com Member
MicrosoftDynamicsForums.com Member


Joined: July 11 2009
Location: Melbourne
Status: Offline
Points: 48
Post Options Post Options   Thanks (0) Thanks(0)   Quote david Quote  Post ReplyReply Direct Link To This Post Posted: January 17 2012 at 4:07pm
Hi,

This is creating a temporary custom table from within the program, no ??   I'm talking about adding standard (or, indeed, custom-built) tables from SQL to a screen so that fields from that table may be added to the screen - ie, when you go to the "Insert Object Wizard" in Customise Mode, you then get a list of fields/tables.  THAT is where I need to be able to select the table.

Thanks,
David
Back to Top
hieuvt View Drop Down
MicrosoftDynamicsForums.com Member
MicrosoftDynamicsForums.com Member


Joined: January 17 2012
Location: Vietnam
Status: Offline
Points: 9
Post Options Post Options   Thanks (0) Thanks(0)   Quote hieuvt Quote  Post ReplyReply Direct Link To This Post Posted: January 26 2012 at 12:24am

Hi,

The code is to call a table from SQL. You may not see the table in the list of "Insert Object Wizard" if you have not added the following code into the screen.

// This buffer is to define the table structure

Type NVVendor
    VendId As String * 15
    Descr As String * 250
End Type
Public bNVVendor As NVVendor, nNVVendor As NVVendor

Public CSR_NVVendor As Integer

Private Sub Form1_Load()
Call VBA_SetAddr("bNVVendor", bNVVendor, nNVVendor, LenB(bNVVendor))

Call SqlCursorEx(CSR_NVVendor, NOLEVEL, "CSR_NVVendor", "NVVendor", "NVVendor")
End Sub

Then you save and close the screen. You open the screen again and go to "Insert Object Wizard" in Customization Mode and now you can see NVVendor table in the list and you can select fields in this table to add to the screen.

Of course, you have to create a table "NVVendor" in the SQL database. You must restart Dynamics SL after creating the new table (in case Dynamics SL is openning). 

Regards,

Hieu Vu

Back to Top
Trinity View Drop Down
MicrosoftDynamicsForums.com Member
MicrosoftDynamicsForums.com Member


Joined: August 14 2012
Status: Offline
Points: 3
Post Options Post Options   Thanks (0) Thanks(0)   Quote Trinity Quote  Post ReplyReply Direct Link To This Post Posted: August 14 2012 at 10:55pm
Hi Hieu Vu
 
I'm new to SL programming, I tried to follow the script that you posted but I have a few questions. I also understand from the manuals that I need to create a DH file for the new table and this needs to be added to the SL Program Directory. I'd like to know which part of the script is for the DH file and where to insert the code for adding the code to the screen. We are using BSL instead of VBA. Would appreciate some direction.
 
Thanks!
Back to Top
hieuvt View Drop Down
MicrosoftDynamicsForums.com Member
MicrosoftDynamicsForums.com Member


Joined: January 17 2012
Location: Vietnam
Status: Offline
Points: 9
Post Options Post Options   Thanks (0) Thanks(0)   Quote hieuvt Quote  Post ReplyReply Direct Link To This Post Posted: August 15 2012 at 12:28am
Hi Trinity,

I don't have much experience with BSL. Anyway, in order to define a new table in your customized screen. You must do the following steps:

1. Create a DH File. Creating a DH File is quite simple. You just need to copy a standard DH File in the SL Program Directory and follow the structure of this field to define your table and field name.

2. Import this file into the customized screen. In VBA, you can insert it by inserting a file in Visual Studio Editor or you can simply create a module and copy the content of your DH File to this module (In the customization mode, press F7 to open the Editor). I don't know how to do it in BSL.

3. Define the cursor for this table in Form1_Load() function.
Code is as below:
"Call VBA_SetAddr("bNVVendor", bNVVendor, nNVVendor, LenB(bNVVendor))
Call SqlCursorEx(CSR_NVVendor, NOLEVEL, "CSR_NVVendor", "NVVendor", "NVVendor")"

You must define CSR_TableName as Integer as global variable.

You should save and close the screen before continuing.

I hope this would help you to add your table to the screen. I would like to recommend you to change from BSL to VBA. In VBA, it is easier to coding and managing your customization.

Regards,
Hieu Vu
Back to Top
Trinity View Drop Down
MicrosoftDynamicsForums.com Member
MicrosoftDynamicsForums.com Member


Joined: August 14 2012
Status: Offline
Points: 3
Post Options Post Options   Thanks (0) Thanks(0)   Quote Trinity Quote  Post ReplyReply Direct Link To This Post Posted: August 22 2012 at 5:13am
Hi Hieu Vu
 
I've tried as you suggested. As far as I know, we need to type the SQL table in the General Declarations window and also the call commands if the Form1 Load code window. Please see the codes below. The table is still not showing, did I do anything wrong?
 
General Declarations:
'$include: "bsl.dh"
Declare Sub Insert(level%, retval%)
Declare Sub cinvtid_Default(newvalue$, retval%)
Declare Sub xProvince_Chk(chkstrg$, retval%)
Declare Sub xCountry_Chk(chkstrg$, retval%)
'$include: "INSetup.dh"
'$include: "Country.dh"
'$include: "State.dh"
'$include: "InventoryADG.dh"
Declare Sub Form1_Load()
Declare Sub cinvtid_Chk(chkstrg$, retval%)
Declare Sub xCalculateDuty_Click()
Declare Sub Update(level%, insertflg%, retval%)
Global CSR_INSetup%, CSR_Country%, CSR_State%, CSR_InventoryADG%
Type NVVendor
    InvtId As String * 30
    Distributor As String * 30
End Type
Global bNVVendor as NVVendor, nNVVendor as NVVendor
Global CSR_NVVendor As Integer
 
Form1 Load
Sub Form1_Load()
Call SqlCursorEx(CSR_INSetup, NOLEVEL + SQLFastReadOnly, "CSR_INsetup", "INSetup", "")
Call SqlCursorEx(CSR_Country, NOLEVEL + SQLFastReadOnly, "CSR_Country", "Country", "")
Call SqlCursorEx(CSR_State, NOLEVEL + SQLFastReadOnly, "CSR_State", "State", "")
Call SqlCursorEx(CSR_InventoryADG, NOLEVEL + SQLFastReadOnly, "CSR_InventoryADG", "InventoryADG", "")
Call Setaddr("bNVVendor", bNVVendor, nNVVendor, Len(bNVVendor))
Call SqlcursorEx(CSR_NVVendor, NOLEVEL, "CSR_NVVendor", "NVVendor", "")
End Sub
 
Kind regards
 
Trinity
Back to Top
hieuvt View Drop Down
MicrosoftDynamicsForums.com Member
MicrosoftDynamicsForums.com Member


Joined: January 17 2012
Location: Vietnam
Status: Offline
Points: 9
Post Options Post Options   Thanks (0) Thanks(0)   Quote hieuvt Quote  Post ReplyReply Direct Link To This Post Posted: August 22 2012 at 8:44pm

Hi Trinity,

I assume your general declarations part is correct. In the Form1_Load() sub, you are using SetAddr function in stead of VBA_SetAddr function. I am not sure it works. I have not tried to add a new table in BSL. I will try to install SL in BSL and test your code. 

In the statement "Call SqlcursorEx(CSR_NVVendor, NOLEVEL, "CSR_NVVendor", "NVVendor", "")", why don't you add NVVendor in the last parameter?

Regards,

Hieu Vu

Back to Top
Trinity View Drop Down
MicrosoftDynamicsForums.com Member
MicrosoftDynamicsForums.com Member


Joined: August 14 2012
Status: Offline
Points: 3
Post Options Post Options   Thanks (0) Thanks(0)   Quote Trinity Quote  Post ReplyReply Direct Link To This Post Posted: August 23 2012 at 6:04am

Hi Hieu Vu

Thanks for your advice, I will put the parameterthat you suggested. In the meantime, I will await for your testing on BSL.
 
Thanks and kind regards
 
Trinity
Back to Top
hieuvt View Drop Down
MicrosoftDynamicsForums.com Member
MicrosoftDynamicsForums.com Member


Joined: January 17 2012
Location: Vietnam
Status: Offline
Points: 9
Post Options Post Options   Thanks (0) Thanks(0)   Quote hieuvt Quote  Post ReplyReply Direct Link To This Post Posted: August 31 2012 at 2:06am

I have tried to add a new table to the screen in BSL. The new table is added to the screen. My steps are as below:

1. Create the new table NVVendor in SQL Server.

2. Add this table in general declaration

Type NVVendor

InvtId As String * 30
Distributor As String * 30
End Type
Global bNVVendor as NVVendor, nNVVendor as NVVendor
Global CSR_NVVendor As Integer

3. In Form1 event Load, I add

Call Setaddr("bNVVendor", bNVVendor, nNVVendor, Len(bNVVendor))
Call SqlcursorEx(CSR_NVVendor, NOLEVEL, "CSR_NVVendor", "NVVendor", "NVVendor")

4. I save the screen and close it.

5. I open the screen again and enter customization mode.

6. Right Click to select Insert Object Wizard.

7. Select a TextBox and click Next. The Wizard will list all declared tables in the screen. You can find the new table in this. The table should be placed at the end of the list. 

8. Then you select this table and continue.

I do this customization in Dynamics SL 6.5. I believe that you can do the same in Dynamics SL 7.0 or 2011. I am working on Dynamics SL 2011. However, I got a little problem with installing a new environment for Dynamics SL 2011.

I hope the above steps would help you.

Regards,

Hieu Vu


Back to Top
lansishao View Drop Down
MicrosoftDynamicsForums.com Member
MicrosoftDynamicsForums.com Member


Joined: March 02 2011
Status: Offline
Points: 12
Post Options Post Options   Thanks (0) Thanks(0)   Quote lansishao Quote  Post ReplyReply Direct Link To This Post Posted: June 10 2013 at 9:08pm
Hi all, i have follow what you all mention:
1)create a table in SQL. (NEW Table)
2)in form load put the setaddress and cursorex
3)in update1_onupdate there use Supdate1 and SInsert1

but when i do either Insert or update a record it still show me the error msg "Cursor specified SqlReadOnly flag". May i know what is the problem?

The below is my sample:

Private Sub Form1_Load()
Call VBA_SetAddr("btable_extend", btable_extend, ntable_extend, LenB(btable_extend))
Call SqlCursorEx(csr_table_extend, NOLEVEL, "csr_table_extend", "table_extend", "table_extend")
End Sub

Private Sub Update1_OnUpdate(Level As Integer, InsertFlg As Integer, retval As Integer)
Dim SqlStr As String
Dim id As String

If Level = 0 Then
    Call GetBufferValue("btable_extend.id", id)
    SqlStr = "select * from table_extend where id= " & SParm(id)
    Call sql(csr_table_extend, SqlStr)
    serr1 = SqlFetch1(csr_table_extend, SqlStr, btable_extend, LenB(btable_extend))
   
    If serr1 = NOTFOUND Then
        btable_extend.id = Trim(SParm(id))
        Call SInsert1(csr_table_extend, "table_extend", btable_extend, LenB(btable_extend))
    Else
        btable_extend.id = Trim(id)
        Call SUpdate1(csr_table_extend, "table_extend", btable_extend, LenB(btable_extend))
    End If
End If
Call SqlFree(csr_table_extend)
End Sub

Back to Top
long123 View Drop Down
MicrosoftDynamicsForums.com Member
MicrosoftDynamicsForums.com Member


Joined: August 11 2015
Location: shanghai
Status: Offline
Points: 6
Post Options Post Options   Thanks (0) Thanks(0)   Quote long123 Quote  Post ReplyReply Direct Link To This Post Posted: August 14 2015 at 2:16am
There are some functions in Customisation manager for VBA
Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down


Copyright 2013 microsoftdynamicsforums.com. All rights reserved. MicrosoftDynamicsForums.com is an independent non-Microsoft website.
Email: contact AT microsoftdynamicsforums DOT com