change default values

[slimjen1]

All, using access 2010. I inherited a database that I need to copy and get it ready for next year’s data. There are several places in the various tables where the default value on a field is 2013. I need to change to 2014. Does anyone know of vba code or macro that I can do this without opening all the tables to change the values? Thanks.

 

[pr2-eugin]

Something along the lines of..

CurrentDb.TableDefs("yourTableName").Fields("yourFieldName").DefaultValue = 2014

AIR CODED, not tested..

 

[ypma]

I am assuming the current year field is a number field and as you don't wish to update the default by going into the tables ,
One way to achieve via code would be as follows:

Private Sub Form_BeforeInsert(Cancel As Integer)

'date when a new record is created

Me.current_year.DefaultValue = "2014"
End Sub

I have used current year as a field name you can have more fields as required

Regards

 

[slimjen1]

Your assumption is correct. This sounds like i would have to run this code on a form from a button but the recordsource would have to be the table give the "me." I have several tables with this default for the field that is 2013. I get the concept but not quite sure how this would this work?

I am assuming the current year field is a number field and as you don't wish to update the default by going into the tables ,
One way to achieve via code would be as follows:

Private Sub Form_BeforeInsert(Cancel As Integer)

'date when a new record is created

Me.current_year.DefaultValue = "2014"
End Sub

I have used current year as a field name you can have more fields as required

Regards

 

[Pat Hartman]

The best solution would be to fix the problem rather than add another band aid. The year should not be hard-coded anywhere. I also don't like using Access like a spreadsheet which is what you are doing by copying the database and starting fresh each year but that is a bigger problem to solve.

A better solution to hard-coding is to use the Year() and Date() functions and add 1 to get "next" year since I assume you would always do this prior to the start of the new year.

NewYear = Year(Date()) + 1

 

[slimjen1]

I absolutly agree. But as I stated; I inherited the db and until I can get the time to make the needed modifications; this is what I need to do. Also, the field with the defaults are number fields not date. I do not want to change this until I know I would not break anything throughout the db. Would I be able to still use you code without changing the datatype?
Thanks

 

[Pat Hartman]

In the example I gave, NewYear isn't a date. It can be defined as text or numeric. Date() is a function that returns the current date. Year() is a function that extracts the year part of a date. So if you take the year from the current date and add 1 to it, you get a number and if we do this today, that number will be 2014.

 

[slimjen1]

I'll try this. Thanks

 

[slimjen1]

This works for the future when I do make the modifications; but for now I need to hard code this and would like not to have to open each table and change to the next year. I used CurrentDb.TableDefs("yourTableName").Fields("yourFieldName").DefaultValue = 2014 in a public function to run it for all the tables i need to change. Thanks for all your help.

 

[ypma]

slimjen1 'Combining the advise already given why not create a form with one command button
,and with one click all your year fields default values will be updated

Private Sub Command0_Click()

CurrentDb.TableDefs("test").Fields("currentYear").DefaultValue = Year(Date) + 1
CurrentDb.TableDefs("test1").Fields("currentYear1").DefaultValue = Year(Date) + 1
CurrentDb.TableDefs("test2").Fields("currentYear2").DefaultValue = Year(Date) + 1
End Sub

'I Have used table test to test 2 and named the fields current year,you can add to the list to include all your tables

Hope this is an option

 

[Mihail]

You say that you don't wish to open all tables in order to reset the default value for the year.
I advice you to DO THAT.

Any solution that you will find now (and the previous posters teach you more than one) is OK for the new year (2014).
Next year (2015) will come with the same question, isn't it ?

So, open the tables and, set the Default Value property to Year(Now()).
This should solve for ever your problem.

Good luck !