Create a note field for the Item Price List Maintenance

Thanks for your response
I think this is exactly what I want to do . I will try to follow your explanation.
I am on step 2 , when creating the custom note window will it  look the same as the dynamics note , or is it just a form that I create in VBA and put a TEXT BOX in it?

Hi,

Your note window will be a custom VBA window that you create--it can look however you want.  You can try and get it to look similar to a GP note window, but it will likely look noticeably different.

One thing to check--in the past I have run into field length limitations with a standard VB6 text box, so  started to use the Rich Text Box control instead.  When you create your custom window, check to see if you will run into a length limitation on the text box.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified Professional

Thank you Steve for the clarification, I am gonna start working on it now , and come back with some feedback.
My first question is regarding the Rich Text Box , I do I put it in my window it doesn't show in my toolbox in VBA

Thanks,
David

Hi,

Unfortunately it looks like the Text Box is your only option in VBA.

From what I can tell, the limit is roughly 2048 characters.  If that is enough for your users, you could write code to ensure that they don't exceed that limit.  If you need more, you could use two of the text box controls.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified Professional

Well I am working on it.

I created the form and 2 buttons and a field to display/write the note.
This is what I currently have in my code, which is very basic.


-----------------------------------------------------------

Option Explicit
Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim strsql As String

Private Sub ButSave_Click()

    'Retrieve an ADO Connection for the current user
    Set cn = UserInfoGet.CreateADOConnection
    'Set the current Database company
    cn.DefaultDatabase = UserInfoGet.IntercompanyID
    cmd.ActiveConnection = cn

End Sub

------------------------------------------------------

Now I am very not familiar with Stored procedure. So I tried to copy and paste your code in my code but it become red so I know something is wrong.
Can you tell me how and where to put those lines , thanks

Hi,

You're in luck--I happened to find an old VBA script that calls the get next node procedure.

It did several other things, so I have tried to carve out the code that gets the next index, and there may be a few syntax issues you may need to adjust.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified Professional

Dim cn As New ADODB.connection
Dim cmd As New ADODB.Command
Dim strSQL As String

Dim intReturn As Integer

Set cn = UserInfoGet.CreateADOConnection
cn.DefaultDatabase = UserInfoGet.IntercompanyID
cmd.ActiveConnection = cn

'Get next note id and then insert batch record
strSQL = "DECLARE @I_sCompanyID smallint, @O_mNoteIndex numeric(19,5), @O_iErrorState int; " & _
         "select @I_sCompanyID = CMPANYID from DYNAMICS..SY01500 where INTERID = DB_Name() " & _
         "exec DYNAMICS.[dbo].[smGetNextNoteIndex] @I_sCompanyID, 1, @O_mNoteIndex output, @O_iErrorState output; SELECT @O_mNoteIndex"

cmd.CommandText = strSQL
cmd.Execute intReturn

Select allOpen in new window

thanks I am gonna try it

sorry for my long delay in responding to this one. well, I am back again on it and I am trying to understand your code and I sorry for my ignorance but can't figure it out some of the language/code:
and how do I use it.

those it retrieve the number or does it create a new number?
also the intreturn what is it for?

Thanks
David

I moving slowly and I found the Stored procedure in SQL and run it from there , and if I understood right this procedure looks for the next available noteindex.
now the parameter that needs to be pass is the companyid , which I think your code is passing it. but how do I get the notindex from this SQL statment?
when I look at the code in VBA the intreturn returns a 0 or a 1 , and I don't know how I can use that.
I guess I need your help for the next step.

Hi,

The VBA should call the stored procedure and store the value of @O_mNoteIndex into intReturn (you can name that variable whatever you want).

That value will be your next note index.

Does that make sense?

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified Professional

it does make sense , but for some reason when I run this code in my VBA the int return 1.
maybe I am missing something.
the INTERID is the database company that I am currently using , in my case it is 3 , but your code seems to pull the right one.

what is the 1 , in "@I_sCompanyID, 1" do i need to change the one to something?

