Cascading combos with additional data

[Christopher]

I have two cascading combo boxes, "Category" and "Model" on a subform in the
form "Employees". These pull from a query named "Available Assets" that list
assets available to choose from. Once an asset is chosen it is assigned to a
username.

The problem I have is that once the two combos are selected the rest of the
assets details to not populate the rest of the form, things like serial,
asset tas, etc.

Another problem is that instead of taking an asset from the "Available
Assets" query it adds a new record. It should only pull from the query, not
add to it.

 

[John Vinson]

I have two cascading combo boxes, "Category" and "Model" on a subform in the
form "Employees". These pull from a query named "Available Assets" that list
assets available to choose from. Once an asset is chosen it is assigned to a
username.

The problem I have is that once the two combos are selected the rest of the
assets details to not populate the rest of the form, things like serial,
asset tas, etc.

Another problem is that instead of taking an asset from the "Available
Assets" query it adds a new record. It should only pull from the query, not
add to it.

What's the Recordsource of the form? If the combo boxes are bound to
fields in the Form's recordsource, that's exactly what they SHOULD do:
just update those fields. The serial, asset tas (tag??), etc. should
NOT be stored redundantly in a second table, as a rule; even if you do
have a good reason to do so, just selecting a value from a combo box
won't do so.

Again... what are the RowSources of the combos? What is the
Recordsource of the form? Where are the serial, asset tag, etc.
stored? Do you want the combos to *find an existing record* and
display it, or change the value in an existing record? And what do you
mean by "taking an asset from" - do you want to delete a record from
some other table?

John W. Vinson[MVP]

 

[Christopher]

I just got the form to display all the info from the table. But it still
adds a record instead using a record from the table. This is the rowsource
for the combos.

AssetCategoryID=SELECT [Asset Categories].AssetCategory, [Asset
Categories].AssetCategoryID, Assets.AssetCategoryID, Assets.Username FROM
[Asset Categories] RIGHT JOIN Assets ON [Asset
Categories].AssetCategoryID=Assets.AssetCategoryID GROUP BY [Asset
Categories].AssetCategory, [Asset Categories].AssetCategoryID,
Assets.AssetCategoryID, Assets.Username HAVING (((Assets.Username) Is Null));

Model=SELECT Assets.Model, Assets.AssetCategoryID, Assets.Username FROM
Assets WHERE (((Assets.AssetCategoryID)=Forms!Employees![Add Asset
Subform].Form!AssetCategoryID) And ((Assets.Username) Is Null));

 

[Christopher]

I just got the form to display all the info from the table. But it still
adds a record instead using a record from the table. This is the rowsource
for the combos.

AssetCategoryID=SELECT [Asset Categories].AssetCategory, [Asset
Categories].AssetCategoryID, Assets.AssetCategoryID, Assets.Username FROM
[Asset Categories] RIGHT JOIN Assets ON [Asset
Categories].AssetCategoryID=Assets.AssetCategoryID GROUP BY [Asset
Categories].AssetCategory, [Asset Categories].AssetCategoryID,
Assets.AssetCategoryID, Assets.Username HAVING (((Assets.Username) Is Null));

Model=SELECT Assets.Model, Assets.AssetCategoryID, Assets.Username FROM
Assets WHERE (((Assets.AssetCategoryID)=Forms!Employees![Add Asset
Subform].Form!AssetCategoryID) And ((Assets.Username) Is Null));