Solved

Auto Generate a Number with prefix

Posted on 2015-02-20
11
308 Views
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?
0
Comment
Question by:skull52
11 Comments
 
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.
0
 

Author Comment

by:skull52
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.
0
 
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)

/gustav
0
 

Author Comment

by:skull52
ID: 40622971
Gustav,
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.
0
 
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.

/gustav
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

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

Author Comment

by:skull52
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)
0
 
LVL 49

Accepted Solution

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

/gustav
0
 
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")
0
 

Author Comment

by:skull52
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.
0
 

Author Comment

by:skull52
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.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

707 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

17 Experts available now in Live!

Get 1:1 Help Now