Numbering Help

V

Venus as a Boy

Hello Techies...

I KNOW AUTONUMBER DOESN'T WORK for sequentially ordered records.

So WTF does?

I have been given codes by some of you, advised against using them by others, etc.

I NEED SEQUENTIALLY ORDERED RECORDS PLEASE.

Why doesn't Access incorporate a feature for sequentially ordered data? I'd imagine a lot of people could use it.

Sorry for my blunt message but this is like my 10th attempt in the past two weeks.

Table specs:
Name: "Employee Main"
Form Name: "Employee Data Entry"
Primary Index: "New ID" (autonumbered)
Other fields: "SSN","DOB","First","Last","Notes"

Love,
Jason
 
M

Mike Painter

Venus said:
Hello Techies...

I KNOW AUTONUMBER DOESN'T WORK for sequentially ordered records.

So WTF does?
Click to expand...

Autonumber is not intended for that use but works for many.
I have been given codes by some of you, advised against using them by
others, etc.

I NEED SEQUENTIALLY ORDERED RECORDS PLEASE.
Click to expand...

If this is a single user application than a date field using Now() will
work.
Why doesn't Access incorporate a feature for sequentially ordered
data? I'd imagine a lot of people could use it.
Click to expand...

Access is a database and the order of records is frequently determined by
other things such as names or other Items of interest.
I've yet to find an application where such a thing was needed. It might be
there "because that's the way we did it on paper" but for no other reason.

