Solved Value changed after Function (1 Viewer)

[mloucel]

Hello ALL, I have a SUB, when I close a form that checks if there are any fields changed, if no field is changed then UNDO and close the form, if not save the fields, but for a reason I cannot comprehend when I finish checking the fields and my variable is set to "TRUE", comes back to my SUB and now the Variable is ONCE AGAIN set to "F" I've tried different possibilities, changing the names of the sub and the variable, setting the variable to boolean, placing msgbox all over the place to see where is changing and I cannot find why.
Any help will be appreciated, Thanks...

Private Sub SaveNCloseBtn_Click()
    Dim ChkFlds As String ' I Tried changing this to boolean same result
    ChckFlds = "F"
    
    If (IsNull(UserName) Or IsNull(UserLogin) Or IsNull(UserPassw)) Then
        DoCmd.Close acForm, Me.Name, acSaveNo
        Exit Sub
    End If
    
    OfficeID = TempVars!TOfficeID2
    
    Call CheckingFields
    
    If Form.Dirty = True And ChckFlds = "F" Then
      
       MsgBox "Here again " & "Check: " & ChckFlds
      
       Form.Dirty = False
        
    Else
        MsgBox "Close Form"
        Me.Undo
        'DoCmd.RunCommand acCmdUndo
        DoCmd.Close acForm, Me.Name, acSaveNo

    End If
    
End Sub

Function CheckingFields()
    MsgBox UserLogin.Value & " OLD VALUE: " & UserLogin.OldValue
    If UserName.OldValue = UserName.Value And UserLogin.OldValue = UserLogin.Value And _
        UserPassw.OldValue = UserPassw.Value And Level.OldValue = Level.Value Then ChckFlds = "True"
    
    If ChckFlds = "True" Then MsgBox "Now CheckFields is: " & ChckFlds
    
    ' when this goes back to SaveNCloseBtn DAMN
    ' ChckFlds is again F... WHY?????
    
End Function

 

[MajP]

Checkfields is a local variable and you neither pass anything to the function or return anything.
make sure to put option explicit at top of your code, it would catch this problem.
maybe

Private Function CheckFields() as boolean
    MsgBox UserLogin.Value & " OLD VALUE: " & UserLogin.OldValue
    If UserName.OldValue = UserName.Value And UserLogin.OldValue = UserLogin.Value And _
        UserPassw.OldValue = UserPassw.Value And Level.OldValue = Level.Value Then
      CheckFields = True
End Function

You would call this from the main procedure
if CheckFields() = true then

Something like

Private Sub SaveNCloseBtn_Click()
     If (IsNull(UserName) Or IsNull(UserLogin) Or IsNull(UserPassw)) Then
        DoCmd.Close acForm, Me.Name, acSaveNo
        Exit Sub
    End If
    OfficeID = TempVars!TOfficeID2
    
    If Form.Dirty = True And CheckFields() = False Then
       Form.Dirty = False
    Else
        MsgBox "Close Form"
        Me.Undo
        'DoCmd.RunCommand acCmdUndo
        DoCmd.Close acForm, Me.Name, acSaveNo
   End If
    
End Sub

 

[Gasman]

If no change, why the need of an UNDO?

 

[Josef P.]

Dim ChkFlds As String ' I Tried changing this to boolean same result
ChckFlds = "F"

Is ChckFlds as Control in the form?
The variable ChkFlds is never used.

Is Option Explicit declared in codemodule header?

 

[Gasman]

Need to google 'scope of access variables'

 

[MajP]

From Allen Browne

Option Explicit​

If you omit Option Explicit from any module, IMHO, you deserve everything you get. A simple spelling mistake, and your code creates and initializes a new variable to a value very different from what you expect your variable to contain. Option Explicit is a great safety net for your simplest or most daring trapeze act. Omit it to save a few declarations, and expect hours of elusive debugging.

Solution:​

1. Go back and manually insert Option Explicit in the General Declarations of every existing module, included any modules associated with forms or reports.
2. To automatically include Option Explicit in all new modules, check the "Require Variable Declaration" check box. In Access 2000 or 2002, it is under Tools | Options | Editor (from the code edit window). In Access 95 or 97, it is under Tools | Options | Module.

This is why you got in trouble. It is the dumbest thing that VBA allows. VBA allows you to create variables on the fly, unless you set Option Explicit. As Allen Browne states you deserve everything you get.

Function CheckingFields()
      'In your code you assign "true" to some newly created on the fly variable ChkFields which is not want you meant
     'If you had option explicit you would get a compile error telling you there is not such variable as Chkflds
    'At least you would have an idea of what is going on

     UserPassw.OldValue = UserPassw.Value And Level.OldValue = Level.Value Then ChckFlds = "True"
       If ChckFlds = "True" Then MsgBox "Now CheckFields is: " & ChckFlds
        ' when this goes back to SaveNCloseBtn DAMN
    ' ChckFlds is again F... WHY?????
   
End Function

 

[Gasman]

how to set option explicit in vba - Google Search

 

[The_Doc_Man]

As others have mentioned, you are misusing the function concept in multiple ways.

