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
Before-I-click-inside-the-window.JPG
After-I-click-on-the-field.JPG
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
With-Error.JPGBefore-I-click-inside-the-window.JPG
After-I-click-on-the-field.JPG
Which GP version? Please upload the package.
ASKER
I do it right now it's GP 2010
ASKER
here is the package for all my field services
SVC-Package-02-18-11.txt
SVC-Package-02-18-11.txt
Would you please send me the SQL Statement for creating the customized tables please?
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
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
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
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
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.
?
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.
?
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.
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.
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.
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.
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.
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.
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?
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.
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?
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.
ASKER
ok I will try what you recommend me to do.
I let you know.
I let you know.
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.
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.
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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?
ASKER
In the USA. Chicago. You?
Syria, Damascus. I'm +3 GMT.
ASKER
Putting the code in afterline got focus works for me . I don't need to put it in my other sub
thank you
thank you