Auto Increment Detail Seq#

  • Thread starter Thread starter Gary Schuldt
  • Start date Start date
G

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
 
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.)
 
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

Steve Schapel said:
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


Gary said:
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
Click to expand...
Click to expand...
 
Back
Top