How to enable a button if any of controls changed (1 Viewer)

silversun

Registered User.
Local time
Today, 02:05
Joined
Dec 28, 2012
Messages
204
I have an unbounded form that I can successfully load it up with data by VBA codes. Now I need to create a sub routine (or similar) that once user changes any data in my controls then a button named btn_update become enabled. Can you please help me?
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 05:05
Joined
Apr 27, 2015
Messages
6,286
Use the Form’s OnDirty event:

btn_Update.Enabled = True
 

isladogs

MVP / VIP
Local time
Today, 09:05
Joined
Jan 14, 2017
Messages
18,186
Or you could use a bound form and data changes will be saved automatically.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:05
Joined
May 21, 2018
Messages
8,463
Use the Form’s OnDirty event
Does not work on unbound forms AFAIK
https://docs.microsoft.com/en-us/office/vba/api/access.form.dirty(even)
Examples of this event include entering a character directly in the text box or combo box or changing the control's Text property setting by using a macro or Visual Basic.

Modifying a record within a form by using a macro or Visual Basic doesn't trigger this event. You must type the data directly into the record or set the control's Text property.

This event applies only to bound forms, not an unbound form or report.


Also unbound controls do not have an "oldvalue" property to compare. Have to roll your own.
Try this
Code:
Private OldValue As Variant

Public Function EnableButton()
  If Me.ActiveControl.Value <> OldValue Then
    Me.Command8.Enabled = True
  End If
End Function

Public Function SaveOld()
  OldValue = ActiveControl.Value
End Function

In design view select all controls at once.
In the OnEnter event property enter
=SaveOld()
In the OnExit event property enter
=EnableButton

The only issue is that this only works if you exit a control, which means you may have to click out of the current control into something else. You could use the onchange event as soon as they change it (you have to then use the text property), but that may be problematic if they change it back to the original value. The other way would have the button enabled and if anything has changed then do X else message box "no updates done".

You can try this version
Code:
Public Function EnableButton()
  Me.Command8.Enabled = ActiveControl.Text <> OldValue
End Function
And put this instead in the onChange event. The issue with that is you could disable the control after enabling it, in a somewhat complicated set of steps.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:05
Joined
May 7, 2009
Messages
19,170
just make one function in your form:
Code:
Public Function fncEnable()
    Me.commandButton.Enabled=True
End
put on each textbox/combobox On Change Event:

=fncEnable()
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:05
Joined
May 21, 2018
Messages
8,463
Arnelgp's code may be good enough. I avoid the case where you have "cat" in the textbox they start typing "dog", but change it back to "cat". Thus the reason to save the oldvalue to make sure the value has actually changed.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 05:05
Joined
Apr 27, 2015
Messages
6,286
Does not work on unbound forms AFAIK

I was not aware, but it does make sense. I will have to give a test... Thanks for the input.

Edit: It turns out MajP was spot-on. Learn something new every day...
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 09:05
Joined
Jan 14, 2017
Messages
18,186
Arnel's idea should indeed work but it should be a Private Function.

Just tested the Form_Dirty event on both an unbound form and a bound form with an unbound control. Neither work but it of course works perfectly for bound controls

The OP should still consider whether an unbound form is really necessary as it is a lot of extra coding for usually no gain.
 
Last edited:

NauticalGent

Ignore List Poster Boy
Local time
Today, 05:05
Joined
Apr 27, 2015
Messages
6,286
Arnel's idea should indeed work but it should be a Private Function.

The OP should still consider whether an unbound form is really necessary as it is a lot of extra coding for usually no gain.

Agreed...and...my suggestion would work then and that is what's really important!:cool:
 

isladogs

MVP / VIP
Local time
Today, 09:05
Joined
Jan 14, 2017
Messages
18,186
I agree. The form dirty event is definitely the simplest method if you do have a form with bound controls
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:05
Joined
Feb 28, 2001
Messages
27,001
Here is the big question. You wanted a button to allow you to do an update on an unbound form (and you say you have a way to load the form with values initially). Are you perhaps trying to simulate with VBA everything that a bound form would have done anyway?

Is there some reason that you want to avoid making a form bound to whatever it is that you are trying to update? Because updating something after a change is EXACTLY what Access will do for bound forms. I would never tell you that you can't do what you are doing now, but since you came here for advice, I reserve the right to comment. In this case, it SEEMS like you are trying to reinvent the wheel.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:05
Joined
May 21, 2018
Messages
8,463
Arnel's idea should indeed work but it should be a Private Function
Out of curiosity what is the point, of saying this? Seems a little pedantic. I think it "could" be a private function not that it "should" be a private function. I am all for limiting scope and exposure, but cannot envision a scenario where making this public could have any impact. How would you even call this from an external process?

forms("form1").enableButton()

Sure that would enable a button on form1 based on changes in form 2, but that is a reach.
 

silversun

