How do I allow users to change a calculated date in a form field?

  • Thread starter Thread starter Guest
  • Start date Start date
Make the calculated date the default for that field. Then your users could
change it if needed.
 
If you need to allow users to change the value in a field, you need to store
it.

Use the AfterUpdate event of the control(s) its value is based on to assign
the inital value. The users can then change it as needed.

For example, if your invoice is normally due 14 days from the InvoiceDate,
but a user can change to to 30 days or something else, you would use this in
the AfterUpdate event procedure of InvoiceDate:
Private Sub InvoiceDate_AfterUpdate()
Me.DueDate = DateAdd("d", 14, Me.InvoiceDate)
End Sub

More info in article:
Calculated Fields
at:
http://allenbrowne.com/casu-14.html
 
Hi Rick,

Thanks for the info, but now when I make the calculated date the default for
that field, it does not calculate the date correctly...odd.

Here is what I have, several date fields that add a certain number of days
based on criteria for meeting deadlines. For instance, field 1 is a package
mail date, field 2 is a 45-day reminder date field, field 2 adds 45 days to
the date in field 1 and populates field 2 with the calculated date, but I
need users to be able to change the date in field 2. This is the expression
I've put in the default value: =DateSerial(Year([Package Mail
Date]),Month([Package Mail Date])+3,Day([Package Mail Date]+90)), but now
when I put in a package mail date of 12/1/05, the 45-day reminder date is
calculating as 11/8/03...weirder yet.

Any assistance would be greatly appreciated...btw, I am not all that well
versed in Access so please forgive my ignorance.

Regards,
Jean
 
Allen,

Thanks for your reply...what is the Me.?

Allen Browne said:
If you need to allow users to change the value in a field, you need to store
it.

Use the AfterUpdate event of the control(s) its value is based on to assign
the inital value. The users can then change it as needed.

For example, if your invoice is normally due 14 days from the InvoiceDate,
but a user can change to to 30 days or something else, you would use this in
the AfterUpdate event procedure of InvoiceDate:
Private Sub InvoiceDate_AfterUpdate()
Me.DueDate = DateAdd("d", 14, Me.InvoiceDate)
End Sub

More info in article:
Calculated Fields
at:
http://allenbrowne.com/casu-14.html
Click to expand...
 
When you set the After Update property to:
[Event Procedure]
and then click the Build button (...) beside the property, Access opens the
code window.

In that context, you can use Me. to refer to the form that this module
belongs to. For example, if the form was named Form1, then Me is equivalent
to:
Forms![Form1]
 
Thanks Allen...that was very helpful! I really appreciate your assistance on
this, as it's been a while since I've used Access and have forgotten quite a
lot.

I tried your suggestion, but now I get the following error:

Control can't be edited; it's bound to the expression
'DateSerial(Year([Package Mail Date]),Month([Package Mail
Date])+3,Day([Package Mail Date]+90))'.

Does this make any sense to you?

Allen Browne said:
When you set the After Update property to:
[Event Procedure]
and then click the Build button (...) beside the property, Access opens the
code window.

In that context, you can use Me. to refer to the form that this module
belongs to. For example, if the form was named Form1, then Me is equivalent
to:
Forms![Form1]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

jdasnoit said:
Allen,

Thanks for your reply...what is the Me.?
Click to expand...
Click to expand...
 
Change the Control Source property of the text box to the name of the field
where you want the value to be stored.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

jdasnoit said:
Thanks Allen...that was very helpful! I really appreciate your assistance
on
this, as it's been a while since I've used Access and have forgotten quite
a
lot.

I tried your suggestion, but now I get the following error:

Control can't be edited; it's bound to the expression
'DateSerial(Year([Package Mail Date]),Month([Package Mail
Date])+3,Day([Package Mail Date]+90))'.

Does this make any sense to you?

Allen Browne said:
When you set the After Update property to:
[Event Procedure]
and then click the Build button (...) beside the property, Access opens
the
code window.

In that context, you can use Me. to refer to the form that this module
belongs to. For example, if the form was named Form1, then Me is
equivalent
to:
Forms![Form1]

jdasnoit said:
Allen,

Thanks for your reply...what is the Me.?

:

If you need to allow users to change the value in a field, you need to
store
it.

Use the AfterUpdate event of the control(s) its value is based on to
assign
the inital value. The users can then change it as needed.

For example, if your invoice is normally due 14 days from the
InvoiceDate,
but a user can change to to 30 days or something else, you would use
this
in
the AfterUpdate event procedure of InvoiceDate:
Private Sub InvoiceDate_AfterUpdate()
Me.DueDate = DateAdd("d", 14, Me.InvoiceDate)
End Sub

More info in article:
Calculated Fields
at:
http://allenbrowne.com/casu-14.html

