Solved

How to Increment a number in Microsoft Access

Posted on 2014-01-02
8
276 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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
 
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

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Introduction: Sometimes when I receive a call from my users to solve their problems it is very difficult for me to found their computer IP address. Even finding their computer Host to provide remote support can be a problem.  So I resorted to Goo…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

623 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