Link to home
Start Free TrialLog in
Avatar of taverny
taverny

asked on

Problem in my code

I have a problem with my code and I can't figure out why it is doing it.
the code that I have is supposed to grab some fields from a table and also enable or disable some buttons if the flag is set.
when I open my window everything seems to be fine but some of the button doesn't act the way it is suppose to act , if I go in my code and put some break point then the code seems to run fine but it doesn't do it , and also after running the code with break point I get an error message that says that " The selected record has been deleted" eventhow it is still in my database so I just need to go to the next record and comeback and then it's fine. but it seems I can't troubleshoot it.
so attached is a screen shot of my screen with the error message I get when I put my breakpoint . then I also attached a screen shot of the screen when I don't have breakpoint in my code and another screen shot with the same window when I click inside my screen and when I click inside it does show me  2 buttons that are supposed to be  grayed out
i also attached the full code associate with the window
the procedure that is causing the issue is probably the first one: Grid_beforeLinePopulate

MS GP2010 with VBA and Modifier.
I know I am throwing everything at you and I am not probably clear , but please if you need more info or detail I will provide.
thanks
David
'cleaned

Option Explicit

'This is for Database Connection
Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim strsql As String

'This dim is for storing the lineSeqNumber since it keeps changing for no reason
Dim LineSequence As String
'this dim is to disable to run the update code for the field when data is populated
Dim IntRunCode As Integer

'This is to run Dexterity Code
Dim CompilerApp As Object
Dim CompilerMessage As String
Dim CompilerError As Integer
Dim Commands As String
    
'*********
'this Declaration is used to launch the Shell Execute for the report Quote
'*********
Private Declare Function ShellExecute _
    Lib "shell32.dll" _
    Alias "ShellExecuteA" ( _
    ByVal hwnd As Long, _
    ByVal lpOperation As String, _
    ByVal lpFile As String, _
    ByVal lpParameters As String, _
    ByVal lpDirectory As String, _
    ByVal nShowCmd As Long) _
As Long


