Cascading combo Boxes using Junction Table

[JFo]

As I have a number of many to many relationships in my database I am having trouble with this.

Basically I would like a combo box where a user selects a value (clientname) using this clientname afterupdate this should try and match the ClientID with the ClientID in junction table and match this to the SiteID in Junction Table. So whatever SiteIDs match the ClientID it should cascade and populate a second combo box on the form with the SiteName from the Site Table that matches the corresponding SiteID's from junction table.

I can cascade a combo box with one to many relationships, using 1 or 2 tables but cannot work out how to do this using Junction Table

Thanks in advance with my issue

Cheers

[June7]

This is either very simple or I really don't understand what you want to do. Include the Site table in the combobox RowSource query with a join to the junction table, jointype 'Show all records from junction table ...'

You might want to include the DISTINCT qualifier in the query. Must type this into the SQL view editor: SELECT DISTINCT SiteID, SiteName FROM ...

[JFo]

This could be what I am after except I don't understand what you mean. I can't find anywhere to write an SQL query and I can't find SQL view in Access 2010, furthermore I don't know how to write this query and how to populate it after it selects value from first combo box I thought you would have to use onupdate in first and rowsource in second but nothing seems to work for me? Thanks

[JFo]

Apologies I found SQL view and have tried to write a statement as follows

SELECT tblClients.ClientID, tblClientsSitesJunction.ClientID, tblClientsSitesJunction.SiteID, tblSites.SiteID, tblSites.SiteName
FROM tblSites INNER JOIN (tblClients INNER JOIN tblClientsSitesJunction ON tblClients.ClientID = tblClientsSitesJunction.ClientID) ON tblSites.SiteID = tblClientsSitesJunction.SiteID
WHERE (((tblClientsSitesJunction.ClientID)=[Forms]![frmMainMenu]![cmbClients]))
ORDER BY tblSites.SiteName;

This does not work unfortunately not sure how I write it

Thanks again

[June7]

Did you use the query designer to first build the query joining the two tables (looks like it with all those parens)? Then switch to SQL View to type in the DISTINCT qualifier.

Should not be joining all three tables, just two for each combobox.
Join Clients to junction table for first combobox.
Join Sites to junction table for second combobox.

AfterUpdate event of first combobox needs code to requery second combobox.

Check these tutorials
http://datapigtechnologies.com/flash...combobox2.html
http://datapigtechnologies.com/flash...combobox3.html

[JFo]

OK thanks for your reply you are steering me in right direction it seems however is not as easy for me. I am now using rowsource for first cmbClients


SELECT DISTINCT tblClients.ClientName
FROM tblClients INNER JOIN tblClientsSitesJunction ON tblClients.ClientID = tblClientsSitesJunction.ClientID
ORDER BY tblClients.ClientName;

I then put AfterUpdate code

Private Sub cmbClients_AfterUpdate()
Me.cmbSites.Requery
End Sub

Thirdly I put rowsource code for cmbSites

SELECT DISTINCT tblSites.SiteName
FROM tblSites INNER JOIN tblClientsSitesJunction ON tblSites.SiteID = tblClientsSitesJunction.SiteID
ORDER BY tblSites.SiteName;

However as you can see this does not cascade any input from first combo, I figure I am missing a line of WHERE variety something like

WHERE (((tblClientsSitesJunction.ClientID)=[Forms]![frmMainMenu]![cmbClients.ClientID]))

However this is incorrect SQL syntax and I don't know where to put it. Am I on the right track or have I got this all wrong, I tried in a number of ways but unfortunately got No where so far

Thanks again

[June7]

You need to include the ClientID field in the RowSource query of first combobox and the SiteID field in the RowSource of second combobox. Refer to the tutorial on multi-column comboboxes.

Then the WHERE clause should work.

[JFo]

thanks for your input and recommendations however I have been trying to do this for hours again today and cannot do it. Your videos show an old version of access I am using 2010 and they don't really do what I want and have no 3rd table involved, thanks but I still can't seem to match the junction table value and filter the 2nd combo box

[June7]

Can you provide your project for analysis? Make copy, remove confidential data, run Compact & Repair, zip if large, attach to post.

[JFo]

Sorry for delay

Here is my db files zipped. I started with 1.0 version with Junction table containing dual primary keys and have since been instructed that it may be better to instead have junction table with a singular unique junction primary key. Both versions I have not managed to get the cascading combo box working however would be great as I was thinking of starting this database with a series of cascading combo boxes where users can select from successive combo boxes and if option not available they can enter new data and if it is then they can print reports or view recorded data so in a way this would act as my main form for utilising the database and therefore would love to get it right

Thanks again for all your assistance


Attachment 4301

Attachment 4302

[June7]

Set comboboxes with:

Could there be clients in Clients table not yet associated with a site in the junction table? If not, the first RowSource is fine, else use the second.
cmbClients
RowSource: SELECT tblClients.ClientID, tblClients.ClientName FROM tblClients ORDER BY tblClients.ClientName;
or
RowSource: SELECT DISTINCT tblClientsSitesJunction.ClientID, tblClients.ClientName FROM tblClients RIGHT JOIN tblClientsSitesJunction ON tblClients.ClientID = tblClientsSitesJunction.ClientID ORDER BY tblClients.ClientName;
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0";2"

cmbSites
RowSource: SELECT DISTINCT tblClientsSitesJunction.SiteID, tblSites.SiteName FROM tblSites RIGHT JOIN tblClientsSitesJunction ON tblSites.SiteID = tblClientsSitesJunction.SiteID WHERE (((tblClientsSitesJunction.ClientID)=[forms]![frmMainMenu]![cmbClients])) ORDER BY tblSites.SiteName;
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0";2"

Setting these fields as compound primary key will assure that cannot duplicate a client/site pairing. However, compound keys can be a bit harder to work with as primary/foreign key field links. If this compound key will not be used as a link to another table, then not an issue. If will be, just have to decide if you want to go with it. Same goes for the clients/contact junction table.

You should consider splitting contact name into multiple fields if you want to be able to search/filter/sort on lastname, firstname. This will insure consistency in the way names are entered into table so don't have some people entering like lastname,firstname and others like firstname lastname.

[JFo]

Thanks June7, I was going round in circles all makes sense now. Now I know how to cascade combos using a junction table I can start preparing my entry forms and find out if the compound key needs to be used in other tables otherwise I will have to go with the unique junction primary key

Thanks again