By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,705 Members | 1,891 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,705 IT Pros & Developers. It's quick & easy.

Fix Gap in DMax auto incremented numbers

P: 19
I have this code
Expand|Select|Wrap|Line Numbers
  1. SerialNbrValue = Val(Nz(DMax("strSerialNumber", "tblOrderData", "dtmDateOrdered=#" & Date & "#"), 7999)) + 1
  2.  
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.
Jul 13 '17 #1

✓ answered by ADezii

  1. Just thought that I need to clarify Post# 23. The following Data Set will return to the Normal Sequence and the next number in this sequence will be 8009.
    Expand|Select|Wrap|Line Numbers
    1. dtmDateOrdered    strSerialNumber
    2. 01-Aug-17    8000
    3. 01-Aug-17    8001
    4. 01-Aug-17    8002
    5. 02-Aug-17    8003
    6. 02-Aug-17    8004
    7. 02-Aug-17    8005
    8. 02-Aug-17    8006
    9. 02-Aug-17    8007
    10. 09-Aug-17    8008
    11. 09-Aug-17    8250
    12. 10-Aug-17    8251
    13. 11-Aug-17    8252
    14.  
  2. This happens because the Gap occurs on August 9, 2017 (8008 - 8250). August 9 is the Current Date.
  3. The next Data Set will return a Sequence Number of 8253 since the Normal Sequence ended on August 9, 2017 (Current Data) and the GAP occurred on the next day.
    Expand|Select|Wrap|Line Numbers
    1. dtmDateOrdered    strSerialNumber
    2. 01-Aug-17    8000
    3. 01-Aug-17    8001
    4. 01-Aug-17    8002
    5. 02-Aug-17    8003
    6. 02-Aug-17    8004
    7. 02-Aug-17    8005
    8. 02-Aug-17    8006
    9. 02-Aug-17    8007
    10. 09-Aug-17    8008
    11. 10-Aug-17    8250
    12. 10-Aug-17    8251
    13. 11-Aug-17    8252
    14.  
  4. The Segment of Code that contains this Logic is:
    Expand|Select|Wrap|Line Numbers
    1. '***************************** CODE INTENTIONALLY OMITTED *****************************
    2. With rst
    3.   Do While Not rstClone.BOF
    4.     If Abs(CLng(![strSerialNumber]) - CLng(rstClone![strSerialNumber])) > 1 Then
    5.       If rstClone![dtmDateOrdered] = Date And ![dtmDateOrdered] = Date Then
    6.         fRetNextInSequence = CStr((CLng(rstClone![strSerialNumber] + 1)))       'Gap!
    7.           Exit Function
    8.       End If
    9.     End If
    10.  
    11.     .MovePrevious             'Move in sync, 1 Record apart
    12.      rstClone.MovePrevious
    13.   Loop
    14. End With
    15. '***************************** CODE INTENTIONALLY OMITTED *****************************
  5. The problem is that I am not 100% sure that my Logic is correct, and I need your Input on this.

Share this Question
Share on Google+
24 Replies


PhilOfWalton
Expert 100+
P: 1,430
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
Jul 13 '17 #2

P: 19
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.
Jul 14 '17 #3

ADezii
Expert 5K+
P: 8,619
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.
Jul 14 '17 #4

P: 19
So in terms of coding are we talking about just looping through?
Jul 18 '17 #5

ADezii
Expert 5K+
P: 8,619
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?
Expand|Select|Wrap|Line Numbers
  1. 8007
  2. 8000
  3. 8001
  4. 8002
  5. 8003
  6. 8004
  7. 8005
  8. 8006
  9. 8047
  10. 8048
  11. 8049
  12. 8050
  13.  
Jul 19 '17 #6

P: 19
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.
Jul 19 '17 #7