'*********
'this code go in item Maintenance and grab the Short description and Generic Description for the Item being repaired
'it also goes into User-Defined field and grab the Cust part# and requisition #
'*********
Private Sub Grid_BeforeLinePopulate(RejectLine As Boolean)
    
    'this is to initialize the Global Variable in Module1
    XReturn = 1
    
    'This Variable store the Line Item Sequence Because we can't do it on the fly
    LineSequence = LineSEQNumber.Value
    
    'this variable disable to run the changed field while populating the data
    IntRunCode = 0
    
    'this code is to put the line number infront of the RMALine
    StrLine = CStr(CDbl(LineSequence)) / 100
    
    'Retrieve an ADO Connection for the current user
    'Set the current Database company
    Set cn = UserInfoGet.CreateADOConnection
    cn.DefaultDatabase = UserInfoGet.IntercompanyID
    cmd.ActiveConnection = cn
    
    'Pull the RUSH field if the record exists in the database
    strsql = " select RUSH from PSHRMAXTRA where RMANUM = '" & RMAEntryUpdate.RMANumber & "' and LINESQNUM = '" & CStr(CDbl(LineSequence)) & ".00000' "
    Set rst = cn.Execute(strsql)
    If Not rst.EOF Then
        If rst.Fields("RUSH").Value = True Then
            Rush.Value = 1
        Else
            Rush.Value = 0
        End If
    End If

    'grab the Generic Description and Short Description for the item number
    strsql = " select ITMSHNAM,ITMGEDSC from IV00101 where ITEMNMBR = '" & RMAEntryUpdateDetail.ReturnItemNumber & "' "
    Set rst = cn.Execute(strsql)
    If Not rst.EOF Then
        StrManufac = Trim(rst.Fields("ITMSHNAM").Value)
        StrTypeOfProd = Trim(rst.Fields("ITMGEDSC").Value)
    End If
    
    'grab the Cust part# (UserDefined 3) from table SVC05200
    strsql = " select USRDEF03 from SVC05200 where RETDOCID = '" & RMAEntryUpdate.RMANumber & "' and LNSEQNBR = '" & CStr(CDbl(LineSequence)) & ".00000' "
    Set rst = cn.Execute(strsql)
    If Not rst.EOF Then
        StrCustPart.Value = Trim(rst.Fields("USRDEF03").Value)
    End If
    
    'grab the Work Order # or RTV # from table SVC05210
    strsql = " select SVC_Document_Number from SVC05210 where RETDOCID = '" & RMAEntryUpdate.RMANumber & "' and LNSEQNBR = '" & CStr(CDbl(LineSequence)) & ".00000' and SVC_Document_Number <> '' "
    Set rst = cn.Execute(strsql)
    If Not rst.EOF Then
        StrWORTVNum.Value = Trim(rst.Fields("SVC_Document_Number").Value)
        RepairItInHouse.Enabled = False
        Subcontract.Enabled = False
    Else
        RepairItInHouse.Enabled = True
        Subcontract.Enabled = True
    End If
    
    
    'If line Item doesn't exists yet don't execute the code
    If LineSequence <> "$0.00" Then
        
        'backup the Original Noteindex to a dummie string (Backup)
        StrBackupNote = NoteIndex.Value
        
        'Get the new IndexNote number from the WorkOrder Tables
        'if the note exist in the WORK Order run the SQL and Save the new Noteindex to the good NoteIndex for an RMA that is not Historical
        strsql = " select NOTEINDX from SVC06100 where RETDOCID = '" & RMAEntryUpdate.RMANumber & "' and LNSEQNBR = '" & CStr(CDbl(LineSequence)) & ".00000' "
        Set rst = cn.Execute(strsql)
        If Not rst.EOF Then
            NoteIndex = rst.Fields("NOTEINDX").Value
            'check in the table of notes to see if a note actually has content
            strsql = " Select count(*) from SY03900 where NOTEINDX = '" & CStr(CDbl(NoteIndex)) & ".00000'"
            Set rst = cn.Execute(strsql)
            'if content exists higlished the yellow one and disable the other one , if not enable the other one
            If rst.Fields(0).Value = 1 Then
                PBDepotNotePresent.Visible = True
                PBDepotNoteEmpty.Visible = False
            Else
                PBDepotNoteEmpty.Visible = True
                PBDepotNotePresent.Visible = False
            End If
        'No workorder exists yet so make invisible both note fields
        Else
            PBDepotNoteEmpty.Visible = False
            PBDepotNotePresent.Visible = False

            'Restore the original Index to not modify original code
        End If
        NoteIndex = StrBackupNote.Value
       
     End If
    
    'This pass the info to the Sub to rejectline ??? and the intRuncCode is set back to 1 so any changes is covered
    RejectLine = False
    IntRunCode = 1
    
End Sub


'*********
'update the UserDefined window for Customer Part
'*********
Private Sub StrCustPart_Changed()
    
    If IntRunCode = 1 Then
        'Retrieve an ADO Connection for the current user
        'Set the current Database company
        Set cn = UserInfoGet.CreateADOConnection
        cn.DefaultDatabase = UserInfoGet.IntercompanyID
        cmd.ActiveConnection = cn
               
        'push the cust part# in table SVC05200
        strsql = " Select count(*) from SVC05200 where RETDOCID = '" & RMAEntryUpdate.RMANumber & "' and LNSEQNBR = '" & CStr(CDbl(LineSequence)) & ".00000' "
        Set rst = cn.Execute(strsql)
        If rst.Fields(0).Value = 1 Then
            cmd.CommandText = " UPDATE SVC05200 set USRDEF03='" & StrCustPart & "' where RETDOCID = '" & RMAEntryUpdate.RMANumber & "' and LNSEQNBR = '" & CStr(CDbl(LineSequence)) & ".00000' "
            cmd.Execute
        Else
            MsgBox ("The line Item is not created yet. You need to Create the line item then retype the Customer Part#. If you don't retype it, the Customer Part will not be saved")
            StrCustPart.Value = ""
        End If
    End If

End Sub


'*********
'grab the Generic Description and Short Description for the item number that just been entered
'*********
Private Sub ReturnItemNumber_AfterUserChanged()

    'Retrieve an ADO Connection for the current user
    'Set the current Database company
    Set cn = UserInfoGet.CreateADOConnection
    cn.DefaultDatabase = UserInfoGet.IntercompanyID
    cmd.ActiveConnection = cn
        
    'grab the Generic Description and Short Description for the item number
    strsql = " select * from IV00101 where ITEMNMBR = '" & RMAEntryUpdateDetail.ReturnItemNumber & "' "
    Set rst = cn.Execute(strsql)
    If Not rst.EOF Then
        StrManufac = Trim(rst.Fields("ITMSHNAM").Value)
        StrTypeOfProd = Trim(rst.Fields("ITMGEDSC").Value)
    End If
    
