Change textbox default with VBA

[bandclar]

My form opens with a default name in a text box control. If the user changes the name they are prompted to change the default, or leave it like it is. The code below does not crash, and even changes the default in the form properties box - BUT when the form is closed and opened again the original default is still in the text box.

How can I change the default text and have the new default show when the form is opened again? Any suggestions would be appreciated.

-----------------
Private Sub txtMailTo_AfterUpdate()

Dim Msg, Style, Title, Response

'FormValue is global to the form and equals the string in the text box
FormValue = """" & Me.txtMailTo.Text & """"


If FormValue = Me.txtMailTo.DefaultValue Then

'If text box has not changed, do nothing

Else
'If text box has changed, ask user if this should be the new default
Msg = "Do you want to change the default to " & Me.txtMailTo.Text & "?"
Style = vbYesNo + vbQuestion + vbDefaultButton1
Title = "Change Default?"
Response = MsgBox(Msg, Style, Title)
'User chose No - do nothing
If Response = vbNo Then
DoCmd.CancelEvent

'User chose Yes - change the text box default
Else
Me.txtMailTo.DefaultValue = FormValue
DoCmd.Save acForm, "frmMain"

End If

End If

End Sub

-----------------

Private Sub Form_Unload(Cancel As Integer)

Me.txtMailTo.SetFocus
Me.txtMailTo.DefaultValue = FormValue
DoCmd.Save acForm, "frmMain"

End Sub

 

[JeremyNYC]

Have a one-record table in your database where you can store the value you want to be the default. When the form opens, set the default value from there. When the user changes the default value, write the new value to the table and reset the default on the form.

I have a write-up on my website about a Constants table I use for this purpose in all of my databases.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[bandclar]

Jeremy,

Thank you for such a quick suggestion. I neglected to mention that the text box is an unbound control - only used to display it's contents on reports. With this in mind, should I still create the one record table or is there a way around this?

 

[JeremyNYC]

Yeah, the whole point is that you have to store that value somewhere. This is the easiest way to go about it.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.