Nyt auto nummering pr dato

Ikke afprøvet:

Private Sub Form_Current()
Me.dato = Date
If Me.dato > DMax("[dato]", "Tabel1") Then
Me.nr = 1
Else
Me.dato = Date
Me.nr = DMax("[nr]", "Tabel1") + 1
End If
End Sub

THis example uses a table named test with a field named DateID which contains the generated ID. This field should be a unique key just to make sure that you cant get the same ID more than once.

Now make a query and name it qryNextNumber with the following SQL

SELECT Left([DateID],6) AS Dato, Max(CInt(Right([DateID],2))) AS MaxDateID
FROM Test
GROUP BY Left([DateID],6);

Now in the form where you enter new records in the BEFORE UPDATE event use the following code.

If Not Me.NewRecord Then Exit Sub

Dim nextID As Integer

    nextID = Nz(DLookup("MaxdateID", "qryNextNumber", "Dato = '" & Format(Date, "ddmmyy") & "'"), 0)

    Me.DateID = Format(Date, "ddmmyy") & "D" & Format(nextID + 1, "00")

And I am assuming that you want this format
160407D01

and NOT
160407001
(Dato+001)

you WILL get a problem if you add more than 99 records!!

Hej Terry. Det virker tusind tak.

Thanks.
If you need help altering the code/SQL so that you can have more than 99 records per day then drop a comment,

Hej Terry.
Hvis nu jeg skulle bruge dato som er tast ind i et felt "Kørsels dato" hvordan skal koden så se ud? Og nej jeg kommer ikke til at bruge over 99 pr dag

I have a text field on teh same form named txtDato

Change the code, and it assumes that the date is ALWAYS entered ddmmyy

Private Sub Form_BeforeUpdate(Cancel As Integer)


If Not Me.NewRecord Then Exit Sub

Dim nextID As Integer
Dim dato As Date


    dato = DateSerial(Right(Me.txtDato, 2), Mid(Me.txtDato, 3, 2), Left(Me.txtDato, 2))
   
    nextID = Nz(DLookup("MaxdateID", "qryNextNumber", "Dato = '" & Format(dato, "ddmmyy") & "'"), 0)

    Me.DateID = Format(dato, "ddmmyy") & "D" & Format(nextID + 1, "00")


End Sub

Hej Terry.
Det virker ikke. Har bytte txtDato ud med kørselsdato og for en fejl. "Compile error: Method or data member not found" felt som jeg gemme data i er et tekst felt

is it possible for you to send me your dB and I'll take a look tomorrow?
ekspertenATsanthell.dk
change AT to @