Composite Primary Key with an ID and Index

I would like to create a composite primary key using two fields: a random
integer ID, and a "line number" index. I want the index to be incremental,
start with 1, and reset for each unique ID from the first field. For example:

ID INDEX
5127 1
5127 2
5127 3
6435 1
9921 1
9921 2

Is this possible in Access?

The context is a project management database. Each project has a unique ID.
Projects and their descriptions are stored in one table. Progress updates
are stored in a second table. I would like to be able to incrementally
identify the updates to for each project.

Thanks in advance for any help!

--Pete

On Tue, 18 Aug 2009 13:31:01 -0700, Pete
wrote:

I would like to create a composite primary key using two fields: a random
integer ID, and a "line number" index. I want the index to be incremental,
start with 1, and reset for each unique ID from the first field. For example:

ID INDEX
5127 1
5127 2
5127 3
6435 1
9921 1
9921 2

Is this possible in Access?

The context is a project management database. Each project has a unique ID.
Projects and their descriptions are stored in one table. Progress updates
are stored in a second table. I would like to be able to incrementally
identify the updates to for each project.

Thanks in advance for any help!

--Pete

The simplest way to do this is in VBA code on a Form (bound to the projects
table) with a Subform (bound to the updates table). In the subform's
BeforeInsert event use code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Me![INDEX] = NZ(DMax("[INDEX]", "ProgressTable", "[ID] = " & Me!ID)) + 1
Me.Dirty = False ' immediately write the record to disk
End Sub

--

John W. Vinson [MVP]

I'll give it a try -- thank you very much!

"John W. Vinson" wrote:

On Tue, 18 Aug 2009 13:31:01 -0700, Pete
wrote:

I would like to create a composite primary key using two fields: a random
integer ID, and a "line number" index. I want the index to be incremental,
start with 1, and reset for each unique ID from the first field. For example:

ID INDEX
5127 1
5127 2
5127 3
6435 1
9921 1
9921 2

Is this possible in Access?

The context is a project management database. Each project has a unique ID.
Projects and their descriptions are stored in one table. Progress updates
are stored in a second table. I would like to be able to incrementally
identify the updates to for each project.

Thanks in advance for any help!

--Pete

The simplest way to do this is in VBA code on a Form (bound to the projects
table) with a Subform (bound to the updates table). In the subform's
BeforeInsert event use code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Me![INDEX] = NZ(DMax("[INDEX]", "ProgressTable", "[ID] = " & Me!ID)) + 1
Me.Dirty = False ' immediately write the record to disk
End Sub

--

John W. Vinson [MVP]