Default Value on Forms

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form with one field [Month End Date] that I want to have with a
default value. However, I don't want to have to modify the form's design or
the table's design each month with the new month's default value as all users
would have to exit the database in order to do so. My thought was to store
the current "Month End Date" in a table all of its own and then just change
that for each new month. Then when I change the new month end date in the
[Month End} table, the form would pull in the new default date. Any ideas
would be appreciated.

Troy
 
Doable. You'd then use a DLookup function to retrieve the date from the
table. The textbox's Default Value property then would be
=DLookup("FieldNameInTable", "TableName")

Alternatively, if the "month end date" is the last day of the current month,
you could just use this expression as the Default Value property:
=DateSerial(Year(Date())), Month(Date()) + 1, 0)
 
Is the Month End date a date you can calculate? In other words, is it always
the last Friday, the working day, the last calendar day, etc.

Here is what I do. I have a situation where I want to use the current
reporting month as a default value. That month is ususally the previous
calendar month (The reporting month during October will be September). So, I
have a function in my Standard modules that calculates and returns that
month. It is called ReportMonth()
(Clever, what?). Then, in my form I make =ReportMont() the defalut value.
So, If you can give me the rule on how to identify the month end date, I have
several functions for calculating dates. If one of them is not exact, it
will be close enough I can show you how to create such a function.
 
=DateSerial(Year(Date())), Month(Date()) + 1, 0)
One too many parens, should be:
=DateSerial(Year(Date()), Month(Date()) + 1, 0)

Ken Snell said:
Doable. You'd then use a DLookup function to retrieve the date from the
table. The textbox's Default Value property then would be
=DLookup("FieldNameInTable", "TableName")

Alternatively, if the "month end date" is the last day of the current month,
you could just use this expression as the Default Value property:
=DateSerial(Year(Date())), Month(Date()) + 1, 0)

--

Ken Snell
<MS ACCESS MVP>

bondtk said:
I have a form with one field [Month End Date] that I want to have with a
default value. However, I don't want to have to modify the form's design
or
the table's design each month with the new month's default value as all
users
would have to exit the database in order to do so. My thought was to
store
the current "Month End Date" in a table all of its own and then just
change
that for each new month. Then when I change the new month end date in the
[Month End} table, the form would pull in the new default date. Any ideas
would be appreciated.

Troy
Click to expand...
Click to expand...
 
Yes, you're right. Thanks.

--

Ken Snell
<MS ACCESS MVP>

Klatuu said:
=DateSerial(Year(Date())), Month(Date()) + 1, 0)
One too many parens, should be:
=DateSerial(Year(Date()), Month(Date()) + 1, 0)

Ken Snell said:
Doable. You'd then use a DLookup function to retrieve the date from the
table. The textbox's Default Value property then would be
=DLookup("FieldNameInTable", "TableName")

Alternatively, if the "month end date" is the last day of the current
month,
you could just use this expression as the Default Value property:
=DateSerial(Year(Date())), Month(Date()) + 1, 0)

--

Ken Snell
<MS ACCESS MVP>

bondtk said:
I have a form with one field [Month End Date] that I want to have with a
default value. However, I don't want to have to modify the form's
design
or
the table's design each month with the new month's default value as all
users
would have to exit the database in order to do so. My thought was to
store
the current "Month End Date" in a table all of its own and then just
change
that for each new month. Then when I change the new month end date in
the
[Month End} table, the form would pull in the new default date. Any
ideas
would be appreciated.

Troy
Click to expand...
Click to expand...
Click to expand...
 
It appears it varies too much for a formula. They try to have it anywhere
between 14-24 workdays for each month.

Klatuu said:
Is the Month End date a date you can calculate? In other words, is it always
the last Friday, the working day, the last calendar day, etc.

Here is what I do. I have a situation where I want to use the current
reporting month as a default value. That month is ususally the previous
calendar month (The reporting month during October will be September). So, I
have a function in my Standard modules that calculates and returns that
month. It is called ReportMonth()
(Clever, what?). Then, in my form I make =ReportMont() the defalut value.
So, If you can give me the rule on how to identify the month end date, I have
several functions for calculating dates. If one of them is not exact, it
will be close enough I can show you how to create such a function.

