If I declarer a module level variable with a Dim/ Private keyword, do I have to assign it's value at the same time? If I assign the value while declaration, do get to change it's value in different procedures?
If I declarer a module level variable with a Dim/ Private keyword, do I have to assign it's value at the same time? If I assign the value while declaration, do get to change it's value in different procedures?
Why not just try it out? Would take a few seconds?
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
you can assign it anytime.
Perhaps you could tell readers What you are trying to accomplish - there may be options for How to achieve.
Tempvars??
More info on scope of variable here.
If I assign the value while declaration, I get the error as in the pic. It occurs at the click event.
However if I don't assign in the declaration stage it works fine. The code is below.
Code:Option Compare Database Option Explicit Private yctr As Boolean yctr = False Private Sub Form_BeforeUpdate(Cancel As Integer) If Me.NewRecord Then If DCount("Payment_ID", "Payment_CNF") < 1 Then Me.Payment_ID = 1 Else Me.Payment_ID = DMax("Payment_ID", "Payment_CNF") + 1 End If End If ' This procedure checks to see if the data on the form has ' changed. If the data has changed, the procedure prompts the ' user to continue with the save operation or to cancel it. On Error GoTo Err_BeforeUpdate ' The Dirty property is True if the record has been changed. If Me.Dirty And yctr = False Then If MsgBox("Do you want to save?", vbYesNo + vbQuestion, "Save Record") = vbNo Then Me.Undo End If End If Exit_BeforeUpdate: yctr = False Exit Sub Err_BeforeUpdate: MsgBox Err.Number & " " & Err.Description Resume Exit_BeforeUpdate yctr = False End Sub Private Sub PayAssign_Click() Dim FKCNFID As Byte, newAmount As Long, dt As Date, methd As String, _ Sq As String, xCounter As Boolean If Not IsNull(Me.Cons_ID) And IsNull(Me.Cons_ID.OldValue) Then FKCNFID = Me.CNF_ID newAmount = Me.Amount.OldValue - Me.Amount dt = Me.Paid_Dt methd = Me.Payment_Method.OldValue Sq = "insert into Payment_CNF values (" & (DMax("Payment_ID", "[Payment_CNF]") + 1) & " , " _ & "NULL" & " , " & FKCNFID & " , #" & dt & "# , " & newAmount & " , '" & methd & "' );" If newAmount >= 0 Then On Error GoTo out DoCmd.RunSQL Sq yctr = True Me.Dirty = False End If out: Me.Undo Exit Sub End If End Sub
Last edited by bubai; 12-08-2020 at 12:18 AM. Reason: Missed to include some of the code.
The reason for the error is because the first 'procedure' has no header line e.g. Private Sub SomethingOrOther_BeforeUpdate(Cancel As Integer) so none of the initial code is part of a procedure
I just put my foot in it!
Actually the header line was there (ie. Private Sub Form_BeforeUpdate(Cancel As Integer)). I missed to include that in this upload (now corrected- please check). However, the problem persists with this header in place.
I don't believe you can assign a value to yctr outside of a procedure? I think I have always set a module level variable within a procedure.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
But Microsoft says otherwise as in the link.
https://docs.microsoft.com/en-us/off...y#module-level
Declaring a variable refers to the line Private yptr As Boolean or more usually Dim ypt As Boolean'
See Declaring variables
I also always set the value in a procedure.
In any case, a boolean value is automatically false when first defined so that line isn't needed there. You should remove it.
The line is also redundant here:
The Resume Exit_BeforeUpdate line means the following line is never executed. You set it to false in the Exit_BeforeUpdate section anyway.Code:Err_BeforeUpdate: MsgBox Err.Number & " " & Err.Description Resume Exit_BeforeUpdate yctr = False End Sub
Can I also suggest you use proper error handling in your other procedure
This link also says they can be declared outside procedure.Declaring a variable refers to the line Private yptr As Boolean or more usually Dim ypt As Boolean'
See Declaring variables
Is there any way to set the value outside the procedure?I also always set the value in a procedure.
Error also occurs if I set value to some other types of variable (eg integer). I may sometimes need to declare variable other than boolean.In any case, a boolean value is automatically false when first defined so that line isn't needed there.
Exit_BeforeUpdate is derived from Err_BeforeUpdate: statement. So if I want to set yctr to False even when there is no error do I put it on the code like this?The line is also redundant here:
The Resume Exit_BeforeUpdate line means the following line is never executed. You set it to false in the Exit_BeforeUpdate section anyway.Code:Err_BeforeUpdate: MsgBox Err.Number & " " & Err.Description Resume Exit_BeforeUpdate yctr = False End Sub
Can I also suggest you use proper error handling in your other procedure
Also, for the Sub PayAssign_Click() if I click NO to the append warnings, Run-time error 2501 occurs and it says, "The RunSql action was cancelled." So I put Me.Undo on catch of the error. What else do you suggest?Code:yctr = False Exit_BeforeUpdate: yctr = False Exit Sub Err_BeforeUpdate: MsgBox Err.Number & " " & Err.Description Resume Exit_BeforeUpdate End Sub
I think I need to clarify what I was saying:
Yes of course they can. I wasn't saying otherwise
If declared using Dim/Private at module level, their scope applies to the entire module.
If declared in a procedure, the scope applies to that procedure only.
Yes e.g. you can set the value in a table but that's not relevant in your situation
Set the value in the procedure .... but unnecessary in the case of a boolean as it is false by default
Just set the value in the procedure
Where there is no error the code automatically moves to the Exit_BeforeUpdate section where you are setting yptr to False. Where an error occurs, the Resume Exit_BeforeUpdate line also ensures that yptr is set to False.Exit_BeforeUpdate is derived from Err_BeforeUpdate: statement. So if I want to set yctr to False even when there is no error do I put it on the code like this?
Code:yctr = False 'OMIT THIS LINE IS SUPERFLUOUS Exit_BeforeUpdate: yctr = False Exit Sub Err_BeforeUpdate: MsgBox Err.Number & " " & Err.Description Resume Exit_BeforeUpdate End Sub
So there is no need to set it again anywhere else
What I meant was use proper error handling code like you did in the BeforeUpdate event. I would use something like this:
Hope that helps rather than adds further confusionCode:Private Sub PayAssign_Click() On Error GoTo Err_Handler ....your code here Exit_Handler: Exit Sub Err_Handler: If err=2501 Then Cancel=True MsgBox "Error " & err.Number & " : " & err.Description & " in PayAssign_Click procedure, vbCriical, "Program Error" Resume Exit_Handler End Sub
Last edited by isladogs; 12-08-2020 at 08:43 AM. Reason: Restored missing section at start
Declaring a variable is one thing. Assigning a value to it is another. I saw nothing in that link that says you can assign outside of a procedure.
I'm saying you cannot.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
Please use the star below the post to say thanks if we have helped !
↓↓ It's down here ↓↓