REFRESH Combo boxes for records on continous form

[Pele]

Background:
I have a continous form linked to a table and I have 3 combo boxes on the
form (linked to fields on the table). The combo boxes are called JOB TITLE,
SCHEDULE and SHIFT. The source for the combo boxes are queries named
respectively as LKUPJOB, LKUPSCHEDULE and LKUPSHIFT. For the three combo boxes listed abpve, the bound column is the first field of the underlying query and the column width of the first field is set to 0" (so that the second field of the query is displayed).

The way things should work is that for each record on the form, the
LKUPSCHEDULE and LKUPSHIFT queries (queries for the SCHEDULE and SHIFT combo boxes), the queries are filtered based on the bound field of the JOB TITLE combo box. The name of the bound field for the JOB TITLE combo is called Labor_Rate_ID.


Problem:
Unfortunately, It seems like the value of the labor-rate_ID in the first
record (i.e. first job title combo box) determines the query list used by the
SCHEDULE and SHIFT comboboxes for all the other records on the form. That is, even though the form has lots of records displayed, the combo boxes for each record don't have a complete list to work with.

Hence, the SCHEDULE and SHIFT combo boxes for some records are showing a blank (since available otions in the combo query list don't match the value
in the table for that record.

Can anybody tell me how to get a continous form to refress the list for each
record on the form.

I also don't know whether the Requery command for a macro could work and how to use it. I think though, that this problem can't be solved by a macro.

Basically, the question is around what kind of code will let you manipulate the properties of individual controls of a form at the Record level.

Pele

 

[Pat Hartman]

I posted an example database with what I think is an elegent solution for this problem. Search under the Sample Databases forum for my name or cascading combo

 

[LisaRae007]

combo box requery on subform

I posted an example database with what I think is an elegent solution for this problem. Search under the Sample Databases forum for my name or cascading combo

This was from 2002, I realize, but I came across your helps to the other user and realize that it is exactly what I am struggling with as well. I couldn't find the sample you put out as possible solution. Is it still available?

I need a combo box on a subform to refresh/requery (excuse my ignorance) when I go between records on the main form. The problem is, I have been fumbling around with which place to stick a "requery" statement...and for that matter, exactly what is the proper way to requery a combo box on a subform anyway!

A little background:This is an inspection database. The main form are records of inspection visits, and the subforms are where violations and notes are made. When I scroll to a different "visit" or record on the main form, I want the combo box on the subform to refresh its source query so that it will reflect the facilities associated with the location where the inspection took place.

Any comments or helps will be much appreciated! (Happy Holidays!)

 

[Laurentech]

In the OnCurrent event of your main form try Me!Subform1.Form!ControlName.Refresh

Let us know how it works.

Larry

 

[LisaRae007]

It didn't work...I went back and read more carefully and noticed a difference between the original user's question and mine. I have my form set up as "single form" instead of "continuous form". Would this change your approach or explain why your suggestion didn't work? I'm going to continue to play with it.

 

[Laurentech]

I'm not sure why it wouldn't be working Lisa. You need to replace 'Subform1' with the actual name of your subform, and ControlName with the actual name of your combo box that you wish to refresh.

Have you tried coding the subform's OnCurrent with me.comboboxname.refresh?

Larry

 

[Laurentech]

I just looked at some of my code, and I was wrong in the above example. It should be ReQuery, not refresh.

Larry

 

[LisaRae007]

That worked! Thanks for the input. I ended up with a...

Me!ChemicalTestSubform.Form!WaterUnitLkup.Requery
[Me!Subform1.Form!ControlName.Requery]

...in the OnCurrent of my main form. So far it looks like it is doing what I wanted. Thanks again.