bondtk said:
I have a form with one field [Month End Date] that I want to have with a
default value. However, I don't want to have to modify the form's design or
the table's design each month with the new month's default value as all users
would have to exit the database in order to do so. My thought was to store
the current "Month End Date" in a table all of its own and then just change
that for each new month. Then when I change the new month end date in the
[Month End} table, the form would pull in the new default date. Any ideas
would be appreciated.

Troy
Click to expand...
Click to expand...
 
Ken, I'm using the DLookup option you gave me with still no results. The
field just remains blank. My function looks like this in the fields default
value:

=DLookUp("Month End","Month End")

The table is called "Month End" and the only field in the table is called
"Month End"

Ken Snell said:
Doable. You'd then use a DLookup function to retrieve the date from the
table. The textbox's Default Value property then would be
=DLookup("FieldNameInTable", "TableName")

Alternatively, if the "month end date" is the last day of the current month,
you could just use this expression as the Default Value property:
=DateSerial(Year(Date())), Month(Date()) + 1, 0)

--

Ken Snell
<MS ACCESS MVP>

bondtk said:
I have a form with one field [Month End Date] that I want to have with a
default value. However, I don't want to have to modify the form's design
or
the table's design each month with the new month's default value as all
users
would have to exit the database in order to do so. My thought was to
store
the current "Month End Date" in a table all of its own and then just
change
that for each new month. Then when I change the new month end date in the
[Month End} table, the form would pull in the new default date. Any ideas
would be appreciated.

Troy
Click to expand...
Click to expand...
 
Then your original solution may be the way to do. One thing you might
consider to make it so you only have to do it once a year instead of monthly
is to create your table with two fields MONTH_NO and MONTH_END_DATE.
Then you could use a function like:
Function MonthEndDate()
MonthEndDate = DLookup("MONTH_END_DATE", "MonthEnd", _
"[MONTH_NO] = " & Month(date()))
End Function

Now make your default value =MonthEndDate()

Notice I took the space out of your table name. One good idea - Never use a
space in a name. It is always best to limit your naming to letters, numbers,
and the underscore.

bondtk said:
It appears it varies too much for a formula. They try to have it anywhere
between 14-24 workdays for each month.

Klatuu said:
Is the Month End date a date you can calculate? In other words, is it always
the last Friday, the working day, the last calendar day, etc.

Here is what I do. I have a situation where I want to use the current
reporting month as a default value. That month is ususally the previous
calendar month (The reporting month during October will be September). So, I
have a function in my Standard modules that calculates and returns that
month. It is called ReportMonth()
(Clever, what?). Then, in my form I make =ReportMont() the defalut value.
So, If you can give me the rule on how to identify the month end date, I have
several functions for calculating dates. If one of them is not exact, it
will be close enough I can show you how to create such a function.

bondtk said:
I have a form with one field [Month End Date] that I want to have with a
default value. However, I don't want to have to modify the form's design or
the table's design each month with the new month's default value as all users
would have to exit the database in order to do so. My thought was to store
the current "Month End Date" in a table all of its own and then just change
that for each new month. Then when I change the new month end date in the
[Month End} table, the form would pull in the new default date. Any ideas
would be appreciated.

Troy
Click to expand...
Click to expand...
Click to expand...
 
The Default Value will show *only* for new records, not for existing records
that you're editing. I am assuming that the expression otherwise would work
correctly; you can test this by putting the expression as the Control Source
of a textbox on your form; does it show the right value?

Tell us more about the form and its use.

--

Ken Snell
<MS ACCESS MVP>

bondtk said:
Ken, I'm using the DLookup option you gave me with still no results. The
field just remains blank. My function looks like this in the fields
default
value:

=DLookUp("Month End","Month End")

The table is called "Month End" and the only field in the table is called
"Month End"

Ken Snell said:
Doable. You'd then use a DLookup function to retrieve the date from the
table. The textbox's Default Value property then would be
=DLookup("FieldNameInTable", "TableName")

Alternatively, if the "month end date" is the last day of the current
month,
you could just use this expression as the Default Value property:
=DateSerial(Year(Date())), Month(Date()) + 1, 0)

--

Ken Snell
<MS ACCESS MVP>

