Form creates unwanted rows in table

[Sandroid]

I've created an asset database which keeps track of computers, laptops
etc. There is one company which has many departments and every
department has many rooms. (surprise, surprise)

There are four tables: tables Department and Asset and two "middle"
tables DepartmentRoom and AssetRoom.
The table structures are as follows:
DEPARTMENT
id
name
dep_type
dep_num
etc...

ASSET
id
name
a_type
ip_num
vendorID
serial_num
etc...

DEPARTMENTROOM
id
name
depID (foreign key to department)

ASSETROOM
id
assetID (foreign key to asset)
roomID (foreign key to departmentroom)

Relationships are:
DEPARTMENT.id (1-many) DEPARTMENTROOM.depID
DEPARTMENTROOM.id (1-many) ASSETROOM.id
ASSET.id (1-many) ASSETROOM.assetID

I've created two forms: AssetForm and DepForm. There are subform in
both those forms. AssetFormSubform is used to pick department and room
for the current asset (there are two comboboxes). The source of
depCombo is DEPARTMENTROOM.depID (there is a query to DEPARTMENT table
so I can see the names instead of ID numbers) and roomCombo
DEPARTMENTROOM.name so both are bounded.

Likewise there is a subform in DepForm to add rooms in current
department.

Now the problem is that when I try to select department for current
asset (in AssetFormSubform) it creates new rows in DEPARTMENTROOM
table? I only want to select the existing department and room. AND
ONLY ASSETROOM SHOULD BE MODIFIED. The ASSETROOM table works (it fills
up automatically).

Now what? Is table design incorrect or what? shoud I use unbound
combos instead of bound ones?

 

[Jeanette Cunningham]

The asset main form would be based on a query using the asset table.
The subform where you choose Asset and Room, would be based on a query using
the AssetRoom table.
The link master and link child fields would use the field that has the Asset
ID.
You would put one combo to choose the room, as you have already chosen an
asset from the parent form.
You thus don't need an additional combo to choose an asset.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia