Auto Increment composite key

I have two datasheet forms, one linked to the other using a textbox link.
The fist datasheet form I have an auto increment field, and working fine.
In the second datasheet form I would also like a auto increment number.
The second form uses a composite key so I don't get duplicate numbers.

Table 1, auto increments the number
01
02
03
04

Table 2 is a composite key from Table 1 & 2
Two fields
01 01
01 02
01 03
02 01
02 02
02 03
These are two seperate fields in table two. They are linked by the first
field from table 1.
I have both fields as a composite key.
I would like to auto increment the second field in table 2. (if possible)

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200906/1

On Sun, 07 Jun 2009 01:28:46 GMT, "chopper7898 via AccessMonster.com"
u43982@uwe wrote:

I have two datasheet forms, one linked to the other using a textbox link.

What is a "textbox link"?

The fist datasheet form I have an auto increment field, and working fine.
In the second datasheet form I would also like a auto increment number.
The second form uses a composite key so I don't get duplicate numbers.

Table 1, auto increments the number
01
02
03
04

Table 2 is a composite key from Table 1 & 2
Two fields
01 01
01 02
01 03
02 01
02 02
02 03
These are two seperate fields in table two. They are linked by the first
field from table 1.
I have both fields as a composite key.
I would like to auto increment the second field in table 2. (if possible)

The simplest way to do this is to use a Form (single, not datasheet) based on
table 1, with a Subform based on table 2. Let's call the two fields Table1ID
and Table2ID. You'ld use Table1ID as the Master/Child Link Field of the
subform (to populate it into new records on the subform). In the subform's
Beforeinsert event put code to increment the ID:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!Table2ID = NZ(DMax("[Table2ID]", "[Table2]", _
"[Table1ID] = " & [Table1ID])) + 1
End Sub

This will look up the largest existing value of Table2ID for the currently
selected Table1ID; if there is none the NZ() function will return 0. The +1
increments the largest existing value by one to assign a new incremented
value.
--

John W. Vinson [MVP]