Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-04-2018, 11:43 PM   #1
Keith1951
Newly Registered User
 
Join Date: May 2016
Posts: 13
Thanks: 5
Thanked 0 Times in 0 Posts
Keith1951 is on a distinguished road
Updating field value defaults

I often use field value defaults eg Month with for instance "2018-03"

Easy enough for me to do but want to hand it over to someone who has zero knowledge of Access

Can field value defaults be changed programmatically such that the user can simply fill in the value on a form?

Many thanks for any thoughts and suggestions ..... Keith

Keith1951 is offline   Reply With Quote
Old 04-05-2018, 12:32 AM   #2
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,191
Thanks: 2
Thanked 1,925 Times in 1,883 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Updating field value defaults

Are the users putting in data direct in a table, (not recommend - use a form), or why should the users set a default value at table level?
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Old 04-05-2018, 04:41 AM   #3
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,669
Thanks: 10
Thanked 2,075 Times in 2,030 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Updating field value defaults

Quote:
Month with for instance "2018-03"
First, [Month] is a terrible field name. It's a reserved word (https://support.office.com/en-us/art...7-da237c63eabe) which will make coding and querying more difficult in the future.

Second, "2018-03" is a horrible way to store month data. You lose access to all the date functions in Access. So when you want to compare last month to this month you have to invent a formula to work with your hacky storage method.

You should store date related values as dates. If you did that you would have access to the Date() function to use as a defualt value for a Date/Time field.

plog is offline   Reply With Quote
Old 04-05-2018, 07:24 AM   #4
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,211
Thanks: 13
Thanked 1,368 Times in 1,303 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Updating field value defaults

Sounds like you are hard-coding this value someplace and so have to update it every month.

I agree with plog that storing an actual date would be better in the long run. You can always extract year and month if that is all you want to work with.

Probably the best place to add the date to a record is the Form's BeforeUpdate event.

Me.txtDateAdded = Date()
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 04-05-2018, 03:04 PM   #5
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 11,845
Thanks: 55
Thanked 1,064 Times in 972 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Updating field value defaults

As a matter of philosophy, there are two ways to create something. You can either build it from components or you can pick it out of something. Keeping a month and year separately would take at least two WORD fields = 32 bits (2 x 16 bits). Keeping a full date in a single field takes a DATE field = 64 bits (1 x 64 bits). But you could then extract year, month, day, hour, minute, and second separately from the same field. Not to mention weekday.

The difference in size (32 or 64 bits) is negligible unless you have a really long record. I would spend the extra 32 bits to have a single field for DATE. But that's just me - and a few dozen on this forum.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is online now   Reply With Quote
Old 04-06-2018, 08:30 AM   #6
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Posts: 1,264
Thanks: 13
Thanked 241 Times in 239 Posts
Mark_ will become famous soon enough
Re: Updating field value defaults

Quote:
Originally Posted by Keith1951 View Post
I often use field value defaults eg Month with for instance "2018-03"
Are these default values set at the form or table level?
How are you filling them now?

If you are using the "Default value", you can have a function return a default value for you, such as NOW(). If you really need an end user to be able to set these I'd have a "Default" file hold your values. One record for each field you will be defaulting. I'd also have a global function that you can call, something like GetDefault("MyField") that does a lookup in your config file based on the passed "MyField" to return the current default. Then you can have a continuous form to update your defaults as needed.
Mark_ is offline   Reply With Quote
Old 04-06-2018, 12:32 PM   #7
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,211
Thanks: 13
Thanked 1,368 Times in 1,303 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Updating field value defaults

Don't use NOW() when you really mean Date(). You will introduce time components into date fields and the queries may not be built to handle time correctly. I only use Now() when I actually want a timestamp that includes time of day. Otherwise, a date is always only a date.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 04-13-2018, 10:23 PM   #8
Keith1951
Newly Registered User
 
Join Date: May 2016
Posts: 13
Thanks: 5
Thanked 0 Times in 0 Posts
Keith1951 is on a distinguished road
Re: Updating field value defaults

Many thanks to all for pointing out the error of my ways and giving me at least 4 better solutions - I am humbled and wiser, Keith
Keith1951 is offline   Reply With Quote
Old 05-13-2018, 12:16 PM   #9
nardimrk
Newly Registered User
 
Join Date: May 2018
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
nardimrk is on a distinguished road
Re: Updating field value defaults

no way, this is really interesting

nardimrk is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Updating Combo box defaults with Optionbox and/or Check box for a Table Robert88 Forms 8 03-30-2006 08:54 PM
Changing Field Format Defaults CarlyS Tables 2 07-10-2005 01:39 PM
Multiple defaults to a field in a form. myme Forms 6 05-06-2004 05:41 AM
Multiple sequential defaults to a field myme Forms 0 05-03-2004 08:59 PM
Auto Field Defaults hastings Forms 0 12-06-1999 02:24 PM




All times are GMT -8. The time now is 12:36 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World