Results 1 to 8 of 8
  1. #1
    RayMilhon is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,066

    Subform combo box not retrieving the correct data

    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?

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Hard to say without having the dB to analyze.
    Is there code in the afterupdate event of "cbo_Event" that re-queries the control "cbo_Dates" (I'm guessing at the combo box name)??

  3. #3
    RayMilhon is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,066
    Yes there is. it's cbo_eventdates.Requery but it's in the on Change event in cbo_Event

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I always use the after update event. I'm not sure that selecting a option from the combo box fires a change event. (but I haven't tested that theory)
    You might try using the after update event of cbo_Event....

  5. #5
    RayMilhon is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,066
    I did switch it to the after update event it didn't change the results. But I do now understand what's going on. When the Event is updated the choices for the event date combo box are limited to that event. So even though the record the form updates is correct the combo box only displays the dates for the Event combo box that is active and since the first record is a different event that date is not available so it becomes blank. The record has been updated and is correct in the table you just can't see it on the form.
    Any thoughts on how I can fix this?

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I'm not sure I am following the process.

    There is a main form with a tab control (5 tabs). one of the tabs has a subform that has two combo boxes (unbound?).
    More of the "What" you are doing, not how.



    Or I'd have to see the dB...

  7. #7
    RayMilhon is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,066
    The two combo boxes are not unbound the subform is based on a table. The table has 4 fields
    CustomerID - Used to link the subform with the mainform
    EventID - used to link to the table containing the different events
    ResultsID - used to link to the results table (there are 5 possible Results "Pending Membership", "Already a Member", "Became a Member", "Not Interested","Follow up") The Difference between Already a member and Became a Member is that Already a member means they've been a member for sometime before this process was developed Became a Member means they became a member by attending one or more of these events.
    EventDateID used to link to the dates for that particular event.

    One combo box is bound to the EventID the combo box has 2 columns EventID and EventDesc the EventID is not shown but that is the bound column
    The other combo box is bound to the EventDateID That combo box has 3 columns as shown in my original post. The actual date is the only value shown and the EventDateid is the column that is bound.

    I will see about posting the database however Access is only the front end the data is stored in SQL Server so not sure how that would work.

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I have SQL Express 2014 installed on a test confuser.
    Zip the 2 SQL files and the Access FE.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 04-24-2016, 06:04 AM
  2. Replies: 1
    Last Post: 03-17-2012, 08:49 PM
  3. Retrieving values from a Combo Box
    By dreamnauta in forum Programming
    Replies: 3
    Last Post: 01-16-2012, 10:18 AM
  4. Replies: 2
    Last Post: 11-02-2011, 08:10 PM
  5. Replies: 1
    Last Post: 12-10-2005, 04:52 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums