Text box default value - how to change in vba?

Discussion in 'Microsoft Access' started by news2@rgcle.com, Apr 14, 2007.

  1. Guest

    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....
     
    , Apr 14, 2007
    #1
    1. Advertisements

  2. RoyVidar Guest

    "" <> wrote in message
    <>:
    > 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
     
    RoyVidar, Apr 14, 2007
    #2
    1. Advertisements

  3. Guest 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

    "RoyVidar" wrote:

    > "" <> wrote in message
    > <>:
    > > 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
    >
    >
    >
     
    Guest, Apr 14, 2007
    #3
  4. RoyVidar Guest

    "Ken Sheridan" <> wrote in message
    <>:

    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:
    >
    >> "" <> wrote in message
    >> <>:
    >>> 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
     
    RoyVidar, Apr 14, 2007
    #4
  5. Guest 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

    "RoyVidar" wrote:

    > "Ken Sheridan" <> wrote in message
    > <>:
    >
    > 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:
    > >
    > >> "" <> wrote in message
    > >> <>:
    > >>> 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
    >
    >
    >
     
    Guest, Apr 15, 2007
    #5
  6. Guest

    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
     
    , Apr 15, 2007
    #6
  7. Rick Brandt Guest

    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
     
    Rick Brandt, Apr 15, 2007
    #7
  8. RoyVidar Guest

    "" <> wrote in message
    <>:
    > 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
     
    RoyVidar, Apr 15, 2007
    #8
  9. Guest

    On 15-Apr-2007, 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
     
    , Apr 15, 2007
    #9
  10. RoyVidar Guest

    "Ken Sheridan" <> wrote in message
    <>:
    > 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" <> wrote in
    >> message <>:
    >>
    >> 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:
    >>>
    >>>> "" <> wrote in message
    >>>> <>:
    >>>>> 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
     
    RoyVidar, Apr 15, 2007
    #10
  11. Guest

    On 15-Apr-2007, RoyVidar <> wrote:

    > 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


    Right! thanks...
    Dick
     
    , Apr 16, 2007
    #11
  12. Guest 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
     
    Guest, Apr 16, 2007
    #12
  13. On Sun, 15 Apr 2007 20:26:01 -0700, ra2e <>
    wrote:

    >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]
     
    John W. Vinson, Apr 16, 2007
    #13
  14. Guest 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

    "ra2e" wrote:

    > 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
    >
     
    Guest, Apr 16, 2007
    #14
  15. Guest Guest

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

    "John W. Vinson" wrote:

    > On Sun, 15 Apr 2007 20:26:01 -0700, ra2e <>
    > wrote:
    >
    > >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]
    >
     
    Guest, Apr 17, 2007
    #15
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. fredg
    Replies:
    10
    Views:
    867
    mike-strawberry
    Apr 27, 2011
  2. Guest
    Replies:
    2
    Views:
    534
    Guest
    Jul 28, 2005
  3. Guest
    Replies:
    2
    Views:
    585
    Guest
    Sep 15, 2006
  4. Replies:
    2
    Views:
    454
    Tom van Stiphout
    Jan 29, 2009
  5. Larry Kahm

    Change displayed combo box value using VBA

    Larry Kahm, Apr 30, 2009, in forum: Microsoft Access
    Replies:
    4
    Views:
    485
    Jeff Boyce
    Apr 30, 2009
Loading...

Share This Page