button to advance date by one year

[premint]

hello, im very new to access but managing to find what i need with just google, but ive drawn a blank on this one, probably quite easy if you know how, which i dont lol

im making a simple membership database, nothing complex just simple details for renewals,

quite simply id like a button on a form that when pressed adds one year to a date field for that particular member, i know i could just overtype the date by one year but id like to make it nicer if i can

any help is much appreciated

 

[Minty]

The DateAdd function is your friend for this. On your buttons Click event add the following;

Me.YourNextYearControl = DateAdd("yyyy",1,Me.YourCurrentYearControl)

More options can be found here: https://www.techonthenet.com/access/functions/date/dateadd.php

 

[premint]

hello thanks for that, but im not sure where to add that, also how do i tell it which date to modify as ive got two a joined date and a renewal date, im using access 2007

 

[Minty]

Assuming you have already added the button to your form then right click it and select properties. Under the Event tab, there is a OnClick property. Press the ellipses (...) and the VBA code window will open.

Change the code I supplied - the red bit to your Renewal Date , and the blue bit to your Joined Date assuming I've got them around the right way.

Me.[COLOR="Red"]YourNextYearControl [/COLOR]= DateAdd("yyyy",1,Me.[COLOR="RoyalBlue"]YourCurrentYearControl[/COLOR])

If your field names or controls have spaces in them you may need [ ] around the name.

 

[Uncle Gizmo]

There is an alternative to "DateAdd" which I recently demonstrated in this YouTube video here by adding 10 hours to a date/time value.

MS Access - Add Time to a Date

 

[premint]

hi minty now i understand where to put it thanks, but im not sure ive explained what i want properly, the join date is whatever date they joined and can be any one of the 365 days in the year and once entered does not ever need to change, the renewal date is same as join date but plus one year with yearly aniversary, the only thing i want to change is the renewal year, by adding one year onto it every time you press the button, nothing else

 

[Minty]

Okay then you need to simply update the current renewal year from the existing one then ? If so then a slight change of code;

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

 

[premint]

hi my renewal date field is called Renewal date, ive tried putting brackets round like this [Renewal date] but it didnt do anything

 

[Minty]

Please post up your code as it is now, and check what the name of the control is - It may not be the same as the field name.
If you get an error, what is it?

 

[premint]

this is the code

Private Sub renew_Click()
Me.[Renewal date] = DateAdd("yyyy", 1, Me.[Renewal date])
End Sub

the field i want to add the year to is called "Renewal date"

 

[isladogs]

try adding the line shown below

Private Sub renew_Click()
Me.[Renewal date] = DateAdd("yyyy", 1, Me.[Renewal date])
Me.[Renewal date].Requery
[B][/B]End Sub

 

[premint]

sorry still not working

 

[isladogs]

OK - no error but nothing happens

It may be that your code is disconnected from the renew button
Go into design view, click the button and look in the property sheet

It should say Event Procedure for the On Click event
If not, click the ellipsis button (...) on the right, select Event Procedure & check it opens the procedure shown in this post

If that doesn't solve it, suggest you upload your db removing anything irrelevant to this specific question

 

[premint]

how do i upload the database

 

[isladogs]

As you have less than 10 posts, you need to zip it first

 

[premint]

ok i can zip it but how and where do i upload it

 

[isladogs]

Sorry I overlooked this and nobody else replied.
When you reply, click the Go Advanced button then look below the post & click Manage Attachments.
Any issues see the FAQ section at the top of the screen

 

[premint]

hello heres the database im trying to get the button to work on, all i need to do is every time you click the renew button it adds one year to the renewal date to its left for this record only

 

[Minty]

This was a classic case of why you shouldn't use spaces in field names.

See the attached - I simply renamed the text box to something more sensible and put back in the originally suggest code.

Before you get too far into your design, get a naming convention together and remove all the spaces and any special characters form all your names - tables fields, forms etc.

 

[premint]

hi thanks for taking a look, did you make any changes, did you make the button work because when i download it it still doesn't do anything for me