I have a tab Control which has 5 tabs on one of the tabs there is a subform for data input into another table. On the subform are 2 combo boxes one to select the event and the other to select the date of that event. The purpose is to track Members attendance to different events. We have multiple events and each event can occur on multiple days. The first part works perfectly If a member attends an event the subform shows all of the events that member attended it's the dates that are getting screwed up. The combo box works for the first record but on subsequent records only the dates for the first event display not the current event.
Here's the RowSource for the date combo box
Code:
SELECT
dbo_tbl_Event_Dates.EvtDate,
dbo_tbl_Event_Dates.EventDate,
dbo_tbl_Event_Dates.EventID
FROM dbo_tbl_Event_Dates
WHERE (((dbo_tbl_Event_Dates.EventID)=[forms]![MainMenu]![Navigationsubform].[form]![customerevents].[form]![cbo_event]));
I know the problem is in the Where statement the [forms]![MainMenu]![Navigationsubform].[form]![customerevents].[form]![cbo_event])) is not getting me the current Record of the combo box. What I can't figure out is why.
For example:
Member John Smith attends events-BAM Night and Lifestyle echo. BAM Night is the first Tuesday of each month so possible dates are April 5, 2016 and May 3, 2016 Lifestyle Echo are the 3rd Tuesday of each month so possible dates are April 19, 2016 and May 17, 2016
If BAM Night is the first Event it shows possible dates as April 5, 2016 and May 3, 2016
Then Lifestyle Echo is the second Event that member attended but it's possible dates are April 5, 2016 and May 3, 2016.
I included code that requeried the date combo box each time the event combo box is changed but that changes both records to the same date as the current event combo box. What am I missing?