Hi,

The "1" after the company ID parameter is the SQL session ID, which should theoretically be your PID, but I think can be just about any positive integer.

If you want to do it properly, you could use @@SPID, as mentioned in my first post.

I'll try the VBA code and see if I can get it to work or if it needs some tweaks.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified Professional

Thank you

Hi Steve,
have you got a chance to try the code?
thanks
David

Thank you so much it does work now. I would have never been able to figure this one.
If I understand correctly this last code is to get the next available note index available from the table Sy01500 in the Dynamics database?

what I have so far , is a note field and 2 buttons , one that say save and another one cancel. I have no code besides the save button.
If I click once on the save button , it does work and give me the note index number , but if I click a second time It doesn't work . I need to close my form and reopen it in order to work.
Is that normal ?
attached is the error message I get when clicking the second time.


Capture.JPG

Hi,

That is a strange error.  I'm able to click on my button over and over and it generates the next number without issue.

Attached is a slight update to the code that explicitly closes the recordset and database connection, in case that is an issue on your machine for some reason.

Give the new version a try and let me know if that eliminates the error.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified Professional

Dim cn As New ADODB.connection
    Dim cmd As New ADODB.Command
    Dim strSQL As String
    Dim rs As New ADODB.Recordset
    Dim companyID As Integer
    
    Dim intReturn As Integer
    
    Set cn = UserInfoGet.CreateADOConnection
    cn.DefaultDatabase = UserInfoGet.IntercompanyID
    cmd.ActiveConnection = cn
    
    strSQL = "SELECT CMPANYID from DYNAMICS..SY01500 where INTERID = DB_Name()"
    cmd.CommandText = strSQL
    Set rs = cmd.Execute
    companyID = rs.Fields("CMPANYID").Value
    rs.Close
    Set rs = Nothing
    
    cmd.Parameters.Append cmd.CreateParameter("I_sCompanyID", adSmallInt, adParamInput, , companyID)
    cmd.Parameters.Append cmd.CreateParameter("I_iSQLSessionID", adInteger, adParamInput, , 1)
    cmd.Parameters.Append cmd.CreateParameter("O_mNoteIndex", adNumeric, adParamInputOutput, , 0)
    cmd.Parameters("O_mNoteIndex").Precision = 19
    cmd.Parameters("O_mNoteIndex").NumericScale = 5
    cmd.Parameters.Append cmd.CreateParameter("O_iErrorState", adInteger, adParamInputOutput, , 0)
    
    strSQL = "DYNAMICS.dbo.smGetNextNoteIndex"
    
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = strSQL
    cmd.Execute
    
    cn.Close
    Set cn = Nothing
    
    MsgBox "Note index: " & cmd.Parameters("O_mNoteIndex").Value

Select allOpen in new window

nop that doesn't work . I mean it works but can't keep cliking.
here is the code that I have . and also when I click debug it brings me to line
Set rs = cmd.Execute

Option Explicit
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim strsql As String
Dim companyID As Integer

Private Sub ButSave_Click()
Dim NoteIndex As Integer

    'Retrieve an ADO Connection for the current user
    Set cn = UserInfoGet.CreateADOConnection
    'Set the current Database company
    cn.DefaultDatabase = UserInfoGet.IntercompanyID
    cmd.ActiveConnection = cn
    
    'this grab the companyID logged in
    strsql = "SELECT CMPANYID from DYNAMICS..SY01500 where INTERID = DB_Name()"
    cmd.CommandText = strsql
    Set rs = cmd.Execute
    companyID = rs.Fields("CMPANYID").Value
    rs.Close
    
    Set rs = Nothing
    cmd.Parameters.Append cmd.CreateParameter("I_sCompanyID", adSmallInt, adParamInput, , companyID)
    cmd.Parameters.Append cmd.CreateParameter("I_iSQLSessionID", adInteger, adParamInput, , 1)
    cmd.Parameters.Append cmd.CreateParameter("O_mNoteIndex", adNumeric, adParamInputOutput, , 0)
    cmd.Parameters("O_mNoteIndex").Precision = 19
    cmd.Parameters("O_mNoteIndex").NumericScale = 5
    cmd.Parameters.Append cmd.CreateParameter("O_iErrorState", adInteger, adParamInputOutput, , 0)
    
    strsql = "DYNAMICS.dbo.smGetNextNoteIndex"
    
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = strsql
    cmd.Execute
