Adding records - automatic numbering with two primary keys

N

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?
 
J

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
 
N

Natalie

Thank you! This works GREAT!
--
Natalie


John W. Vinson said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top