In VBA, how to make form properties "stick"? | Newbie | | Join Date: Feb 2008
Posts: 1
| |
Is there a way to update form/control properties via VBA and have them still be there after the form is re-opened?
When a textbox value is updated by the user, I need that value to be there next time the form is loaded. Sure I could store the value in the database, but this happens to be an unbound form, so I'm hoping for something simple.
The forum post Default Value set the same as last valus for field describes how how to set a control's Default Value in VBA. (I am proud to say I independently came up with the same solution as Allen Browne, though the quadruple double-quotes took some experimentation.:))
Problem is, the change does not persist. When the form is re-opened, the default value is gone.
By the way, I know properties can be "permanently" updated programmatically, because I've seen it happen. I'm not sure how, but I have had changes appear in my property sheet that I did not put there directly. I do a lot of debugging of others' VBA code, so it is possible that a breakpoint+abort caused this, but I have not been able to (intentionally) reproduce it.
|  | Moderator | | Join Date: Aug 2007 Location: Derbyshire,England
Posts: 639
| | | re: In VBA, how to make form properties "stick"?
Quote:
Originally Posted by Uh Clem Is there a way to update form/control properties via VBA and have them still be there after the form is re-opened?
When a textbox value is updated by the user, I need that value to be there next time the form is loaded. Sure I could store the value in the database, but this happens to be an unbound form, so I'm hoping for something simple.
The forum post Default Value set the same as last valus for field describes how how to set a control's Default Value in VBA. (I am proud to say I independently came up with the same solution as Allen Browne, though the quadruple double-quotes took some experimentation.:))
Problem is, the change does not persist. When the form is re-opened, the default value is gone.
By the way, I know properties can be "permanently" updated programmatically, because I've seen it happen. I'm not sure how, but I have had changes appear in my property sheet that I did not put there directly. I do a lot of debugging of others' VBA code, so it is possible that a breakpoint+abort caused this, but I have not been able to (intentionally) reproduce it. Have you considered having a simple config table designed to only ever contain one row ? Place your values in each field and then simply Dlookup the value you need and apply that value when the form opens?
It need not be 'one' row of course..you could have a field in it that is called Formname for instance where you store the name of the form lookup the formname correponding to the form that you are opening and retrieve the values for 'that' form if you understand me.
Regards
Jim
|  | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,213
| | | re: In VBA, how to make form properties "stick"?
Quote:
Originally Posted by Uh Clem Is there a way to update form/control properties via VBA and have them still be there after the form is re-opened?
When a textbox value is updated by the user, I need that value to be there next time the form is loaded. Sure I could store the value in the database, but this happens to be an unbound form, so I'm hoping for something simple.
The forum post Default Value set the same as last valus for field describes how how to set a control's Default Value in VBA. (I am proud to say I independently came up with the same solution as Allen Browne, though the quadruple double-quotes took some experimentation.:))
Problem is, the change does not persist. When the form is re-opened, the default value is gone.
By the way, I know properties can be "permanently" updated programmatically, because I've seen it happen. I'm not sure how, but I have had changes appear in my property sheet that I did not put there directly. I do a lot of debugging of others' VBA code, so it is possible that a breakpoint+abort caused this, but I have not been able to (intentionally) reproduce it. One little trick I use fairly often is that whenever I want certain critical values/settings/properties, etc. to persist for Forms and/or Controls, I'll write them to the System Registry, when they can easily be retrieved at will, they are not stored internally, and cannot be modified without some effort.
|  | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,672
| | | re: In VBA, how to make form properties "stick"?
Try duplicating what you do as an operator.
Open the form IN DESIGN VIEW, then make changes and close/save.
Changes to the design (I assume Default Value property) during running mode are never persistent.
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,213
| | | re: In VBA, how to make form properties "stick"?
Quote:
Originally Posted by Uh Clem Is there a way to update form/control properties via VBA and have them still be there after the form is re-opened?
When a textbox value is updated by the user, I need that value to be there next time the form is loaded. Sure I could store the value in the database, but this happens to be an unbound form, so I'm hoping for something simple.
The forum post Default Value set the same as last valus for field describes how how to set a control's Default Value in VBA. (I am proud to say I independently came up with the same solution as Allen Browne, though the quadruple double-quotes took some experimentation.:))
Problem is, the change does not persist. When the form is re-opened, the default value is gone.
By the way, I know properties can be "permanently" updated programmatically, because I've seen it happen. I'm not sure how, but I have had changes appear in my property sheet that I did not put there directly. I do a lot of debugging of others' VBA code, so it is possible that a breakpoint+abort caused this, but I have not been able to (intentionally) reproduce it. You can 'Persist" a Default Value for a Table Field, as such: -
CurrentDb.TableDefs("<Table Name>").Fields("<Field Name>").DefaultValue = "<Default Value>"
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,672
| | | re: In VBA, how to make form properties "stick"?
As ADezii says, properties for tables (and querydefs) will persist when changed. For forms (and reports) however, they will be of instance scope only unless you update the object itself. That is you need to make the change in the form via Design-mode.
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,213
| | | re: In VBA, how to make form properties "stick"?
Quote:
Originally Posted by Uh Clem Is there a way to update form/control properties via VBA and have them still be there after the form is re-opened?
When a textbox value is updated by the user, I need that value to be there next time the form is loaded. Sure I could store the value in the database, but this happens to be an unbound form, so I'm hoping for something simple.
The forum post Default Value set the same as last valus for field describes how how to set a control's Default Value in VBA. (I am proud to say I independently came up with the same solution as Allen Browne, though the quadruple double-quotes took some experimentation.:))
Problem is, the change does not persist. When the form is re-opened, the default value is gone.
By the way, I know properties can be "permanently" updated programmatically, because I've seen it happen. I'm not sure how, but I have had changes appear in my property sheet that I did not put there directly. I do a lot of debugging of others' VBA code, so it is possible that a breakpoint+abort caused this, but I have not been able to (intentionally) reproduce it. Here is an example of how you can 'Persist' a Default Value for a Form Field. Let's assume you have a Field named [txtDefault] on an Active Form. Let's further assume that anytime a User modifies the value in [txtDefault], you want that value to 'Persist' and then become the new Default Value for all New Records (Form Level), until further modified. Instead of babbling on, I'll simple post the code on how this can be done, and if you have any questions, please feel free to ask. Notice specifically, the 2 Procedures where the code is placed, and the conditional statements. I'll shut up now! -
Private Sub txtDefault_AfterUpdate()
-
If Not IsNull(Me![txtDefault]) Then
-
SaveSetting appname:="Your App Name", Section:="Control Defaults", _
-
Key:="ID", setting:=Me![txtDefault]
-
Else
-
SaveSetting appname:="Your App Name", Section:="Control Defaults", _
-
Key:="ID", setting:=""
-
End If
-
End Sub
-
Private Sub Form_Current()
-
If Me.NewRecord Then
-
Me![txtDefault] = GetSetting(appname:="Your App Name", Section:="Control Defaults", _
-
Key:="ID", Default:="")
-
End If
-
End Sub
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,295 network members.
|
