Default Value Autoincrement within a Subform

[Mike]

I have a form that has an embedded subform (Datasheet View) that are
linked based on a 1-many ID field. I have a field (SET) in my embedded
subform that I want to Autoincrement starting with 1...and adding 1 to
every new record.

The Autoincrement needs to be separate for each ID. For example when I
go to the form and enter a new record in the main form, the SET field
should start over at 1 as a default and continue (+1) for each new
record in the table that has many records related to the 1 table based
on ID.

My question is separate from Using the Dmax value
(me.SET=nz(dmax("SET","tbl_catch"),0)+1 ) in a table because i want the
autoincrement to start from 1 in the subform with each new record.

How do I do this?

Thanks for your help.

Mike

[Wayne Gillespie]

On 22 Jun 2006 12:02:55 -0700, "Mike" <mi***@upperskagit.com> wrote:

I have a form that has an embedded subform (Datasheet View) that are
linked based on a 1-many ID field. I have a field (SET) in my embedded
subform that I want to Autoincrement starting with 1...and adding 1 to
every new record.

The Autoincrement needs to be separate for each ID. For example when I
go to the form and enter a new record in the main form, the SET field
should start over at 1 as a default and continue (+1) for each new
record in the table that has many records related to the 1 table based
on ID.

My question is separate from Using the Dmax value
(me.SET=nz(dmax("SET","tbl_catch"),0)+1 ) in a table because i want the
autoincrement to start from 1 in the subform with each new record.

How do I do this?

Thanks for your help.

Mike

Add a WHERE clause as the third argument of the DMax.

Me.SET=Nz(DMax("SET","tbl_catch","[ID]=Forms!NameOfMainForm!ID"),0)+1

This will provide a separate result for each ID.

I would not be using "SET" as a field name as it is a reserved word in SQL and
may cause conflicts down the line.

Wayne Gillespie
Gosford NSW Australia