Solved

How to Increment a number in Microsoft Access

Posted on 2014-01-02
8
266 Views
Last Modified: 2014-01-23
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
0
Comment
Question by:frank_guess
  • 5
  • 3
8 Comments
 
LVL 6

Expert Comment

by:ButlerTechnology
ID: 39752196
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.
0
 

Author Comment

by:frank_guess
ID: 39752318
Where do I need to put this code at.
0
 
LVL 6

Expert Comment

by:ButlerTechnology
ID: 39752400
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?
0
 

Author Comment

by:frank_guess
ID: 39754591
Yes I would like to see what you are talking about and the code behind it.
Thank you
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 6

Accepted Solution

by:
ButlerTechnology earned 500 total points
ID: 39756340
Here's a rough copy of using VBA code to create a new record using the two form (Search and Edit) method.
Example.mdb
0
 

Author Comment

by:frank_guess
ID: 39763677
Thank you.  I will review and get back to you tomorrow.
0
 

Author Comment

by:frank_guess
ID: 39803124
It works but not the way I thought it would.  Thank you for the example.
0
 

Author Closing Comment

by:frank_guess
ID: 39803279
Not exactly what I was looking for but it puts me on the track.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Back in July, I blogged about how Microsoft's new server pricing model, combined with the end of the Small Business Server package, would result in significant cost increases for many small businesses (see SBS End of Life: Microsoft Punishes Small B…
The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now