How to update if user want to add data in between data like insert row above in excel

[isumasama]

Hi all,
I have a table where it stores Process.
The Process has each processNumber which is unique,
& the processNumber indicates the step of a procedure.

My problem is, user request to add a Process in between existing processes.
In other word the new Process overwrite the existing process number, & the others existing need to be updated to processnumber+1

What is the best way to do this in VBA access?, if you get what i'm saying
Thanks

[boblarson]

Copy this function into a standard module (not form, report or class) and name the module something other than the name of the function:

Function AdjustProcNum(strTableName As String, strFieldName As String, lngNewNum As Long)
   Dim strSQL As String
   Dim rst As DAO.Recordset

strSQL = "Select [" & strFieldName & "] From [" & strTableName & "] " & _
         "ORDER BY [" & strFieldName & "]"

Set rst = CurrentDb.OpenRecordset(strSQL)

Do Until rst.EOF
   If rst(strFieldName).Value >= lngNewNum Then
      rst.Edit
      rst(strFieldName).Value = rst(strFieldName).Value + 1
      rst.Update
   End If
   rst.MoveNext
Loop

MsgBox "You can now enter the new process number " & lngNewNum, vbInformation

rst.Close
Set rst = Nothing   
End Function

[boblarson]

I forgot to include how to call this.

It should be:

Call AdjustProcNum("YourTableNameInQuotes", "YourFieldNameInQuotes", YourNewNumber)

[isumasama]

hi boblarson
great answer! thanks a lot