[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Auto Generate a Number with prefix

Posted on 2015-02-20
11
Medium Priority
?
493 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 52

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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

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 52

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 52

Accepted Solution

by:
Gustav Brock earned 1000 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
Dale Fye earned 1000 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
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 …
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 …

656 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