Cascading combos with additional data

[Guest]

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]

 

[Guest]

I have tried using a query that pulls only the available assets, meaning they
don't have a "username" attached to the record.

I also tried pulling the records straight from the table "Assets" with a sql
statement bringing only usernames with a null value.

I want to be able to choose for example "Laptop" from the first combo box
"AssetCategoryID".

Then choose "Dell D505" from the second combo box "Model"

Then the other records information like serial number and asset tag, should
appear but they are not. Everything comes from the same table or query. All
it does is add another record instead of selecting the ones without a
username and assigning it to the person.

Here is some code for the forms

Main "Employees" form:
Option Compare Database
Option Explicit
Private Sub Form_Activate()
Me![Add Asset Subform].Requery
End Sub
Private Sub Form_Load()
If Me.OpenArgs = "GotoNew" And Not IsNull(Me![Username]) Then
DoCmd.DoMenuItem acFormBar, 3, 0, , acMenuVer70
End If
End Sub


Employees Add Asset Subform
Option Compare Database
Private Sub AssetCategoryID_AfterUpdate()
Me.Model.Requery
End Sub

 

[Guest]

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));

 

[Guest]

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));