subform combobox rowsource query to maintain referential integrity in a junction table (1 Viewer)

[hkc94501]

I think I know what I need but i don't know enough SQL to write it.

I have a many to many relationship between assets and vulnerabilities (and a bunch more but one is sufficient) and a junction table assets_vulnerabilities.
A vulnerability may apply to multiple assets and an asset can have multiple vulnerabilities.

My main form creates vulnerability records and part of that is specifying what assets the vulnerability applies to.
I am using a subform with a continuous form based on a combobox to populate the junction table.
My first attempt used the list of assets to populate the checkbox but that allowed multiple entries with the same (asset, vulnerability) pair; a violation of referential integrity. Which brings me to the SQL I don't know how to write.
In English what I want is, Given the current vulnerability (v) find all assets (a) for which (a,v) is not in the junction table.

Many thanks,
Hank Cohen

 

[arnelgp]

1.create first a query (query1), on both assets and vulnerability table.
note there is no Join in them:

SELECT asset_table.asset, vulnerability_table.vulnerability
FROM asset_table, vulnerability_table
ORDER BY asset_table.asset, vulnerability_table.vulnerability;

2.from above query create another query that
Join the above query (query1) with assets_vulnerabilities table:

SELECT Query1.asset, Query1.vulnerability
FROM Query1 LEFT JOIN assets_vulnerabilities ON (Query1.vulnerability = assets_vulnerabilities.vulnerability) AND (Query1.asset = assets_vulnerabilities.asset)
WHERE (((assets_vulnerabilities.asset) Is Null))
ORDER BY Query1.asset, Query1.vulnerability;


this will be your final query.
you will then need to "filter" further the final query to only
confine to a specific vulnerability.

 

[hkc94501]

arnelgp,
I am immensely thankful for your help. I thought this would be a one-liner. Looking at your answer I realize that the question was hopeless for a newbie like myself.

I created the first query without a problem. [Actual field names from my tables] saved as qryAssetVuln
SELECT Assets.ASidx, Vulnerabilities.[Vidx]
FROM Assets, Vulnerabilities
ORDER BY Assets.ASidx, Vulnerabilities.[Vidx];

This worked fine but the second query gave me a syntax error and I can't see why.

SELECT qryAssetVuln.ASidx,qryAssetVuln.Vidx
FROM qryAssetVuln LEFT JOIN assets_vulnerabilities
ON (qryAssetVuln.Vidx = assets_vulnerabilities.JVuln) AND (qryAssetVuln.ASidx = assets=vulnerabilities.JAsset)
WHERE (((assets_vulnerabilities.JAsset) is null))
ORDER BY qryAssetVuln.ASidx, qryAssetVuln.Vidx;

Access says "Syntax error on JOIN operation" and highlighted the word in red.
I've checked all the parentheses and they match, the Join is between a query and a table and I think that should work although the Access reference manual says it should be between a table and a table. Do I need to save the results of the first query in a temporary table and run the second query against that?

Thanks again,
Hank

 

[arnelgp]

ON (qryAssetVuln.Vidx = assets_vulnerabilities.JVuln) AND (qryAssetVuln.ASidx = assets=vulnerabilities.JAsset)

you have error on the Line?
should be:

...AND (qryAssetVuln.ASidx = assets_vulnerabilities.JAsset)

 

[hkc94501]

Thank you, thank you, thank you.
Looks like I mistook the dash which is on the = key for the underscore. Weird two finger typo.
It's working now.

 

[hkc94501]

Actually it didn't really work. No syntax errors and it did generate a table but the table was simply the cross product of the Assets and Vulnerabilities tables. This was the same for both quereys. Maybe I wasn't clear about this. I need a list of assets for which there is no existing row in the junction table with the junction table vulnerability equal to the current vulnerability from the parent form.. From a set theoretical point of view I think this is a simple query but I don't know how to translate it into SQL.

 

[bastanu]

Is this what you're trying to achieve?

Cheers,
Vlad

 

[hkc94501]

Is this what you're trying to achieve?

Cheers,
Vlad

Vlad,
Thanks for looking at this.
Your example doesn't exactly achieve what I want. I want to have a many to many relation defined between the assets assets_vulnerabilities and vulnerabilities tables and to force referential integrity. This means that a particular asset can only be assigned once to a particular vulnerability.
Your implementation allows the same asset to be assigned to the same vulnerability multiple times.

