Michał Plich Michał Plich - 25 days ago 10
SQL Question

New rows get sequential value within specified range, periodically re-using old values

I need a solution (Probably in Access) to get a value from table (Client Nr) and put a Client Name. The conditions is that ClientNr must be values between 3001 and 8000. Once Client 8000 is filled, the next Client should get new record with number 3001. (so Client Nr will be duplicated, but old client must be still in database).
Eg: First client in DB was "Patrik" with nr 3001, but after 7999 next clients has been registered, I need number 3001 to be used again for Aleksandra. (So now Patrik and Aleksandra will be both in database with numbers 3001).

Is it a way to do that in Access?
I was thinking of making ClientNR field incrementing by one - then new record would be filled automatically, but is it possible to create a rule to set new record value to 3001 once 8000 has been reached?
Other solution is to prepare database forehead and create several loops with ClientNr already been prefilled, but is it a wy to find first free record with empty ClientNr?
I really appreciate any suggestion on this.enter image description here

Answer Source

If you want a solution in the table-design, I think you should go for SQL-Server. If running VBA-code is a possibility, perhaps the next code can help.

    Option Compare Database
    Option Explicit

    Public Function AddNewClient()
    Const StartNewClientNr As Long = 3001
    Const MaxNewClientNr As Long = 8000
    Dim S As String
    Dim NewClientName As String
    Dim NewClientNr As Long
    Dim MaxID As Long
    '
    ' existing table :
    ' Table1 (ID as AutoNumber, ClientNr as Long, ClientName as Text)
    '
    S = "Please give the name of the new client."
    NewClientName = InputBox(S)
    If NewClientName = "" Then
        Exit Function
    End If

    MaxID = Nz(DMax("ID", "Table1", ""), 0)
    If MaxID = 0 Then
        NewClientNr = StartNewClientNr
    Else
        NewClientNr = Nz(DLookup("ClientNr", "Table1", "ID = " & MaxID), 0)
        If NewClientNr = 0 Then
            ' do something ??
            NewClientNr = StartNewClientNr
        Else
            NewClientNr = NewClientNr + 1
            If NewClientNr > MaxNewClientNr Then
                NewClientNr = StartNewClientNr
            End If
        End If
    End If

    S = "Are you sure to add the client '" & NewClientName & "' with" & _
        " number '" & NewClientNr & "' ?"
    If MsgBox(S, vbYesNo + vbDefaultButton2 + vbQuestion) = vbYes Then
        S = "INSERT INTO Table1 (ClientNr, ClientName) " & _
            " VALUES (" & NewClientNr & _
            " ,'" & Replace(NewClientName, "'", "''") & "') "
        CurrentDb.Execute S
    End If    
    End Function