line item numbers in a subform

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Does anyone know how to set up a field in a subform to enter sequential numbers? I have a field in the main form which is the "one" side, then my subform contains the "many" details for the main form. In the subform, as the user enter records I need a field to automatically enter numbers (i.e. 1, 2, 3) and so on for each record. I tried an autonumber, but that carried the numbers for all the records, I need this field to start with 1 for the first record in the subform

Thanks!
 
Amy said:
Does anyone know how to set up a field in a subform to enter sequential
Click to expand...
numbers? I have a field in the main form which is the "one" side, then my
subform contains the "many" details for the main form. In the subform, as
the user enter records I need a field to automatically enter numbers (i.e.
1, 2, 3) and so on for each record. I tried an autonumber, but that
carried the numbers for all the records, I need this field to start with 1
for the first record in the subform.

In the BeforeUpdate event of the subform...

If IsNull(Me!SubID) = True Then
Me!SubID= Nz(DMax("SubID", "SubTableName", "MainID = " &
Me.Parent!MainID & ""),0) + 1
End If
 
On my website (see sig below) there is a small sample database called
"FormSubform.mdb" which illustrates how to do this. It is basically the
same method that Rick mentioned, but you can see it in action.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Amy said:
Does anyone know how to set up a field in a subform to enter sequential
Click to expand...
numbers? I have a field in the main form which is the "one" side, then my
subform contains the "many" details for the main form. In the subform, as
the user enter records I need a field to automatically enter numbers (i.e.
1, 2, 3) and so on for each record. I tried an autonumber, but that carried
the numbers for all the records, I need this field to start with 1 for the
first record in the subform.
 
The best way is to not actaully store the number, as then if a deletion
occurs, you have to re-number.

Hence, you can:

You can put the follwing code in the sub form:

Function Rpos(vId As Variant) As Long

Rpos = 0
If IsNull(vId) = False Then
Me.RecordsetClone.FindFirst "id = " & vId
If Me.RecordsetClone.NoMatch = False Then
Rpos = Me.RecordsetClone.AbsolutePosition + 1
End If
End If

End Function

Then, you can put a un-bound text box in the continoues form,and

=(rpos([id]))

The above assumes you have a key field called id. It also assumes dao.
 
Back
Top