cn.Close
Set cn = Nothing
    MsgBox "Note index: " & cmd.Parameters("O_mNoteIndex").Value
    'NoteIndex = cmd.Parameters("O_mNoteIndex").Value
    
    'strsql = "insert INTO PSHITEMNOTE (ITEMNMBR, NOTEINDX)VALUES ('" & LblItem & "' , '" & NoteIndex & "') "
    'Set rs = cn.Execute(strsql)
    End Sub

Select allOpen in new window

I am not sure if I understand parameters correctly , but could it be that the paremeters are already saved and interfere with the code when running a second time?

well, I modified the code to work exactly the way I want it . it saves, update and read the note correctly.
attched is the full working code.
thank you for your help.
I am sure more questions will come up tomorrow.

Option Explicit
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim strSQL As String
Dim companyID As Integer
Dim NoteIndex As Integer
Dim binchanged As Boolean


'*********
'Sub called when the button save is clicked
'*********
Private Sub ButSave_Click()

    'Retrieve an ADO Connection for the current user
    Set cn = UserInfoGet.CreateADOConnection
    'Set the current Database company
    cn.DefaultDatabase = UserInfoGet.IntercompanyID
    cmd.ActiveConnection = cn
    
    'Check if the note exists in the database
    strSQL = " Select count(*) from PSHITEMNOTE where ITEMNMBR ='" & LblItem & "'"
    Set rs = cn.Execute(strSQL)
    
    'if record exists in PSHITEMNOTE table look if the records has text in the SY03900 table
    If rs.Fields(0).Value = 1 Then
        strSQL = " Select NOTEINDX from PSHITEMNOTE where ITEMNMBR ='" & LblItem & "'"
        Set rs = cn.Execute(strSQL)
        'store the noteindex for the item selected
        NoteIndex = rs.Fields("NOTEINDX").Value
        
        'check if the noteindex exists in the SY03900
        strSQL = "select count(*) from SY03900 where NOTEINDX='" & NoteIndex & "' "
        Set rs = cn.Execute(strSQL)
        
        'if record exists do an update otherwise create it
        If rs.Fields(0).Value = 1 Then
            strSQL = "UPDATE SY03900 set TXTFIELD='" & txtnote & "' where NOTEINDX= '" & NoteIndex & "'  "
            Set rs = cn.Execute(strSQL)
        Else
            strSQL = "insert INTO SY03900 (NOTEINDX, TXTFIELD)VALUES ('" & NoteIndex & "' , '" & txtnote & "') "
            Set rs = cn.Execute(strSQL)
        End If
    'records is new so we need to find the next index
    Else
        'this grab the companyID where the user is logged in
        strSQL = "SELECT CMPANYID from DYNAMICS..SY01500 where INTERID = DB_Name()"
        cmd.CommandText = strSQL
        Set rs = cmd.Execute
        companyID = rs.Fields("CMPANYID").Value
        'close the connection
        rs.Close
        Set rs = Nothing
    
        'set the parameters
        cmd.Parameters.Append cmd.CreateParameter("I_sCompanyID", adSmallInt, adParamInput, , companyID)
        cmd.Parameters.Append cmd.CreateParameter("I_iSQLSessionID", adInteger, adParamInput, , 1)
        cmd.Parameters.Append cmd.CreateParameter("O_mNoteIndex", adNumeric, adParamInputOutput, , 0)
        cmd.Parameters("O_mNoteIndex").Precision = 19
        cmd.Parameters("O_mNoteIndex").NumericScale = 5
        cmd.Parameters.Append cmd.CreateParameter("O_iErrorState", adInteger, adParamInputOutput, , 0)
        'execute the Stored Procedure to get the next note index #
        strSQL = "DYNAMICS.dbo.smGetNextNoteIndex"
        cmd.CommandType = adCmdStoredProc
        cmd.CommandText = strSQL
        cmd.Execute
    
        'MsgBox "Note index: " & cmd.Parameters("O_mNoteIndex").Value
        'store the noteindex available to a string
        NoteIndex = cmd.Parameters("O_mNoteIndex").Value
        
        strSQL = "insert INTO PSHITEMNOTE (ITEMNMBR, NOTEINDX)VALUES ('" & LblItem & "' , '" & NoteIndex & "') "
        Set rs = cn.Execute(strSQL)
        
        strSQL = "insert INTO SY03900 (NOTEINDX, TXTFIELD)VALUES ('" & NoteIndex & "' , '" & txtnote & "') "
        Set rs = cn.Execute(strSQL)
    
    End If
    binchanged = False
