Using MS Access, I have a table called "Locations" and a table called "Routes". I want to create a form that will basically present the routes table to the user (i.e select "from" and select "to") and will filter the "to" locations based on what the user has selected for the "from" location, i.e. a cascading combo box. The corresponding "Route.ID" will be stored in a resultant table on completion of the form.

Location has the following columns -

Routes has the following columns -

I want to create a form with a cascading combo box - "ComboFrom", "ComboTo". Where "ComboFrom" will search the table for a list of distinct IDs, and using a JOIN with the Locations table, I can display the location names. "ComboTo" is dependant on the ID value in "ComboFrom" and will therefore only return a list of locations where it matches the From_LocationID of the routes table.

I have the first part more or less done. ComboFrom has the below query:

SELECT DISTINCT Location.Location_ID, Location.[Location Name], Location.Description
FROM Location INNER JOIN Route ON Location.Location_ID = Route.From_LID
ORDER BY Location.[Location Name];

ComboTo has the below query:

SELECT Location.Location_ID, Location.[Location Name], Location.Description, Route.From_LID
FROM Location INNER JOIN Route ON Location.Location_ID = Route.To_LID
WHERE (((Route.From_LID)=[Forms]![fmrRoute1]![From_LID]))
ORDER BY Location.[Location Name];

The piece of code in the "Where" clause in the ComboTo field basically gets the input of the ID from ComboFrom and outputs the correct IDs from the corresponding To_LID list. I then add vba against the update events for ComboFrom to requery ComboTo field.

What I am having trouble figuring out is how I can get the corresponding route ID to display correctly. i.e if I choose "1" for ComboFrom, and "3" for ComboTo, the Route ID should show "5" and not "7" (where "7" is a new record in the Routes table). Do I have to have a separate query to search for ComboFrom and ComboTo and return the resultant Routes ID? If so, how do I do this and attach it to the field in the form that will automatically update every time the ComboFrom and ComboTo fields change?

Is there a better way to do what I am trying to do?

Thanks in advance for any assistance!

CodePudding user response：

This looks like cascading combo boxes into filtering a form from those two unbound combo boxes. You can google those two ideas.

The self join made this tricky. The key was ignoring the relationship between Location.ID and From_LID. That relationship is in the database but ignored (deleted) in the query.

this gives us the sql we will need for the second combo box record source.

SELECT Routes.From_LID, Routes.To_LID, First(Locations.LocationName) AS FirstOfLocationName
FROM Locations INNER JOIN Routes ON Locations.ID = Routes.To_LID
GROUP BY Routes.From_LID, Routes.To_LID
HAVING (((Routes.From_LID)=2))
ORDER BY First(Locations.LocationName)  
'just need to replace the 2

Private Sub cmbFrom_AfterUpdate()
'cascade combobox
Dim strRowSource As String
strRowSource = "SELECT Routes.From_LID, Routes.To_LID, First(Locations.LocationName) AS FirstOfLocationName" & _
" FROM Locations INNER JOIN Routes ON Locations.ID = Routes.To_LID " & _
"GROUP BY Routes.From_LID, Routes.To_LID " & _
"HAVING (((Routes.From_LID) = " & Me.cmbFrom & "))" & _
"ORDER BY First(Locations.LocationName)"
Debug.Print strRowSource
Me.cmbTo.RowSource = strRowSource
Me.cmbTo.Visible = True 
'a look and feel choice
End Sub



[![enter image description here][2]][2]

Private Sub cmbTo_AfterUpdate()
'filter form 'most of the time you want to create a search form where you filter to the records you want
Me.Filter = "From_LID = " & Me.cmbFrom & " AND To_LID = " & Me.cmbTo
Me.FilterOn = True
Me.Detail.Visible = True
End Sub