Cascading combo box unfiltered blank result

[Ruegen]

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?


(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



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

[Ruegen]

I think it has something to do with this although I don't quite understand...

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

[ItsMe]

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.

[Ruegen]

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....

[ItsMe]

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.

[Ruegen]

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...

[ItsMe]

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.

[Ruegen]

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.

[ItsMe]

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

[Ruegen]

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