(It took me about five years to get my community swervice district to
automate. I finally did it about six years ago but they insisted on
maintaining the old card system and carbon(less) copies of receipts. Last
month they got audited and were told to get rid of them. I got to say "I
told you so.")


Sorry for my blunt message but this is like my 10th attempt in the
past two weeks.

Table specs:
Name: "Employee Main"
Form Name: "Employee Data Entry"
Primary Index: "New ID" (autonumbered)
Other fields: "SSN","DOB","First","Last","Notes"
Click to expand...

In this case the date of hire would give a sequence and if you needed 1, 2,
3 etc this could be generated in reports. The SSN is a unique identifier.

If you want something that will let you know the hiring sequence than a
HireDate is safer since the 12th employee might not be entered until after
employee 15 is entered.
 
U

user

This function will create a new record with an ID field that is the lowest
available value. I'm sure there's an easier way, but this seems to work.

Private Sub cmdNewRecord_Click()
On Error GoTo Err_cmdNewRecord_Click

Dim rs As DAO.Recordset
Dim newID As Long
Dim sql As String

Set rs = CurrentDb().OpenRecordset("select * from tblSample order by
ID;")

rs.MoveFirst

' check if first ID is not 1 or table is empty, if so then 1 is available

If rs.EOF = True Then
newID = 1
ElseIf rs.Fields("ID").Value <> 1 Then
newID = 1
Else

' loop through records and look for a gap

Do While (Not rs.EOF)
newID = rs.Fields("ID").Value
rs.MoveNext
If rs.EOF Then Exit Do 'reached the end, can use next value
If rs.Fields("ID").Value > newID + 1 Then Exit Do 'found a gap,
can use next value
Loop
newID = newID + 1
End If

' create new record, set ID.

sql = "insert into tblSample (ID) values (" & newID & ");"
DoCmd.RunSQL sql

' requery the form, then navigate to the new record

Forms!frmDataEntry.Requery
Set rs = Me.RecordsetClone
rs.MoveFirst
Do
If rs.Fields("ID").Value = newID Then Exit Do
rs.MoveNext
Loop While Not rs.EOF

Me.Bookmark = rs.Bookmark

Exit_cmdNewRecord_Click:

Exit Sub

Err_cmdNewRecord_Click:
MsgBox Err.Description
Resume Exit_cmdNewRecord_Click

End Sub




Venus as a Boy said:
Hello Techies...

I KNOW AUTONUMBER DOESN'T WORK for sequentially ordered records.

So WTF does?

I have been given codes by some of you, advised against using them by others, etc.

I NEED SEQUENTIALLY ORDERED RECORDS PLEASE.

Why doesn't Access incorporate a feature for sequentially ordered data?
Click to expand...
I'd imagine a lot of people could use it.
 
U

user

This function will create a new record with an ID field that is the lowest
available value. I'm sure there's an easier way, but this seems to work.

Doug

-----------------------------

Private Sub cmdNewRecord_Click()
On Error GoTo Err_cmdNewRecord_Click

Dim rs As DAO.Recordset
Dim newID As Long
Dim sql As String

Set rs = CurrentDb().OpenRecordset("select * from tblSample order by
ID;")

rs.MoveFirst

' check if first ID is not 1 or table is empty, if so then 1 is available

If rs.EOF = True Then
newID = 1
ElseIf rs.Fields("ID").Value <> 1 Then
newID = 1
Else

' loop through records and look for a gap

Do While (Not rs.EOF)
newID = rs.Fields("ID").Value
rs.MoveNext
If rs.EOF Then Exit Do 'reached the end, can use next value
If rs.Fields("ID").Value > newID + 1 Then Exit Do 'found a gap,
can use next value
Loop
newID = newID + 1
End If

' create new record, set ID.

sql = "insert into tblSample (ID) values (" & newID & ");"
DoCmd.RunSQL sql

' requery the form, then navigate to the new record

Forms!frmDataEntry.Requery
Set rs = Me.RecordsetClone
rs.MoveFirst
Do
If rs.Fields("ID").Value = newID Then Exit Do
rs.MoveNext
Loop While Not rs.EOF

Me.Bookmark = rs.Bookmark

Exit_cmdNewRecord_Click:

Exit Sub

Err_cmdNewRecord_Click:
MsgBox Err.Description
Resume Exit_cmdNewRecord_Click

End Sub

---------------------------------------------


Venus as a Boy said:
Hello Techies...

I KNOW AUTONUMBER DOESN'T WORK for sequentially ordered records.

So WTF does?

I have been given codes by some of you, advised against using them by others, etc.

I NEED SEQUENTIALLY ORDERED RECORDS PLEASE.

Why doesn't Access incorporate a feature for sequentially ordered data?
Click to expand...
I'd imagine a lot of people could use it.
 
V

Venus as a Boy

Doug/whoever,

I posted the code straight to VB but the first new record came up as 0, so
thinking that was the first blank I tried continuing on and the next record
came up as 0 too. I didn't edit anything in your code (which looks beautiful
thanks for your effort).

What kind of blanks is the code looking for? While I was trying to maintain
sequential order, I was doing other things like erasing duplicates. So up
until around 4500 when I finished ironing out my SSN Dupe preventing
code(which causes the autonumber to 'tick' I had to copy paste to a new table
in order to preserve autonumber's 'sequentiality', so I've got hundreds of
records in which there is the 'New ID' entry but no other info.

So what should I do?
Query for blank records?

The big WHY...
Every 10 records go into a folder (3451-3460, 3461-3470, etc.)
So if I have 9 duplicates out of the next 10 records, that leaves 1 record
for the folder, which is like having 4 pieces of paper stacked around the 1
necessary piece of paper, which is like taking up 5x the space required,
which could eventually require 5x extra floor space with filing cabinets.
Granted that's the fullest extent of extreme, but maximizing our capacity is
part of my job.

Jbones
 
V

Venus as a Boy

Hey everyone,

Guess what I did?

I got my SSN Dupe Prevention code to stop tripping autonumber!
By replacing me.undo
with
me!ssn.undo

But thanks again for your help.
 
U

user

Sorry, it doesn't work properly until there are already some records
(doesn't work if the table is empty). After the first record is added it
should work OK?

I missed the bit about the duplicates, you mean you cleared the data out
of some records? Are you wanting to reuse those cleared out records by
moving data from the "higher up" records?

Doug
 
Top