How to calculate a sequential control on a form?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello.

I have a table with a Primary Key (UniqueID) of Autonumber type.
Here is the problem I'm having. Sometimes, I have to delete some records
from the table, and that creates a gap in the autonumber field.
There is a control on the form bound to this field, and this is also
included when viewing the records. My boss is not happy with the gaps in the
Autonumber.

Is there a way to have another numeric field that allows unique values, but
when a new record is added, the value of this field is set to the number of
records in the table? How would I do this?

For example, if I have 5 records in the table with UniqueIDs: 1, 2, 4, 7, 9;
then the corresponding numeric field should have the value 1, 2, 3, 4, 5 and
any new record added will have UniqueID = 10, and numeric field value
(calculated) = 6.

Thanks for any pointers.

-Amit
 
Hi Amit

The whole point of an AutoNumber field is that it is not intended to be
viewed by the user. It is simply a convenient way to generate a unique key
which can be used in a relation with another table.

If this does not meet your requirements, then it is certainly possible to
use as your primary key a number (long integer) that you generate yourself.

Given that you have a table named SeqTable with a primary key names SeqNum,
the following query will return the first unused number AFTER the lowest
number already in the table:

Select top 1 SeqNum+1 as NextNum from SeqTable as A where not exists
(select B.SeqNum from SeqTable as B where A.SeqNum+1=B.SeqNum);

Note that if the lowest number present is not 1, then it will not return 1.

For example, if the first few records are: 4, 5, 6, 9, 10
then it will return 7, not 1, 2, or 3.

So you might first need to check if number 1 is there.
 
Hi Graham,

Thanks again for your prompt and thoughtful response.

Graham Mandeno said:
Hi Amit

The whole point of an AutoNumber field is that it is not intended to be
viewed by the user. It is simply a convenient way to generate a unique key
which can be used in a relation with another table.
Click to expand...

Yes. I'm realizing that it is causing some confusion among my users, and it
is best to not display it on the form.
If this does not meet your requirements, then it is certainly possible to
use as your primary key a number (long integer) that you generate yourself.
Click to expand...

I think I'll continue using an Autonumber PK, but just to number the record,
I'll add another numeric field whose value I calculate. Yes, I'm aware that
this will cause some duplication, but I feel comfortable having an Autonumber
PK.
Given that you have a table named SeqTable with a primary key names SeqNum,
the following query will return the first unused number AFTER the lowest
number already in the table:

Select top 1 SeqNum+1 as NextNum from SeqTable as A where not exists
(select B.SeqNum from SeqTable as B where A.SeqNum+1=B.SeqNum);

Note that if the lowest number present is not 1, then it will not return 1.
Click to expand...

Will this work in a multi-user environment though?
And, what are the advantages of using this query over using (DCount +1) to
assign value to this control?
For example, if the first few records are: 4, 5, 6, 9, 10
then it will return 7, not 1, 2, or 3.

So you might first need to check if number 1 is there.
Click to expand...

I'm not sure I understand this. If the SeqNum is being incremented by 1
right from the start, there shouldn't be any gaps. So, for example, if
currently, I have 5 records with SeqNum 1, 2, 3, 4, 5; and then do some
testing by adding 3 records (SeqNum = 6, 7, 8) and then delete these 3
records after testing, the next record added will/should have a SeqNum = 6.
Whereas in case of using an AutoNumber, it would be 9.

Thanks.

-Amit
 
Hi Amit
Thanks again for your prompt and thoughtful response.
Click to expand...

My pleasure :-)
I think I'll continue using an Autonumber PK, but just to number the
record,
I'll add another numeric field whose value I calculate. Yes, I'm aware
that
this will cause some duplication, but I feel comfortable having an
Autonumber
PK.
Click to expand...

I think there is nothing wrong with doing this, but some would advise that
you should use the "natural" key as your primary key if there is one
available.
Will this work in a multi-user environment though?
Click to expand...

Yes, unless two users are asking for the next number at exactly the same
time. If this happens, and they get the same number, the first to save the
record will be successful and the second will get a "duplicate index" error.
So, you need to have some logic in your code to handle such an error and
"retry" to get the next available number.
And, what are the advantages of using this query over using (DCount +1) to
assign value to this control?
Click to expand...

You said you wanted to fill the gaps in your numbers that were left after
deleting records. The DMax+1 method (NOT DCount!) will give you a number
one greater than the highest already in the table, but it will not fill the
gaps.
I'm not sure I understand this. If the SeqNum is being incremented by 1
right from the start, there shouldn't be any gaps. So, for example, if
currently, I have 5 records with SeqNum 1, 2, 3, 4, 5; and then do some
testing by adding 3 records (SeqNum = 6, 7, 8) and then delete these 3
records after testing, the next record added will/should have a SeqNum =
6.
Whereas in case of using an AutoNumber, it would be 9.
Click to expand...

Ah! Do you mean that you would only delete records from the END of the
table. I assumed you were deleting them anywhere and therefore leaving gaps
in the numbers.

If you are only concerned with deleting records from the end of the table
after testing, the following function might be helpful. It works for both
local and linked tables, and resets the autonumber to (by default) the next
unused number.

Public Function ResetAutonumber( _
sTable As String, _
Optional sField As String, _
Optional lSeed As Long _
) As Long
'sTable = Table containing autonumber field
'sField = Name of the autonumber field
' (default is the first Autonumber field found in the table)
'lSeed = Long integer value you want to use for next AutoNumber
' (default is one more than the current maximum)
Dim cnn As Object 'ADODB.Connection
Dim cat As Object 'ADOX.Catalog
Dim col As Object 'ADOX.Column
Dim tbl As Object 'ADOX.Table
Dim sRemoteTable As String
On Error GoTo ProcErr
Set cat = CreateObject("ADOX.Catalog")
cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables(sTable)
If tbl.Type = "LINK" Then
Set cnn = CreateObject("ADODB.Connection")
cnn.Open _
CurrentProject.Connection.ConnectionString & ";" & _
"Data Source=" & _
tbl.Properties("Jet OLEDB:Link Datasource")
cat.ActiveConnection = cnn
sRemoteTable = tbl.Properties("Jet OLEDB:Remote Table Name")
Set tbl = cat.Tables(sRemoteTable)
End If
If Len(sField) = 0 Then
For Each col In tbl.Columns
If col.Properties("AutoIncrement") Then
sField = col.Name
Exit For
End If
Next
If Len(sField) = 0 Then GoTo ProcEnd
Else
Set col = tbl.Columns(sField)
End If
If lSeed = 0 Then
lSeed = Nz(DMax(sField, sTable), 0) + 1
End If
col.Properties("Seed") = lSeed
ResetAutonumber = lSeed
ProcEnd:
On Error Resume Next
If Not cnn Is Nothing Then
cnn.Close
Set cnn = Nothing
End If
Set col = Nothing
Set tbl = Nothing
Set cat = Nothing
Exit Function
ProcErr:
MsgBox Err.Description
Resume ProcEnd
End Function
 
Back
Top