Sequenced Key When Adding New Record

  • Thread starter Thread starter Davy
  • Start date Start date
D

Davy

I have a database which has a number field as the primary key. The number
sequence has many gaps.

How can I when a user hits the 'new record' arrow or my 'new record'
command button, automatically insert the next number in the sequence or
better still the first unused number?

I can manage a bit of macros and VBA - but not too ambitious!

thanks

Davy
 
How can I when a user hits the 'new record' arrow or my 'new record'
command button, automatically insert the next number in the sequence or
better still the first unused number?
Click to expand...

Change the Autonumber to a Long Integer. For the next number in
sequence use the following code on your Form's BeforeInsert event (and
yes, a Form is obligatory, table datasheets don't have any usable
events):

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtID = NZ(DMax("[ID]", "[YourTable]") + 1
End Sub

This may be a bit risky if you have multiple users updating
simultaneously - there is more elaborate code to prevent collisions if
need be.

Filling in the gaps is more work, and less efficient. I'd suggest
creating a utility table NUM with a single long integer field N,
filled with values from 1 to well above the maximum record ID you'll
ever need (you can use Excel fill-down to quickly populate such a
table, just import the spreadsheet).

Create a Query, qryNextID:

SELECT Min(Num.N) As NextID
FROM Num LEFT JOIN [yourtable]
ON Num.N = yourtable.ID
WHERE yourtable.ID IS NULL;

and replace the expression above with

Me!txtID = DLookUp("[NextID]", "[qryNextID]")
 
You can look up the highest value using DMax function.
Like so:
' test for new record
If Me.NewRecord then
'if true then look up the largest number in the PrimaryKey
' field and add one to that value
Me.PrimaryKey = Dmax("PrimaryKey","Table") +1
End If
HTH
look in online help while in vba editor for further help on DMax function.
 
Back
Top