If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Form creates unwanted rows in table
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 a 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? |
#2
|
|||
|
|||
Form creates unwanted rows in table
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 "Sandroid" wrote in message ... 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 a 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? |
Thread Tools | |
Display Modes | |
|
|