Setting default line number on a sub form

[suepowell]

Hi

I have a form where I enter customer orders, the main form containing customer name address etc.

On this form I have a sub form which holds the individual line sof the the order, part no qty etc.

I need to number these lines starting at 1 for each new order, and increasing for each line of the order.

I can get it to work correctly from line 3 onwards, but I get 2 for the first two lines. I have tried using the value currentrecord and adding 1 , but as I am line 1 when I set the value for line 1 it gives 2, and if I don't add 1 each line number is 1 too low.

Any ideas?

Thanks

Sue Powell.

 

[pono1]

Sue,

This works in A2K on a continuous subform:

Paste the function that's below into your subform's code module and then drop a new textbox onto the subform. In the textbox's control source, call the function:

=GetLineNum([MyUniqueId])

MyUniqueId must be a field in the subform's record source that holds a unique number for each rec, such as the primary key.

Private Function GetLineNum(MyID As Long) As Long
'Pass in a unique number from your form's dataset,
'such as the primary key.
      
'DAO must be selected from Tools-References
    Dim rst As DAO.Recordset
    Dim i As Long
    
'Get a copy of the records behind the form.
    Set rst = Me.RecordsetClone

'Prepare to move through the records.    
    rst.MoveFirst
    i = 1

'Start moving.    
    Do
     'Is this the record?
       If rst.Fields("MyUniqueID") = MyID Then
        'Yes, so get it...
         GetLineNum = i
          'and cut out of the loop.
            Exit Do
       End If
   
       i = i + 1
       rst.MoveNext
       
    Loop Until rst.EOF
        
    Set rst = Nothing

End Function

Regards,
Tim