Increasing Membership Expiration Date one year upon renewal

[Dave Eliot]

On a form called Members I have a field named ExpDate, as well as a button
that opens a payment form. On the payment form, there are check boxes for
new and renewal. as well as a button to close the payment form (and return
to the Members form). How can I have the ExpDate on the Members form
increase one year if the renewal check box is "true" on the payment form?

Am I asking too much? If so, I'm willing to put a button on the Members form
that would increase the ExpDate by a year. I did find "DateAdd( "yyyy", 1,
ExpDate)" but couldn't make it work no matter how I tried.

Thanks to all of you who provide so much help.

 

[Guest]

In the after update event of the renewal check box add
if checkbox = -1 then
me.expDate = date() + 364
end if

 

[Dave Eliot]

Well, I tried that and got a Compile error: Method or data member not found.
and ".ExpDate =" was highlighted.
The problem may be that the Members form is tied to the table called
Members, and the Payment form is tied to the table called Payments. Se
perhaps there's more to this.

Thanks again.

 

[Guest]

You could trigger an update query that updates the data

 

[Dave Eliot]

I think -- no -- I know that I'm in over my head. I haven't a clue about
what you just suggested. I don't want to take any more of your time. I will
just continue to manually enter the new ExpDate.

Thank you.

You could trigger an update query that updates the data

 

[Guest]

You need a control on your payment form named ExpDate that is bound to the
field in the recordset that is the expiration date.
Then do as Eric said, except DO NOT use the calculation that way. You way
is the correct way:
me.expDate = DateAdd("yyyy", 1, Nz(Me.expDate,Date))

This way you don't loose a day each year and it is more obvious to the
reader what you are doing.

 

[Dave Eliot]

When I designed the payment form I only chose the payment table for fields.
Now, I can't figure out how to add "a control on your payment form named
ExpDate that is bound to the field in the recordset that is the expiration
date." Each member's expiration date is stored in the Members table.

So . . .

Is there a way to add a button to the Members form that could use
me.expDate = DateAdd("yyyy", 1, Nz(Me.expDate,Date))
and change the ExpDate right before my very eyes?

I'm soon giving up on this one. I guess you can tell I'm new at this.

You need a control on your payment form named ExpDate that is bound to the
field in the recordset that is the expiration date.
Then do as Eric said, except DO NOT use the calculation that way. You way
is the correct way:
me.expDate = DateAdd("yyyy", 1, Nz(Me.expDate,Date))

This way you don't loose a day each year and it is more obvious to the
reader what you are doing.

 

[Guest]

You don't need to do anything with the member form. You can do this from the
Payment form.
I will invent my own names, you may need to change them.

First, add a text box to the Payment form. txtExpireDate
It will be an unbound control.

Now, we need to load txtExpireDate with the current date for the member in
the current record. That should be done in the Current event of the form.
Again, I don't know your names, so these are just dummies:

