How to lookup value in the previous record? (1 Viewer)

[maxkmv]

I need to tackle the following problem: I need to be able to automatically fill the field in the record based on the value in the same column but previous record + 3.
In other words it should look up the value in the previous record and add a number to it (or concatenate two string) For example:
-----Contract Number---- ------Item------
001 Equipment
004 Materials
007 Etc.

 

[Alexandre]

I hope you DO NOT intend to use it as a primary key, but just a 'trivial' unique index.

Set the default value of your 'Counter' control o your form (your counter field should be a long integer):

set.defaultvalue = IIF(ISNull(DMax("ID","MyTable")),0,(CLng(DMax("ID","MyTable"))+3))
The iif allows to chek for an empty table that would return a null.
MyTable is your underlying table, an ID your no duplicate index.

Use in combination with this function called from the form after insert event, to avoid duplicates values caused by records inserts:

Public Function nextIndex(MyControl As Control, MyForm As Form)
On Error Resume Next
       
    Dim DB As Database
    Dim RecSet As Recordset
       
    Set DB = DBEngine(0)(0)
    Set RecSet = MyForm.RecordsetClone
    RecSet.MoveLast
       
    MyControl.DefaultValue = RecSet.Fields(0) + 3
       
End Function

Use it this way: set the After Insert event to
=nextIndex([YourFormName]![YourCounterControl],[YourFormName])

Where YourCounterControl is bound to ID in MyTable

Alex