Reference id format problem

[aman]

To generate Reference id for each record in format(First Letter,5 next digits)

Hi guys

I want to generate ref id for each record when they get inserted through access form.The ref id should start from A00001 to A99999,and then from B00001 to....And when it reaches at Z99999 then the counter again starts with A00001.
Eg starting with the premise that ref number starts at A00000, then the IF is if the number is greater than 99,999 then A becomes B and the counter resets to 00000.

Is it possible to generate ref id for each record in this format? Could anyone please figure it out?

Regards
AMan

 

[Peter Reid]

When you get to 99999, you'll have to find out the 'character code' of the letter, add 1 to it and then return the character of the incremented character code.

eg, Chr(Asc(Left(sRefID, 1)) + 1)

You will also have to test for the letter being Z

 

[aman]

Hi Peter

Can you please give me the whole vba code to do this. Like whenever I insert a new record ,it should insert the reference id field like A00001 ...

Many Thanks for your reply ..

AMan

 

[aman]

Hi Guys

I have tried a lot to figure out how to generate the reference id in the format(first letter and then next 5 numbers) e.g A00001 but coundln't solve it. Can someone please solve my problem as its very urgent to solve.

Thanks in advance for your assistance.

Aman

 

[JANR]

To generate your own sequence, create a function and call this when you need the next RecId in a relevant event. If you create a table to hold the seed number and current alfa character and open a recordset on this table, you can then validate and get the next RecID.


Here is a samplebase of a possible solution.

JR

 

[aman]

Hi JANR

Thanks for your reply.I have written the following function to generate ref id in the format I wanted but how can I call this function in the insert statement as written below after the function.

Function decode(ByVal mySequence As Long) As String
   Dim mySeq As Long
   Dim highNum As Integer
   Dim lowNum As Long
   
   mySeq = (mySequence - 1) Mod (99999 * 26)
   highNum = mySeq \ 99999
   lowNum = mySeq - highNum * 99999 + 1
   
   decode = Chr(65 + highNum) & Format(lowNum, "00000")
End Function
 
Private sub submit_click()
sSQL = "INSERT INTO tblmain(RDateMPU,Received_by,SendingDept,Refid)" & _
"VALUES (#" & Format(Date, "mm-dd-yy") & "#,'" & Text54.Value & "','" & Me.Combo2.Value & "',[COLOR=darkred].........(refid) [/COLOR][COLOR=black])" [/COLOR]
DoCmd.SetWarnings False
DoCmd.RunSQL SSQL
end sub

Could you please modify it so that it gives me the exact result.

Thanks
Aman

 

[JANR]

Try something like this:

Private sub submit_click()
[COLOR=red]Dim NewId as String[/COLOR]
[COLOR=red]Dim sSQL as String[/COLOR]
[COLOR=red][/COLOR] 
[COLOR=red]NewID = decode[/COLOR]
sSQL = "INSERT INTO tblmain(RDateMPU,Received_by,SendingDept,Refid)" & _
"VALUES (#" & Format(Date, "mm-dd-yy") & "#,'" & Text54.Value & "','" & Me.Combo2.Value & "',[COLOR=red]'"[/COLOR][COLOR=red] & NewID & "'[/COLOR]) )" 
DoCmd.SetWarnings False
DoCmd.RunSQL SSQL
end sub

JR

 

[aman]

Thanks All

My problem is resolved.
Cheers