Member Avatar for 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

Edited by isumasama because: n/a
  • 2 Contributors
  • 3 Replies
  • 119 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by isumasama

Dani AI

Generated

This thread is about inserting a new step by shifting later processNumber values up by one. ’s DAO recordset loop is a clear, practical solution for small tables and when processNumber is not enforced by a unique index. Two important factors to pick the safest approach are (1) whether processNumber has a unique/index or is referenced by other tables, and (2) table size and performance.

If a unique index exists, updating rows in ascending order can raise a duplicate-key error because an update may try to write a value that already exists. Two safer patterns are:

  • Update from highest to lowest (so you always move a value into an unused slot).
  • Use a set-based, two-step offset trick that avoids collisions entirely. Example (choose N larger than the current maximum processNumber):
-- pick N (e.g. current max + 1)
UPDATE tblProcess
SET ProcessNumber = ProcessNumber + N
WHERE ProcessNumber >= NewNum;

UPDATE tblProcess
SET ProcessNumber = ProcessNumber - (N - 1)
WHERE ProcessNumber >= NewNum + N;

After those updates, insert the new row with ProcessNumber = NewNum.

Always wrap the changes in a transaction so a failure rolls everything back. In Access use CurrentDb.BeginTrans, then commit or Rollback on error. Also consider design changes to avoid frequent renumbering: use an AutoNumber primary key and a separate non-unique SortOrder; or give gaps (10, 20, 30) or fractional values for easier inserts (with occasional rebalancing).

Troubleshooting tips: if you see a duplicate-key error, switch to descending updates or use the offset method; if updates are slow on large tables, prefer set-based SQL over row-by-row recordsets; and if ProcessNumber is a foreign key elsewhere, update related tables or avoid using the process number as the FK. This complements ’s reply and gives safer, scalable alternatives for production use.

Recommended Answers

All 3 Replies

Member Avatar for 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
Member Avatar for boblarson

I forgot to include how to call this.

It should be:

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

Member Avatar for isumasama

hi boblarson
great answer! thanks a lot

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.