Auto Increment Detail Seq#

[Gary Schuldt]

I have a standard Master/Detail situation with corresponding frmMaster and
subfrmDetail with linked fields.

In tDetail there is a Seq# field which is displayed as the leftmost field on
the subform.

tDetail has its own Autonumber ID primary key field.

I would like the Seq# to be automatically numbered when entering new
subform record data, starting at 1 and incrementing by one for a specific
Master record.

I tried using Autonumber, but Access won't let me have more than one
Autonumber field per table.

What is the best way to get what I want?

Thanks.

Gary

 

[Steve Schapel]

Gary,

Use a vba procedure on the BeforeInsert event of the subform, like this...
Private Sub Form_BeforeInsert(Cancel As Integer)
Me.SeqNo = Nz(DMax("[SeqNo]","tDetail","[MasterID]=" &
Me.Parent.MasterID),0) + 1
End Sub
(Note I have changed Seq# to SeqNo, it is not a good idea to use a # as
part of the name of a field or control.)

 

[Gary Schuldt]

Thanks, Steve!

I worked the first time (maybe it will keep working with the 2nd, 3rd, and
4th as well!).

(I don't think that even "100 Monkeys Typing" would have hit on the code you
suggested! LOL!)

Gary

Gary,

Use a vba procedure on the BeforeInsert event of the subform, like this...
Private Sub Form_BeforeInsert(Cancel As Integer)
Me.SeqNo = Nz(DMax("[SeqNo]","tDetail","[MasterID]=" &
Me.Parent.MasterID),0) + 1
End Sub
(Note I have changed Seq# to SeqNo, it is not a good idea to use a # as
part of the name of a field or control.)

--
Steve Schapel, Microsoft Access MVP

I have a standard Master/Detail situation with corresponding frmMaster and
subfrmDetail with linked fields.

In tDetail there is a Seq# field which is displayed as the leftmost field on
the subform.

tDetail has its own Autonumber ID primary key field.

I would like the Seq# to be automatically numbered when entering new
subform record data, starting at 1 and incrementing by one for a specific
Master record.

I tried using Autonumber, but Access won't let me have more than one
Autonumber field per table.

What is the best way to get what I want?

Thanks.

Gary