Incremental Default Value

[bluedemon186]

Hello,

I am creating a database for my company and I need to find a way to insert incremental default value for my "Sample_Nbr" Field. The Sample Number has the following format.

MMDDYYYY-01
MMDDYYYY-02
MMDDYYYY-03
and so on...

Is there way so that I can have the value inserted automatically when the user adds a new record? Also, resets the count to 1 for the new day?

 

[apr pillai]

1. Copy and paste the following Code into a Standard Module and save it:
   

   Public Function DefValue(ByVal strTable As String, ByVal strField As String) As String
   Dim dmval As Long, dt1 As Long, dt2 As Long, seq As Integer
   
   dmval = Nz(dmax(strField, strTable), 0)
   
   If Val(dmval) = 0 Then
      dv = Val(Format(Now(), "mmddyyyy")) * 100 + 1
      DefValue = dv
      Exit Function
   End If
   
   dv = Format(Nz(dmval, 0), "0000000000")
   seq = Val(Right(dv, 2))
   dt1 = Val(Left(dv, 8))
   dt2 = Val(Format(Now(), "mmddyyyy"))
   If dt1 = dt2 Then
      seq = seq + 1
      DefValue = dt1 * 100 + seq
      Exit Function
   Else
      DefValue = dt2 * 100 + 1
   End If
   
   End Function

2. Open your Data Entry Form in Design View.
3. Select the Field where you want the number to appear.
4. Display it's Property Sheet (ALT+Enter).
5. Find the Default Value Property and type the expression: DefValue("Table1","FieldName")
6. Replace the Table Name and Field Name with your own Name.
7. Try entering new records into the Table.

 

[bluedemon186]

Thank you, Mr. Pillai. Would this code reset the sequece back to 1 for a new day? If you don't mind can you briflely explain what the code does? I am trying to teach myself vba and I want to understand the code rather than copying it in here and pasting it.

 

[apr pillai]

First of all the code runs only on a new record, that is why we have placed it on the Default Value Property of the Field.

We are using the Table Name and Field Name parameters in the DMax() Function to get the maximum value that exists in the field in mmddyyyyxx format. If you have started with a totally new table then there will not be any output to the DMax() function except 0. In that case the first part of the Code segment shown below will start with the current date in mmddyyyy01 for the first record.

If Val(dmval) = 0 Then
   dv = Val(Format(Now(), "mmddyyyy")) * 100 + 1
   DefValue = dv
   Exit Function
End If

If the result of DMax() is a value greater than zero then the next part of the following code segment starts working. It extracts the first 8 digits separate as date number in variable dt1 (not actually the real date number) and last two digit as existing serial number in variable seq.

dv = Format(Nz(dmval, 0), "0000000000")
seq = Val(Right(dv, 2))
dt1 = Val(Left(dv, 8))
dt2 = Val(Format(Now(), "mmddyyyy"))
If dt1 = dt2 Then
   seq = seq + 1
   DefValue = dt1 * 100 + seq
   Exit Function
Else
   DefValue = dt2 * 100 + 1
End If

Variable dt2 holds the current date number in mmddyyyy format. If dt1 = dt2 then you are entering new records for the current system date and seq variable is incremented and returned the result number to the new record.

Else

You have started a new record for a different date and the sequence number resets to 1 in mmddyyyy01 format.

PS: The Code is written yesterday only, after reading your Forum Post, and not extensively tested for side effects. Please use it at your own risk.

 

[bluedemon186]

Thank you for the explaination. I will test it out and will let you know if it works or not. I really appreciate you spending the time to explain the code.

 

[bluedemon186]

Oh just one more question. If I wan't the fomat to be mmddyyyy-01, should I just enter a "-" in dv = Format(Nz(dmval, 0), "0000000000") so that it would be dv = Format(Nz(dmval, 0), "00000000-00")?