First, you define the function but give it no datatype. A function is meant to RETURN a value and that value has a datatype. It will therefore default to a VARIANT datatype, which can be anything - including nothing, which it is because ...

Second, inside the function, the name of the function is the ONLY thing that the calling program would ever see. You use a locally declared variable to hold your T/F result - but that locally declared variable sits there - and will vanish the MOMENT the function exits and returns control to its caller. It does so because the locally declared variables in the function only exist while you are still executing code inside the function. Also inside the function, the name of the function is treated as a variable of the indicated data type. So to return a value, you assign the function's name to the desired value. MajP shows an example of this in post #2 of this thread.

Third, when you invoke a function for which you intend to use the result, you must ASSIGN a variable to have that value using the "=" operator. When you activate a function with the CALL syntax, you DO run the function code - but that syntax leaves you no place to hold the returned value, which means that you DISCARD the results of the function. Unfortunately, it happens that the technical definition of VBA syntax does NOT make this usage illegal, since it is possible that your function depends on alternate methods to leave data behind. Such alternatives aren't illegal but usually are treated as highly suspicious, so I won't go into "side effect" methods.

Fourth, you used ChckFlds outside the function - but the laws of variable scope say that this is a different entity than the ChckFlds inside the function because they have different scope of declaration. (In both the calling subroutine and the called function, that variable's declaration is IMPLIED.) So when you say that you see the value as TRUE in the function but FALSE in the code that called it, it is because you were looking at two different things. As pointed out by Josef P. and MajP, an OPTION EXPLICIT would have at least called out the declaration errors.

Read this linkTHOROUGHLY and be sure you understand it. There is a lot to learn in this link, but it should answer a lot of the WHY questions.

Function statement (VBA)

Office VBA reference topic

learn.microsoft.com

 

[mloucel]

Checkfields is a local variable and you neither pass anything to the function or return anything.
make sure to put option explicit at top of your code, it would catch this problem.
maybe

Private Function CheckFields() as boolean
    MsgBox UserLogin.Value & " OLD VALUE: " & UserLogin.OldValue
    If UserName.OldValue = UserName.Value And UserLogin.OldValue = UserLogin.Value And _
        UserPassw.OldValue = UserPassw.Value And Level.OldValue = Level.Value Then
      CheckFields = True
End Function

You would call this from the main procedure
if CheckFields() = true then

Something like

Private Sub SaveNCloseBtn_Click()
     If (IsNull(UserName) Or IsNull(UserLogin) Or IsNull(UserPassw)) Then
        DoCmd.Close acForm, Me.Name, acSaveNo
        Exit Sub
    End If
    OfficeID = TempVars!TOfficeID2
   
    If Form.Dirty = True And CheckFields() = False Then
       Form.Dirty = False
    Else
        MsgBox "Close Form"
        Me.Undo
        'DoCmd.RunCommand acCmdUndo
        DoCmd.Close acForm, Me.Name, acSaveNo
   End If
   
End Sub

Thank you, I am actually learning and working, it is a process of failures and corrections, Thanks for the advice I just learn a new lesson, actually 2 I declare all the variables (and will change the rest of the application code as well)

 

[mloucel]

As others have mentioned, you are misusing the function concept in multiple ways.

First, you define the function but give it no datatype. A function is meant to RETURN a value and that value has a datatype. It will therefore default to a VARIANT datatype, which can be anything - including nothing, which it is because ...

Second, inside the function, the name of the function is the ONLY thing that the calling program would ever see. You use a locally declared variable to hold your T/F result - but that locally declared variable sits there - and will vanish the MOMENT the function exits and returns control to its caller. It does so because the locally declared variables in the function only exist while you are still executing code inside the function. Also inside the function, the name of the function is treated as a variable of the indicated data type. So to return a value, you assign the function's name to the desired value. MajP shows an example of this in post #2 of this thread.

Third, when you invoke a function for which you intend to use the result, you must ASSIGN a variable to have that value using the "=" operator. When you activate a function with the CALL syntax, you DO run the function code - but that syntax leaves you no place to hold the returned value, which means that you DISCARD the results of the function. Unfortunately, it happens that the technical definition of VBA syntax does NOT make this usage illegal, since it is possible that your function depends on alternate methods to leave data behind. Such alternatives aren't illegal but usually are treated as highly suspicious, so I won't go into "side effect" methods.

Fourth, you used ChckFlds outside the function - but the laws of variable scope say that this is a different entity than the ChckFlds inside the function because they have different scope of declaration. (In both the calling subroutine and the called function, that variable's declaration is IMPLIED.) So when you say that you see the value as TRUE in the function but FALSE in the code that called it, it is because you were looking at two different things. As pointed out by Josef P. and MajP, an OPTION EXPLICIT would have at least called out the declaration errors.

Read this linkTHOROUGHLY and be sure you understand it. There is a lot to learn in this link, but it should answer a lot of the WHY questions.

Function statement (VBA)

Office VBA reference topic

learn.microsoft.com

Thanks a LOT @The_Doc_Man this was believe or not my very FIRST function EVER, but I have learned very important lessons, your advise is extremely appreciated. My learning is a process while I am working, I am learning, I am so grateful for your kind advise.