End Sub


'*********
'update the Rush Field CheckBox
'*********
Private Sub Rush_Changed()
    
    If IntRunCode = 1 Then
        'Retrieve an ADO Connection for the current user
        'Set the current Database company
        Set cn = UserInfoGet.CreateADOConnection
        cn.DefaultDatabase = UserInfoGet.IntercompanyID
        cmd.ActiveConnection = cn
    
        'Check in the database if the record exists in the database
        strsql = " Select count(*) from PSHRMAXTRA where RMANUM = '" & RMAEntryUpdate.RMANumber & "' and LINESQNUM = '" & CStr(CDbl(LineSequence)) & ".00000' "
        Set rst = cn.Execute(strsql)
        
        'if the record already exists we do an update otherwise we do an insert
        If rst.Fields(0).Value = 1 Then
            cmd.CommandText = " UPDATE PSHRMAXTRA set RUSH = '" & Rush & "' WHERE RMANUM = '" & RMAEntryUpdate.RMANumber & "' and LINESQNUM = '" & CStr(CDbl(LineSequence)) & ".00000' "
            cmd.Execute
        Else
            cmd.CommandText = "insert INTO PSHRMAXTRA (RMANUM, RUSH, LINESQNUM) VALUES ('" & RMAEntryUpdate.RMANumber & "', '" & Rush & "', '" & CStr(CDbl(LineSequence)) & ".00000' )"
            cmd.Execute
        End If
        
        ' Create link without having reference marked
        Set CompilerApp = CreateObject("Dynamics.Application")
        Commands = "local boolean Result;" & vbCrLf
        If Rush.Value = 1 Then
            Commands = Commands & "Result = Field_SetFontColor('Return Document ID' of window SVC_RMA_Entry_DropDown of form SVC_RMA_Update, COLOR_RED);" & vbCrLf
        Else
            Commands = Commands & "Result = Field_SetFontColor('Return Document ID' of window SVC_RMA_Entry_DropDown of form SVC_RMA_Update, COLOR_BLACK);" & vbCrLf
        End If
        CompilerApp.CurrentProductID = 949 ' Field Services
        CompilerApp.CurrentProduct = CompilerApp.CurrentProduct & "!Modified"
        CompilerError = CompilerApp.ExecuteSanscript(Commands, CompilerMessage)
        If CompilerError <> 0 Then
            MsgBox CompilerMessage
        End If
        
    End If
    
End Sub


'*********
' this code is for the lookup for RTV# and WO# and launch the window
'*********
Private Sub ButWoRTVLookup_Changed()

    If Left(StrWORTVNum.Value, 3) = "WRK" Then
        WorkOrderEntryUpdate.Open
        WorkOrderEntryUpdate.WorkOrderNumber = StrWORTVNum.Value
    End If
    
    If Left(StrWORTVNum.Value, 3) = "RTV" Then
        RTVEntryUpdate.Open
        RTVEntryUpdate.RTVNumber = StrWORTVNum.Value
    End If
    
End Sub


'*********
' This sub is to pull the Note associate to the RMA from The WorkOrder, this is the external note for Client , on a note empty
'*********
Private Sub PBDepotNoteEmpty_Changed()

    'if the line item is not 00
    If LineSEQNumber <> "$0.00" Then
        'we backup the original value of the index
        StrBackupNote = NoteIndex.Value
        Set cn = UserInfoGet.CreateADOConnection
        cn.DefaultDatabase = UserInfoGet.IntercompanyID
        cmd.ActiveConnection = cn
        'if record exist then fill the field with the note index from RMA else check the note in the historical database
        strsql = " select NOTEINDX from SVC06100 where RETDOCID = '" & RMAEntryUpdate.RMANumber & "' and LNSEQNBR = '" & CStr(CDbl(LineSequence)) & ".00000' "
        Set rst = cn.Execute(strsql)
        If Not rst.EOF Then
            NoteIndex = rst.Fields("NOTEINDX").Value
        End If
        'open the note by calling the original note button
        RMAEntryUpdate.ItemNotePresent.Value = 1
        'NoteArrayPresentWindowArea.Value = 1
        'put back the original index to leave the code intact
        NoteIndex = StrBackupNote.Value
     End If
     
