cascading combo boxes with a junction table

[reb0101]

hey all,
I have either given up or taken a break, not sure with on table / relationship structure with considerable help from both gemma-the-husky and Mihail.
Thank you both as much for your patience as technical help.

I'm jumping to the forms development and going to try what I need via queries and SQL.
I'm trying to do a series of cascading combo boxes which have worked out fine, right till I hit the first junction table.

I will include the VBA code below as well as a screenshot but here's how it goes.
REGION cascades down to COUNTRY cascades down to PORT and then to CARRIER.
This is for a shipping program.
When it gets to PORT and are trying to cascade to the various CARRIER's thats where it hits a junction table of PORT_CARRIER.
Hopefully the screen shot will better explain it.

Here is REGION to COUNTRY:

Me.cboCountry.RowSource = "SELECT COUNTRY.CNTRY_ID, COUNTRY.COUNTRY FROM COUNTRY " & _ " WHERE REG_ID = " & Nz(Me.cboRegion) & _
" ORDER BY COUNTRY"
Me.cboCountry = Null


COUNTRY to PORT :

Me.cboPort.RowSource = "SELECT PORT.PORT_ID, PORT.PORT FROM PORT " & _
" WHERE CNTRY_ID = " & Nz(Me.cboCountry) & _
" ORDER BY PORT"
Me.cboPort = Null


PORT to CARRIER:

Me.cboCarrier.RowSource = "SELECT PORT_CARR.PORT_ID, PORT_CARR.CARR_ID FROM PORT_CARR " &_ " WHERE PORT_ID = " & Nz(Me.cboPort) & _
" ORDER BY CARR_ID"
Me.cboCarrier = Null

PORT to CARRIER is where the problem is.
It populates the combo box, but with the ID numbers instead of actual CARRIER names.
(the Junction table are two PK fields and are lookups to PORT in PORT table and CARRIER in CARRIER table.)

Is there a magic spell for cascading combo boxes when you hit a junction table?

 

[Mihail]

Let's return to your database design.
After we'll set as better as we can the relationships for the database the queries will become a breeze.
And, I think, no need for other threads. Let's try to keep things in only one place (a good normalization ).

 

[reb0101]

thanks Mihail,
In America and so a few hours behind Romania.
Going to get some sleep and will check in first thing in the morning

 

[Mihail]

Good night !

 

[JHB]

Try the below:

Me.cboCarrier.RowSource = "SELECT Carrier.Carrier " _
& "FROM Carrier INNER JOIN Port_Carr ON Carrier.Carrier_ID = Port_Carr.Carr_ID " _
& " WHERE PORT_ID = " & Nz(Me.cboPort) _
& " ORDER BY CARR_ID"

 

[reb0101]

JHB,
thanks for the effort but it didn't work.
Before it was giving me the id numbers, now it's just blank when I click teh combo box, it's like the values are there but not displayed.
Then oddly enough, when I try and close the form I get a Parameter Query msg box with the name of a carrier, in this case MAEU and an input box asking for a value?
I may and try and mess with the code for an hour or so but after that I guess I need to put it on hold and work on other stuff.
Thanks for the effort though

 

[JHB]

Ok - by me it works.

 

[reb0101]

JHB,
turns out you were right. It worked for me, just can't 'see' it.
Can't figure that one out for the life of me but now trying to get the selected values into another table.

 

[JHB]

No ideas what happen, could it be because the column width is set to 0?
Are you able to post your database with some sample data?

 

[nanscombe]

You started by bringing back 2 columns of data:

Me.cboCarrier.RowSource = "SELECT [COLOR="Red"]PORT_CARR.PORT_ID[/COLOR], [COLOR="SeaGreen"]PORT_CARR.CARR_ID[/COLOR] FROM PORT_CARR " &_ " WHERE PORT_ID = " & Nz(Me.cboPort) & _
" ORDER BY CARR_ID"
Me.cboCarrier = Null

Now you are only bringing back one column of data.

Me.cboCarrier.RowSource = "SELECT [COLOR="Red"]Carrier.Carrier[/COLOR] " _
& "FROM Carrier INNER JOIN Port_Carr ON Carrier.Carrier_ID = Port_Carr.Carr_ID " _
& " WHERE PORT_ID = " & Nz(Me.cboPort) _
& " ORDER BY CARR_ID"

I bet your Listbox still says:

Number of Columns: 2
Column Widths: 0;x

You need it to be:

Number of Columns: 1
Column Widths: x

 

[reb0101]

JHB, as far as the test data, just about to post that ....5 minutes.

 

[reb0101]

