Hi

My issue is that we have created a couple of date fields that we are updating with some pretty basic VB so that when a particular field is changed it updates a date field setting it to today’s date.

 The VB code is as follows:

 Sub cmdelecrejadmindate (Sender)

    Dim strSQL   

    Dim objCL, objSQL

    Dim objSLXDB

    Set objSLXDB = New SLX_DB

    set objCL = Application.GetNewConnection

    strSQL = "UPDATE DCLACCLOA SET ELEC_ADMINREJECTEDDATE = GETDATE() WHERE ACCOUNTID = '" &       Application.BasicFunctions.CurrentAccountID & "'"

    set objSQL = objCL.Execute (strSQL)

End sub

When we attach the sub routine to an on-change event on a field and then test it by changing the field value, it stamps the date field on to the correct field as it should but then also stamps another 1-3 (random) accounts with the date also.

Has anybody any idea what could be doing this?

What could possibly make the on-change event update other accounts other than the one that I am in?

The field that event is attached to is a TPickList – can I use this field type to run sub routines?

Is the syntax correct I’m using?

It’s a bit worrying that this could be happening in other areas also.

Any advice would be appreciated.

Thanks

Dave

What you will find is happening is that this event is firing multiple times (you just don't notice it). Put a MsgBox just after Dim strSQL and then run this again. You'll find it pops up 3 or 4 times. You need to move your event to somewhere more consistent (like the OnValidate part or OnClose as this will fire just the once.

Run the SLX Profiler and find out when it actually is firing.....OnChange events occur often for a field.....like every time you open up the form. Don't ever mistake an OnChange event for a WhenTheUserManuallyChangesTheFieldValue event...... not the same thing.

To fire off this function for a WhenTheUserManuallyChangesTheFieldValue we usually:

1. establish the initial value of the field in a global variable upon entry\loading of the form.

2. Either in the OK Button (save the data) for the form or OnExit event of the field.....compare the value of the field to the initial value of the field.....that's a user change.....WhenTheUserManuallyChangesTheFieldValue event.

3. note that prior to 7.5.x if you have an OnChange event for a field in a PANEL you will get two OnChange events for a field....once for exiting the field, once for exiting the field for being in a panel.....someone from Wheaton IL discovered the bug in 2009 after 2 years of fighting it and SLX fixed it years later....you are welcome.

PS: this is NOT Legacy development (Cypress Depressed Disabled BASIC), this is vbScript LAN development, still very much alive and kicking for high speed snappy use the power of your personal computer client server on premise computing.

<em>this is vbScript LAN development, still very much alive and kicking for high speed snappy use the power of your personal computer client server on premise computing.

Thanks for the replies (RJ & Mike), very much appreciated,

I did actually mistake an OnChange event for a WhenTheUserManuallyChangesTheFieldValue event....!!

So i need to obtain the current field value of the update field then hold it in a global variable when the form loads and then compare it with the value of the field on the form exit and if there is any difference then run my script to update the required date field.

How would I best obtain the field value initially on loading the loading the form?

i'm thinking I need to get the field value on load/open so something like:

Dim StatusChange

Sub AXFormOpen (sender)

StatusChange = getfield("pklAdminStatus")

Exit Sub

and then do a comparison whent the form closes/exits on to the next account:

Sub AXFormClose (sender)

If getfield("pklAdminStatus") <> StatusChange Then

UpdateSubRoutine

End If

Exit Sub

Would this work? I'm a bit unsure how to obtain the initial field value - do I use the get field function?

Any help again will be appreciated.

Thanks

Dave

Not quite - as the form won't close (if this is a detail form?) - use the OnChangeRecord instead. Make sure your dim'd variable is after option explicit (not within the sub) such that it is global.

Hi Mike - thanks for the reply again

Right......I think have the the first part of the solution where on form change the staus variable is obtained using the getfield function - i have test this by displaying it on screen and all looks well......My dilema is that where now do the comparison part, which I have already included, but it didn't work as you have already suggested as I put it on a form close event and as you have said the form doesn't close- the form is a details form by the way where we move from account to account using the arrow keys at the top of the screen.

You suggest using the onChangeRecord but I have already used this to get the original field value  - can I use the same to do the comparison and update the date field?

Also what about NULL fields if the field has a NULL value will it still do the comparrison oaky?

Here is my updated code:

option explicit

Dim elec_rejstatus

Sub AXFormChange(Sender)
elec_rejstatus = CheckForNull(getfield("ELEC_REJECTEDBYADMIN","DCLACCLOA","accountid='"& Application.BasicFunctions.CurrentAccountID &"'"))

If CheckForNull(getfield("ELEC_REJECTEDBYADMIN","DCLACCLOA","accountid='"& Application.BasicFunctions.CurrentAccountID &"'")) <> elec_rejstatus Then
cmdelecrejadmindate
End If

End Sub

As I said I can get the correctly variable displaying to screen so I know the first part is retrieving the field value, but i'm unsure where to do the update part.

Do you have any further suggestions?

Thanks again

Dave

Put a text box on the form that is the primary ID (the AccountID). Use its OnChange to get the value of the current picklist/object you want to examine. This will set the global var based on what's read during the screen update (i.e out of the database).
Use the OnRecordChange to examine the this gvar with whatever the content is now of the picklist/object you want to examine for change. If it's different then fire whatever you need to do. It should then consistently work.

Remember that the Change event of Controls will fire as you load Data the View and as you change the Current Record (Navigating between records).

So, you may want to keep in mind using the "IsReading" property of the Form.

When a form opens/refreshes, the Change events may fire multiple times.

By checking the Status Properties of the Form you can typically avoid these multiple unwanted firings of an OnChange event.

So, you could wrap your code with:

  If Not  frmMyForm.IsReading Then

    ExecuteMyCode Here

  End If

Hi Mike

I can understand what you want me to do and have successfully managed to insert into the global variable, however i can't find the OnRecordChange event.

Sorry.......just to put you in the picture incase i've posted this on the wrong forum I am using SalesLogix 7.2 - Is the on record change event available on this?

I've tried creating a subroutine on the onchange event on the actual filed itself and then doing the comparison with the global variable but it still isn't updating.

Thanks

Dave

Sorry, it's called OnBeforeRecordClose (not related to version).

Hi Mike thanks again for the replies

Just to update you.......

I have created a AXChangeAccount sub-routine that now has my date field update contained see below:

Sub AXChangeAccount (Sender)
    Dim strSQL
    Dim objCL, objSQL
    Dim objSLXDB
    Dim elec_newstatus

    elec_newstatus = CheckForNull(getfield("ELEC_REJECTEDBYADMIN","DCLACCLOA","accountid='"& Application.BasicFunctions.CurrentAccountID &"'"))

    If IsNull(elec_newstatus) Then
    msgbox "Null"
    Else
    msgbox elec_newstatus
    End If

    If elec_newstatus <> elec_rejstatus Then

    Set objSLXDB = New SLX_DB
    set objCL = Application.GetNewConnection
    strSQL = "UPDATE DCLACCLOA SET ELEC_ADMINREJECTEDDATE = GETDATE() WHERE ACCOUNTID = '" & Application.BasicFunctions.CurrentAccountID & "'"
    set objSQL = objCL.Execute (strSQL)
End If

As you can see i've added a variable (elec_newstatus) within the sub-routine  to hold the after update data. So now I'm displaying on screen the global variable (elec_rejstatus) which seems to display correctly and then then after changing the field and displaying and moving accounts i'm displaying on screen the correct new value in the elec_newstatus field. I'm achieving  this by using the OnAfterPost event - if I use the OnBeforeRecordClose I seem to display the same value as the global variable ine the elec_newstatus variable.

The only way i've managed to overcome this and get it working is to use the OnAfterPost event which seems to work initially and update on the first record I enter but then subsequent records don't update on the first'

This strikes me as being some sort of issue similar to the onclose event as the form isn't closing or something.

I know i'm nearly there its just very frustrating.....!!

Again any advice would be appreciated.

Thanks

Dave

I'm still confused why you are manually getting the data again (as I can't see all, I'm assuming this is the case). As SLX will load that data onto the form then it should be a simply matter of capturing it?

1) SLX Loads the record (OnChange of the AccID) - it doesn't matter this fires a few additional times - as the value will always be the same
2) You store the current data from the .Text property of the test object into a globalvar
3) OnBeforeRecordChange - you test the globalvar with the current content of your test object
4) If it's changed - do your update

