I wish to establish a new default value for a text box - based on what the
user entered. How I do that in VBA?

<identification of default default value entry> = <whatever the user
entered>

I can handle the second part, but....

"(E-Mail Removed)" <(E-Mail Removed)> wrote in message
<(E-Mail Removed)>:
> I wish to establish a new default value for a text box - based on
> what the user entered. How I do that in VBA?
>
> <identification of default default value entry> = <whatever the user
> entered>
>
> I can handle the second part, but....

For numeric field, it could look like this

Me!txtNameOfControl.DefaultValue = Me!txtNameOfControl.Value

For text field, it could look like this

Me!txtNameOfControl.DefaultValue = chr$(34) & _
Me!txtNameOfControl.Value & chr$(34)

For date field, it could look like this

Me!txtNameOfControl.DefaultValue = "#" & _
Format$(Me!txtNameOfControl.Value, "yyyy-mm-dd") & "#"

Note - this is typed, not tested, but it should be approximately
something like this. The event to use, would probably be the after
update event of the control.

--
Roy-Vidar

Actually the DefaultValue property of a control is always a string expression
regardless of the data type, so whether it’s a number, text or date you
simply delimit it with literal quotes:

Me.txtNameOfControl.DefaultValue = """" & Me.txtNameOfControl & """"

Dates are the one which often catch people out because if they put

Me.txtNameOfControl.DefaultValue = Me.txtNameOfControl

and the value in the control is in a short date format this will be
interpreted not as a date time value but as an arithmetical expression, so
today's date in European format 14/04/2007 would evaluate to
0.00174389636273044 which is the underlying value of a date time value of 30
December 1899 00:02:31, a date/time value in Access being implemented as a 64
bit floating point number as an offset from 30 December 1899 00:00:00.

By wrapping the value in quotes on the other hand, then the default value
placed in the control when at a new record is just a string of characters,
which might represent a number, a text string or a date/time. Formatting the
value as you did and wrapping it in date/time delimiter characters would
help, but assumes the value entered is a date without any particular time of
day (which in fact would be midnight at the start of the day as there is no
such thing as a date value without a time of day in Access).

The same applies if you call the Date function to set the DefaultValue
property in code. If you were to put:

Me.MyDate.DefaultValue = Date

then the result would be 30 December 1899 00:02:31 again because the return
value of the Date function is by default expressed in the local short date
format, and consequently interpreted as an arithmetical expression. If you
put:

Me.MyDate.DefaultValue = "#" & Date & "#"

then this would work in countries using US date formats, but here only on
days like today where 14/04/2007 in European format is an invalid date in US
format, so Access 'corrects' it. If the European date is a valid US date
however, then its not 'corrected', e.g. 11/04/2007 European format for 11
April 2007 is of course 4 November 2007 in US format and would incorrectly
set the default value to that date. Whereas:

Me.MyDate.DefaultValue = """" & Date & """"

would work correctly both here and in the USA whatever the date.

BTW the differences in short date formats initially caused a lot of problems
for British and American signallers during the Second World War (I recall
when I was young one former Royal Corps of Signals sergeant telling me how
they would repeatedly request US units to re-send signals, even though they
were well aware what date was meant!), so it was decided that in signals
traffic long date formats should always be employed.

Ken Sheridan
Stafford, England

"RoyVidar" wrote:

> "(E-Mail Removed)" <(E-Mail Removed)> wrote in message
> <(E-Mail Removed)>:
> > I wish to establish a new default value for a text box - based on
> > what the user entered. How I do that in VBA?
> >
> > <identification of default default value entry> = <whatever the user
> > entered>
> >
> > I can handle the second part, but....
>
> For numeric field, it could look like this
>
> Me!txtNameOfControl.DefaultValue = Me!txtNameOfControl.Value
>
> For text field, it could look like this
>
> Me!txtNameOfControl.DefaultValue = chr$(34) & _
> Me!txtNameOfControl.Value & chr$(34)
>
> For date field, it could look like this
>
> Me!txtNameOfControl.DefaultValue = "#" & _
> Format$(Me!txtNameOfControl.Value, "yyyy-mm-dd") & "#"
>
> Note - this is typed, not tested, but it should be approximately
> something like this. The event to use, would probably be the after
> update event of the control.
>
> --
> Roy-Vidar
>
>
>

