Text box default value - how to change in vba?

N

news2

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....
 
R

RoyVidar

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.
 
G

Guest

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
 
R

RoyVidar

<[email protected]>:

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 ;)
 
G

Guest

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
 
N

news2

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
 
R

Rick Brandt

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).
 
R

RoyVidar

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.
 
N

news2

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
 
R

RoyVidar

Ken Sheridan said:
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

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.
 
N

news2

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.

Right! thanks...
Dick
 
G

Guest

I'm new to Access and know almost nothing about code, but could really use
this function in my database. I only care about assigning a temporary
default value for EPN, the default value can go away after closing form.
Based on Ken's guidance, I've inserted the code exactly as shown below. I
get a Compile Error that also states "Method or data member not found".
Also, .txtEPN is highlighted in blue. EPN is a field in a subform that
populates a table. Is there more code required? When you refer to Control,
does that mean "Control Source"? Also, what is the best way to get a jump
start on learning code, VBA? Many thanks for helping a newbie!

Private Sub EPNCombo_AfterUpdate()

Me.txtEPN.DefaultValue = """" & Me.txtEPN & """"

End Sub
 
J

John W. Vinson

I'm new to Access and know almost nothing about code, but could really use
this function in my database. I only care about assigning a temporary
default value for EPN, the default value can go away after closing form.
Based on Ken's guidance, I've inserted the code exactly as shown below. I
get a Compile Error that also states "Method or data member not found".
Also, .txtEPN is highlighted in blue. EPN is a field in a subform that
populates a table. Is there more code required? When you refer to Control,
does that mean "Control Source"? Also, what is the best way to get a jump
start on learning code, VBA? Many thanks for helping a newbie!

Private Sub EPNCombo_AfterUpdate()

Me.txtEPN.DefaultValue = """" & Me.txtEPN & """"

End Sub

A Control has a Name property - the "handle" by which you refer to the form.
This code assumes that you have a control named txtEPN.

A Control also has a Control Source - the name of the table or query field to
which the control is bound. The name of the field might be the same or might
be different from the name of the control; Access defaults to naming a control
the same as its bound field, but you can (and probably should) change this
name.

It appears from the Private Sub line that you have a control named

EPNCombo

Is that the control you're trying to set a default value? If so... use that
name. Ken presumably assumed that the name of your control was txtEPN.

John W. Vinson [MVP]
 
G

Guest

Hopefully John has sorted you out as far as the control name is concerned.

To make the default value stick between sessions create a table named
Defaults with three text fields named FormName, ControlName and DefaultVal.
The first two can be set as the tables composite primary key as they will be
unique in combination. To store the value entered into a control as its
default value put the following code in its AfterUpdate event procedure:

Dim cmd As ADODB.Command
Dim strForm As String
Dim strControl As String
Dim strSQL As String
Dim strCriteria As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strCriteria = "FormName = """ & Me.Name & """ " & _
"And ControlName = """ & Me.ActiveControl.Name & """"
' is there an existing default for this control?
If Not IsNull(DLookup("FormName", "Defaults", strCriteria)) Then
' if so then update row in table
strSQL = "UPDATE Defaults " & _
"SET DefaultVal = """ & Me.ActiveControl & """ " & _
"WHERE " & strCriteria
Else
' insert new row
strSQL = "INSERT INTO Defaults(" & _
"FormName,ControlName,DefaultVal) " & _
"VALUES(""" & Me.Name & """,""" & _
Me.ActiveControl.Name & """,""" & _
Me.ActiveControl & """)"
End If

cmd.CommandText = strSQL
cmd.Execute

To set the default values of any controls in a form which are stored in this
table put the following code in the form's Open event procedure:

Dim strCriteria As String
Dim varDefault As Variant
Dim ctrl As Control

For Each ctrl In Me.Controls
strCriteria = "FormName = """ & Me.Name & """ " & _
"And ControlName = """ & ctrl.Name & """"

varDefault = DLookup("DefaultVal", "Defaults", strCriteria)

If Not IsNull(varDefault) Then
ctrl.DefaultValue = """" & varDefault & """"
End If
Next ctrl

As you can see this nowhere uses the actual form or control names but gets
them via the Name property of the form and controls, so you can use it with
any control which accepts a default value in any form. It will work with
text, numbers and date/time data types.

A lot of code repetition could be avoided of course by putting most of the
above code in separate public functions into which the form and control name
would be passed, thus reducing the amount of code in the form's modules, but
it will work fine doing it as above; I'm anxious not to confuse you by
introducing further complications at this stage.

Ken Sheridan
Stafford, England
 
G

Guest

EPNCombo works! John/Ken, many thanks for your time and patience. It's
foreign to me, but trying to learn. Ray
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top