numbering multiple key fields

[Hydra]

I have tables A, B, and C, each one to many, so my key fields look like
A
1
2

B
1,1
1,2

C
1,1,1
1,1,2
1,1,3
1,2,1
1,2,2
1,2,3

So the primary key for B is two fields and the primary Key for C is three
fields.

I have a form on A with B and C as subforms. I would like B and C to
autonumber as new records are added. How do I go about this?

 

[Jeff Boyce]

Migrating your primary keys from parent to child (to grandchild) tables
makes it faster to find all the grandchildren of a given parent.

But keeping all those sync'd can be problematic.

Another approach is to use a (single field) primary key in each table, then
use a foreign key field that points back "up" ONLY to that record's
predecessor. That way, you only are ever keeping track of one level, rather
than 3 or more...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Hydra]

Bah!

This should be easy. It's a common enough problem. I seem to recall "another
product" that managed this automatically, unfortunately, I'm stuck with this.

I think I need a query attached to the on enter event of the title field. It
looks up the previous record witht he same parent/grandparent and increments
it.

 

[Jeff Boyce]

I've not run across an 'easy' (or automatic) way to do that in Access. I
don't recall if I've even seen a way in SQL-Server.

It looks like you get to "roll your own" on this one. But consider posting
back how you solved this, because others will surely be looking for a way to
do it sometime...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

I have a form on A with B and C as subforms. I would like B and C to
autonumber as new records are added. How do I go about this?

You should be able to use each subform's BeforeInsert event, e.g.

Private Sub Form_BeforeInsert(Cancel as Integer)
' B subform
Me!B_ID = NZ(DMax("[B_ID]", "[TableB]", "[A_ID] = " & Me!A_ID)) + 1
End Sub

Private Sub Form_BeforeInsert(Cancel as Integer)
' C subform
Me!C_ID = NZ(DMax("[C_ID]", "[TableC]", ""[A_ID] = " & Me!A_ID _
& " AND [B_ID] = " & Me![B_ID])) + 1
End Sub

assuming that the three fields in the third table are named A_ID, B_ID and
C_ID respectively.