Autonumber using the date 1

[Guest_imported]

I wish to make an autonumber using using the current date and a sequence number.&nbsp;&nbsp;For instance the autonumber for the first record inputted on August 3, 2000 would be 000803-01 the second record would be 000803-02 (year, month, day - sequence number).&nbsp;&nbsp;Does anyone have anysuggestions.&nbsp;&nbsp;Thanks for the help in advance.<br>Dave<br><A HREF="

http://www.supplychaintoday.com"

TARGET="_new">

http://www.supplychaintoday.com</A>

 

[Akrogh]

Here is one possible solution (haven't tested it, but give it a go):<br><br>Add 2 columns to the table where you ID is, 1 for the date and 1 for the number.&nbsp;&nbsp;To generate the next number you need to get the Maximum of the number for the date in question, then add 1 to the number.<br><br>In the BeforeUpdate event for the form put something like this (not too sure about the format function from memory):<br><br>dim rs as recordset<br>dim db as database<br><br>set db = currentdb<br>set rs = db.openrecordset(&quot;SELECT MAX(TheValue) FROM MyTable WHERE date = #&quot; & me.DateField & &quot;#&quot<br><font color=#00ff00>'On 1 line. Not sure if the #'s are necessary</font><br><br>IF rs.eof THEN <font color=#00ff00>'No records were found</font><br>Me.ID = format(now,&quot;dd-mm-yyyy&quot & &quot;-1&quot;<br>ELSE<br>Me.ID = rs.fields(&quot;TheDate&quot & &quot;-&quot; & rs.fields(&quot;TheValue&quot<br>END IF

 

[Guest_imported]

Thanks for the help.&nbsp;&nbsp;I will see what I can do with it.&nbsp;&nbsp;Very new at the programming aspect of Access.&nbsp;&nbsp;<br>dave<br><A HREF="

http://www.supplychaintoday.com"

TARGET="_new">

http://www.supplychaintoday.com</A>

 

[kathryn]

A couple of suggestions.&nbsp;&nbsp;Unless you are absolutely, positively, bet your life sure that you will never input more than 99 records, you should probably format your ID to be YYMMDD-XXX.&nbsp;&nbsp;Of course, this only works if you are absolutely, positively, bet your life sure that you will never input more than 999 records a day!<br><br>Everything following assumes that all data entry is done thru forms; you can't do this in a table.<br><br>How do your users finish entering a record; do they press an Apply button or a Next Record button?<br><br>You could put code in such a button to create the ID field.<br><br>Something like this<br><br><br>***Begin Code snippet***<br><br>&nbsp;&nbsp;&nbsp;&nbsp;Dim rst As Recordset<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim strSQL As String<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;strSQL = &quot;SELECT Max(Right([id],3)) AS Max FROM table3;&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Set rst = CurrentDb.OpenRecordset(strSQL)<br>&nbsp;&nbsp;&nbsp;&nbsp;Me!ID = Format(Date, &quot;yymmdd&quot & &quot;-&quot; & Format(rst!max + 1, &quot;000&quot<br><br>***End Code Snippet***<br><br>Be careful where you put this code, though.&nbsp;&nbsp;You don't want this code to run when anyone just edits a record, because it will overwrite the ID.<br><br>Hmmm.&nbsp;&nbsp;We could add an If...End if, to check if ID was null, and only run code then.<br><br>Let me know if this helps and if I can be of more help. <p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br>

 

[iccpm]

how about the code to simply look up a number value and add one to it?