Auto Number

[Ev]

Is there anyway that I can use the text data type wth a set format ie /MM"000" and so that it will contine onto 001, 002, 0003 etc WITHOUT using the Autonumber data type

 

[DougP]

Yes,<br>You need a table to store a number, so each time the number increments one. I called it &quot;LastNumber&quot;<br>-----------------------------<br>'open table and get the last number used<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim MyDB As Database, MySet As Recordset<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim Temp1, Temp2 As Single, MyValue As String<br>&nbsp;&nbsp;&nbsp;&nbsp;Set MyDB = CurrentDb<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Set MySet = MyDB.OpenRecordset(&quot;LastNumber&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MySet.MoveFirst<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MySet.Edit<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Temp1 = Trim(Str(MySet.Fields(&quot;LastNumber&quot))<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MyValue = Format(Now, &quot;/MM&quot & String(3 - Len(Temp1), &quot;0&quot & Temp1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Temp2 = Val(Temp1) + 1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MySet.Fields(&quot;LastNumber&quot = Temp2<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MySet.Update<br>&nbsp;&nbsp;&nbsp;&nbsp;MySet.Close<br>&nbsp;&nbsp;&nbsp;&nbsp;MyDB.Close<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Text2 = MyValue<br>-------------------------------------<br><br>If you want Double quotes &quot;&quot; around it then change &quot;MyValue&quot; line to:<br>MyValue = Format(Now, &quot;/MM&quot & Chr$(34) & String(3 - Len(Temp1), &quot;0&quot & Temp1 & Chr$(34)<br><br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.