Synchronizing a form with a table

  • Thread starter Thread starter sabz
  • Start date Start date
S

sabz

Hi, I'm trying to synchronize a data entry form to work together with a
table that has a lot of entries. Here's my problem: I have a field
called First mailing date, and another field called Next mailing date.
I enter the First mailing date and want access to calculate next
mailing date by adding 3 years to first mailing date. I tried and tried
to do this in the TABLE. It didn't work successfully, kept on saying I
had a error. So instead, since I use a FORM to enter data into the
table I wrote up a DateAdd function that add's 3 years to First mailing
date. It work's fine when I look at the FORM's as a whole, but as soon
as I go back to the TABLE, the next mailing date field is blank... Can
anyone help? I tried looking for the properties of the table and
changing the DATA to get it's info from the FORM, but there is no such
option to do that to a table! Also, the TABLE has no control source
option so i can't successfully add a dateadd function... Any help
whatsoever would be greatly appreciated. Thanks, Sabz
 
The next mailing date control on your form needs to be bound to the next
mailing date field in your table.
Use the form's next mailing date control's Default Value for your calculation:
= DateAdd("yyyy", 3, Me.FirstMailingDate)
 
If you want the next mailing date value to always be three years after the
first mailing date then you should not store it in a field in the table; that
is redundancy. You just need to compute it in a control on a form (as you
have done) or report or in a computed column in a query when required, using
the DateAdd function.

The only circumstance in which you should store it in the table is if you
only want the 3 year date difference as the default and want to be able to
change this in individual cases by editing the next mailing date. In that
scenario you should assign the value to the next mailing date field in the
AfterUpdate event procedure of the first mailing date control on the form:

If Not IsNull(Me.[First Mailing Date]) Then
[Next Mailing Date] = DateAdd("yyyy",3, [First Mailing Date])
Else
[Next Mailing Date]) = Null
End If

Ken Sheridan
Stafford, England
 
Back
Top