"Ken Sheridan" <(E-Mail Removed)> wrote in message
<815D316A-5841-4C63-A029-(E-Mail Removed)>:

Some comments inline

> Actually the DefaultValue property of a control is always a string
> expression regardless of the data type, so whether it’s a number,
> text or date you simply delimit it with literal quotes:
>
> Me.txtNameOfControl.DefaultValue = """" & Me.txtNameOfControl & """"

I didn't know that - but some additional comments - this would also
work where I live, for text of course, but not necessarily for dates
and numbers.

And - what I posted for numerics in my first reply, actually doesn't
work, either, if the number contains decimals (should have tested).
The below, however, works

Me!txtNameOfControl.DefaultValue = _
Replace(Cstr(Me!txtNameOfControl), ",", ".")

> Dates are the one which often catch people out because if they put
>
> Me.txtNameOfControl.DefaultValue = Me.txtNameOfControl
>
> and the value in the control is in a short date format this will be
> interpreted not as a date time value but as an arithmetical
> expression, so today's date in European format 14/04/2007 would
> evaluate to 0.00174389636273044 which is the underlying value of a
> date time value of 30 December 1899 00:02:31, a date/time value in
> Access being implemented as a 64 bit floating point number as an
> offset from 30 December 1899 00:00:00.

But, no, on my locale, that produces #Name, because what is entered
into the Default Value property, say for todays date, is 14.04.2007,
which isn't recognized as anything sensible by Access.

> By wrapping the value in quotes on the other hand, then the default
> value placed in the control when at a new record is just a string of
> characters, which might represent a number, a text string or a
> date/time. Formatting the value as you did and wrapping it in
> date/time delimiter characters would help, but assumes the value
> entered is a date without any particular time of day (which in fact
> would be midnight at the start of the day as there is no such thing
> as a date value without a time of day in Access).

If i wanted time, I would probably add "hh:nn:ss".

> The same applies if you call the Date function to set the
> DefaultValue property in code. If you were to put:
>
> Me.MyDate.DefaultValue = Date
>
> then the result would be 30 December 1899 00:02:31 again because the
> return value of the Date function is by default expressed in the
> local short date format, and consequently interpreted as an
> arithmetical expression. If you put:

Again - #Name, because Access doesn't understand 14.04.2007

> Me.MyDate.DefaultValue = "#" & Date & "#"
>
> then this would work in countries using US date formats, but here
> only on days like today where 14/04/2007 in European format is an
> invalid date in US format, so Access 'corrects' it. If the European
> date is a valid US date however, then its not 'corrected', e.g.
> 11/04/2007 European format for 11 April 2007 is of course 4 November
> 2007 in US format and would incorrectly set the default value to
> that date. Whereas:

Again - #Name, because Access doesn't understand #14.04.2007#

> Me.MyDate.DefaultValue = """" & Date & """"
>
> would work correctly both here and in the USA whatever the date.
>
> BTW the differences in short date formats initially caused a lot of
> problems for British and American signallers during the Second World
> War (I recall when I was young one former Royal Corps of Signals
> sergeant telling me how they would repeatedly request US units to
> re-send signals, even though they were well aware what date was
> meant!), so it was decided that in signals traffic long date formats
> should always be employed.

Here, when using doublequoting, what is actually placed into the
property (again with todays date) is "14.04.2007", which Access will
happily understand as long as the locale is set to Norwegian. But,
both me and a couple of my customers, will regularly toggle these
settings during sessions (Norwegian to either UK or US), in which
case such Default Value produces #Error.

Same might happen for numbers containing decimals.

So, my reasoning with this, is to ensure the format is unambiguous,
at least as far as the interface is concerned, which makes me
reasonable sure that Access will understand it, regardless of locale,
and regardless of whether locale is changed during session.

Part of what makes it fun living in Norway, is that period is used
as date separator, and comma is used as decimal separator

