Increment

[q4s72534]

I have a number that changes in one table. Let say today it is 15..
I have a table that I want to take that value 15 and add 1 to it for each row so I would then have
16
17
18
...

What should i do to add the rows 16, 17 and 18 to a different table.

 

[hymn]

Have you thought about an update query

Hope this helps
Hymn

 

[lespaul]

actually, I think he would need an INSERT query.



Leslie

 

[MoLaker]

First, you need to find the last (highest) number. If you're using an Access application and not just Access tables, you can use the DMax() aggregate function. Or, you can use the SQL Max() function. Then add 1 to the returned value to increment to the next number. Use that number as the next value in your INSERT INTO SQL.

 

[src23]

Hi. Try this:

I defined two functions which I use in all my apps to get a new numeric ID in a table:

1 - GetNewID - which gives me two options:
(a) gets the maximum ID value + 1 from a table
OR
(b) gets THE FIRST FREE ID value from a table (useful in this case: lets say you have a table with ID from 1 to 20, then you delete records 10, 14 and 17 - well, then the IDs 10,14 and 17 can be assigned to new records). Note that this options is not useful in case of very large tables.

2 - IsUniqueID - checks if an ID is unique in a table.

Note that these two functions work WITH ANY TABLE, but only on numeric ID types.


Here is the code:

Option Explicit

Public Enum enumNewIDType
    NewIDMaximum = 1
    FindFirstFreeID = 2
End Enum


Public Function GetNewID(strTableName As String, strPK_FieldName As String, NewIDMode As enumNewIDType) As Long
    On Error GoTo Err_Trap
    
    Dim rs        As Object
    Dim s         As String
    Dim k         As Integer
    Dim blnFound  As Boolean
    
    
    
    Select Case NewIDMode
        Case 1 'NewIDMaximum
            s = "SELECT Max(" & strPK_FieldName & ") FROM " & strTableName
          
            Set rs = CurrentDb.OpenRecordset(s)
            GetNewID = Nz(rs.Fields(0), 0) + 1
        
        Case 2
            s = "SELECT " & strPK_FieldName & " FROM " & strTableName & " ORDER BY " & strPK_FieldName
            Set rs = CurrentDb.OpenRecordset(s)
            
            If rs.RecordCount = 0 Then
                GetNewID = 1
                blnFound = True
                Else
                Dim prevID As Long
                Dim currID As Long
                
                prevID = rs.Fields(0)
                rs.MoveNext
                

                Do While Not rs.EOF
                
                    currID = rs.Fields(0)
                    
                    If (currID - prevID) > 1 Then
                        
                        If IsUniqueID(strTableName, strPK_FieldName, prevID + 1) = True Then
                            GetNewID = prevID + 1
                            blnFound = True
                            Exit Do 'For
                        End If
                        
                        Else
                        
                        prevID = rs.Fields(0)
                        rs.MoveNext
                    
                    End If

                Loop
            End If
            
            If blnFound = False Then
                GetNewID = prevID + 1 'rs.Fields(0) + 1
            End If
            
        End Select
        
Exit_Here:
        Exit Function
        
Err_Trap:
    Select Case Err.Number
        Case Is <> 0
            MsgBox Err.Number & " - " & Err.Description
            Stop
            Resume Exit_Here
    End Select
        
End Function



Public Function IsUniqueID(strTableName As String, strIDFieldName As String, strIDValue As String) As Boolean
    Dim rs As Object
    Dim s As String
        

    Set rs = CurrentDb.OpenRecordset("SELECT * FROM " & strTableName)
    
    s = strTableName & "." & strIDFieldName & " = " & strIDValue
    rs.FindFirst s
    
    If rs.NoMatch Then
        IsUniqueID = True
        Else
        IsUniqueID = False
    End If
    
    Set rs = Nothing
    
End Function

You can use them like this:

Dim lngNewID as Long

lngNewID = GetNewID("YourTableName", "PrimKeyFieldName",NewIDMaximum )
'then  do whatever you want with this new ID; or you can
'declare another argument to the GetNewID function, which 
'passes the value of that number you mentioned in your post

Paste this code in a module, and feel free to use it.