Solved

Incremental Numbering for clients & Year

Posted on 2013-12-29
6
276 Views
Last Modified: 2013-12-29
I need to create an incremental numbering system based on a client ID and the year. Right now I have a numbering system that works well and increments based on the client that is logged in. It numbers the client records sequentially all starting from 0001. But when the year changes I need that numbering system to start back at 0001. Here's what I currently have that is working fine but now I need to somehow change it to start over when the new year starts

Dim i, a
 i = Me.cboClinicName
Me.txtCRecordID = [txtClinicCode] & Format(Date(),"yy") & Nz(DMax("CRecordID", "tblCogginsDetail", "ClinicName = " & i), 0) + 1

Open in new window


So here's how it looks now for each record

When the clinic name is NEHL or KOS for example:

NEHL130001
NEHL130002
KOS130001
KOS130002
KOS130003

So now what I need to do is restart the last 4 digits of the above sequence  when the year changes. For example:

NEHL130001
NEHL130002
NEHL140001

etc...

How can I rewrite my code to restart this when the year changes?
0
Comment
Question by:Lawrence Salvucci
  • 3
  • 3
6 Comments
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
ID: 39744927
Try this code:
Dim a As String, b As String
a = Me.[txtClinicCode] & Format(Date, "yy")
b = Nz(DMax("CRecordID", "tblCogginsDetail", "instr(1,CRecordID," & Chr(34) & a & Chr(34) & ") = 1"))
If b = "" Then
    b = a & "0001"
Else
    b = a & Format((Mid(b, Len(a) + 1) + 1), "0000")
End If
Me.txtCRecordID = b

Open in new window

0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 39744935
It's not incrementing the last 4 digits of the sequence. It's just putting the same values in the field "NEHL130001".
0
 
LVL 39

Expert Comment

by:als315
ID: 39744952
Can you upload sample DB with this table only and form?
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.

 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 39744958
That's gonna take a little time. Give me a few and I'll upload a copy of it.
0
 
LVL 39

Expert Comment

by:als315
ID: 39744961
Look at my sample
DBincrement.accdb
0
 
LVL 1

Author Closing Comment

by:Lawrence Salvucci
ID: 39744970
Got it working. I had the wrong field name in your code. Thank you for your help. I appreciate it!
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

776 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