me.controlsource not retaining value

[motleyjew]

I have a unbound text box being used as a label. In the control source of the text box I have a date ie. =#12/31/2008#. I am using this date for criteria in a dsum function, so whatever date is in this field effects the data being displayed. I need to be able to have the user edit this date when necessary. I created another unbound text box and put the following code in the after update: Me.ReestDate.ControlSource = "=#" & Me.EnterReestDate & "#"
This is working as it is displaying the correct change. My problem is that the control does not retain the date. When I exit out and go back in, the original date is still there. Is there some code that will save the field after I update it? Or maybe another suggestion to do what I need.
Thanks
Gregg

 

[boblarson]

you'll have to store it in a table

 

[motleyjew]

Thats what I figured after searching and searching but I figured the great Bob would set me straight. Thanks for the quick reply.

 

[CraigDolphin]

Hmm. I don't think you can permanently change the design-view properties like that. If you want a value to be persistent like that then you need to save it in a table somewhere.

One solution is to create a table (tmptbl_Settings) to store the value (in a TEXT field [CriteriaDate])

Then save the following function and sub in a public code module

'Credit to Moniker for this code
Function GetSettings(settingname As String) As String

    GetSettings = DLookup(settingname, "tmptbl_Settings")

End Function

Sub SetSettings(settingname As String, settingvalue As String)

    CurrentDb.Execute "UPDATE tmptbl_Settings SET " & settingname & "='" & settingvalue & "';"

End Sub

In your form, leave the control source of your criteria txtbox (let's call it txtCriteria) empty.

In the on_current event of your form use

Me.txtCriteria = CDate(GetSettings ("CriteriaDate"))

In the after update event of txtCriteria use

if Me.txtCriteria & "" <> "" then
Call SetSettings("CriteriaDate",CStr(Me.txtCriteria))
Else
Me.txtCriteria = Me.txtCriteria.Oldvalue
End if

[EDIT] Dang-nab it all! AGAIN Bob!