End Sub


'*********
' This sub is to pull the Note associate to the RMA from The WorkOrder, this is the external note for Client , on a note empty
'*********
Private Sub PBDepotNotePresent_Changed()

    'if the line item is not 00
    If LineSEQNumber <> "$0.00" Then
        'we backup the original value of the index
        StrBackupNote = NoteIndex.Value
        Set cn = UserInfoGet.CreateADOConnection
        cn.DefaultDatabase = UserInfoGet.IntercompanyID
        cmd.ActiveConnection = cn
        
        'if record exist then fill the field with the note index from RMA else check the note in the historical database
        strsql = " select NOTEINDX from SVC06100 where RETDOCID = '" & RMAEntryUpdate.RMANumber & "' and LNSEQNBR = '" & CStr(CDbl(LineSequence)) & ".00000' "
        Set rst = cn.Execute(strsql)
        If Not rst.EOF Then
            NoteIndex = rst.Fields("NOTEINDX").Value
        End If
        'open the note by calling the original note button
        RMAEntryUpdate.ItemNotePresent.Value = 1
        'NoteArrayPresentWindowArea.Value = 1
        'put back the original index to leave the code intact
        NoteIndex = StrBackupNote.Value
     End If
     
End Sub


'*********
'This Sub is to Create the WO directly from the RMA
'*********
Private Sub RepairItInHouse_AfterUserChanged()

    'this set the Xreturn to 5 wich is the index for 'Work Order Open' in the RMALineProcessReturn
    XReturn = 5
    'this click the process button to call the RMALineProcessReturn
    Process = 1
    'RMALineProcessReturn.hide

End Sub


'*********
'This Sub is to Create the RTV directly from the RMA
'*********
Private Sub Subcontract_AfterUserChanged()
    
    'this set the Xreturn to 5 wich is the index for 'Return To Vendor' in the RMALineProcessReturn
    XReturn = 2
    'this click the process button to call the RMALineProcessReturn
    Process = 1
    'RMALineProcessReturn.hide

End Sub


'*********
' Code to print Quote with No Rush Fee
'*********
Private Sub PrintQuote_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
    
    Dim res As Long
    Dim URL As String
    ' it is mandatory that the URL is prefixed with http:// or https://
    URL = "http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fRMAQuote9_NoRush&rs:Command=Render&RMANumbr=" & RMAEntryUpdate.RMANumber
    res = ShellExecute(0, "open", URL, 0, 0, 1)

End Sub


'*********
' Code to print Quote with Rush Fee
'*********
Private Sub PrintQuoteWithRush_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
    
    Dim res As Long
    Dim URL As String
    ' it is mandatory that the URL is prefixed with http:// or https://
    URL = "http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fRMAQuote9&rs:Command=Render&RMANumbr=" & RMAEntryUpdate.RMANumber
    res = ShellExecute(0, "open", URL, 0, 0, 1)

End Sub

Open in new window

With-Error.JPG
Before-I-click-inside-the-window.JPG
After-I-click-on-the-field.JPG
Avatar of Abdulmalek_Hamsho
Abdulmalek_Hamsho
Flag of United Arab Emirates image

Which GP version? Please upload the package.
Avatar of taverny
taverny

ASKER

I do it right now it's GP 2010
Avatar of taverny

ASKER

here is the package for all my field services
SVC-Package-02-18-11.txt
Would you please send me the SQL Statement for creating the customized tables please?
Avatar of taverny

ASKER

USE [TEST4]
GO

