0

I have two cascading lookup table comboboxes on a Datasheet form.(SessionCategories - SessionTypes). I'm having the following problems:

1) When the form loads I only see the values from the SessionTypes that correspond to the first SessionCategories ID.

2) When I change a value on the SessionCategories combo, all the SessionTypes values change across the datasheet and not only the selected.

Any ideas? Do I need to include something on the Form Load? More info and a couple of different approaches below.

Thanks.

ATHLETE_SESSION table
sessionOverviewID
sessionTypeID

SESSION_CATEGORIES
sessionOverviewID   sessionOverview
1   aerobic
2   anaerobic 


SESSION_TYPE
sessionTypeID   sessionType sessionOverviewID
1   anaerobic1  2
2   anaerobic2  2
3   anaerobic3  2
4   aerobic1    1
5   aerobic2    1
6   aerobic3    1

sessionCategoriesID lookup properties:
Display control: Combo Box
Row Source Type: Table/Query
Row Source: SessionCategories
Bound Column: 1
Column Count: 2
Column Widths: 0cm;2cm

sessionTypeID lookup properties:
Display control: Combo Box
Row Source Type: Table/Query
Row Source:
Bound Column: 1
Column Count: 2
Column Widths: 0cm;2cm


1st approach:

Private Sub comboSessionCategories_AfterUpdate()

Dim strSQL As String
strSQL = "SELECT SessionType.sessionTypeID, SessionType.sessionType FROM SessionType WHERE (((SessionType.sessionOverviewID)=" & [Forms]![Session]![comboSessionOverview] & "))"
Me.comboSessionType.RowSource = strSQL

End Sub

2nd approach:

Private Sub comboSessionCategories_AfterUpdate()

Me.comboSessionType.RowSource = "SELECT SessionType.sessionTypeID, SessionType.sessionType" & _
" FROM SessionType WHERE SessionType.sessionOverviewID=" & Me.comboSessionOverview
Me.comboSessionType = Me.comboSessionType.ItemData(0)

End Sub
Chris Kotsiopoulos
  • 57
  • 6
  • Have a look at [Cascading Combos on Continuous Forms and Datasheets](https://www.experts-exchange.com/articles/5950/Cascading-Combos-on-Continuous-Forms-and-Datasheets.html) – Rene Dec 15 '17 at 23:56
  • The `WHERE` clause of your SQL references something called `comboSessionOverview`, which I don't see anywhere else. Isn't `comboSessionType` supposed to change according to `comboSessionCategories`? – Pat Jones Dec 17 '17 at 05:59
  • Pat, sorry for that. comboSessionOverview is wrong. comboSessionCategories is the correct one. – Chris Kotsiopoulos Dec 18 '17 at 10:47
  • Rene, this is the best approach that I was able to find but it has some serious restrictions/compromises in terms of usability. It looks more like a hack rather than a normal approach. I have decided to make these fields read-only and allow the user to double click on each row to open a form containing the selected record. This way the user can drill down to even more information that can be problematic to show on a datasheet anyway (eg. a large Notes field) and update the record. – Chris Kotsiopoulos Dec 18 '17 at 10:58

0 Answers0