Solved

Auto Generate a Number with prefix

Posted on 2015-02-20
11
421 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 66

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 50

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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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 50

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
 

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 50

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 48

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

726 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