/****** Object:  Table [dbo].[PSHRMAXTRA]    Script Date: 02/18/2011 17:05:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[PSHRMAXTRA](
      [RMANUM] [char](15) NULL,
      [LINESQNUM] [numeric](19, 5) NULL,
      [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL,
      [RUSH] [bit] NULL,
      [REQUISITION] [char](30) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


Avatar of taverny

ASKER

USE [TEST4]
GO

/****** Object:  Table [dbo].[PSHUSERS]    Script Date: 02/18/2011 17:05:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[PSHUSERS](
      [PASSWORD] [nvarchar](10) NULL,
      [USERNAME] [nvarchar](10) NULL,
      [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]

GO


Avatar of taverny

ASKER

I think those 2 tables are the only one you need for RMA. let me know if you want the one that I created for Depot
You're not supposed to let the Scrolling window lose the focus while it's in PopulateLine event, for this reason you're getting the error message.

I think you want to enable the two buttons when the RMA is not linked to any RTV or WO. Am I correct?

It's working fine at my side, so please tell the exact situation you're facing? Is it:

1- The RMA is linked to RTV/WO but the buttons are enabled? OR

2- The RMA is not linked to any RTV/WO but the buttons are disabled.

?
Avatar of taverny

ASKER

Yes you are correct when my box on the bottom is empty ,I am assuming that the RTV or the WO is not created yet , but if there is one then I would like to button to be grayed out..
that would be the #1

try this and tell me if it works on your side. you have an RMA setup and you also have an WO associate to the first line of your RMA . now close the RMA window and reopen it , click on the arrow to expand RMA lines. and now use the bottom Arrow to scroll to the record that has a WO created .
on my side I see the WO # but I also see the 2 buttons enabled.

Avatar of taverny

ASKER

if you would like I can give you access to my screen remotely for you to experience the issue directly just let me know if you want.
Yes, that's what I was going to tell you. BeforePopulate is used to fill data not to set object properties, you in order to set the buttons properties you need to use the AfterLineGotFocus event.
Avatar of taverny

ASKER

Yes you want control of it ?
No need, I didn't see your message regarding the remote access. Just apply the Enable/Disable property in AfterLineGotFocus event.
Avatar of taverny

ASKER

ok I will try that , but doesn't the line get focus only if I click in the bottom part of the window?
It gets focus when you click on any object belong to the Scrolling window; the Grid.
Avatar of taverny

ASKER

so it currently works like that. I thought I could see the button disbale or enable just by scrolling record by record
I didn't get you.
Avatar of taverny

ASKER

Sorry. it's hard to explain. :)

if I use the buttons on the left bottom of the RMA to scroll from records to records , when the grid is expanded , I see everything populated correctly besides the Subcrontract button and Repair it in house button . those last 2 buttons seem to be enable . but as soon as I click on one of the field inside the grid , those button get disable like they are supposed to be. What I wanted to accomplish is to actually show the proper value of all the button just by scrolling through records. it's not a big issue if we can't but I thought it would be a cleaner apps if I could accomplish that.

I guess it's the same issue that the note field has, I don't know if you ever pay attention to this. when there is a note for a line item in the  RMA , the yellow sticky note doesn't turn yellow until that the line is actually active eventhow that the line is open , you would think that the sticky note would be showing without needing to click on one of the field.

does make sense?
Okay, since you have just one line per RMA trx, you can forget enabling/disabling the buttons in AfterLineGotFocus and BeforeLinePopulate, and apply the Enable/Disable buttons code along with the Note code in the Tempoary Control Number_Changed/RMA Number _Changed events.
Avatar of taverny

ASKER

I see I will try that now.
but I was talking about the default note that is on top of the line that doesn't work , not the one I created. isn't it weird?
Yes, alot of these non-standard things are there in the Field Service module.
Avatar of taverny

ASKER

ok I will try what you recommend me to do.
I let you know.
Avatar of taverny

ASKER

well, I guess I can't do that since I might have multiple RMA line .
I think I will just leave it this way for now, unless you see something else.
Okay, then initiate the two buttons according to the first line in the RMA, then put the same Enable/Disable code in the AfterLineGotFocus.
Avatar of taverny

ASKER

ok you lost me again.
so I leave the code where it is currently. and also put it in afterlinegotfocus? if not where is the sub for the first line?
ASKER CERTIFIED SOLUTION
Avatar of Abdulmalek_Hamsho
Abdulmalek_Hamsho
Flag of United Arab Emirates image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of taverny

ASKER

I will try that and post the result tonight. I will probably ask new questions late tonight or tommorrow morning.

Do you need me to alert you when I post a new question or do you get an alert on my username?
I'm getting an immediate notification upoon posting any MS Dynamics Question. In which country in the world you are?
Avatar of taverny

ASKER

In the USA. Chicago. You?
Syria, Damascus. I'm +3 GMT.
Avatar of taverny

ASKER

Putting the code in afterline got focus works for me . I don't need to put it in my other sub

thank you