0

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: n/a

2
Contributors
3
Replies
4
Views
6 Years
Discussion Span
Last Post by isumasama
1

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
0

I forgot to include how to call this.

It should be:

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

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.