Filling Holes in Primary Key Field

[dsajones]

Hi All,

I've imported some historic user data from some spreadsheets and I have a field which is a unique PIN code for each user. This is set as the primary key on my new table. Duplicates are not allowed. It's a text field with values ranging from 0005 through to 9576. The maximum allowed range of values will be from 0001 to 9999. There are currently only 300 records in the table so as you can see, there are lots of PINs available for use.

I'm building a form to allow a new user record to be created and want to automatically allocate the next available PIN. So if I was entering a new record now, the PIN to be allocated would be 0001.

I have limited skills with Access so would be grateful for any suggestions as to how I can create an event for creating a new record that looks up the next available free PIN.

Many thanks

 

[CJ_London]

try this sql

SELECT Min([Pin]+1) 
FROM mytable 
WHERE (((Exists (select pin from mytable as tmp where pin = mytable.pin+1))=False));

Note that this is for a numeric pin not a text pin (because you need to add 1). I recommend you convert your pin to a number and use format to display the preceding zeros - you would format as '0000'

 

[dsajones]

Thanks CJ_London. The SQL side of it all makes sense. Now the next challenge, how to actually put it in to the VBA code!!

I thought this would do the trick to just get a message box popping up to see if it had found the correct value. But this errors with runtime error 3065 saying I can't execute a Select statement

 Dim strSQL As String
    
    strSQL = "select min([Pin]+1) from loneworkermaster where (((Exists (select pin from loneworkermaster as tmp where pin = loneworkermaster.Pin+1))=False))"
    CurrentDb.Execute strSQL
    MsgBox (strSQL)
[\code]

Cheers

 

[CJ_London]

you need to open a recordset

Dim strSQL As String
dim rst as DAO.recordset
 
strSQL = "select min([Pin]+1) from loneworkermaster where (((Exists (select pin from loneworkermaster as tmp where pin = loneworkermaster.Pin+1))=False))"
set rst=CurrentDb.openrecordset strSQL
MsgBox (rst.fields(0))
[\code]

 

[dsajones]

OK thanks. I'll give that a try later.