Registered User.
Local time
Today, 02:05
Joined
Dec 28, 2012
Messages
204
Thanks for reply. I wanted to have couple of functions that do the job without copy and pasting the same expression into OnChange or AfterUpdate event of all 15 controls which adds too much codes into my VBA module.
Your code seems to be the right one. It has only two functions and small changes in property sheet. But unfortunately it does give me error as " The object doesn't contain the Automation object 'EnableButton.' ".
Here is the code I put into my VBA module:
Code:
Private OldValue As Variant
Public Function EnableButton()
  If Me.ActiveControl.Value <> OldValue Then
    Me.btn_update.Enabled = True
  End If
End Function

Public Function SaveOld()
  OldValue = ActiveControl.Value
End Function
Please help me to find a solution. Thanks
 

silversun

Registered User.
Local time
Today, 02:05
Joined
Dec 28, 2012
Messages
204
You are right with bounded forms. Initially I started my project with bounded forms but it would make duplicated records or some other weird actions so that I switched to unbounded forms and using VBA codes I can now control everything with least number of errors.
 

silversun

Registered User.
Local time
Today, 02:05
Joined
Dec 28, 2012
Messages
204
Thank you for reply.
I didn't want to copy and paste the same expression into OnChange or AfterUpdate event of all 15 controls which adds too much codes into my VBA module. I am trying to find a simpler solution by couple of functions or changes into my VBA module in order to do the same job. Please let me know if there is a solution like that to my issue.
Thanks
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:05
Joined
Feb 28, 2001
Messages
27,001
If you were worried about a bound form leading to duplicates, you could have placed a unique index on the field that was getting duplicated, then just trap the error. You lose more than you gain by unbinding the fields.
 

isladogs

MVP / VIP
Local time
Today, 09:05
Joined
Jan 14, 2017
Messages
18,186
Out of curiosity what is the point, of saying this? Seems a little pedantic. I think it "could" be a private function not that it "should" be a private function. I am all for limiting scope and exposure, but cannot envision a scenario where making this public could have any impact. How would you even call this from an external process?

forms("form1").enableButton()

Sure that would enable a button on form1 based on changes in form 2, but that is a reach.

@MajP
Well perhaps you're right about me being pedantic.
I must have been channeling my inner MajP when I wrote that.

Some might think the point was to give you something pedantic to say in response...:rolleyes:

But in fact the point is valid and I expect it applies equally to your Public functions in post #4 though I've not tested those.

It took me less than 5 minutes to create a simple example to illustrate the point. For a highly capable programmer like yourself it would probably have taken less a minute to check before replying.

The example app has 2 almost identical unbound forms:
Form1 - contains Arnel's Public function - it works perfectly on that form
Form2 - includes code to call that function using Forms!Form1.fncEnable in the controls Change events

With both forms open, any change to controls in Form2 triggers the function and enables the Update button in Form1 which isn't what you want!
If only Form2 is open, the change event triggers error 2450 - Access cannot find Form1

However if the fncEnable function is Private, its scope would be limited to Form1 and there would be no issues
 

Attachments

  • EnableButtonTest.zip
    23.1 KB · Views: 84
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:05
Joined
May 21, 2018
Messages
8,463
I didn't want to copy and paste the same expression into OnChange or AfterUpdate event of all 15 controls which adds too much codes into my VBA module. I am trying to find a simpler solution by couple of functions or changes into my VBA module in order to do the same job. Please let me know if there is a solution like that to my issue.

VBA does not make it easy or clear for a single procedure to handle multiple events. In something like VB.Net you can write a single procedure and using the "Handles" clause you can have it handle multiple events. It is very clear which event it handles.

Code:
Private Sub Button_Click(sender As System.Object, e As System.Windows.RoutedEventArgs) Handles Button1.Click, Button2.Click,.. Button15.click
    MessageBox.Show(sender.Name & " clicked")
End Sub
So the above handles 15 different button click events.

That does not exist in VBA, but there is a limited work around. You create a function (must be a function not a sub), and put the functions name in the event property of multiple controls.
=SomeFunction()

As to your issue
But unfortunately it does give me error as " The object doesn't contain the Automation object 'EnableButton.' ".

My guess is that you did not put the function properly into the event property of the controls. Must be in the form
=FunctionName()
if you leave out = or () it will not work
 

Attachments

  • Common_Event.accdb
    424 KB · Views: 73

silversun

Registered User.
Local time
Today, 02:05
Joined
Dec 28, 2012
Messages
204
Private OldValue As Variant

Public Function EnableButton()
If Me.ActiveControl.Value <> OldValue Then
Me.Command8.Enabled = True
End If
End Function

Public Function SaveOld()
OldValue = ActiveControl.Value
End Function
[/code]

In design view select all controls at once.
In the OnEnter event property enter
=SaveOld()
In the OnExit event property enter
=EnableButton
I spent a lot of time on your codes and finally I was able to make it work when I changed the name of OldValue" variable to "myOldValue". I believe this was a reserved word in Access so that it would prevent my functions working.
Thank you anyway for your help
:)
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:05
Joined
May 21, 2018
Messages
8,463
I believe this was a reserved word in Access so that it would prevent my functions
No. "OldValue" is a property of a class and therefore not exposed except through the class.
 

Users who are viewing this thread

Top Bottom