> Ken Sheridan
> Stafford, England
>
> "RoyVidar" wrote:
>
>> "(E-Mail Removed)" <(E-Mail Removed)> wrote in message
>> <(E-Mail Removed)>:
>>> I wish to establish a new default value for a text box - based on
>>> what the user entered. How I do that in VBA?
>>>
>>> <identification of default default value entry> = <whatever the
>>> user entered>
>>>
>>> I can handle the second part, but....
>>
>> For numeric field, it could look like this
>>
>> Me!txtNameOfControl.DefaultValue = Me!txtNameOfControl.Value
>>
>> For text field, it could look like this
>>
>> Me!txtNameOfControl.DefaultValue = chr$(34) & _
>> Me!txtNameOfControl.Value & chr$(34)
>>
>> For date field, it could look like this
>>
>> Me!txtNameOfControl.DefaultValue = "#" & _
>> Format$(Me!txtNameOfControl.Value, "yyyy-mm-dd") & "#"
>>
>> Note - this is typed, not tested, but it should be approximately
>> something like this. The event to use, would probably be the after
>> update event of the control.
>>
>> --
>> Roy-Vidar
>>
>>
>>

--
Roy-Vidar

I'd suspect that with dates and numbers, delimiting the value with quotes
would work in your case as in essence its just pushing a string of characters
into the control, and as the date in the control would presumably be in your
local comma-separated date format it would, I'd guess, push the value in
using that format. If not then using the control's Text property as the
source might be an alternative.

Ken Sheridan
Stafford, England

"RoyVidar" wrote:

> "Ken Sheridan" <(E-Mail Removed)> wrote in message
> <815D316A-5841-4C63-A029-(E-Mail Removed)>:
>
> Some comments inline
>
> > Actually the DefaultValue property of a control is always a string
> > expression regardless of the data type, so whether it’s a number,
> > text or date you simply delimit it with literal quotes:
> >
> > Me.txtNameOfControl.DefaultValue = """" & Me.txtNameOfControl & """"
>
> I didn't know that - but some additional comments - this would also
> work where I live, for text of course, but not necessarily for dates
> and numbers.
>
> And - what I posted for numerics in my first reply, actually doesn't
> work, either, if the number contains decimals (should have tested).
> The below, however, works
>
> Me!txtNameOfControl.DefaultValue = _
> Replace(Cstr(Me!txtNameOfControl), ",", ".")
>
> > Dates are the one which often catch people out because if they put
> >
> > Me.txtNameOfControl.DefaultValue = Me.txtNameOfControl
> >
> > and the value in the control is in a short date format this will be
> > interpreted not as a date time value but as an arithmetical
> > expression, so today's date in European format 14/04/2007 would
> > evaluate to 0.00174389636273044 which is the underlying value of a
> > date time value of 30 December 1899 00:02:31, a date/time value in
> > Access being implemented as a 64 bit floating point number as an
> > offset from 30 December 1899 00:00:00.
>
> But, no, on my locale, that produces #Name, because what is entered
> into the Default Value property, say for todays date, is 14.04.2007,
> which isn't recognized as anything sensible by Access.
>
> > By wrapping the value in quotes on the other hand, then the default
> > value placed in the control when at a new record is just a string of
> > characters, which might represent a number, a text string or a
> > date/time. Formatting the value as you did and wrapping it in
> > date/time delimiter characters would help, but assumes the value
> > entered is a date without any particular time of day (which in fact
> > would be midnight at the start of the day as there is no such thing
> > as a date value without a time of day in Access).
>
> If i wanted time, I would probably add "hh:nn:ss".
>
> > The same applies if you call the Date function to set the
> > DefaultValue property in code. If you were to put:
> >
> > Me.MyDate.DefaultValue = Date
> >
> > then the result would be 30 December 1899 00:02:31 again because the
> > return value of the Date function is by default expressed in the
> > local short date format, and consequently interpreted as an
> > arithmetical expression. If you put:
>
> Again - #Name, because Access doesn't understand 14.04.2007
>
> > Me.MyDate.DefaultValue = "#" & Date & "#"
> >
> > then this would work in countries using US date formats, but here
> > only on days like today where 14/04/2007 in European format is an
> > invalid date in US format, so Access 'corrects' it. If the European
> > date is a valid US date however, then its not 'corrected', e.g.
> > 11/04/2007 European format for 11 April 2007 is of course 4 November
> > 2007 in US format and would incorrectly set the default value to
> > that date. Whereas:
>
> Again - #Name, because Access doesn't understand #14.04.2007#
>
> > Me.MyDate.DefaultValue = """" & Date & """"
> >
> > would work correctly both here and in the USA whatever the date.
> >
> > BTW the differences in short date formats initially caused a lot of
> > problems for British and American signallers during the Second World
> > War (I recall when I was young one former Royal Corps of Signals
> > sergeant telling me how they would repeatedly request US units to
> > re-send signals, even though they were well aware what date was
> > meant!), so it was decided that in signals traffic long date formats
> > should always be employed.
>
> Here, when using doublequoting, what is actually placed into the
> property (again with todays date) is "14.04.2007", which Access will
> happily understand as long as the locale is set to Norwegian. But,
> both me and a couple of my customers, will regularly toggle these
> settings during sessions (Norwegian to either UK or US), in which
> case such Default Value produces #Error.
>
> Same might happen for numbers containing decimals.
>
> So, my reasoning with this, is to ensure the format is unambiguous,
> at least as far as the interface is concerned, which makes me
> reasonable sure that Access will understand it, regardless of locale,
> and regardless of whether locale is changed during session.
>
> Part of what makes it fun living in Norway, is that period is used
> as date separator, and comma is used as decimal separator
>
> > Ken Sheridan
> > Stafford, England
> >
> > "RoyVidar" wrote:
> >
> >> "(E-Mail Removed)" <(E-Mail Removed)> wrote in message
> >> <(E-Mail Removed)>:
> >>> I wish to establish a new default value for a text box - based on
> >>> what the user entered. How I do that in VBA?
> >>>
> >>> <identification of default default value entry> = <whatever the
> >>> user entered>
> >>>
> >>> I can handle the second part, but....
> >>
> >> For numeric field, it could look like this
> >>
> >> Me!txtNameOfControl.DefaultValue = Me!txtNameOfControl.Value
> >>
> >> For text field, it could look like this
> >>
> >> Me!txtNameOfControl.DefaultValue = chr$(34) & _
> >> Me!txtNameOfControl.Value & chr$(34)
> >>
> >> For date field, it could look like this
> >>
> >> Me!txtNameOfControl.DefaultValue = "#" & _
> >> Format$(Me!txtNameOfControl.Value, "yyyy-mm-dd") & "#"
> >>
> >> Note - this is typed, not tested, but it should be approximately
> >> something like this. The event to use, would probably be the after
> >> update event of the control.
> >>
> >> --
> >> Roy-Vidar
> >>
> >>
> >>
>
> --
> Roy-Vidar
>
>
>