I'm about a day away from Filemaker PRO!
So I finally get the cascading combo boxes to work, the last step to get the final one to pull from a Junction table courtesy of JHB even though the last box seems a little shy and doesn't want to show itself, but first things first.
The goal here is to pull vales from REGION, filtering down to COUNTRY, filtering down to PORT and then PORT filtering down to available CARRIER's.
The PORT to CARRIER's was the tricky part from a junction table that technically workd, just does not display (for the eye to see) the CARRIER values.
You can see this in screen shot 3.
I wish that was the only problem
REGION, COUNTRY and PORT and even CARRIER are all their own separate tables, all one to one relationships except for PORT and CARRIER with a many to many relationship and junction table.
Each of the selected values are to be then inserted into another table, tblSHIPPING.
I have (so far) an ID field, and the following fields;
ORIGIN_REGION
ORIGIN_COUNTRY
ORIGIN_PORT
CARRIER.
I will eventually add
DESTINATION_REGION
DESTINATION_COUNTRY
DESTINATION_PORT
If I can't get it to work with ORIGIN then destination won't really matter.
When the form opens I set ENABLE to False for all but REGION and also set the RowSource to " " which is empty.
REGION enables COUNTRY and sets it's RowSource, and then on down from there.
I can get one (1) and only ONE new record into the tblSHIPPING.
When I click a button to add a new record, the minute I select and value for REGION it 'jumps' back to record 1.
I just added 3 test records.
In screen 4 you will see the result.
First Value selected was Middle East ....I continued to add Country, Port and even Carrier.
The DEST_REGION, DEST_COUNTRY and DEST_PORT are empty as explained before.
I then add 2 more records, ASIA and CENTRAL AMERICA.
Opened the tables to see what 'took' and it correctly saves the ORIGIN COUNTRY but I can clearly see that the COUNTRY and PORT selections I made for the last entry, CENTRAL AMERICA only got saved in the first records fields.
Any idea what is going on here?

 

[nanscombe]

Any idea what is going on here?

Have you seen Post 10? For your missing carrier at least.

 

[reb0101]

yes, just saw it.
If I change from Column Count 2 to 1, then I get nothing.
Before, it was working from code JHB gave me and I could tell by selecting a CARRIER and it show up in the table.
But I could not see the value in the combo box, it was there, but you could not see it.
I believe screen shot 2 or 3 from recent post shows it

 

[nanscombe]

It's late and I'm looking at the problem from the wrong way around.

I take it you're trying to store Carrier_ID?

Me.cboCarrier.RowSource = "SELECT Carrier.Carrier " _
& "FROM Carrier INNER JOIN Port_Carr ON Carrier.Carrier_ID = Port_Carr.Carr_ID " _
& " WHERE PORT_ID = " & Nz(Me.cboPort) _
& " ORDER BY CARR_ID"

What's missing?

Me.cboCarrier.RowSource = "SELECT [COLOR="Red"]Carrier.Carrier_ID[/COLOR], Carrier.Carrier " _
& "FROM Carrier INNER JOIN Port_Carr ON Carrier.Carrier_ID = Port_Carr.Carr_ID " _
& " WHERE PORT_ID = " & Nz(Me.cboPort) _
& " ORDER BY CARR_ID"

Trying to save the Carrier (Text) into the Carrier_ID (Long) field probably isn't going to work.

 

[reb0101]

BINGO !
Thank you Nigel and thank you JHB.
I just tried it and it shows up but still need to work with enabling it after Port Selected.
Again, thank you both !

 

[JHB]

I'm little confused - does it work or does it not, if not, what is missing?

 

[nanscombe]

I believe that there were two problems:

1) The query was only returning CARRIER (TEXT) and not CARRIER_ID (LONG) so it was trying to populate a Long field with a Text value, so it wouldn't save.

2) The query was only returning one column and the Listbox was set to show two columns, the first one with width 0cm, so you wouldn't see it.



I have attached a demo of how I would tackle this task. In fact I once had to write something similar for tracking international shipments.

 

[reb0101]

JHB and nanscombe,
Yes it works, or for the most part.
It was pulling the correct data into Carrier but it was not displaying, could not see it.
So got that solved and moved on.
The next problem that I ran into was having the form that the cascading combo boxes on was bound to a table.
All kinds of issues so I removed the record source, made the form unbound and used VBA to insert
the selections into the SHIPMENTS table.
This worked great initially. I could have a Submit button that would insert the values into the ORIGIN Region, Country and Port and then another Button that would then use the same combo boxes to insert values into DESTINATION Region, Country and Port.
Only one problem on that one, it was inserting the ID numbers and not the actual Regions, Country's or Ports selected Values.
I looked at what you did nanscombe and this is perfect as well, even imported some data into those tables but yours, like mine, is storing the ID and not values.
What I had hoped to do is have text fields that will display the selections as the user makes them.
They select Region and a row of text boxes will display these values (Country, Port, etc) as more of a confirmation of the choices they have made, a 'breadcrumb' trail of the many selections they will be making as they set up a shipping record.
If the SHIPMENTS table is taking the ID numbers then all of those fields in the SHIPMENTS table have to be lookup fields.
The text fields that I just described become bomb boxes when you try and drag them to the form.
I am enclosing a file and you can see for yourself.
I had hoped that it would be an easy fix. Now that I got (actually you two) got it to cascade and even accurately capture the Carrier from junction table.
If I change the bound column on any of these cascading text boxes to capture the value and not the ID then it all craps out.
You can see from the access file enclosed.
Meanwhile Mihail was kind enough to create a table structure that I need to get back to.
I did not look closely enough at it at first but for the overall structure it is certainly better than I could have done.
With all of the time I have spent on this, pretty much the whole day yesterday fighting with these combo boxes I have to take a break and get back to another project and finish it up today.
It got sidelined by this.
So hope and certainly need to be back on this tomorrow.
Thanks to both of you for all your help

 

[nanscombe]

You would normally store the ID in the table, rather than the ID.

When you want to view the data you would create a query linking the main shipment table with the other tables; Region, Country, Port etc to show the values.