Autonumber problem. (with sample file)

[MaxMiller]

Hi everyone! Hope you can help me with this.

I have a field called Line in the Orders table that I use as a reference for changes made to a customer order. I need this field to generate an AutoNumber sequence starting from 1 up to the number of lines (orders) the customer has.

Then when I start a new order for a new customer, I need it to start again to one.

Please see my sample database and pardon for the lousy data inside. It was meant as a sample only.

 

[Isskint]

This is not the role of an autonumber. An autonumber is used as a unique identifier on a single record. If you did reset this field each time, there would be no way to identify which customer Line 1 refers to.

I guess the purpose of this Line number is to sort/identify the changes. You would be better suited to a time/date field for this. If you specifically want a Line number, then a little code will help you. Use DCOUNT() to count how many changes exist already for an order and add 1. So when a new change is recorded, the Line field will be assigned the value;

Dcount("Line","tblChanges","CustomerID=" & CustomerID)+1
(Can not open your attach so not sure of your field/table names)

 

[jdraw]

I realize that Isskint has given you the answer, but for reference here is a link about autonumbers -- some good facts to remember.

http://www.utteraccess.com/wiki/index.php/Autonumbers

Good luck with your project.

 

[Pat Hartman]

@isskint
It is better to use DMax() to generate sequence numbers. DCount() could generate a duplicate number if a prior row was deleted. When you use DMax() for this purpose, you need to account for no previous rows.
Nz(Dmax("Line","tblChanges","CustomerID=" & CustomerID))+1

 

[MaxMiller]

Here's what I did:

Private Sub Item_BeforeUpdate(Cancel As Integer)
    
If Me.Line.Value = 0 Then
 'Do Nothing
    
If IsNull(DMax("[Line]", "Detail", "[Reference] = '" & Me.Reference & "'")) = True Then
          Me.Line.Value = 1
    Else
          Me.Line = DMax("[Line]", "Detail", "[Reference] = '" & Me.Reference & "'") + 1
        End If
    ElseIf IsNull(Me.Line) = True Then
        Me.Line.Value = 1
    End If
    
End Sub

What should happen is the Line field should increment by 1 each time a new row was added. What happens is when I add a new row, that row always get assigned 1. Instead of say, 7 when the last row was at 6.

 

[PeterF]

With this code, editing a record would also change the number, you should check for new record like:

Private Sub Item_BeforeUpdate(Cancel As Integer)
    If Me.NewRecord Then
        Me.Line = nz(DMax("[Line]", "Detail", "[Reference] = '" & Me.Reference & "'"),0) + 1
    End If
End sub