ADezii
Expert 5K+
P: 8,619
  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.
    Expand|Select|Wrap|Line Numbers
    1. MyNum
    2. 8000
    3. 8001
    4. 8002
    5. 8003
    6. 8004
    7. 8005
    8. 8006
    9. 8007
    10. 8008
    11. 8009
    12. 8010
    13. 8047
    14. 8048
    15. 8049
    16. 8050
    17.  
  4. Function Definition:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fRetNextInSequence() As Long
    2. Dim MyDB As DAO.Database
    3. Dim rst As DAO.Recordset
    4. Dim rstClone As DAO.Recordset
    5.  
    6. Set MyDB = CurrentDb
    7. Set rst = MyDB.OpenRecordset("tblData", dbOpenSnapshot)
    8. Set rstClone = rst.Clone
    9.  
    10. rst.MoveLast        'Move to Last Record [MyNum]
    11. With rstClone       'Move to Next-to-Last Record [MyNum]
    12.   .MoveLast
    13.   .Move -1          'Clone now at Next-to-Last Record [MyNum]
    14. End With
    15.  
    16. With rst
    17.   Do While Not rstClone.BOF
    18.     If Abs(![MyNum] - rstClone![MyNum]) > 1 Then
    19.       fRetNextInSequence = (rstClone![MyNum] + 1)       'Found the Gap!
    20.         Exit Function
    21.     End If
    22.       .MovePrevious             'Move in sync, 1 Record apart
    23.       rstClone.MovePrevious
    24.   Loop
    25. End With
    26.  
    27. rst.MoveLast        ‘Need last Value in [MyNum]
    28. fRetNextInSequence = (rst![MyNum] + 1)       'No Gap found, next number!
    29.  
    30. rstClone.Close
    31. rst.Close
    32. Set rstClone = Nothing
    33. Set rst = Nothing
    34. End Function
  5. OUTPUT:
    Expand|Select|Wrap|Line Numbers
    1. 8011
Jul 19 '17 #8

P: 19
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.
Jul 19 '17 #9

ADezii
Expert 5K+
P: 8,619
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.
Expand|Select|Wrap|Line Numbers
  1. Public Function fRetNextInSequence() As Long
  2. Dim MyDB As DAO.Database
  3. Dim rst As DAO.Recordset
  4. Dim rstClone As DAO.Recordset
  5.  
  6. 'If there are no Records in tblData, then have the Function return 8000
  7. If DCount("*", "tblData") = 0 Then
  8.   fRetNextInSequence = 8000
  9.     Exit Function
  10. End If
  11.  
  12. Set MyDB = CurrentDb
  13. Set rst = MyDB.OpenRecordset("tblData", dbOpenSnapshot)
  14. Set rstClone = rst.Clone
  15.  
  16. rst.MoveLast        'Move to Last Record [MyNum]
  17. With rstClone       'Move to Next-to-Last Record [MyNum]
  18.   .MoveLast
  19.   .Move -1          'Clone now at Next-to-Last Record [MyNum]
  20. End With
  21.  
  22. With rst
  23.   Do While Not rstClone.BOF
  24.     If Abs(![MyNum] - rstClone![MyNum]) > 1 Then
  25.       fRetNextInSequence = (rstClone![MyNum] + 1)       'Found the Gap!
  26.         Exit Function
  27.     End If
  28.       .MovePrevious             'Move in sync, 1 Record apart
  29.       rstClone.MovePrevious
  30.   Loop
  31. End With
  32.  
  33. rst.MoveLast
  34.  
  35. fRetNextInSequence = (rst![MyNum] + 1)       'No Gap found, return next number in sequence!
  36.  
  37. rstClone.Close
  38. rst.Close
  39. Set rstClone = Nothing
  40. Set rst = Nothing
  41. End Function
Jul 19 '17 #10

P: 19
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.
Aug 3 '17 #11

ADezii
Expert 5K+
P: 8,619
How many Fields are in tblData, what are their Names and Data Types? If possible, post some Sample Data.
Aug 3 '17 #12

P: 19
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.
Attached Files
File Type: txt dATA.txt (1.1 KB, 97 views)
Aug 3 '17 #13

ADezii
Expert 5K+
P: 8,619
  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?
Aug 3 '17 #14

P: 19
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
Aug 3 '17 #15

