skull52
asked on
Auto Generate a Number with prefix
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?
ASKER
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.
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
=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
ASKER
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.
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.
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
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
ASKER
The access RA number will have a different format so they won't try to generate the same number.
ASKER
<If not, you simply generate the RAI-number in Access (like shown above). > where would I put this ="RAI_" & Right("00000" & CStr([Id]), 6)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
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.
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.