Adding Customer Inactive checkbox to National Accounts form.

Question Status

Verified
David Flowers asked a question on 2 Jul 2014 11:53 AM

I'm struggling with a way to show the RM00101.INACTIVE field for Child Customers on the National Accounts Maintenance form. I don't want to restrict the Child Customers that show. I just need the checkbox to show if the Child account is Active ( unchecked ) or Inactive ( checked ). 

I added a checkbox field to the ChildScroll scrolling window but I can't come up with a vba script to populate the checkbox based on the Child Accounts returned.

Any help or sample code would be appreciated.

Thanks

DavidF.

Reply
Almas Mahfooz responded on 7 Jul 2014 4:58 AM

David,

It's a whole although small customization, don't you have any partners to do it?

Yes Modifier and VBA is a good option but it needs some expertise.

Reply
Somakarpagamoorthy Kanagasabapathy responded on 7 Jul 2014 5:27 AM

This would be helps us to resolve your issue if you share the VBA source code for above customization.

Reply
Somakarpagamoorthy Kanagasabapathy responded on 7 Jul 2014 5:38 AM

Are you added a checkbox field to the ChildScroll scrolling window with using Modifier? or Dexterity?

Note: I think we can't add the fields to the scrolling window rather than attached table with using Modifier.

Your reply is much appreciated.

Reply
David Flowers responded on 8 Jul 2014 6:33 AM

I added a checkbox field to the scrolling window using Modifier. But since the R00101.INACTIVE field isn't available to select from the Toolbox, tried using the "standard" ADODB script to query the field and try to update the checkbox.

I think a couple things I'm missing is I need to add a cursor of some sort and I don't think "Private Sub Grid_BeforeLinePopulate()" is what I need either.

I get the error message "Compile error: Procedure declaration does not match description of event or procedure having the same name."

I just want to show in the list on the National Accounts Maintenance window which Child Customers are Active or Inactive. It doesn't HAVE to be a checkbox, I'd be happy with a font color switch or anything else to visibly identify the Children.

Here's the vba script - for what it's worth.

Option Explicit

Dim cn As New ADODB.Connection

Dim rst As New ADODB.Recordset

Dim cmd As New ADODB.Command

Dim sqlstring As String

Private Sub Window_BeforeOpen(OpenVisible As Boolean)

   'Return an open ADO connection object using the credentials of the GP user logged into the current session

   Set cn = UserInfoGet.CreateADOConnection

   'Set the Default Database property to the company for the current session

   cn.DefaultDatabase = UserInfoGet.IntercompanyID

   'Setup to command object so it is ready for use

   cmd.ActiveConnection = cn

   cmd.CommandType = adCmdText

End Sub

Private Sub Grid_BeforeLinePopulate()

   If CustomerNumber = "" Then Exit Sub

   'Get the Inactive from the Customer Master table

   cmd.CommandText = "SELECT Inactive FROM RM00101 WHERE CUSTNMBR='" & CustomerNumber & "'"

   Set rst = cmd.Execute

   CustInactive = rst!Inactive

End Sub

Private Sub Window_AfterClose()

 'Release the objects we created

 Set rst = Nothing

 Set cmd = Nothing

 Set cn = Nothing

End Sub

Reply
Somakarpagamoorthy Kanagasabapathy responded on 8 Jul 2014 7:51 AM

Please have a look on the sample code for "Modifier - Adding a field to a scrolling window using ADO Example" from below link.

blogs.msdn.com/.../adding-a-field-to-a-scrolling-window-using-ado-example.aspx

Hope this helps!!!

Reply
David Flowers responded on 9 Jul 2014 7:18 AM

I have looked at that example before and dismissed it because it was designed to do something different than what I was wanting. However, I did try stripping out the VBA code from the package and tried modifying it for my purposes.

When I step through the Debug, I get this error at the " cmd.CommandType = 1 " line.

" Run-time error '3709': Requested operation requires an OLE DB Session object, which is not supported by the current provider."

Here's my current (non-working and highly plagiarized) script:

Option Explicit

   Dim cn As New ADODB.Connection

   Dim rst As New ADODB.Recordset

   Dim cmd As New ADODB.Command

   Dim sqlstring As String

Private Sub Window_BeforeOpen(OpenVisible As Boolean)

   ' ADO Connection

   Set cn = UserInfoGet.CreateADOConnection

   'Use a client-side cursor so that a recordset count can be obtained later.

   cn.CursorLocation = 3

   'set the database to the currently logged in db

   cn.DefaultDatabase = UserInfoGet.IntercompanyID

   ' df added from contractpricingmaint vba

   'Setup to command object so it is ready for use

   cmd.ActiveConnection = cn

   cmd.CommandType = adCmdText

End Sub

Private Sub Grid_BeforeLineGotFocus()

   ' ADO Command

   cmd.ActiveConnection = cn

   ' adCmdText

   cmd.CommandType = 1

   ' Command

   cmd.CommandText = sqlstring

   sqlstring = "SELECT INACTIVE FROM RM00101 WHERE CUSTNMBR='" & CustomerNumber & "'"

   ' Pass through SQL

   Set rst = cmd.Execute

   If Not (rst.EOF And rst.BOF) Then

   CustInactive.Value = RTrim(rst!Inactive)

   End If

   rst.Close

   Set rst = Nothing

   Set cmd = Nothing