My case is fairly simple (no dates) and I still don't seem to get it to
work. I'm overlooking something, I guess.

I have tried

Me.Addresses.DefaultValue = Chr$(34) & Addresses.Value & Chr$(34)
Me.Addresses.DefaultValue = Chr$(34) & Me!Addresses & Chr$(34)
Me.Addresses.DefaultValue = Chr$(34) & Me.Addresses & Chr$(34)

in the exit on-click button code. It is followed by

DoCmd.Close acForm, "BackupForm"

When I reopen the form the default value has not changed.

I finally solved the problem by saving the value in a public constant on
exit and then reloading it from there on form open. But I'm still curious
about why the original scheme doesn't work

Dick

(E-Mail Removed) wrote:
> My case is fairly simple (no dates) and I still don't seem to get it
> to work. I'm overlooking something, I guess.
>
> I have tried
>
> Me.Addresses.DefaultValue = Chr$(34) & Addresses.Value & Chr$(34)
> Me.Addresses.DefaultValue = Chr$(34) & Me!Addresses & Chr$(34)
> Me.Addresses.DefaultValue = Chr$(34) & Me.Addresses & Chr$(34)
>
> in the exit on-click button code. It is followed by
>
> DoCmd.Close acForm, "BackupForm"
>
> When I reopen the form the default value has not changed.
>
> I finally solved the problem by saving the value in a public constant
> on exit and then reloading it from there on form open. But I'm still
> curious about why the original scheme doesn't work
>
> Dick

You misunderstand. That code is temporarily changing the default value property
only until the form is closed. To make a permanent change the form would have to
be opened in design view when the code is executed. This applies to any change
you make to a form or report using code when not in design view. It is never
permanent with a few exceptions like the OrderBy or Filter properties.