bondtk said:
I have a form with one field [Month End Date] that I want to have with a
default value. However, I don't want to have to modify the form's
design
or
the table's design each month with the new month's default value as all
users
would have to exit the database in order to do so. My thought was to
store
the current "Month End Date" in a table all of its own and then just
change
that for each new month. Then when I change the new month end date in
the
[Month End} table, the form would pull in the new default date. Any
ideas
would be appreciated.

Troy
Click to expand...
Click to expand...
Click to expand...
 
Ken,

I found the problem after looking in a help file. Your code is missing the
[] around the field name:

=DLookUp("Month End","Month End Table") Should Be
=DLookUp("[Month End]","Month End Table")

After I put the brackets, it works! Thanks a bunch!


Ken Snell said:
The Default Value will show *only* for new records, not for existing records
that you're editing. I am assuming that the expression otherwise would work
correctly; you can test this by putting the expression as the Control Source
of a textbox on your form; does it show the right value?

Tell us more about the form and its use.

--

Ken Snell
<MS ACCESS MVP>

bondtk said:
Ken, I'm using the DLookup option you gave me with still no results. The
field just remains blank. My function looks like this in the fields
default
value:

=DLookUp("Month End","Month End")

The table is called "Month End" and the only field in the table is called
"Month End"

Ken Snell said:
Doable. You'd then use a DLookup function to retrieve the date from the
table. The textbox's Default Value property then would be
=DLookup("FieldNameInTable", "TableName")

Alternatively, if the "month end date" is the last day of the current
month,
you could just use this expression as the Default Value property:
=DateSerial(Year(Date())), Month(Date()) + 1, 0)

--

Ken Snell
<MS ACCESS MVP>

I have a form with one field [Month End Date] that I want to have with a
default value. However, I don't want to have to modify the form's
design
or
the table's design each month with the new month's default value as all
users
would have to exit the database in order to do so. My thought was to
store
the current "Month End Date" in a table all of its own and then just
change
that for each new month. Then when I change the new month end date in
the
[Month End} table, the form would pull in the new default date. Any
ideas
would be appreciated.

Troy
Click to expand...
Click to expand...
Click to expand...
 
Those [ ] characters are not required in the first argument of a DLookup
function (nor in the second argument either). But if it fixed the problem
for you, then that's good.

--

Ken Snell
<MS ACCESS MVP>

bondtk said:
Ken,

I found the problem after looking in a help file. Your code is missing
the
[] around the field name:

=DLookUp("Month End","Month End Table") Should Be
=DLookUp("[Month End]","Month End Table")

After I put the brackets, it works! Thanks a bunch!


Ken Snell said:
The Default Value will show *only* for new records, not for existing
records
that you're editing. I am assuming that the expression otherwise would
work
correctly; you can test this by putting the expression as the Control
Source
of a textbox on your form; does it show the right value?

Tell us more about the form and its use.

--

Ken Snell
<MS ACCESS MVP>

bondtk said:
Ken, I'm using the DLookup option you gave me with still no results.
The
field just remains blank. My function looks like this in the fields
default
value:

=DLookUp("Month End","Month End")

The table is called "Month End" and the only field in the table is
called
"Month End"

:

Doable. You'd then use a DLookup function to retrieve the date from
the
table. The textbox's Default Value property then would be
=DLookup("FieldNameInTable", "TableName")

Alternatively, if the "month end date" is the last day of the current
month,
you could just use this expression as the Default Value property:
=DateSerial(Year(Date())), Month(Date()) + 1, 0)

--

Ken Snell
<MS ACCESS MVP>

I have a form with one field [Month End Date] that I want to have
with a
default value. However, I don't want to have to modify the form's
design
or
the table's design each month with the new month's default value as
all
users
would have to exit the database in order to do so. My thought was
to
store
the current "Month End Date" in a table all of its own and then just
change
that for each new month. Then when I change the new month end date
in
the
[Month End} table, the form would pull in the new default date. Any
ideas
would be appreciated.

Troy
Click to expand...
Click to expand...
Click to expand...
 
I wonder why it didn't work without them. All the help file examples had the
bracket so that's where I got the idea to try it. And it worked so thanks
for your quick response on this. I was surprised to figure this out so
quickly.

