Auto Number with data entry

[Chhunhour]

1).I have a problem with AutoNumber on my database with ID
field, example if I have ID like: E001,E002,E003,E004,E005
then if I delete a record with ID E003 then the data store
in my table will become as follow: E001,E002,E004,E005 and
when a form loard AutoNumber will start ID from E006 so
how should I do to make it start among the missing ID,E003?

Below is my coding of AutoNumber:

Function AutoID() As String
Dim id%
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("select ID from
tblemployee order by ID")
If rst.RecordCount > 0 Then
rst.MoveLast
id = Right(rst(0), Len(rst(0)) - 1)
id = id + 1
AutoID = "E" & Format(id, "000")
Else
AutoID = "E001"
End If
rst.Close
End Function

Please help correct the above code to my requirement as
possible.

2).Please help show code printing a document or report on
any kind of printer?


Thanks,
Chhunhour.

 

[Peter Row]

Hi,

The short answer is you can't.
If it were a SQL Server backend you could do it with a bit of
hassle and triggers but the question is why would you?

Its called an autonumber field for a reason in that it's automatic.
If it were to check for gaps and use the lowest value each time,
i.e. in your example insert next with E003 then just think about
how much checking it would have to do everytime you did
an insert, not to mention the problems if several users tried to
insert at the same time.

The code extract you have looks like asking for misery.

Regards,
Peter

 

[Rick Brandt]

"Hi"

You damn techies! I have been trying myself to maintain a sequentially

numbered table and the only answer I have gotten OVER AND OVER is "You
shouldn't use Autonumber." Okay. How about something more contsructive?

You would start with what you have but instead of moving to the last (max)
ID you would have to start at the first (min) ID and walk through ALL the
records in a loop until you encountered a GAP in the sequence which you
would then use as the next ID. You can surely see how inefficient this
would be as your table gets larger and larger.

 

[Guest]

Rick,

Not to contradict you, but couldn't Access just scan for each new record entered? The delay (speed of searches is my comparison) is nothing compared to the time it takes me to ready my next record. (And not to mention doesn't the computer return 158million hits from Google in .1 seconds?)

So you press [tab] at the end of each record and Access scans all records (or only the id number field) and assigns the lowest available id#.

This process would eventually eliminate itself as you filled in all the blanks, to be replaced by a code to the effect of maxID+1.

Thank you very much for trying sir.

 

[Douglas J. Steele]

That's exactly what Rick suggested. However, you have to write code to do
it: Access won't do it automatically for you.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Rick,

Not to contradict you, but couldn't Access just scan for each new record

entered? The delay (speed of searches is my comparison) is nothing compared
to the time it takes me to ready my next record. (And not to mention
doesn't the computer return 158million hits from Google in .1 seconds?)

So you press [tab] at the end of each record and Access scans all records

(or only the id number field) and assigns the lowest available id#.

This process would eventually eliminate itself as you filled in all the

blanks, to be replaced by a code to the effect of maxID+1.

 

[user]

Instead of going to the last record, trimming the number off, adding one,
and continuing, you'd have to loop through the sorted recordset checking if
the current ID is one less than the next ID. If not then use the ID plus
one, otherwise carry on looping. If you get to the end of the recordset,
there are no gaps so just use the last ID plus one.

Doug