How to Increment a number in Microsoft Access

I have a table with a field named TestNumber and I want to fill it with "20140001" and then as I add records I want to add the next record as "20140002" and so on.  I would like to have the 2014 be entered from  a year function but the 0001, 0002,0003 be entered as the next number in the list.
The 2014 could be a place I change in the code or if I need a table with parameters use that.
I have been trying several paths but so far nothing has worked like I wanted it to.  Has anyone got a good method that I could had to either the field or to an add button to make this increment.
Thank you fg
frank_guessAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ButlerTechnologyCommented:
I like to create VBA routine that handles the creation of new records.  The routine that would using would look something like this.

Public Function NewRecord () as intger
dim NewID as integer
NewID = dlookup ("Max(ID)", "Table") + 1
docmd.runsql "Insert into table (ID) values (" & newID & ")"
NewRecord = newID 

Open in new window


I have read several articles that really discourage using the MAX feature and they have some good arguments, but I like to still use it.
frank_guessAuthor Commented:
Where do I need to put this code at.
ButlerTechnologyCommented:
You would new to put the code in a VBA Module.  If you aren't familiar with VBA, then this approach may not be the best.  It could also be a good introduction as I find myself working more on the VBA side of access more and more.

I take a two form process when working with a table.  The first form is used to edit existing records.  The second form is use to located the record that you want to work with.  I call it a Search Form as it allows me to filter the records and find the one that I want to work with.  It also contains a new button that calls the function which creates a new record and then opens the first form with the new record.  Would you like to see sample database with the two form approach in action?
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

frank_guessAuthor Commented:
Yes I would like to see what you are talking about and the code behind it.
Thank you
ButlerTechnologyCommented:
Here's a rough copy of using VBA code to create a new record using the two form (Search and Edit) method.
Example.mdb

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
frank_guessAuthor Commented:
Thank you.  I will review and get back to you tomorrow.
frank_guessAuthor Commented:
It works but not the way I thought it would.  Thank you for the example.
frank_guessAuthor Commented:
Not exactly what I was looking for but it puts me on the track.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Server Apps

From novice to tech pro — start learning today.