Ken Snell said:
Those [ ] characters are not required in the first argument of a DLookup
function (nor in the second argument either). But if it fixed the problem
for you, then that's good.

--

Ken Snell
<MS ACCESS MVP>

bondtk said:
Ken,

I found the problem after looking in a help file. Your code is missing
the
[] around the field name:

=DLookUp("Month End","Month End Table") Should Be
=DLookUp("[Month End]","Month End Table")

After I put the brackets, it works! Thanks a bunch!


Ken Snell said:
The Default Value will show *only* for new records, not for existing
records
that you're editing. I am assuming that the expression otherwise would
work
correctly; you can test this by putting the expression as the Control
Source
of a textbox on your form; does it show the right value?

Tell us more about the form and its use.

--

Ken Snell
<MS ACCESS MVP>

Ken, I'm using the DLookup option you gave me with still no results.
The
field just remains blank. My function looks like this in the fields
default
value:

=DLookUp("Month End","Month End")

The table is called "Month End" and the only field in the table is
called
"Month End"

:

Doable. You'd then use a DLookup function to retrieve the date from
the
table. The textbox's Default Value property then would be
=DLookup("FieldNameInTable", "TableName")

Alternatively, if the "month end date" is the last day of the current
month,
you could just use this expression as the Default Value property:
=DateSerial(Year(Date())), Month(Date()) + 1, 0)

--

Ken Snell
<MS ACCESS MVP>

I have a form with one field [Month End Date] that I want to have
with a
default value. However, I don't want to have to modify the form's
design
or
the table's design each month with the new month's default value as
all
users
would have to exit the database in order to do so. My thought was
to
store
the current "Month End Date" in a table all of its own and then just
change
that for each new month. Then when I change the new month end date
in
the
[Month End} table, the form would pull in the new default date. Any
ideas
would be appreciated.

Troy
Click to expand...
Click to expand...
Click to expand...
 
Just guessing, but Month is the name of a VBA function; so it's possible
that the DLookup programming got confused over using it as part of a field
name and not knowing if it was supposed to be the function.

--

Ken Snell
<MS ACCESS MVP>

bondtk said:
I wonder why it didn't work without them. All the help file examples had
the
bracket so that's where I got the idea to try it. And it worked so thanks
for your quick response on this. I was surprised to figure this out so
quickly.

Ken Snell said:
Those [ ] characters are not required in the first argument of a DLookup
function (nor in the second argument either). But if it fixed the problem
for you, then that's good.

--

Ken Snell
<MS ACCESS MVP>

bondtk said:
Ken,

I found the problem after looking in a help file. Your code is missing
the
[] around the field name:

=DLookUp("Month End","Month End Table") Should Be
=DLookUp("[Month End]","Month End Table")

After I put the brackets, it works! Thanks a bunch!


:

The Default Value will show *only* for new records, not for existing
records
that you're editing. I am assuming that the expression otherwise would
work
correctly; you can test this by putting the expression as the Control
Source
of a textbox on your form; does it show the right value?

Tell us more about the form and its use.

--

Ken Snell
<MS ACCESS MVP>

Ken, I'm using the DLookup option you gave me with still no results.
The
field just remains blank. My function looks like this in the fields
default
value:

=DLookUp("Month End","Month End")

The table is called "Month End" and the only field in the table is
called
"Month End"

:

Doable. You'd then use a DLookup function to retrieve the date from
the
table. The textbox's Default Value property then would be
=DLookup("FieldNameInTable", "TableName")

Alternatively, if the "month end date" is the last day of the
current
month,
you could just use this expression as the Default Value property:
=DateSerial(Year(Date())), Month(Date()) + 1, 0)

--

Ken Snell
<MS ACCESS MVP>

I have a form with one field [Month End Date] that I want to have
with a
default value. However, I don't want to have to modify the
form's
design
or
the table's design each month with the new month's default value
as
all
users
would have to exit the database in order to do so. My thought
was
to
store
the current "Month End Date" in a table all of its own and then
just
change
that for each new month. Then when I change the new month end
date
in
the
[Month End} table, the form would pull in the new default date.
Any
ideas
would be appreciated.

Troy
Click to expand...
Click to expand...
Click to expand...
 
Back
Top