How to get the next row id using access

[muana]

I want to retreive the Next Value_ID from a table in MS Access Database, incremented automatically, and stopping after reaching the MAX VALUE_ID.
I can manage this with Oracle. Can any body tell me how to write the syntax for getting the NEXT VALUE_ID in MS Access and stopping when the MAX VALUE_ID is reached.

[Glenton]

I know I had to do something similar some years back, and found it was very difficult. Eventually hired an access guru who coded a macro or something. I didn't look into the details, but I gather it was tricky.

Hopefully I'm wrong...

[jota69]

Assuming you have a database with at least one table, and some records.
Assuming also that the table contains a primary key or id field, and that key value increases automatically.
If you create a function that has id field records, and checks that this value is not greater than MaxValue.
If so, a message informs you of the event, otherwise, what you want to do, for example save the new record.

Assuming you have a process that validates data entered by the user before saving.
From this process, you can call the function, before validating the data is done.
If the function returns true (reached Maxvalue), warn the user through a message, and exit the process.
Otherwise, save your changes.

Function FindRecordCount(strSQL As String) As Long
Dim IsMaxValue as Boolean = false
Dim YourDatabase As DAO.Database
Dim rstRecords As DAO.Recordset
 
On Error GoTo ErrorHandler
 
Set YourDatabase = CurrentDb
 
Set rstRecords = YourDatabase.OpenRecordset(strSQL) ' strSQL = Connection string.
 
If rstRecords.EOF Then
      FindRecordCount = 0
Else
      rstRecords.MoveLast
FindRecordCount = rstRecords.RecordCount
End If
 
if FindRecordCount>=100 then    ' here determine Max value yourself.
IsMaxValue =  true
Else
IsMaxValue =  false
End If
 
   rstRecords.Close
   YourDatabase.Close
 
   Set rstRecords = Nothing
   Set YourDatabase = Nothing
 
Return IsMaxValue 
 
Exit Function
 
ErrorHandler:
   MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Function

hope this help.