ADezii
Expert 5K+
P: 8,619
Little short on time right now, but as soon as I can I will look into this further.
Aug 4 '17 #16

ADezii
Expert 5K+
P: 8,619
  1. I made a few Revisions in the Function Code. After learning that the Sequence Number (strSerialNumber) is TEXT and not a LONG, I made some explicit Data Type conversions within the Code.
  2. The Function now returns a STRING and not a LONG.
  3. In addition to checking for a 'GAP' it also checks the Date (dtmDateReceived) against the Current Date. Only if there is a GAP AND dtmDateReceived = Date() will strSerialNumber be incremented, if both these conditions are not met then the Code simply falls through.
  4. Try the Revision and see what happens:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fRetNextInSequence() As String
    2. Dim MyDB As DAO.Database
    3. Dim rst As DAO.Recordset
    4. Dim rstClone As DAO.Recordset
    5.  
    6. 'If there are no Records in tblData, then have the Function return 8000
    7. If DCount("*", "tblData") = 0 Then
    8.   fRetNextInSequence = "8000"
    9.     Exit Function
    10. End If
    11.  
    12. Set MyDB = CurrentDb
    13. Set rst = MyDB.OpenRecordset("tblData", dbOpenSnapshot)
    14. Set rstClone = rst.Clone
    15.  
    16. rst.MoveLast        'Move to Last Record [strSerialNumber]
    17.  
    18. With rstClone       'Move to Next-to-Last Record [strSerialNumber]
    19.   .MoveLast
    20.   .Move -1          'Clone now at Next-to-Last Record [strSerialNumber]
    21. End With
    22.  
    23. With rst
    24.   Do While Not rstClone.BOF
    25.     If Abs(CLng(![strSerialNumber]) - CLng(rstClone![strSerialNumber])) > 1 Then
    26.       If rstClone![dtmDateReceived] = Date Then
    27.         fRetNextInSequence = CStr((CLng(rstClone![strSerialNumber] + 1)))       'Found the Gap!
    28.           Exit Function
    29.       End If
    30.     End If
    31.  
    32.     .MovePrevious             'Move in sync, 1 Record apart
    33.      rstClone.MovePrevious
    34.   Loop
    35. End With
    36.  
    37. rst.MoveLast
    38.  
    39. fRetNextInSequence = CStr((CLng(rst![strSerialNumber] + 1)))       'No Gap found, return next number in sequence!
    40.  
    41. rstClone.Close
    42. rst.Close
    43. Set rstClone = Nothing
    44. Set rst = Nothing
    45. End Function
    46.  
Aug 4 '17 #17

P: 19
I tried fixing it myself, but I am coming up with nothing on it. I have the function set on the AfterInsert Form code. Every time I try to submit the form it gives me Run-Time Error 94 "Invalid use of Null" at Line 25. Is it because my field is not a numeric field?
Aug 7 '17 #18

ADezii
Expert 5K+
P: 8,619
strSerialNumber is TEXT and is being pulled from tblData, don't know how the Form Reference fits into the overall scheme. Can you Upload the Database stripped of any sensitive information?
Aug 7 '17 #19

NeoPa
Expert Mod 15k+
P: 31,411
A couple of points on your code ADezii.
  1. Is the table not named [tblOrderData] (from the OP)?
  2. Instead of opening the whole table in a recordset, why not open the table using SQL that includes :
    Expand|Select|Wrap|Line Numbers
    1. WHERE ([dtmDateOrdered]=Date())
    Faster and more directly focused.
Aug 7 '17 #20

ADezii
Expert 5K+
P: 8,619
Is the table not named [tblOrderData] (from the OP)?
  1. Yes it is, tblData is simply a standard naming convention that I use for Demos. I assumed, perhaps incorrectly, that the OP would make the required substitution.
  2. The Recordset and a generated Clone already exist within a Looping Structure, I figured comparing the Dates would fit neatly within the overall Logic, but your suggestion makes perfect sense, thanks.
Aug 7 '17 #21

