2 autonumbers from 2 tbl's in 1 query

[Denise]

I have two tables that are combined in a query. The design
and join, etc. is as follows:

tblTenants
TenantID (PK - autonumber)
LastName
FirstName
BuildingID (Foreign key - duplicates ok)

tblApartments
AptID (PK - autonumber)
Apartment
TenantID (foreign key - dupes ok)
BuildingID (foreign key - dupes ok)

Join tblTenants.TenantID on tblApartments.TenantID

Join type: Include all records from tblTenants and only
those records from tblApartments where the joined fields
are equal.
Here's the question. In my query the recordset is
updateable. When I add a new tenant, that tenant gets
his/her autonumber assigned right away as it should be BUT
I can't enter an apartment unless I first move to another
record. Then when I move back to the formerly "new" record
I can enter an apt and the aptID autonumber does its thing.

Why doesn't it assign both autonumbers when a new record is
started? How can I remedy this?

Any help is tremendously appreciated!

dcw

 

[Jeff Boyce]

Denise

Another approach to what you've described would be to use a mainform and
subform. The mainform is bound to the tblTenant, while the subform is bound
to tblApaprtment, and linked with parent-child by the TenantID.

This way, you add a Tenant on the mainform, then, when you add info into the
Apartment subform, it "inherits" the mainform's key (TenantID).

Good luck!

Jeff Boyce
<Access MVP>