Reference Numbers Haphazard in Reports

[Purpleswanage]

Help! Very Very new to VBA & totally out of my depth!!!!

I have an automatically generated reference field (text box) within the main data entry form of my database. AR/2008/### which is generated by the code below. The 'AR' is constant for every record, the '2008' is generated from the current year and the '###' is the next available number in the sequence.

Private Sub Form_Current()
If Me.NewRecord Then
On Error GoTo Err_Click 'It should never occur, just to be sure...
Me!ARN.Value = "AR/" + Format(Now(), "yyyy") + "/" + LTrim(Str(Nz(DMax("[ID]", "ActionRequests"), 0)) + 1)

End If
Exit Sub

This works but the queries return a haphazard order of reference numbers i.e. AR/2008/1, AR/2008/10, AR/2008/100, AR/2008/101 etc. It has been suggested that placing 'leading zeros' in front of the number i.e AR/2008/00001, AR/2008/00002 may resolve the issue but I don't know how to achieve this?

Would this solve the problem? How would I do this? or does anyone else have any suggestions?

Database has approximately 250 records at the moment, is it possible to renumber these in the new format automatically?

All assistance gratefully received.

Helen

 

[RuralGuy]

Try <<<AIR CODE>>>:
Me!ARN = "AR/" & Format(Now(), "yyyy") & "/" & _
Format(Nz(DMax("[ID]", "ActionRequests"), 0) + 1,"00000")

 

[Purpleswanage]

This works a treat. Thank you so much for your help

Helen

 

[RuralGuy]

Excellent! Glad I could help.