P: 19
You are correct in assuming that I did catch the naming convention and corrected it accordingly. I have attached the backend of the database stripped of sensitive info at your request.
Attached Files
File Type: zip DMS Database_be.zip (31.3 KB, 38 views)
Aug 8 '17 #22

ADezii
Expert 5K+
P: 8,619
I download the DB but only see the Back End Tables, so I executed the Function from the Click() Event of a Command Button on a Form. I revised the Code to increment the Normal Sequence Number within a 'GAP' only if the [dtmDateOrdered] Field in both the Recordset and its Clone are for the Current Date. Remember that these Recordsets are 1 day apart in order to check for the GAP. If my Logic is correct, then I cannot limit the Recordset to today's Date ([dtmDateOrdered] = Date()) as previously planned. In any event, download the Revision and see where it takes you.
Attached Files
File Type: zip Sequence_Revised_3.zip (29.6 KB, 37 views)
Aug 8 '17 #23

NeoPa
Expert Mod 15k+
P: 31,411
ADezii:
tblData is simply a standard naming convention that I use for Demos. I assumed, perhaps incorrectly, that the OP would make the required substitution.
Perfectly sensible. I generally use an existing name if already provided, but use the same approach of a standard name otherwise. That just saves relying on the OP being on the ball ;-) In this case there was clearly nothing to worry about, as the OP's confirmed already.
Aug 9 '17 #24

ADezii
Expert 5K+
P: 8,619
  1. Just thought that I need to clarify Post# 23. The following Data Set will return to the Normal Sequence and the next number in this sequence will be 8009.
    Expand|Select|Wrap|Line Numbers
    1. dtmDateOrdered    strSerialNumber
    2. 01-Aug-17    8000
    3. 01-Aug-17    8001
    4. 01-Aug-17    8002
    5. 02-Aug-17    8003
    6. 02-Aug-17    8004
    7. 02-Aug-17    8005
    8. 02-Aug-17    8006
    9. 02-Aug-17    8007
    10. 09-Aug-17    8008
    11. 09-Aug-17    8250
    12. 10-Aug-17    8251
    13. 11-Aug-17    8252
    14.  
  2. This happens because the Gap occurs on August 9, 2017 (8008 - 8250). August 9 is the Current Date.
  3. The next Data Set will return a Sequence Number of 8253 since the Normal Sequence ended on August 9, 2017 (Current Data) and the GAP occurred on the next day.
    Expand|Select|Wrap|Line Numbers
    1. dtmDateOrdered    strSerialNumber
    2. 01-Aug-17    8000
    3. 01-Aug-17    8001
    4. 01-Aug-17    8002
    5. 02-Aug-17    8003
    6. 02-Aug-17    8004
    7. 02-Aug-17    8005
    8. 02-Aug-17    8006
    9. 02-Aug-17    8007
    10. 09-Aug-17    8008
    11. 10-Aug-17    8250
    12. 10-Aug-17    8251
    13. 11-Aug-17    8252
    14.  
  4. The Segment of Code that contains this Logic is:
    Expand|Select|Wrap|Line Numbers
    1. '***************************** CODE INTENTIONALLY OMITTED *****************************
    2. With rst
    3.   Do While Not rstClone.BOF
    4.     If Abs(CLng(![strSerialNumber]) - CLng(rstClone![strSerialNumber])) > 1 Then
    5.       If rstClone![dtmDateOrdered] = Date And ![dtmDateOrdered] = Date Then
    6.         fRetNextInSequence = CStr((CLng(rstClone![strSerialNumber] + 1)))       'Gap!
    7.           Exit Function
    8.       End If
    9.     End If
    10.  
    11.     .MovePrevious             'Move in sync, 1 Record apart
    12.      rstClone.MovePrevious
    13.   Loop
    14. End With
    15. '***************************** CODE INTENTIONALLY OMITTED *****************************
  5. The problem is that I am not 100% sure that my Logic is correct, and I need your Input on this.
Aug 9 '17 #25

Post your reply

Sign in to post your reply or Sign up for a free account.