From the code above - you are combining (1) and (4). Which won't work.

PS You can re-do your code (for future ref) as follows:

elec_newstatus = Getfield("IsNull(ELEC_REJECTEDBYADMIN,'')","DCLACCLOA","accountid='"& Application.BasicFunctions.CurrentAccountID &"'")

Your next line then becomes:

If elec_newstatus = "" Then

Quote: Originally posted by Mike Spragg 1) SLX Loads the record (OnChange of the AccID) - it doesn't matter this fires a few additional times - as the value will always be the same 2) You store the current data from the .Text property of the test object into a globalvar

Sometimes the field could be blank, sometimes will have the ID of the Previous records (when navigating across records).

It is possible that the AccID has the correct value, but other Fields are still refreshing and contain data from the Previous record read, or are blank, which will make your comparissons fail.

This is why you I mentioned that you also need to check the Form Status to make sure that it has finished Loading:

If Not form.IsReading Then

  'Do something here

End If

Hi Raul

Yes, I use that function but my point was, in this particular case, the "settlement" of AccID - even after multiple firings - would always be the AccID. If this wasn't the case then all hell would break loose ! Naturally, also, the value after that would be whatever data is in the form for that object. The separate event (onrecchange) can only be the data that is about to post (and to my knowledge, only fires once).

Your solution, to use .IsReading is the better course of action to take - but in this case I don't see the real need - it can be made to work as is. Just something else is being lost in translation here.

Regards

Mike

Hi Mike,Raul

Thanks for all your advice on this.

I know have it working.....Finally...!!

I'll be honest i'm not too sure why it has suddenly started working whether it was Mike's suggestion of using the IsNull instead of the function I was using I don't know. In the end I used the OnAfterPost event which occurs after the data has been comitted to the database, this ran my AXAccountChange sub routine, correctly compared the two variables and if there is a change it now runs my script to update the field. I've tested this and can't seem to find any issues with it. I thought I used event earlier this afternoon but maybe the syntax wasn't correct which is why it wasn't updating.

I have also incorporated Raul's sugesstion form.IsReading just to be safe.

Thanks again for your helpon this.

Dave