End Sub

Private Sub Window_AfterClose()

   ' Close ADO Connection

   cn.Close

   Set cn = Nothing

End Sub

Reply
Somakarpagamoorthy Kanagasabapathy responded on 10 Jul 2014 3:06 AM

I never tried this on earlier. Give me some time I will try the same on my environment and let you know.

Reply
Arunprasath responded on 4 Aug 2014 11:01 AM

did you try writing this validation in grid_AfterLinePopulate, which is equivalent to line fill event in dexterity.

Reply
David Flowers responded on 4 Aug 2014 11:51 AM

Tried it, still not getting any results. I just want the grid to show whether  the Child account is Active (0) or Inactive (1).

Reply
Somakarpagamoorthy Kanagasabapathy responded on 4 Aug 2014 12:48 PM

I am not sure whether you can achieve your requirement by using VBA. But, you can achieve the same by using Dexterity.

Hope this helps!!!

Reply
Almas Mahfooz responded on 5 Aug 2014 3:32 AM

Davis,

That's what I tell you on first place, that if going for VBA then you should have expertise on it.

Dexterity is the best option if you know it.

btw you didn't mention any error you are getting, while running your code.

 

 

Reply
Verified Answer
Almas Mahfooz responded on 5 Aug 2014 4:47 AM

here http://evspakistan.wordpress.com/2014/08/05/adding-customer-inactive-checkbox-to-national-accounts-form/  find code snippet for your requirement. Don't need to mention you have to mark modified form to do the stuff.

almas

Reply
Mahmoud M. AlSaadi responded on 5 Aug 2014 7:48 AM

Nice article ... as usual, always such a hard working person ;)

Reply
Almas Mahfooz responded on 5 Aug 2014 7:53 AM

Just trying to be like great Saadi. :)

Reply
Mahmoud M. AlSaadi responded on 5 Aug 2014 7:57 AM

Come on .. !

It is absolutely an honor Ms.  ;)

Reply
Verified Answer
Almas Mahfooz responded on 5 Aug 2014 4:47 AM

here http://evspakistan.wordpress.com/2014/08/05/adding-customer-inactive-checkbox-to-national-accounts-form/  find code snippet for your requirement. Don't need to mention you have to mark modified form to do the stuff.

almas

Reply
Verified Answer
David Flowers responded on 5 Aug 2014 11:13 AM

Thanks Almas,

I marked yours as the answer even though your code did not work in my environment. You did help me clean up the jumbled mess of code I was trying to make work.

I started clean and came up with a working modification.

So to summarize:

  1. I added the RM_NationalAccounts_Maintenance window to Modifier.
  2. I created a Checkbox field named "CustInactive"  and added it to the scrolling Window, making room between the CustomerName and CurrentBalance fields.
  3. I added the RM_NationalAccounts_Maintenance window to VBA
  4. Added the CustomerNumber and CustInactive fields to VBA.
  5. Then added this vba code.
    1. ***  Start of VBA Code ***

      Option Explicit

      'Open the ADO connection

      Dim cn As New ADODB.Connection
      Dim rst As New ADODB.Recordset
      Dim cmd As New ADODB.Command

      Private Sub Grid_AfterLineLostFocus()

      'This code clears all variables used and closes the connections.

      If rst.State = adStateOpen Then rst.Close
      If cn.State = adStateOpen Then cn.Close

      End Sub


      Private Sub Grid_BeforeLinePopulate(RejectLine As Boolean)
      'Opens the connection to the Customer Master and retrieves the Inactive field

      'This code looks to see if there is already an open connection and if
      'so a command object for read-only recordsets will be established
      If rst.State = adStateOpen And cn.State = adStateOpen Then
      cmd.ActiveConnection = cn

      'This retrieves the Inactive field from Customer Master

      cmd.CommandType = adCmdText
      cmd.CommandText = "SELECT INACTIVE FROM RM00101 WHERE CUSTNMBR='" & CustomerNumber & "' "

      'This updates the CustInactive field which is a Checkbox Field Added via
      'Modifier to the NationalAccountsMaintDetail grid

      Set rst = cmd.Execute
      CustInactive = rst!Inactive

      Else
      'If a connection is not open, then the following will establish a link
      'to the data, create the recordset and select the data.
      Set cn = UserInfoGet.CreateADOConnection
      cmd.ActiveConnection = cn
      cmd.CommandType = adCmdText

      'Updates the CustInactive field

      cmd.CommandText = "SELECT INACTIVE FROM RM00101 WHERE CUSTNMBR='" & CustomerNumber & "' "
      Set rst = cmd.Execute
      CustInactive = rst!Inactive
      End If
      End Sub

      *** End of VBA Code ***

Thanks so much for your help. This is now working exactly the way I want it to.

David

Reply