Change Field When Criteria Is Met

[fredfox]

You will see it is a simple problem if you know what your are doing.

When entering the joining date I would like the Status to change automatically to Member and when the renewal date is reached I would like the status to automatically change to Renewal but I am unable to work out a formula for this.

Many Thanks,

Fredfox.

 

[ColinEssex]

In the AfterUpdate event of DateJoined put this

Me.MembershipStatus = "Member"
Me.Renewal = Me.DateJoined + 365

In the OnCurrent event of the form put this

If Me.Renewal = Date Then
Me.MembershipStatus = "Renewal"
End If

The field Renewal set the Control source to the Renewal field in the table or it won't store a date.

Lose the ComboBox as you don't need it, just make it a TextBox.
Remember your Renewal will be a day out in a leap year

Col

 

[neileg]

I think the code should be

If Me.Renewal <= Date Then
Me.MembershipStatus = "Renewal"
End If

Some more tips:
Why have you got such a strange mask on the date?
You should base your form on a query, not on the table. This will make future modifications much easier.

 

[ColinEssex]

Thanks Neil, I did it rather quickly, I forgot the <=

Col

 

[fredfox]

Thanks for that, would it be too much to ask for you to change the zip file accordingly and send it back to me so I can see where I have to put it etc.

Many Thanks,

Fredfox.

 

[ColinEssex]

In design view put them as I described in the properties of the form or field.

Col

 

[fredfox]

Need Help from Colin Essex

Colin,

Here is the zip file attachment, if you would be so kind as to complete the problem and then send it back so I can see just where you put what etc

Fred Fox

 

[jrjr]

I fixed it up for you. Also, now when the date has passed, the status says "Over Due"

Look it over and see how it is done.....