How do I allow users to change a calculated date in a form field?
Click to expand...
Click to expand...
Click to expand...
 
Hi Rick,

I'm still trying to get this to work. Here is more detail...

Subject: Allowing users to change data in bound fields on form 1/4/2006
11:35 AM PST

By: jdasnoit In: microsoft.public.access.forms


Hello All,

This is a repeat question with the hopes of getting more detailed answers if
I provide a more detailed question.

I have form that is used to populate data on a table, from which queries are
run. On my form, called 'BD Form', I have several calculated date fields.
The dates are calculated off one field 'Package Mail Date' after a date is
entered, and are the 'Package Mail Date' + 10 days, 15 days or 90 days and
then placed in one of the following fieds respectively:
'10 Day Courtesy Call'
'45 Day Reminder' or
'Expiration Date',
and the Control Source for each looks like this:
=DateSerial(Year([Package Mail Date]),Month([Package Mail
Date]),Day([Package Mail Date]+10))
=DateSerial(Year([Package Mail Date]),Month([Package Mail
Date])+1,Day([Package Mail Date]+15))
=DateSerial(Year([Package Mail Date]),Month([Package Mail
Date])+3,Day([Package Mail Date]+90))

I need to be able to allow users to change the date that is calculated in
the 'Expiration Date' field. I am fairly new to MSA, so idiot proof
instructions are appreciated, and any and all suggestions/assistance are/is
greatly appreciated.

Best Regards,
Jean
 
Hi Allen,

I'm still trying to get this to work. Here is more detail...

Subject: Allowing users to change data in bound fields on form 1/4/2006
11:35 AM PST

By: jdasnoit In: microsoft.public.access.forms


Hello All,

This is a repeat question with the hopes of getting more detailed answers if
I provide a more detailed question.

I have form that is used to populate data on a table, from which queries are
run. On my form, called 'BD Form', I have several calculated date fields.
The dates are calculated off one field 'Package Mail Date' after a date is
entered, and are the 'Package Mail Date' + 10 days, 15 days or 90 days and
then placed in one of the following fieds respectively:
'10 Day Courtesy Call'
'45 Day Reminder' or
'Expiration Date',
and the Control Source for each looks like this:
=DateSerial(Year([Package Mail Date]),Month([Package Mail
Date]),Day([Package Mail Date]+10))
=DateSerial(Year([Package Mail Date]),Month([Package Mail
Date])+1,Day([Package Mail Date]+15))
=DateSerial(Year([Package Mail Date]),Month([Package Mail
Date])+3,Day([Package Mail Date]+90))

I need to be able to allow users to change the date that is calculated in
the 'Expiration Date' field. I am fairly new to MSA, so idiot proof
instructions are appreciated, and any and all suggestions/assistance are/is
greatly appreciated.

Best Regards,
Jean



Allen Browne said:
Change the Control Source property of the text box to the name of the field
where you want the value to be stored.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

jdasnoit said:
Thanks Allen...that was very helpful! I really appreciate your assistance
on
this, as it's been a while since I've used Access and have forgotten quite
a
lot.

I tried your suggestion, but now I get the following error:

Control can't be edited; it's bound to the expression
'DateSerial(Year([Package Mail Date]),Month([Package Mail
Date])+3,Day([Package Mail Date]+90))'.

Does this make any sense to you?

Allen Browne said:
When you set the After Update property to:
[Event Procedure]
and then click the Build button (...) beside the property, Access opens
the
code window.

In that context, you can use Me. to refer to the form that this module
belongs to. For example, if the form was named Form1, then Me is
equivalent
to:
Forms![Form1]

Allen,

Thanks for your reply...what is the Me.?

:

If you need to allow users to change the value in a field, you need to
store
it.

Use the AfterUpdate event of the control(s) its value is based on to
assign
the inital value. The users can then change it as needed.

For example, if your invoice is normally due 14 days from the
InvoiceDate,
but a user can change to to 30 days or something else, you would use
this
in
the AfterUpdate event procedure of InvoiceDate:
Private Sub InvoiceDate_AfterUpdate()
Me.DueDate = DateAdd("d", 14, Me.InvoiceDate)
End Sub

More info in article:
Calculated Fields
at:
http://allenbrowne.com/casu-14.html

How do I allow users to change a calculated date in a form field?
Click to expand...
Click to expand...
Click to expand...
 
Jean, there is nothing new in this reply, and no indication of what problem
you are experiencing, or what error message you are receiving. Time for me
to move on to other threads.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

jdasnoit said:
Hi Allen,

I'm still trying to get this to work. Here is more detail...

Subject: Allowing users to change data in bound fields on form 1/4/2006
11:35 AM PST
Click to expand...

[followed by repeat of original post]
 
Back
Top