Formatting form with form_current causing dirty event?

[Guest]

I use some code in a form's current event to format some controls and to null
some controls depending on other control's values . I also have some code in
the form's before update event that checks to see if the form is dirty, and
if it is, it prompts whether to save the changes or not. Everything works
except that I am prompted whether to save changes even when I am only
scrolling through the records.

Private Sub Form_Current()
If FundType Is Null
FundStatus.Locked = False
FundStatus.Enabled = True
FundStatus = Null
FundStatus.BorderColor = 0
FundStatus.Locked = True
Command12.Visible = False
End If
setupformforbalance
setupformforstatus
End Sub

Here are the two subs that are called by form_current:

Private Sub setupformforbalance()
If FundBalance = 0 Then
FundStatus = "Setup"
FundStatus.BackColor = 13421587
Else
FundStatus = "Active"
FundStatus.BackColor = 2284862
End If
End Sub


Private Sub setupformforstatus()
If FundStatus = "Setup" Then
cmdPauseButton.Caption = "Activate"
FundStatus.BackColor = 13421587
End If
If FundStatus = "Active" Then
cmdPauseButton.Caption = "Pause"
FundStatus.BackColor = 2284862
End If
End Sub

Here is the code in the form's before update event:

If Me.Dirty Then
strMsg = "Are you sure you want to save changes?"
If MsgBox(strMsg, vbQuestion + vbYesNo, strTitle) = vbNo Then
Me.Undo
End If
End If

I need to keep my formatting, etc. in the form's current event, but is there
a way to avoid getting the prompt to save changes when I'm only scrolling
through records?
Thank you.

 

[Guest]

If you modify a bound field in the OnCurrent event, you have updated the
record.

-Dorian

 

[Guest]

Does anyone know of a method or technique I can use to still get the
functionality I have in my form_current event but also not get the prompt
when I'm only scrolling through the records?
Thanks.

 

[Tim R.]

Does anyone know of a method or technique I can use to still get the
functionality I have in my form_current event but also not get the prompt
when I'm only scrolling through the records?
Thanks.

What you can do is create a global boolean variable, blnCurrentChange
and place it in your code like this:

dim blnCurrentChange as boolean

Private Sub Form_Current()
blnCurrentChange=true
.... your original code here....
End Sub

Private Sub Form_BeforeUpdate(Cancel as integer)
if blnCurrentChange = true then
blnCurrentChange = false
goto UpdateExit
end if
.... your original code here....
UpdateExit:
exit sub
End sub

Then create a FUNCTION called ResetSwitch() and bind this to all
beforeUpdate events for each of your bound controls (text boxes, combo
boxes etc); do this by setting the On Before Update property of each
control to "=ResetSwitch()" (without the quotes):

private function ResetSwitch()
blnCurrentChange = false
end function

now if any data is changed,then the switch is reset and your typical
Form_BeforeUpdate() sub routine will run.

two things:
1) ResetSwitch() has to be a function, not a sub or an error will
occur.
2) when a field's value is changed through VBA, it will not trigger a
control's BeforeUpdate event; only a direct change of a control's
value will cause this to happen. That is how you can get away with
this:
FundStatus = "Setup" will not trigger FundStatus' before update event,
but if you type data into FundStatus (or any other control), then that
control's event will be triggered.

Hope this helps

Tim

 

[Guest]

Hi Tim,
Thanks very much for your help and time. I will have think about whether I
should do this or just rely on my undo button. I've never created a function
or done anything with boolean, and even though I'm sure they're very clear, I
really don't fully understand your instructions yet. So I'm a little
intimidated, and also concerned that I might forget how it all works after a
while and troubleshooting might be tougher.
I should've known that it would take a good work around for me to get what I
wanted.

Thanks for the solution though.