My table field is not being populated

[Guest]

In my Member form, I have a field that automatically calculates the date to 1
year after what is inputed in the previous field.

So in the field called "Inscription date", if I put July 1, 2000, then in
the field called "Renewal date", I have a formula in the Control Source that
automatically updates that field to go one year later, so it would read:
July 1, 2001.

In my control source for that field, I have this formula:

=DateAdd("yyyy",1,[carte de membre])

My problem is, when I go to the Member Table, the fields for the Renewal
date are not being populated, they are blank...so I can't report on those
fields.

First time building any kind of database....

Please help.

Thanks,

Tracy

 

[Dave]

a control can either be bound to a database column or filled in with some
other method like you are doing. if it is bound to a column then changes in
the value on the form will get saved back to the table, otherwise they
won't. there are at least 2 ways to do what you want.
1. make the [renewal date] column a calculated column in the table and then
bind the control to the column. this is probably the simplest method, but
it locks in the renewal date so you can not change it directly.
2. use the form 'on current' event to calculate the renewal date and write
it to the table, this would also need to be done again if the user changes
the date it is calculated from. i don't like this so much because you have
to manually handle several events and be sure you write the date back to the
table manually, but it does give you control over it directly.

 

[Rick B]

Yes you can. In your report (or the query upon which your report is based)
include the calculation.

Proper database design dictates that you don't store calculated values in
the table. That is redundant. You have the original date in the table.
When you need to see that date plus 1 year, calculate it.

One big reason is that you would be taking up the space of two dates when
you only need one. Another reason is data entry errors. What if you need
to go back and correct the original date? How do you make sure that the
calculated date gets updated?

If you will do some searches, you will find this same response many many
many times in here. You should always search before you post a new
question.

Hope that helps,

Rick B

 

[[MVP] S.Clark]

It is one thing to create a calculated text box. It is another to update a
bound text box.

Suppose that the first textbox is called txtInscriptionDate, and the 2nd is
txtRenewalDate, and that both are bound to a table which is the recordsource
of the form.

In the AfterUpdate Event of txtInscriptionDate put:

if not isnull(txtInscriptionDate) then
txtRenewalDate = DateAdd("yyyy",1,txtInscriptionDate)
else
txtRenewalDate = Null
end if

 

[Guest]

Thanks to everyone for your help and input.

Rick,

Thank you...I did as you suggested and that works great, however, I fixed
one thing and broke another it seems.

Before all of this, when I was inputting the renewal dates manually, I had a
query built to pull out the renewals coming out for the month, this is my
code for that query:

SELECT Members.Prenom, Members.Nom, Members.[Renouvellement de carte]
FROM Members
WHERE (((Month([Renouvellement de carte]))=[Mois]));

So when I run that query, it prompts me for a month, I put "6" for example
and it will pull out all the renewals coming out in the month of June.

But now, this doesn't work because it's looking in a blank table field. Am
I to put my If statement somewhere in there as well?

Thanks,

Trace

Yes you can. In your report (or the query upon which your report is based)
include the calculation.

Proper database design dictates that you don't store calculated values in
the table. That is redundant. You have the original date in the table.
When you need to see that date plus 1 year, calculate it.

One big reason is that you would be taking up the space of two dates when
you only need one. Another reason is data entry errors. What if you need
to go back and correct the original date? How do you make sure that the
calculated date gets updated?

If you will do some searches, you will find this same response many many
many times in here. You should always search before you post a new
question.

Hope that helps,

Rick B

In my Member form, I have a field that automatically calculates the date to 1
year after what is inputed in the previous field.

So in the field called "Inscription date", if I put July 1, 2000, then in
the field called "Renewal date", I have a formula in the Control Source that
automatically updates that field to go one year later, so it would read:
July 1, 2001.

In my control source for that field, I have this formula:

=DateAdd("yyyy",1,[carte de membre])

My problem is, when I go to the Member Table, the fields for the Renewal
date are not being populated, they are blank...so I can't report on those
fields.

First time building any kind of database....

Please help.

Thanks,

Tracy