Results 1 to 10 of 10
  1. #1
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496

    Cascading combo box unfiltered blank result

    I have subforms on a form that have cascading combo boxes - when you open the main form, from another form via id = & id, the form and cascade combo box is fine. If you click the unfilter in the navigation box (so that you can step through each record) the next record won't show the correct value in the combo box. It seems to have the same value in the master combo box as the previous record (not refreshing or something) and so the second combo box won't show the result.



    How do I get around this?

    Click image for larger version. 

Name:	Capture1.JPG 
Views:	22 
Size:	23.8 KB 
ID:	14139
    (ignore the Blank (before 2013) that is just the value of all old shows, also ignore SHOW field as that isn't required and unrelated.)

    and as you can see the year is later and the second is blank

    Click image for larger version. 

Name:	Capture.JPG 
Views:	22 
Size:	21.0 KB 
ID:	14140

    in the show combo box row source the query has forms!frmMain.Form!frmSub.form!cmbBookingYear

  2. #2
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    I think it has something to do with this although I don't quite understand...

    http://msdn.microsoft.com/en-us/libr.../ff845736.aspx

  3. #3
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    I have never used cascading combo boxes that are dependent on a form's recordsource. It should be the other way around. A form's recordsource should depend on the combobox. I will normaly place them on an unbound form and then have a subform display the results or an entirely new form open based on the comboxes' values.

    Otherwise, it should be one combobox that is bound to one control that, in turn, is bound to a form's recordsource.

  4. #4
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    I have never used cascading combo boxes that are dependent on a form's recordsource. It should be the other way around. A form's recordsource should depend on the combobox. I will normaly place them on an unbound form and then have a subform display the results or an entirely new form open based on the comboxes' values.

    Otherwise, it should be one combobox that is bound to one control that, in turn, is bound to a form's recordsource.
    I have a many to many relationship - each teacher has a subform of the booking.

    You make a teacher (which is attached to a school) and you give it to a booking (the subform).

    The moment you enter the subform the teacher is tied to the booking and the booking is made.

    In the subform I had it so you could select the year of the booking and then the corresponding shows. (the cascade box)

    If I made an unbound, it would have two be two subforms displayed....

  5. #5
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    Just not sure how it can be both unbound and bound at the same time. As soon as you navigate to another record, the chaon will be broken, even if each combox is bound to a form/control. One of the form looks at a different record and the chain breaks.

  6. #6
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Quote Originally Posted by ItsMe View Post
    Just not sure how it can be both unbound and bound at the same time. As soon as you navigate to another record, the chaon will be broken, even if each combox is bound to a form/control. One of the form looks at a different record and the chain breaks.
    Can't I get it to refresh or look/filter at it again as it clicks through a record? this works fine when I load from another form however if I drop the filter and move from one record to the next...

  7. #7
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    There almost always is a way. You could save the value(s) of the combobox(es) or the default values in global variables and then reasign those values to the rowsource of your choice.

    Honestly, I am not quite sure what it is you are doing. Every time I start to look at it I go cross eyed.

  8. #8
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Quote Originally Posted by ItsMe View Post
    There almost always is a way. You could save the value(s) of the combobox(es) or the default values in global variables and then reasign those values to the rowsource of your choice.

    Honestly, I am not quite sure what it is you are doing. Every time I start to look at it I go cross eyed.
    If this helps

    I have a table with two fields. Each field stores a number value (number fields). One is for the year (2012, 2013, 2014 etc) and that is the master combox then the cascaded one is the second field (showsid combo box) which is bound in the relationship view to tblShows. It only allows data to go in that matches the year. of course the row source is from the query of that same table anyway and in the query of that row source I have the criteria point to the master.

    I figured that when you click the next record that because the value in box 1 is say 2 then the form would display the show in the second combox which is the value stored in showsid. If I take away the criteria yes then it shows but I need it to restricted to the year in case the admin changes the show to a different one.

  9. #9
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    You can see for yourself the behavior of the comboboxes. Store the values of whatever field you think you need in a variable. On the form's current event you can reassign it to the combo box. The trick will be telling the VBA if your User is moving from one record to the next or simply opening the form. On the form's load event you can assign a value that your variable would never see. Maybe a negative number or something. This way, you can do validation in the current event.

    'declare your variable at the top of the page
    dim intYear as integer

    'in the form's load event assign a value that will never equal your control/field
    intYear = -1

    get the value in an afterupdate
    'intYear = Me.cmbYear.value


    'then in the form's current event
    If intyear >= 0 then
    me.cmbYear.collumn (0) = intYear
    end if

  10. #10
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Quote Originally Posted by ItsMe View Post
    You can see for yourself the behavior of the comboboxes. Store the values of whatever field you think you need in a variable. On the form's current event you can reassign it to the combo box. The trick will be telling the VBA if your User is moving from one record to the next or simply opening the form. On the form's load event you can assign a value that your variable would never see. Maybe a negative number or something. This way, you can do validation in the current event.

    'declare your variable at the top of the page
    dim intYear as integer

    'in the form's load event assign a value that will never equal your control/field
    intYear = -1

    get the value in an afterupdate
    'intYear = Me.cmbYear.value


    'then in the form's current event
    If intyear >= 0 then
    me.cmbYear.collumn (0) = intYear
    end if
    I figure I am just going to display the values as text fields - ditch any effort to be able to edit the record on the form but have a button which when clicked opens the same record in a popup modal form (which will be filtered to the record) that will allow me to change the show there and then - it saves me the hassle.

    Thanks for your help though

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

Similar Threads

  1. Cascading combo box
    By Plix in forum Access
    Replies: 1
    Last Post: 02-20-2013, 06:54 AM
  2. Replies: 11
    Last Post: 11-28-2012, 04:29 PM
  3. Replies: 8
    Last Post: 05-16-2012, 09:30 AM
  4. Controls go blank on empty query result
    By kevdfisch in forum Programming
    Replies: 4
    Last Post: 08-25-2009, 08:07 AM
  5. Cascading Combo Box
    By nywi6100 in forum Forms
    Replies: 0
    Last Post: 10-23-2006, 01:45 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