Multiple Auto Number

[Muaz]

Hi All;

I have a table having the following fields:

StaffNo TextField PrimaryKey
Name
....
...

The data in StaffNo will be alpha numeric, like AKA-111, AKA-112, LMN-100, LMN-102

Here AKA and LMN describes the Sites where employee is working.

On Add New Employee, When user enter AKA- in StaffNo, on exit the next number on that site should be generated. i.e AKA-113

Any suggestion Please


Muaz

 

[Mihail]

Here is a discussion about this subject.
Take a look, maybe is what you are looking for.
http://www.access-programmers.co.uk/forums/showthread.php?t=258517

 

[Muaz]

Dear Mihail;

I have checked the details in the link you mentioned but my case is slightly different. In my case the staff number start with alphabets then 4 numerics. I have to check the next counter of the last four numeric.

If the first alphabet is AKA- then pick the next counter of the AKA- .... for example the last Staff Number is AKA-1006, the next number should be like AKA-1007

If the first alphabet is LMN- then pick the next counter of the LMN- .... for example the last number is LMN-2014, th next number should be like AKA-2015

Thanking you in advance.

Regards
Muaz

 

[JHB]

Try the below code, remember to change the names to yours, (Me.StaffNo is a control on a form where the site code like "AKA-" is typed in)

  Dim dbs As Database, rst As Recordset, Response
  
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset("SELECT Max(Right([Badge_Num],Len([Badge_Num])-" & Len(Me.StaffNo) & ")) AS MaxNo " _
  & "FROM Main_Tab " _
  & "HAVING InStr(1,[Badge_Num],'" & Me.StaffNo & "')=1")
  If Not IsNull(rst![Maxno]) Then
    Me.StaffNo = Me.StaffNo & rst![Maxno] + 1
  Else
    Me.StaffNo = Me.StaffNo & "0001"
  End If

 

[Mihail]

I think that you made a mistake here:

for example the last number is LMN-2014, th next number should be like AKA-2015

Should be simple to create a query that split your "numbers"
Alpha: Left([CodeField],3)
Nums:Mid([CodeField,5)

After this, the DMax should give you the last number used:
LastNumberUsed = DMax("[Nums]","QueryName","[Alpha] = " & varAlpha)
varAlpha is a variable, string, that should store "LMN" or "AKA" etc

Now, create the new code:
varNewCode = varAlpha & "-" & LastNumberUsed +1

I see a problem when the LastNumberUsed = 9999.
What should happen in this case ?

 

[jdraw]

Use atomic fields for storage (1 fact 1 field). You can concatenate and "glam any concoction of fields " for display. Too often we see people, most often relative newbies, trying to over complicate a simple numbering/identification scheme. Codes with too much embedded significance are more trouble than worth.

 

[Galaxiom]

The data in StaffNo will be alpha numeric, like AKA-111, AKA-112, LMN-100, LMN-102

Here AKA and LMN describes the Sites where employee is working.

And if the employee moves to another site????

Your StaffNo field is utterly unsuitable as a Primary Key for that reason alone.

 

[Muaz]

Issue Resolve. Thank you JHB. This is what I want. The code is working exactly what I want. Thank you again.

Thank you all for providing the solution of my problem, it was not possible by me without your support.

Regards
Muaz

 

[JHB]

You're welcome, luck with your project.