Can I use a check box

[BobLincs]

I am building a database for the membership of a club I belong to. I'm happy with what I've achieved so far but little embellishments pop into mind and I would like to know if it is possible to use a check box to change a date.

I have a field which shows the date of renewal for a member's subs.

What I would like to do is to have a check box that when checked would change the date in the renew field to the same date next year and then clear the check.

I have never got to grips with VB so if such code is involved I would appreciate something that I could cut and paste. I would also like to know where to put it (politely).

 

[John Big Booty]

Use the DateAdd() function.

In the on click event of your check box try;

Me.SubDate = DateAdd("yyyy",1,Me.SubDate)

 

[BobLincs]

Thanks for try but all I get is a message saying it doesn't recognise the macro.

I am assuming that I replace Me.SubDate with my tablename.fieldname.

As the checkbox didn't work I've added a yes/no field to the table/form but the result is the same.

 

[John Big Booty]

The only part of;

Me.[B][COLOR="Magenta"]SubDate[/COLOR][/B] = DateAdd("yyyy",1,Me.[COLOR="Magenta"][B]SubDate[/B][/COLOR])

That you need change is the highlighted section which you should replace with the name of the control that hold the subscription date.

 

[BobLincs]

Thanks again but it's still no go. Must be me.

My date field is called renew. The data properties for the field show the control as renew.

I have substituted renew for SubDate in your expression and put it in the on click box for the checkbox event properties but when trying it I get a message saying that Access does not recognise the macro "Me".

Presumably I'm doing something wrong but I've not tried using code before although your instruction seems straightforward and I can't see where I'm going wrong.

 

[boblarson]

I have substituted renew for SubDate in your expression and put it in the on click box for the checkbox event properties but when trying it I get a message saying that Access does not recognise the macro "Me".

That's because you are making the common mistake of trying to put code in the event PROPERTY dialog and not in the VBA Window where it should go.

 

[BobLincs]

Many thanks for taking the time to suggest a remedy John.

I hadn't got a clue regarding where I should put the code Boblarson but your comment set me searching. I found the correct destination by luck, entered your instruction John and Hey Presto, it worked.

Thankyou both for taking the time to assist me.

Just one more thing. Probably very simple but beyond my knowledge.

Having clicked the box and changed the date tick remains in the box. Is there some thing I can tack on to the code that will clear the tick when it's activated the date change.

 

[boblarson]

I hadn't got a clue regarding where I should put the code Boblarson but your comment set me searching.

You didn't need to search because I included a link on the words VBA Window which, if you had clicked on it, would have taken you to my site and directly to screenshots which SHOWED you where to put it.

 

[Rich]

How odd, two things stick out here, the first is that your renewal date is in fact a calculated value and as such shouldn't be stored, in any case the value can always be retrieved via a Query

 

[Brianwarnock]

You didn't need to search because I included a link on the words VBA Window which, if you had clicked on it, would have taken you to my site and directly to screenshots which SHOWED you where to put it.

Ok Bob but I missed that and I'm not a complete beginner, sometimes the simplest things do need to be spelt out as people are not aware of the "rules" of engagement. He should be congratulated on his efforts not critized for not knowing that that was a link.

Brian

 

[BobLincs]

Sorry, I was a bit dim there, for some reason it just didn't register as a link.

I've since checked the link and realise how easy it could have been.

Thanks

 

[smig]

not sure why you want to use a checkbox that will unmark itself
don't you think it's better to use a button ? use the button OnClick event for the code John gave you.

make sure a user won't click the button 10 times and add 10 years
you can do it by adding an year only if the current date is less than a month before the end date, for example.

 

[BobLincs]

I would just like to say thank you to all who replied and offered/provided help.

I personally took no offence from your email Bob.

Thanks for button suggestion Smig I hadn't thought of that, but I see less chance of erroroneous entry with the check box.

And much to my surprise I tried adding a bit of code that I thought might clear it and it worked.

So thanks again. Problem solved but I'm sure to be back with others, although now Bob's pointed me in the right direction I will be more encouraged to have a go myself.