combo box based on previous combo box

[vicsaccess]

not sure where exactly would be the best place to post this so lets start here. Does anyone have any experiance with this or a sample database that i can view? I have-

several locations
each location has assets
each asset can have parts

now for my delima, each location does not have the same assets, each asset does not have the same parts. my form is based on a table that has locationID, AssetID and PartID, what i am trying to do is have a combo box for location, that locationID triggers assets of that location in a new combo box. that asset combo box triggers the parts of that asset. what i have tried is in the asset table is to add a field for locationID with all the locations that have that asset just listed one after another (10203040, each locationID is either 10 or 20 or 30 or 40), then based my asset combo box on a SQL with a like statement from the location CBObox to only show assets of that location. it works great except when there is multiple locations strung together, if only 30 then it brings up the 30 assets but if the asset if for 30 and 40 (3040) then it won't get it. not sure if the table field for locationID in asset should be number or text, i havn't worked with wildcards that much. my SQL for the combo box is

SELECT AssetT.AssetID, AssetT.LocationID, AssetT.Asset
FROM AssetT
WHERE (((AssetT.LocationID) Like [Forms]![TestF]![LocID]));

any direction would be appreciated, am i on the right path or way off.

[orange]

First approximation, based on your post

Location-->LocationHasAsset<--Asset---->AssetHasPart<----Part

[June7]

Give comboboxes a name different from the fields, like cbxLoc, cbxAsset. Then cbxAsset RowSource maybe simply:

SELECT AssetID, LocationID, Asset FROM AssetT WHERE LocationID = [cbxLoc];

[orange]

Get your tables and relationships designed and tested/vetted before forms.

[vicsaccess]

thanks June7, where's the wildcard aspect? i have the naming as you stated. what is the best way to put the values in the asset table, 102030 or 10,20,30 or 10*20*30.

[vicsaccess]

attached a sample of what i'm trying to do.

[June7]

LIKE operator and wildcard should not be needed because the filter criteria is a specific LocationID.

Each asset should have a record for each location they are associated with. Perhaps you need another table - tblAssetLocation.

I cannot view db until Nov 28. If you still need help then, bump this thread with a new post.

[vicsaccess]

thanks, here's what i ended up figuring out that worked-

SELECT AssetT.AssetID, AssetT.LocationID, AssetT.Asset
FROM AssetT
WHERE (((AssetT.LocationID) Like '*' & [forms]![TestF]![LocID] & '*'));

the problem was that each asset is not owned by each location so i believe i need a wildcard sort from location to only show assets of that location. so far its working but you have me intrieged with the new table. is that just a many to many relationship?

[June7]

Yes, that would be a many-to-many and a normalized structure. I did not mean to suggest that each asset is owned by each location. The junction table would have records that associate asset with whatever location(s) own that asset. A record for each pairing.

[vicsaccess]

ok, June7, I had it working but after researching your idea about using a many to many I think you are right and that may be the best solution. my next head scratcher is how to give the supervisor or who ever gets task with this, the best way to get the connections into the database. my ideal solution would be a form with a combo box on the top in the header based on the location, all the possible assets below in the form body with an unbound checkbox beside the asset. the individual selects the location from the combo, then checks all the assets that apply to that location and push a button that I put an insert command behind that would update the ID from the location and the ID from the asset into the new many to many table that we'll call LocAssetT. I've been playing with the idea but can't seem to make it work, anyone have any dealings or thoughts on this?

[June7]

Simplest approach is a form with two comboboxes - locations and assets. Bind form to table and comboboxes to fields. Enter a new record for each pair.

If you want to use checkboxes, how many assets are there? VBA code would have to loop through the unbound checkbox controls, save record with INSERT sql action. Exactly what have you done? Post code.

Whatever method, be sure to set the two fields as compound index so duplicate pairs will not be allowed.

[vicsaccess]

thanks June7, as for the number, these two links are just the first in a string that starts with location then the assets then the components of the assets then the parts of the component and such. its a method to start with a large object and link the repair down to the individual part or piece. locations will be about 7 and it works its way down to the part that might have thousands. that's the reason I was wondering about the form with checkboxes next to the item that I could then run a insert sql action off the form. not sounding good though. as for the code, not that far yet, I am still trying to get the location and list of assets both on a form that would allow me to do something with but everytime I tried adding an unbound checkbox on the form and clicking on it it would check all the check boxes. not sure if this would be worth the time, I can update the tables off of excel's if need be but that would not allow for easy future updates to the locations assets, components and parts. any ideals that I can look into or databases out there that I could study would be appreciated.

[June7]

Sounds a manufacturing or assembly type database - IMO, one of the more difficult to build. This a fairly common topic - have you searched forum?

[vicsaccess]

yes but to no avail. suggestions on key words. I tried many to many update and didn't get anything good

[vicsaccess]

ok, progress, I added a yes/no box to the Part table, based the continuous form on partT with and unbound combo based on componentID which is a sub field of PartT. short story I got a form with all my assets and a check box next to it but now when I run the insert code it only updates the first record it finds. insert and VBA for that matter are new to me so please look and show me how to grab all the records with the checkbox "update" checked, is there a way to loop it?

Private Sub btnupdate_Click()
    On Error Resume Next
    If update = True Then
    Dim strsql As String
    strsql = "INSERT INTO [comppartT]( [componentID], [partID] )"
    strsql = strsql & "SELECT [copy of 8partT].[componentID], [copy of 8partT].[partID] "
    
    DoCmd.SetWarnings False
    DoCmd.RunSQL (strsql)
    DoCmd.SetWarnings True
    
    End If
End Sub