VBA Code for ID Number Increment on User Form. I'll need help to correct this code to have it increase automatically. But have it save to the file once the.

• 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. • '***************************** CODE INTENTIONALLY OMITTED ***************************** • With rst • Do While Not rstClone.BOF • If Abs(CLng(![strSerialNumber]) - CLng(rstClone![strSerialNumber])) > 1 Then • If rstClone![dtmDateOrdered] = Date And![dtmDateOrdered] = Date Then • fRetNextInSequence = CStr((CLng(rstClone![strSerialNumber] + 1))) 'Gap! • Exit Function • End If • End If • •.MovePrevious 'Move in sync, 1 Record apart • rstClone.MovePrevious • Loop • End With • '***************************** CODE INTENTIONALLY OMITTED ***************************** • The problem is that I am not 100% sure that my Logic is correct, and I need your Input on this. 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? Korean song free download. 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. • 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.

• I accomplish this by: • Creating a Recordset based on the [MyNum] Field in tblData. • Creating a Clone of this Recordset. • Move to the last Record in the Original Recordset and the next-to-last Record in the Cloned Recordset. • 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'. • Have the Function return the next number in sequence whether or not there is a Gap • Sample Data in the [MyNum] Field. • 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 • OUTPUT.