End Sub

'*********
'Note text has changed
'*********
Private Sub txtnote_Change()
    binchanged = True
End Sub

'*********
'Load the note if one exists
'*********
Private Sub UserForm_Initialize()

    'retrieve the Item Number from the Item Maintenance
    LblItem = ItemPriceListMaintenance.ItemNumber.Value
    
    'Retrieve an ADO Connection for the current user
    Set cn = UserInfoGet.CreateADOConnection
    'Set the current Database company
    cn.DefaultDatabase = UserInfoGet.IntercompanyID
    cmd.ActiveConnection = cn
    
    'Check in the database if the record exists in the database
    strSQL = " Select count(*) from PSHITEMNOTE where ITEMNMBR ='" & LblItem & "'"
    Set rs = cn.Execute(strSQL)
    
    'if record exists look if the records has text in the SY03900 table
    If rs.Fields(0).Value = 1 Then
        strSQL = " Select NOTEINDX from PSHITEMNOTE where ITEMNMBR ='" & LblItem & "'"
        Set rs = cn.Execute(strSQL)
        'store the noteindex for the item selected
        NoteIndex = rs.Fields("NOTEINDX").Value
        'check if the noteindex exists in the SY03900
        strSQL = "select count(*) from SY03900 where NOTEINDX='" & NoteIndex & "' "
        Set rs = cn.Execute(strSQL)
        If rs.Fields(0).Value = 1 Then
            strSQL = "select TXTFIELD from SY03900 where NOTEINDX= '" & NoteIndex & "' "
            Set rs = cn.Execute(strSQL)
            txtnote = rs.Fields("TXTFIELD").Value
        End If
    End If
    binchanged = False
    
End Sub


'*********
'The x button on top of the form is clicked to close the form
'*********
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

    Dim AnswerFromBox As String
    AnswerFromBox = ""
    'if x is pressed(0) and the form has been modified then call the yesnomessage
    If CloseMode = 0 And binchanged = True Then
        ' changed has been made do we want to save the change?
        AnswerFromBox = YesNoMessageBox()
        If AnswerFromBox = "YesAndSaved" Then
            Cancel = 0
        ElseIf AnswerFromBox = "NoNeedSave" Then
            Cancel = 0
        ElseIf AnswerFromBox = "Cancel" Then
            Cancel = 1
        End If
    Else
        ' nothing changed in the form we can close safely
        Cancel = 0
    End If
    
End Sub


'*********
'Message Box to see if we want to save the modif
'*********
 Function YesNoMessageBox() As String
 
    Dim Answer As String
    Answer = MsgBox("Do you Want To save your changes?", vbQuestion + vbYesNoCancel, "Save?")
    If Answer = vbYes Then
        Call ButSave_Click
        YesNoMessageBox = "YesAndSaved"
    ElseIf Answer = vbCancel Then
        YesNoMessageBox = "Cancel"
    ElseIf Answer = vbNo Then
        YesNoMessageBox = "NoNeedSave"
    End If

End Function

Select allOpen in new window