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.
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