?
Solved

Auto Generate a Number with prefix

Posted on 2015-02-20
11
Medium Priority
?
533 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 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 49

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

850 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