secondary key field

[action]

I am using a linked table and have created a create new record button but discovered that the linked table has an important secondary reference number field(userid) similar to the primary key(id) but isn't the primary. The userid field is sequential to the last record but gaps appear where records have been deleted.

I need to generate a sequential number based off the last userid in the table when the add record button is activated.

Note: this is not the Primary Key field.

Also I can only handle basic code.

Hope someone can help.

Cheers

 

[dcx693]

This query should return to you the maximum value in a field:
SELECT Max(tblData.userid) AS MaxOfuserid FROM tblData;

Here's a sample of how to use the query in a custom function using ADO in Access 2000+ (if you need DAO code for Access 97 let me know). Call the function from your code or form and add 1 to it to get the next number:
Function MaxOfID()
Dim rst As ADODB.Recordset
Dim strSQL As String

Set rst = New ADODB.Recordset
Set rst.ActiveConnection = CurrentProject.Connection
strSQL = "SELECT Max(tblData.userid) AS MaxOfuserid FROM tblData;"

rst.Open strSQL
MaxOfID = rst.Fields("MaxOfuserid")

rst.Close
Set rst = Nothing
End Function