!No Duplicates!

[MyWaterloo]

I have a database for keeping track of samples that come in. I need the Sample ID's to not allow duplicates, but...I don't want to use an auto number. The auto number will not allow me to use a number again even if it is deleted. I need my numbers to be sequential with no duplicates. If I delete a record I want to be able to fill it in with a diff sample. This can't be that complicated...w hat am I missing? Any help?

Thanks

[ChipR]

Edit the table properties and set the Sample ID field to Indexed(No Duplicates).

[OldBirdman]

Solution 1) Build your table with SampleID as number (not AutoNumber) and make it the Primary Key. You will have to keep track of existing but unused keys.
Solution 2) SampleID as AutoNumber, but have a Deleted field and mark it as True if the sample is no longer valid. You can then overwrite with new sample.
Solution 3) Have an AutoNumber field, and just ignore it. Doesn't have to be the first field. Name it something like "ToKeepAccessHa ppy". If the fact that it is close to, but not exactly, like your SampleID, make it random, not sequential. Then it is easy to ignore.

OldBirdman

[MyWaterloo]

Thanks all. I'll give some of these a go.

[MyWaterloo]

OK, so I need a little more help. I have changed my ID field from an auto number to a regular number field and have indexed it to prevent duplicates. Works great. But....I still want to keep the auto number feature where the next number in the sequence is automatically entered in the ID field. I don't want to have to look up what the last number used was. I don't know how to reuse deleted auto number record numbers, and I don't know how to make a regular number that is indexed to prevent duplicates automatically insert the next number in the sequence???? Any ideas??? Thanks.

[OldBirdman]

I think you've painted yourself into a corner. If you don't want Access' autosequencing, and you don't want to do it yourself, I don't think it is going to be done.
Your first decision must be a)use Access autonumber or b)do it yourself.
Either can be made to work. Without knowing why you want to have everything sequential with no gaps, I think you are on your own.

[MyWaterloo]

I work at a lab. The lab takes in samples on a regular basis. I have a database that we keep track of the samples with. The "Sample ID" field was an auto number. An auto number worked best because the same person was not always entering samples and didn't know what number sample was last entered. An auto number inserted the next number without the employee having to look up what the last sample ID entered was. The problem with the auto number is if someone deletes a sample record, that number is gone forever. I can't assign it to another sample. At the end of the month/quarter/year the auto number sample ID numbers may indicate 500 samples, but in reality some of those samples may have been deleted/mistakes and we only have 492 samples. Is there now way to prevent duplicates in a record field and have that field insert the next number in a sequence without the field being "locked" as it is with an auto number? Or, is there anyway to "revive" a deleted auto number?

[DonRayner]

You could use a function something like this to get the next available number from your database. Just change the TableName and IDField with whatever the names you are using.

Public Function NumGen() As Integer
Dim db As DAO.Database, rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("Select [TableName].[IDField] From Table4 Order by [IDField]")
rs.MoveFirst
NumGen = rs![IDField]
Do While Not rs.EOF
    rs.MoveNext
    If rs.EOF Then
        NumGen = NumGen + 1
        Exit Function
    Else
        If NumGen + 1 = rs![IDField] Then
            NumGen = rs![IDField]
        Else
            NumGen = NumGen + 1
            Exit Function
        End If
    End If
Loop
End Function

Then place a button on your form with the following code for the click event

Private Sub btnGetIDNum_Click()
txtIDNumber = NumGen()
End Sub

[OldBirdman]

There is no way to revive a deleted autonumber key. Again, if not autonumber, you are going to have to determine the next key for any new record. You are going to have to keep track of numbers made available from error entries. There are many ways to do this, and the choice might depend on the number of samples expected in the database.
You are going to have problems anyway. A record "removed" just before the end of a period (year/month/quarter/whatever) will be in your count if not overwritten immediately with a new sample.
From an audit standpoint, you are creating problems.
The COUNT function in SQL will give you an accurate count of records, whether keys are missing or not, if that is your objection. If you had an "Unused" field in your record, a query using the MIN function could find the lowest key so marked. Likewise, the MAX function against all the records would give the highest key used.

[FishVal]

Hello gentlemen.

As an addition to Don's suggestion I would like to propose SQL approach to find "numeration gaps".

SELECT t1.IDField

FROM tblTableName AS t1 LEFT JOIN tblTableName AS t2
  ON t1.IDField=(t2.IDField-1)

WHERE t2.IDField Is Null

ORDER BY t1.IDField;

This will return dataset where each record [IDField] value has no sequential number in table [tblTableName].

Regards,
Fish

[OldBirdman]

FishVal-Nice SQL, but I can't say I understand it, but it works. Whether there is a recordset with the missing values or one with the present values, code needs to open this recordset and look at it. The missing values recordset might be empty, and then the samples table must be opened for the highest key plus one.
DonRayner's post #8 finds the gaps, or returns the next number. The other issue, of end-of-period reporting, will still have to count the actual rows in the table, as at any time there can be gaps. Autonumber just guarantees that once a gap, always a gap.
I don't like to suggest code until the design is resolved, as it is often presented untested, and is therefore misleading. Although the SQL statement in post #10 by FishVal works, the code in post #8 by DonRayner does not; if the missing numbers are before the first table entry, the function may fail (will fail if no other gaps).
Finally, if records were simply marked as deleted, and then reused, the dataset would not grow as fast, and perhaps compact not needed.

[DonRayner]

Hey OldBirdman. The Function that I posted does work, I made sure of that before I posted it. The function will work because the SQL statement for the recordset opens it ordered on the field that we are checking for gaps in. The loop will step through the recordset until it either encounters a gap or eof and then calculates the next value accordingly.

Although I do rather like FishVals solution.

[OldBirdman]

I didn't actually run the code. I assume IDField has the following values: 3;4;5;7;9; in order. Then:

Line Result
5    MoveFirst-IDField=3
6    NumGen = 3
8    MoveNext-IDField=4 
14   NumGen = 4
8    MoveNext-IDField=5 
14   NumGen = 5
8    MoveNext-IDField=7 
16   NumGen = 6
17   Exit Function

But first missing ID is 1, then 2. Am I missing something?

[DonRayner]

Ahhhhhh got ya. My bad, I assumed that the function would be running on a table that had the inital record intact. But if the very first record is missing then the function fails. It should be NumGen = 1 (or whatever the first number is supposed to be) on line 6.

But I still like FishVals SQL solution better.

[OldBirdman]

I wasn't trying to get in any contest over this. I don't understand FishVal's SQL, but maybe I'll study it some more. Unfortunately, if I forget, and open it in design view, Access looses the part that it can't put into the design grid. If I don't notice, and close it, it is gone!
I'm kind of a purest, so line 8 should be moved to the end of the loop, at line 19.5.