Fix Gap in DMax auto incremented numbers

[astroshark]

I have this code

SerialNbrValue = Val(Nz(DMax("strSerialNumber", "tblOrderData", "dtmDateOrdered=#" & Date & "#"), 7999)) + 1

This code increments a number starting at 8000 and restarts every day, but I am running into a problem. Our database requires that some of these numbers be entered manually which are completely different numbers usually higher than 8000 leaving a gap between the numbers sequence. An example is we have our number starting from 8000 then we have to put in let's say 8042 manually, so instead of the next entry going in as 8001 it will increment to 8043.

So I am curious is there a way that I can make it to where this DMax code can skip rows in the database that are entered manually and continue off the proper sequence? Please let me know if you require further clarification.

[PhilOfWalton]

DMax will give the maximum value.

To do what you want, you need a flag to say that the number has been added manually and your DMax expression must exclude the flagged numbers.

This is extremely dodgy and I strongly advise against.
What happens when your last automatic number is 8041 and 8042 has been added manually?

A possible solution, providing the number is set up as no duplicates is to increment the numbers starting at 8000, and when a duplicate is detected, add 1 and try again. That would require VBA code

Don't really like that either.

Phil

[astroshark]

Right now there are multiple values that the system could ask itself does this entry have a certain value? And yes it does there are several fields within the table that if these entries are entered in manually it will say "MANUAL ENTRY". So is there VBA code that will allow me to flag these entries that say that within the DMax code?

Because I don't care if the number duplicates or not I just need it to go in proper sequence is all.

[ADezii]

If I am understanding you correctly, if there is a Gap in the sequence, then this would automatically indicate a manual entry, and no 'flagging' of manual entries would be required. If this is true then you could simply start from the last Record in the Table, navigate upwards to you reach the first Gap. The number above this Gap + 1 would be the next number in the original sequence. DMax() would not even enter into the picture.

[astroshark]

So in terms of coding are we talking about just looping through?

[ADezii]

Before I respond, I want to make sure that I am making the correct assumptions. Would a typical series look like that below where the next number in sequence would be 8007 and NOT 8051?

8007
8000
8001
8002
8003
8004
8005
8006
8047
8048
8049
8050

[astroshark]

Yes you are correct the proper sequence would continue off 8006 to go to 8007 and the larger series of numbers would just be manual entries. Which I would like the larger series of numbers to be ignored from the proper sequence. I would like to note that it does NOT matter if the numbers end up duplicating each other.

[ADezii]

1. Based on what you have told me, I created the following Function that will return the next number in sequence if there is no Gap or restore the next number in normal sequence if there is a Gap.
2. I accomplish this by:
   1. Creating a Recordset based on the [MyNum] Field in tblData.
   2. Creating a Clone of this Recordset.
   3. Move to the last Record in the Original Recordset and the next-to-last Record in the Cloned Recordset.
   4. Compare Values in [MyNum] for the Original and Cloned Recordsets (off by 1 Record) to see if there is a difference > 1 which would indicate a 'Gap'.
   5. Have the Function return the next number in sequence whether or not there is a Gap
3. Sample Data in the [MyNum] Field.
   

   MyNum
   8000
   8001
   8002
   8003
   8004
   8005
   8006
   8007
   8008
   8009
   8010
   8047
   8048
   8049
   8050

4. Function Definition:
   

   Public Function fRetNextInSequence() As Long
   Dim MyDB As DAO.Database
   Dim rst As DAO.Recordset
   Dim rstClone As DAO.Recordset
   
   Set MyDB = CurrentDb
   Set rst = MyDB.OpenRecordset("tblData", dbOpenSnapshot)
   Set rstClone = rst.Clone
   
   rst.MoveLast        'Move to Last Record [MyNum]
   With rstClone       'Move to Next-to-Last Record [MyNum]
     .MoveLast
     .Move -1          'Clone now at Next-to-Last Record [MyNum]
   End With
   
   With rst
     Do While Not rstClone.BOF
       If Abs(![MyNum] - rstClone![MyNum]) > 1 Then
         fRetNextInSequence = (rstClone![MyNum] + 1)       'Found the Gap!
           Exit Function
       End If
         .MovePrevious             'Move in sync, 1 Record apart
         rstClone.MovePrevious
     Loop
   End With
   
   rst.MoveLast		‘Need last Value in [MyNum]
   fRetNextInSequence = (rst![MyNum] + 1)       'No Gap found, next number!
   
   rstClone.Close
   rst.Close
   Set rstClone = Nothing
   Set rst = Nothing
   End Function

5. OUTPUT:
   

   8011

[astroshark]

Thank you very much this helps solve part of the problem as it does go in sequence. I am trying to tinker with the code because I need the number to seed at 8000 and reset daily. But I am unfortunately getting no where with that. I am assuming that I need to use it as an argument within the function? Because right now since I have no records as of today in the database that functions starts off at the number 10 only and just goes up from there, it still stays in sequence as I tried putting in a manual entry with it. It's just that I can't get it to start off at 8000 or know how to reset it daily.

[ADezii]

One problem at a time. If I understand you correctly, you need the Function to return a Value of 8000 as the initial Seed if there are no Records in the Table. Why not have the Function check for the existence of any Records in the Table before any other processing (line numbers 7 thru 10)? If the Table is empty, have the Function return 8000 then exit.

Public Function fRetNextInSequence() As Long
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim rstClone As DAO.Recordset

'If there are no Records in tblData, then have the Function return 8000
If DCount("*", "tblData") = 0 Then
  fRetNextInSequence = 8000
    Exit Function
End If

Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("tblData", dbOpenSnapshot)
Set rstClone = rst.Clone

rst.MoveLast        'Move to Last Record [MyNum]
With rstClone       'Move to Next-to-Last Record [MyNum]
  .MoveLast
  .Move -1          'Clone now at Next-to-Last Record [MyNum]
End With

With rst
  Do While Not rstClone.BOF
    If Abs(![MyNum] - rstClone![MyNum]) > 1 Then
      fRetNextInSequence = (rstClone![MyNum] + 1)       'Found the Gap!
        Exit Function
    End If
      .MovePrevious             'Move in sync, 1 Record apart
      rstClone.MovePrevious
  Loop
End With

rst.MoveLast

fRetNextInSequence = (rst![MyNum] + 1)       'No Gap found, return next number in sequence!

rstClone.Close
rst.Close
Set rstClone = Nothing
Set rst = Nothing
End Function

[astroshark]

I apologize for the extremely late response I was away on business. But it looks like it's headed in the right direction. I tried adding a WHERE clause to the statement to ensure that it only counts the current day's records, but when I do the sequence number doesn't increase and stays the same.

[ADezii]

How many Fields are in tblData, what are their Names and Data Types? If possible, post some Sample Data.

[astroshark]

There are about 48 fields within this table I have attached a text file of the fields. The data types of the fields are denoted by the name of the fields. I had to do a little bit of reformatting of the text file so I am note for sure if it will show up the same way on your computer.

[ADezii]

1. Is [strSerialNumber] the Field that needs to be incremented? If so, why is it a String?
2. Is [dtmDateReceived] the Date Field that needs to be evaluated against the Current Date?
3. The way I am reading things is that the Sequence Number gets incremented only if [dtmDateReceived] = Current Date, is this True?

[astroshark]

1. There are some numbers (The Manual Entries) that will have leading zeroes and those leading zeroes are required, and the only way I have found to make it work is to make it a string. The manual entries will have a wide range of entries ranging from 0001 to 9999.

2. Yes

3. Yes