Me.txtExpireDate = DLookup("[EXPIRE_DATE]", "tblMembers", "MEMBER_NO =
'" & Me.txtMemberNo & "'")

Now, each time you move to a different record or create a new record, the
expiration date will show up on the form. Then, to update the date and add a
year to it when the payment is entered, use the After Update event of the
control where you enter the payment:

Private Sub txtPayment_AfterUpdate()

If Not IsNull(Me.txtPayment) Then
Me.txtExpireDate = DateAdd("yyyy", 1, Me.txtExpireDate)
CurrentDb.Execute("UPDATE tblMember SET EXPIRE_DATE = #" &
Me.txtExpireDate & "# WHERE MEMBER_NO = '" & Me.txtMemberNo & "';"),
dbFailOnError
End If
End Sub

Note, the last line (CurrentDb.Execute.....) updates the expiration date in
the member table.

Don't give up. Post back with any questions.

 

[Dave Eliot]

I thought I try this one step at a time.

I created the unbound box called txtExpireDate on the payments form just
fine.

In my table called members there's a field called ID which is an AutoNumber,
I substituted ID for your MEMBER_NO, my ExpDate for your EXPIRE_DATE -- no
problem. I sustituted MemberID from the Payments table for your
Me.txtMemberNo. (It's the MemberID field in Payments that sets the
relationship with ID in Members.) Was that correct substitution?

I have no idea if I'm supposed to fill anything in between the "". Anyway,
with these substituions I can't get the date to appear in the box named
txtExpireDate.

You don't need to do anything with the member form. You can do this from
the
Payment form.
I will invent my own names, you may need to change them.

First, add a text box to the Payment form. txtExpireDate
It will be an unbound control.

Now, we need to load txtExpireDate with the current date for the member in
the current record. That should be done in the Current event of the form.
Again, I don't know your names, so these are just dummies:

Me.txtExpireDate = DLookup("[EXPIRE_DATE]", "tblMembers", "MEMBER_NO =
'" & Me.txtMemberNo & "'")

Now, each time you move to a different record or create a new record, the
expiration date will show up on the form. Then, to update the date and
add a
year to it when the payment is entered, use the After Update event of the
control where you enter the payment:

Private Sub txtPayment_AfterUpdate()

If Not IsNull(Me.txtPayment) Then
Me.txtExpireDate = DateAdd("yyyy", 1, Me.txtExpireDate)
CurrentDb.Execute("UPDATE tblMember SET EXPIRE_DATE = #" &
Me.txtExpireDate & "# WHERE MEMBER_NO = '" & Me.txtMemberNo & "';"),
dbFailOnError
End If
End Sub

Note, the last line (CurrentDb.Execute.....) updates the expiration date
in
the member table.

Don't give up. Post back with any questions.

 

[Guest]

Me.txtMemberNo referes to a control on the Payments form. In your case, it
may be ID. What you need is the reference to the record in the member table
that corresponds to the member number in the Payment form so you update
expire date in the correct record.

Here are some important lines of code describes. Hopefully this will help:

Me.txtExpireDate = DLookup("[EXPIRE_DATE]", "tblMembers", "MEMBER_NO =
'" & Me.txtMemberNo & "'")

This line of code put the expiration date on the payment form
Me. = Payment Form
..txtExpireDate = the control on the payment form
[EXPIRE_DATE] = the field in tblMembers that has the expiration date
MEMBER_NO = the field in tblMembers that uniquely identifies the member
txtMemberNo = the control on the payment form that contains the member number.
So what happens is it finds the record in the members table for the member
you are recording a payment for. retrieves the value in the exiration date
field and populates the control that displays the current exiration date.

CurrentDb.Execute("UPDATE tblMember SET EXPIRE_DATE = #" &
Me.txtExpireDate & "# WHERE MEMBER_NO = '" & Me.txtMemberNo & "';"),
dbFailOnError

This is an SQL Update Query that updates the value of the expiration date
field with the value in the control on the payment form.

--
Dave Hargis, Microsoft Access MVP

I thought I try this one step at a time.

I created the unbound box called txtExpireDate on the payments form just
fine.

In my table called members there's a field called ID which is an AutoNumber,
I substituted ID for your MEMBER_NO, my ExpDate for your EXPIRE_DATE -- no
problem. I sustituted MemberID from the Payments table for your
Me.txtMemberNo. (It's the MemberID field in Payments that sets the
relationship with ID in Members.) Was that correct substitution?

I have no idea if I'm supposed to fill anything in between the "". Anyway,
with these substituions I can't get the date to appear in the box named
txtExpireDate.

You don't need to do anything with the member form. You can do this from
the
Payment form.
I will invent my own names, you may need to change them.

First, add a text box to the Payment form. txtExpireDate
It will be an unbound control.

Now, we need to load txtExpireDate with the current date for the member in
the current record. That should be done in the Current event of the form.
Again, I don't know your names, so these are just dummies:

Me.txtExpireDate = DLookup("[EXPIRE_DATE]", "tblMembers", "MEMBER_NO =
'" & Me.txtMemberNo & "'")

Now, each time you move to a different record or create a new record, the
expiration date will show up on the form. Then, to update the date and
add a
year to it when the payment is entered, use the After Update event of the
control where you enter the payment:

Private Sub txtPayment_AfterUpdate()

If Not IsNull(Me.txtPayment) Then
Me.txtExpireDate = DateAdd("yyyy", 1, Me.txtExpireDate) c
End If
End Sub

Note, the last line (CurrentDb.Execute.....) updates the expiration date
in
the member table.

Don't give up. Post back with any questions.
--
Dave Hargis, Microsoft Access MVP

When I designed the payment form I only chose the payment table for
fields.
Now, I can't figure out how to add "a control on your payment form named
ExpDate that is bound to the field in the recordset that is the
expiration
date." Each member's expiration date is stored in the Members table.

So . . .

Is there a way to add a button to the Members form that could use
me.expDate = DateAdd("yyyy", 1, Nz(Me.expDate,Date))
and change the ExpDate right before my very eyes?

I'm soon giving up on this one. I guess you can tell I'm new at this.



You need a control on your payment form named ExpDate that is bound to
the
field in the recordset that is the expiration date.
Then do as Eric said, except DO NOT use the calculation that way. You
way
is the correct way:
me.expDate = DateAdd("yyyy", 1, Nz(Me.expDate,Date))

This way you don't loose a day each year and it is more obvious to the
reader what you are doing.


--
Dave Hargis, Microsoft Access MVP


:

Well, I tried that and got a Compile error: Method or data member not
found.
and ".ExpDate =" was highlighted.
The problem may be that the Members form is tied to the table called
Members, and the Payment form is tied to the table called Payments. Se
perhaps there's more to this.

Thanks again.

message
In the after update event of the renewal check box add
if checkbox = -1 then
me.expDate = date() + 364
end if


:

On a form called Members I have a field named ExpDate, as well as a
button
that opens a payment form. On the payment form, there are check
boxes
for
new and renewal. as well as a button to close the payment form (and
return
to the Members form). How can I have the ExpDate on the Members
form
increase one year if the renewal check box is "true" on the payment
form?

Am I asking too much? If so, I'm willing to put a button on the
Members
form
that would increase the ExpDate by a year. I did find "DateAdd(
"yyyy",
1,
ExpDate)" but couldn't make it work no matter how I tried.

Thanks to all of you who provide so much help.

 

[Dave Eliot]

Your explanation is very clear and makes sense. I've been very careful with
details. It still doesn't populate the txtExpireDate box. I did notice that
when I went to Form-Event-OnCurrent, the Expression Builder added = to the
beginning of the code, so then I tried it with Code Builder and got a syntax
error. I think it's time to call a halt to this. You've been so generous
with your time and knowledge, but there is obviously something wrong with
what I'm doing.

I did populate another field on the payment form (MemberID) with
Data-Default-Value =[Forms]![Members]![ID]. So I tried the same thing with
txtExpireDate. Sure enough the date showed up! However I couldn't add a
year.

So, in conclusion. Thanks for all you've done. It will not kill me to simply
type the new date before I go to the payment form. I just thought it would
be a nice to make it easier.

Thanks again.

Me.txtMemberNo referes to a control on the Payments form. In your case,
it
may be ID. What you need is the reference to the record in the member
table
that corresponds to the member number in the Payment form so you update
expire date in the correct record.

Here are some important lines of code describes. Hopefully this will
help:

Me.txtExpireDate = DLookup("[EXPIRE_DATE]", "tblMembers", "MEMBER_NO =
'" & Me.txtMemberNo & "'")

This line of code put the expiration date on the payment form
Me. = Payment Form
.txtExpireDate = the control on the payment form
[EXPIRE_DATE] = the field in tblMembers that has the expiration date
MEMBER_NO = the field in tblMembers that uniquely identifies the member
txtMemberNo = the control on the payment form that contains the member
number.
So what happens is it finds the record in the members table for the member
you are recording a payment for. retrieves the value in the exiration date
field and populates the control that displays the current exiration date.

CurrentDb.Execute("UPDATE tblMember SET EXPIRE_DATE = #" &
Me.txtExpireDate & "# WHERE MEMBER_NO = '" & Me.txtMemberNo & "';"),
dbFailOnError

This is an SQL Update Query that updates the value of the expiration date
field with the value in the control on the payment form.

--
Dave Hargis, Microsoft Access MVP

I thought I try this one step at a time.

I created the unbound box called txtExpireDate on the payments form just
fine.

In my table called members there's a field called ID which is an
AutoNumber,
I substituted ID for your MEMBER_NO, my ExpDate for your EXPIRE_DATE --
no
problem. I sustituted MemberID from the Payments table for your
Me.txtMemberNo. (It's the MemberID field in Payments that sets the
relationship with ID in Members.) Was that correct substitution?

I have no idea if I'm supposed to fill anything in between the "".
Anyway,
with these substituions I can't get the date to appear in the box named
txtExpireDate.

You don't need to do anything with the member form. You can do this
from
the
Payment form.
I will invent my own names, you may need to change them.

First, add a text box to the Payment form. txtExpireDate
It will be an unbound control.

Now, we need to load txtExpireDate with the current date for the member
in
the current record. That should be done in the Current event of the
form.
Again, I don't know your names, so these are just dummies:

Me.txtExpireDate = DLookup("[EXPIRE_DATE]", "tblMembers", "MEMBER_NO
=
'" & Me.txtMemberNo & "'")

Now, each time you move to a different record or create a new record,
the
expiration date will show up on the form. Then, to update the date and
add a
year to it when the payment is entered, use the After Update event of
the
control where you enter the payment:

Private Sub txtPayment_AfterUpdate()

If Not IsNull(Me.txtPayment) Then
Me.txtExpireDate = DateAdd("yyyy", 1, Me.txtExpireDate) c
End If
End Sub

Note, the last line (CurrentDb.Execute.....) updates the expiration
date
in
the member table.

Don't give up. Post back with any questions.
--
Dave Hargis, Microsoft Access MVP


:

When I designed the payment form I only chose the payment table for
fields.
Now, I can't figure out how to add "a control on your payment form
named
ExpDate that is bound to the field in the recordset that is the
expiration
date." Each member's expiration date is stored in the Members table.

So . . .

Is there a way to add a button to the Members form that could use
me.expDate = DateAdd("yyyy", 1, Nz(Me.expDate,Date))
and change the ExpDate right before my very eyes?

I'm soon giving up on this one. I guess you can tell I'm new at this.



You need a control on your payment form named ExpDate that is bound
to
the
field in the recordset that is the expiration date.
Then do as Eric said, except DO NOT use the calculation that way.
You
way
is the correct way:
me.expDate = DateAdd("yyyy", 1, Nz(Me.expDate,Date))

This way you don't loose a day each year and it is more obvious to
the
reader what you are doing.


--
Dave Hargis, Microsoft Access MVP


:

Well, I tried that and got a Compile error: Method or data member
not
found.
and ".ExpDate =" was highlighted.
The problem may be that the Members form is tied to the table
called
Members, and the Payment form is tied to the table called Payments.
Se
perhaps there's more to this.

Thanks again.

message
In the after update event of the renewal check box add
if checkbox = -1 then
me.expDate = date() + 364
end if


:

On a form called Members I have a field named ExpDate, as well
as a
button
that opens a payment form. On the payment form, there are check
boxes
for
new and renewal. as well as a button to close the payment form
(and
return
to the Members form). How can I have the ExpDate on the Members
form
increase one year if the renewal check box is "true" on the
payment
form?

Am I asking too much? If so, I'm willing to put a button on the
Members
form
that would increase the ExpDate by a year. I did find "DateAdd(
"yyyy",
1,
ExpDate)" but couldn't make it work no matter how I tried.

Thanks to all of you who provide so much help.