Thanks for your help,
Hank

 

[bastanu]

Hi Hank,
If you enforce the use of the subform to add records to the junction table you shouldn't be able to add the same asset to the same vulnerability multiple times. I added Asset3 to Vulnerability1 and that is now complete (meaning all three assets are assigned to it) so the combo is empty.

 

[Pat Hartman]

1. m-m relationships require a junction table and are implemented via a subform.
2. If the m-m relationship is unique, you need to create a two field unique index on the junction table. You could remove the autonumber and make the two fields the PK. However, if the junction table has child tables (sometimes they do), then it is better to have an autonumber PK and a unique index on the two FK's to implement the business rule. To create a multi-column unique index, you MUST use the indexes dialog.

1. Give the index a name and define it as unique and choose the first column
2. On the next line, leave the name blank and choose the second column
3. Continue for additional columns if more than two are required. Access allows up to 10. SQL Server allows 32 last time I checked.

 

[hkc94501]

Hi Hank,
If you enforce the use of the subform to add records to the junction table you shouldn't be able to add the same asset to the same vulnerability multiple times. I added Asset3 to Vulnerability1 and that is now complete (meaning all three assets are assigned to it) so the combo is empty.
View attachment 93633

Vlad,
The referential integrity problem does not happen when the junction table fields are initially created but can occur if you change the value of a previously set record. Still this is a big step forward in my understanding. I am studying your queries very carefully.

 

[hkc94501]

I think I have found the fix. In the subform on current event handler the AssetID.RowSource property needs to be set to "qryNewAssetCurrentVulnerability for all records, not only new records. This will prevent a user from going to a previously set record and choosing a duplicate asset.
But it introduces a new problem. If I use a continuous form instead of a datasheet I loose all of the other combox.value fields. They come up blank on the form.
Thank you again for your help.

 

[bastanu]

Hank,

I don't think that is going to work, as the problem you are having is the expected outcome. If you want to prevent the users from creating a duplicate you can set the unique combined index at the junction table level as Pat suggested earlier or you can add validation to the BeforeUpdate event of the AssetID combo by checking the value of a dCount for the assetId, and if >0 issue a message box warning and a Cancel to prevent it.

If dCount("*","tAsset_Vulnerability","[VulnerabilityID]=" & Me.VulnerabilityID & " And [AssetID]=" & Me.AssetID)>0 Then
    Msgbox "You already have this asset attached to this vulnerability!",vbCritical
    cancel=True
End IF

Cheers,
Vlad

 

[hkc94501]

Vlad,
Getting Access to enforce referential integrity was pretty easy. It was just a matter of defining a many to many relation between the tables and requesting referential integrity.
However, once that is done it will raise a runtime error if the user actually tries to add a duplicate.
This is the reason for re-querying the combo box to restrict the choices so that they cannot add a second instance.
That is what you helped with, tremendously. Your SQL is perfect.
But I ran into an insurmountable problem trying to do all this on a continuous form.
Turns out that all of the instances of a combobox on a continuous form share many of the same properties. Specifically, they share the recordsource property. So when you change the recordsource to restrict choices it applies to all checkboxes on the form and if the value of the checkbox is a restricted value the checkbox will appear blank.

This thread from Substack describes the problem. https://stackoverflow.com/questions...ce-for-combo-box-in-continuous-form-in-access

After beating my head on the wall for two days I have decided to give up on the continuous form. The tentative solution suggested on the Substack thread seems too unreliable and it also has the problem that all of the instances of a textbox will also share properties so stuffing the checkbox value into a textbox will just replicate across all instances, not what I want.

Thanks again for your valuable help!

 

[bastanu]

Hi Hank,
That is why my initial solution has the combo box row source include all assets (qryAllAssets) and you change it to qryNew... only when it gets the focus. On lost focus you reset it to all so you avoid seeing the blanks in the other rows. Have you tried that?
Cheers,
Vlad

 

[arnelgp]

here is another sample using queries i posted (post #2).

 

[Pat Hartman]

Getting Access to enforce referential integrity was pretty easy. It was just a matter of defining a many to many relation between the tables and requesting referential integrity.

As I suggested in #10. If you don't want the user to get the Access error message, you need to add code in the on Error event to trap the error and give the user a message that he will understand.