Search code examples
sqlms-access

MS Access Cascading Combo box against a single table, then store the resultant ID

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 -

ID Name Notes
1 London Great Britain
2 Manchester Great Britain
3 Alabama USA

Routes has the following columns -

ID From_LID To_LID KM Notes
1 1 2 450 Using the M1 route
2 2 1 450 Using the M1 route
3 1 2 485 Using the inside routes
4 2 1 485 Using the inside routes
5 1 3 5450 Too far to consider
6 3 1 5450 Too far to consider

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!

Solution

  • 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.
    enter image description here

    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

    enter image description here

    enter image description here