Consider code that does something like "when this date is more than two weeks
old make this other control have a red background". You wouldn't want such
changes to be permanent (and they aren't).


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

"(E-Mail Removed)" <(E-Mail Removed)> wrote in message
<(E-Mail Removed)>:
> My case is fairly simple (no dates) and I still don't seem to get it
> to work. I'm overlooking something, I guess.
>
> I have tried
>
> Me.Addresses.DefaultValue = Chr$(34) & Addresses.Value & Chr$(34)
> Me.Addresses.DefaultValue = Chr$(34) & Me!Addresses & Chr$(34)
> Me.Addresses.DefaultValue = Chr$(34) & Me.Addresses & Chr$(34)
>
> in the exit on-click button code. It is followed by
>
> DoCmd.Close acForm, "BackupForm"
>
> When I reopen the form the default value has not changed.
>
> I finally solved the problem by saving the value in a public constant
> on exit and then reloading it from there on form open. But I'm still
> curious about why the original scheme doesn't work
>
> Dick

I don't think setting the default value property of controls are
persistant between settings. I think you will need to store it
elsewhere (a table?), if you need that.

--
Roy-Vidar

On 15-Apr-2007, (E-Mail Removed) wrote:

> I finally solved the problem by saving the value in a public constant on
> exit and then reloading it from there on form open. But I'm still curious
> about why the original scheme doesn't work

I was wrong (twice). First, it wasn't a constant, it's as a public string.
That saved the value while the application was open, but was not restored
when it was restarted.

Must I save it in an external (text) file - like an .INI?

Dick

"Ken Sheridan" <(E-Mail Removed)> wrote in message
<E14BAE8F-9946-430E-84BB-(E-Mail Removed)>:
> I'd suspect that with dates and numbers, delimiting the value with
> quotes would work in your case as in essence its just pushing a
> string of characters into the control, and as the date in the
> control would presumably be in your local comma-separated date
> format it would, I'd guess, push the value in using that format. If
> not then using the control's Text property as the source might be an
> alternative.
>
> Ken Sheridan
> Stafford, England
>
> "RoyVidar" wrote:
>
>> "Ken Sheridan" <(E-Mail Removed)> wrote in
>> message <815D316A-5841-4C63-A029-(E-Mail Removed)>:
>>
>> Some comments inline
>>
>>> Actually the DefaultValue property of a control is always a string
>>> expression regardless of the data type, so whether it’s a number,
>>> text or date you simply delimit it with literal quotes:
>>>
>>> Me.txtNameOfControl.DefaultValue = """" & Me.txtNameOfControl &
>>> """"
>>
>> I didn't know that - but some additional comments - this would also
>> work where I live, for text of course, but not necessarily for dates
>> and numbers.
>>
>> And - what I posted for numerics in my first reply, actually doesn't
>> work, either, if the number contains decimals (should have tested).
>> The below, however, works
>>
>> Me!txtNameOfControl.DefaultValue = _
>> Replace(Cstr(Me!txtNameOfControl), ",", ".")
>>
>>> Dates are the one which often catch people out because if they put
>>>
>>> Me.txtNameOfControl.DefaultValue = Me.txtNameOfControl
>>>
>>> and the value in the control is in a short date format this will be
>>> interpreted not as a date time value but as an arithmetical
>>> expression, so today's date in European format 14/04/2007 would
>>> evaluate to 0.00174389636273044 which is the underlying value of a
>>> date time value of 30 December 1899 00:02:31, a date/time value in
>>> Access being implemented as a 64 bit floating point number as an
>>> offset from 30 December 1899 00:00:00.
>>
>> But, no, on my locale, that produces #Name, because what is entered
>> into the Default Value property, say for todays date, is 14.04.2007,
>> which isn't recognized as anything sensible by Access.
>>
>>> By wrapping the value in quotes on the other hand, then the default
>>> value placed in the control when at a new record is just a string
>>> of characters, which might represent a number, a text string or a
>>> date/time. Formatting the value as you did and wrapping it in
>>> date/time delimiter characters would help, but assumes the value
>>> entered is a date without any particular time of day (which in
>>> fact would be midnight at the start of the day as there is no such
>>> thing as a date value without a time of day in Access).
>>
>> If i wanted time, I would probably add "hh:nn:ss".
>>
>>> The same applies if you call the Date function to set the
>>> DefaultValue property in code. If you were to put:
>>>
>>> Me.MyDate.DefaultValue = Date
>>>
>>> then the result would be 30 December 1899 00:02:31 again because
>>> the return value of the Date function is by default expressed in
>>> the local short date format, and consequently interpreted as an
>>> arithmetical expression. If you put:
>>
>> Again - #Name, because Access doesn't understand 14.04.2007
>>
>>> Me.MyDate.DefaultValue = "#" & Date & "#"
>>>
>>> then this would work in countries using US date formats, but here
>>> only on days like today where 14/04/2007 in European format is an
>>> invalid date in US format, so Access 'corrects' it. If the
>>> European date is a valid US date however, then its not
>>> 'corrected', e.g. 11/04/2007 European format for 11 April 2007 is
>>> of course 4 November 2007 in US format and would incorrectly set
>>> the default value to that date. Whereas:
>>
>> Again - #Name, because Access doesn't understand #14.04.2007#
>>
>>> Me.MyDate.DefaultValue = """" & Date & """"
>>>
>>> would work correctly both here and in the USA whatever the date.
>>>
>>> BTW the differences in short date formats initially caused a lot of
>>> problems for British and American signallers during the Second
>>> World War (I recall when I was young one former Royal Corps of
>>> Signals sergeant telling me how they would repeatedly request US
>>> units to re-send signals, even though they were well aware what
>>> date was meant!), so it was decided that in signals traffic long
>>> date formats should always be employed.
>>
>> Here, when using doublequoting, what is actually placed into the
>> property (again with todays date) is "14.04.2007", which Access will
>> happily understand as long as the locale is set to Norwegian. But,
>> both me and a couple of my customers, will regularly toggle these
>> settings during sessions (Norwegian to either UK or US), in which
>> case such Default Value produces #Error.
>>
>> Same might happen for numbers containing decimals.
>>
>> So, my reasoning with this, is to ensure the format is unambiguous,
>> at least as far as the interface is concerned, which makes me
>> reasonable sure that Access will understand it, regardless of
>> locale, and regardless of whether locale is changed during session.
>>
>> Part of what makes it fun living in Norway, is that period is used
>> as date separator, and comma is used as decimal separator
>>
>>> Ken Sheridan
>>> Stafford, England
>>>
>>> "RoyVidar" wrote:
>>>
>>>> "(E-Mail Removed)" <(E-Mail Removed)> wrote in message
>>>> <(E-Mail Removed)>:
>>>>> I wish to establish a new default value for a text box - based on
>>>>> what the user entered. How I do that in VBA?
>>>>>
>>>>> <identification of default default value entry> = <whatever the
>>>>> user entered>
>>>>>
>>>>> I can handle the second part, but....
>>>>
>>>> For numeric field, it could look like this
>>>>
>>>> Me!txtNameOfControl.DefaultValue = Me!txtNameOfControl.Value
>>>>
>>>> For text field, it could look like this
>>>>
>>>> Me!txtNameOfControl.DefaultValue = chr$(34) & _
>>>> Me!txtNameOfControl.Value & chr$(34)
>>>>
>>>> For date field, it could look like this
>>>>
>>>> Me!txtNameOfControl.DefaultValue = "#" & _
>>>> Format$(Me!txtNameOfControl.Value, "yyyy-mm-dd") & "#"
>>>>
>>>> Note - this is typed, not tested, but it should be approximately
>>>> something like this. The event to use, would probably be the after
>>>> update event of the control.
>>>>
>>>> --
>>>> Roy-Vidar
>>>>
>>>>
>>>>
>>
>> --
>> Roy-Vidar
>>
>>
>>

Perhaps you should try it?

There shouldn't even be any need to switch to Norwegian locale, just
try the following:

In the default value property of a control bound to a date field, enter
a Norwegian date "15.04.2007" enclosed in quotes.

In the default value property of a control bound to a numeric (single,
double, currency) enter a Norwegian number "5,335" enclose in quotes
(this number is 5.335 in UK/US format)

Then switch to form view, hit new record. On my setup, that gives
#Error on the date and original default value * 1000 for the number.
But that happens only on my setup when using UK locale?

For me, this is a reason to use unambiguous formats when using the
default value property with litterals for dates and/or numbers
containing decimals.

--
Roy-Vidar