Solved

How to Increment a number in Microsoft Access

Posted on 2014-01-02
8
274 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
WSUS approved update report 3 116
.exe to msi 7 98
System Center Endpoint Protection Antimalware client version 1 151
The DFS Replication service stopped 5 544
When you are trying to access the server, have you ever encountered "The terminal server has exceeded the maximum number of allowed connection" error?  or "The user is attempting to log on to a Terminal Server in Remote Administration mode, but the …
Microsoft has released remote PowerShell capabilities to all commercial Office 365 customers. So you can be controlled via PowerShell and not from the Office 365 admin center Download Windows PowerShell Module for Lync Online http://www.micros…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

749 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