By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,476 Members | 1,553 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,476 IT Pros & Developers. It's quick & easy.

In VBA, how to make form properties "stick"?

P: 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.
Feb 6 '08 #1
Share this Question
Share on Google+
6 Replies


Jim Doherty
Expert 100+
P: 897
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
Feb 7 '08 #2

ADezii
Expert 5K+
P: 8,597
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.
Feb 7 '08 #3

NeoPa
Expert Mod 15k+
P: 31,186
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.
Feb 7 '08 #4

ADezii
Expert 5K+
P: 8,597
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:
Expand|Select|Wrap|Line Numbers
  1. CurrentDb.TableDefs("<Table Name>").Fields("<Field Name>").DefaultValue = "<Default Value>"
Feb 7 '08 #5

NeoPa
Expert Mod 15k+
P: 31,186
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.
Feb 7 '08 #6

ADezii
Expert 5K+
P: 8,597
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!
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtDefault_AfterUpdate()
  2. If Not IsNull(Me![txtDefault]) Then
  3.   SaveSetting appname:="Your App Name", Section:="Control Defaults", _
  4.                         Key:="ID", setting:=Me![txtDefault]
  5. Else
  6.   SaveSetting appname:="Your App Name", Section:="Control Defaults", _
  7.                         Key:="ID", setting:=""
  8. End If
  9. End Sub
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.   If Me.NewRecord Then
  3.     Me![txtDefault] = GetSetting(appname:="Your App Name", Section:="Control Defaults", _
  4.                       Key:="ID", Default:="")
  5.   End If
  6. End Sub
Feb 8 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.