Adding records - automatic numbering with two primary keys

[Natalie]

I have two tables: one is for addresses and has one primary key [User ID] the
other is for tenants at the address which has a compound primary key
consisting of two fields [User ID] + [Tenant ID] in the tenant table. When I
create a new record in the address form I want it to automatically update the
[Tenant ID] to the next available Tenant ID number. For example:

Addresses Tenants
[User ID] [User ID] [Tenant ID]
55 55 1
55 2
55 3
56 56 1
56 2
57 57 1
57 2
57 3

So if I add a new form, it will automatically add the next [User ID] but I
also want it to add the next available [Tenant ID] to the tenant field and I
want it to start at 1 if there are no previous tenants.

Any ideas?

 

[John W. Vinson]

I have two tables: one is for addresses and has one primary key [User ID] the
other is for tenants at the address which has a compound primary key
consisting of two fields [User ID] + [Tenant ID] in the tenant table. When I
create a new record in the address form I want it to automatically update the
[Tenant ID] to the next available Tenant ID number. For example:

Addresses Tenants
[User ID] [User ID] [Tenant ID]
55 55 1
55 2
55 3
56 56 1
56 2
57 57 1
57 2
57 3

So if I add a new form, it will automatically add the next [User ID] but I
also want it to add the next available [Tenant ID] to the tenant field and I
want it to start at 1 if there are no previous tenants.

Any ideas?

Very clear question with fieldnames... thank you!

I presume you have a Form based on User with a Subform based on Address? If
so, open the Address subform; view its Properties; and on the Events tab
select the "Before Insert" event. Click the ... icon by it and choose Code
Builder and enter the following code:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me![Tenent ID] = NZ(DMax("[Tenent ID]", "TenentsTable", _
"[User ID] = " & [User ID])) + 1
End Sub

 

[Natalie]

Thank you! This works GREAT!
--
Natalie

I have two tables: one is for addresses and has one primary key [User ID] the
other is for tenants at the address which has a compound primary key
consisting of two fields [User ID] + [Tenant ID] in the tenant table. When I
create a new record in the address form I want it to automatically update the
[Tenant ID] to the next available Tenant ID number. For example:

Addresses Tenants
[User ID] [User ID] [Tenant ID]
55 55 1
55 2
55 3
56 56 1
56 2
57 57 1
57 2
57 3

So if I add a new form, it will automatically add the next [User ID] but I
also want it to add the next available [Tenant ID] to the tenant field and I
want it to start at 1 if there are no previous tenants.

Any ideas?

Very clear question with fieldnames... thank you!

I presume you have a Form based on User with a Subform based on Address? If
so, open the Address subform; view its Properties; and on the Events tab
select the "Before Insert" event. Click the ... icon by it and choose Code
Builder and enter the following code:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me![Tenent ID] = NZ(DMax("[Tenent ID]", "TenentsTable", _
"[User ID] = " & [User ID])) + 1
End Sub