Auto Generate a Number with prefix

Posted on 2015-02-20
Last Modified: 2016-02-10
I have an Access 2013 front end that writes to a SQL table and one of the columns contains a unique RA number I want when the user clicks the summit button to have some VBA code generate a sequential number with a prefix of RAI_ for example RAI_000001 the the next would be RAI_000002 and so on for each submission, is that possible?
Question by:skull52
LVL 65

Expert Comment

by:Jim Horn
ID: 40622282
Would you want this unique RA number to be stored in the SQL record, so it can be accessed again?
Please tell us the logic for creating these RAI_ numbers.

Offhand, if your SQL Server table had an identity field (same as Access AutoNumber), then the text 'RAI_' plus that number can be used.

Author Comment

ID: 40622427
Jim, yes the the number will be stored in the RA column and I can not make that column an identity field as another application uses that same table. I could use a hidden testbox to hold the RA number the VBA code would generate until the submit button is clicked to save the record.
LVL 49

Expert Comment

by:Gustav Brock
ID: 40622781
You can use this expression:

  =Left([Number], 4) & Right("00000" & CStr(Val(Right([Number], 6)) + 1), 6)

But I too would strongly suggest to use the autonumber and just format it letting SQL Server do the job:

  ="RAI_" & Right("00000" & CStr([Id]), 6)


Author Comment

ID: 40622971
I can't change the SQL table RA number column to an identity field because this table is also used by a C# application that generates the RA number from the C# code and then passes it to the RA number column so if I change it to an identity field it would cause issues for that app. I was thinking that maybe another option may be to save my data in a separate table and when the submit button is clicked  have it insert the results into the SQL table where the RA number is not in the table.
LVL 49

Expert Comment

by:Gustav Brock
ID: 40622987
OK, many ways to solve this.

The main issue is concurrency - could it happen that the C# application and your Access application within the second both would try to create a new record with a new RAI-number.

If not, you simply generate the RAI-number in Access (like shown above).
If yes, you will have to perform some kind of verification or reservation of the next number.

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)


Author Comment

ID: 40622995
The access RA number will have a different format so they won't try to generate the same number.

Author Comment

ID: 40623008
<If not, you simply generate the RAI-number in Access (like shown above). >  where would I put this   ="RAI_" & Right("00000" & CStr([Id]), 6)
LVL 49

Accepted Solution

Gustav Brock earned 250 total points
ID: 40623041
You could use the BeforeInsert event of the record:

    Dim strRAI As String

    ' Look up the highest RAI-number of your format.
    strRAI = DMax("[Number]", "tblYourTable", "[Number] Like 'RAI_??????'")
    ' Construct the next number.
    strRAI = "RAI_" & Right("00000" & CStr(Val(Right(strRAI, 6)) + 1), 6)
    ' Apply the RAI-number to be inserted.
    Me![Number].Value = strRAI

LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 250 total points
ID: 40624234
You might want to take a look at my response to a similar question.

This solution involves creating another table and storing the NextVal in that table.  In your case, you would want to do something like:

Me.RAI_Number = "RAI_" & Format(fnNextVal("SomeTable", "RAI"), "000000")

Author Comment

ID: 40625802
Well, after re-thinking this I decided to create a locale table and populate that table with the data I need, then insert into the SQL table. Doing it this way I could make the Repair number  field an autonumber datatype and set the format to "RAI_"000000 and that gave me the sequence I needed. Thanks to everyone for your suggestions which I will file away for future use.

Author Comment

ID: 40626418
I know I should select my own solution as the accepted solution but  Dale and Gustav offered viable solutions, and for additional reasons I decided on the local table solution, therefore I am splitting the points.

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel 2016 Not Responding Issues 6 27
Excel copy picture into Outlook email 7 44
In search of x17-22375.exe 2 12
linked subforms are yielding error:  ... (800110108) 3 14
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
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 start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

912 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

20 Experts available now in Live!

Get 1:1 Help Now