Solved

How to Increment a number in Microsoft Access

Posted on 2014-01-02
8
273 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

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…
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…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

839 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