Missing Numbers In Sequence

[IanLowe]

Sorry if there is already an answer to this but I couldn't find it.

I manage a membership database where the membership number is the key. As members die, retire or simply resign their membership number becomes vacant.
I would like to be able to reallocate these blank numbers automatically when the user presses Add Record.

Any thoughts

 

[jzwp22]

You will need a way to flag those members who are no longer members. You can add a field that denotes their status as Active or Inactive. Then I would write a nested query that returns all member numbers of inactive members that are not being used by active members. Here is the general format of the query (not tested):

SELECT min(memberno) as NewMemberNo
FROM yourtablename
Where status="inactive" and memberno not in (Select memberno from yourtablename where status="active")

Then when you are adding the record, you can use a dlookup() function to populate the memberno

dlookup("NewMemberNo", "querynamefromabove")

 

[IanLowe]

JZWP22, many thanks for your answer. I had worked a solutiuon in the mean time which is similar but, given my lack of realy expertise in Access, perhaps not as efficient as yours. Would you please comment.

When the user presses the New Record button I run a Make-Table query with the folowing code.
SELECT TOP 1 [Membership No]+1 AS Expr1 INTO NextNumber FROM Membership AS T1 WHERE ((([Membership No]+1) Not In (SELECT [Membership No] FROM Membership T2 WHERE T2.[Membership No] = T1.[Membership No] + 1))) ORDER BY T1.[Membership No];

This puts the first non-continuous number in a table called NextNumber.
I have also put a "NewRecord" check in the Forms Current event to establish if this recred is a new record or not and if it is I use Dlook to get the record number from the NextRecord table.

 

[jzwp22]

Your suggestion sounds like it will work assuming that you delete people who are no longer members. For many things in Access, there are usually multiple ways of achieving the same outcome. Good luck on the project.