Updating field value defaults (1 Viewer)

Keith1951 · Apr 5, 2018

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

JHB · Apr 5, 2018

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?

plog · Apr 5, 2018

Month with for instance "2018-03"

First, [Month] is a terrible field name. It's a reserved word (https://support.office.com/en-us/ar...-symbols-e33eb3a9-8baa-4335-9f57-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.

Pat Hartman · Apr 5, 2018

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()

The_Doc_Man · Apr 6, 2018

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.

Mark_ · Apr 6, 2018

Keith1951 said:
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.

Pat Hartman · Apr 6, 2018

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.

Keith1951 · Apr 14, 2018

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

nardimrk · May 13, 2018

no way, this is really interesting

Updating field value defaults (1 Viewer)

Keith1951

Registered User.

JHB

Have been here a while

plog

Banishment Pending

Pat Hartman

Super Moderator

The_Doc_Man

Immoderate Moderator

Mark_

Longboard on the internet

Pat Hartman

Super Moderator

Keith1951

Registered User.

nardimrk

Registered User.

Similar threads

Users who are viewing this thread