Combo Box - shows blank for certain types of records

[Guest]

This is a perplexing problem and I can't figure it out.

I'd created a text box (name is Sch_No) on a form linked to a table; the
form will show a filtered list from that table. The textbox was able to show
data for all displayed records.

Then I decided to allow the user to change the entry in the table through
this form, so I changed the textbox to a combo box; the combo box was based
on a query called Lkup_schedule (see code below). The first field in the
query was bound to the table but it was the second field in the query that
would be displayed by the combo box.

The problem is, unlike when I'd used the textbox, the combobox was not
showing the data for certain records. It just showed a blank. I also noticed
that if you clicked on the combo box to see selection options available, the
relevant data for these problematic data were missing. It was as if the
underlying query wasn't running properly. BUT when you ran the query by
itself, it ran properly.

1) Firstly, why are some records shown correctly on the combo box while
others show a blank.
2) Secondly, why does the combo show an incomplete list from the query, yet
when I run teh query by itself, the list is correct.

Any help will be appreciated.

Pele












SELECT [Schedule tbl].sch_no AS [Number], [Schedule tbl].sch_name AS Name,
[Schedule tbl].sch_descr AS Description, [Schedule tbl].st AS [ST No Hlydy],
[Schedule tbl].ot AS [OT No Hlydy], [Schedule tbl].dt AS [DT No Hlydy],
[Schedule tbl].stH AS [ST One Hlydy], [Schedule tbl].otH AS [OT One Hlydy],
[Schedule tbl].dtH AS [DT One Hlydy], [Schedule tbl].stHH AS [ST Two Hlydy],
[Schedule tbl].otHH AS [OT Two Hlydy], [Schedule tbl].dtHH AS [DT Two Hlydy],
[Schedule tbl].[Work Status], [Labor rate tbl].labor_rate_id
FROM ([Schedule tbl] LEFT JOIN [Labor type tbl] ON [Schedule tbl].[Work
Status] = [Labor type tbl].[Work Status]) LEFT JOIN [Labor rate tbl] ON
[Labor type tbl].[Labor Type Code] = [Labor rate tbl].labor_type_cd
WHERE ((([Labor rate tbl].labor_rate_id)=[forms]![main
form]![frm_headcount]![subfrm_headcount]![labor_rate_id]));

 

[Guest]

Is the query referring to a control on the form? if so, check that. It may
run when you type in the value but not be able to get the value from the
form. Also , check the 'bound column' property for the combo box.

Dorian

 

[Guest]

Dorian,

Thanks for taking a stab at this.

1) Yes , the query is referring to a control on the form. Also, the query by
itself does run as long as I leave the form open, so I am certain it is
getting the value from the form.

2) The bound property is 1 and this is what I'd used for the Column Widths
(0";0.9";1.6";0.65";0.65";0.65";0.65";0.65";0.65";0.65";0.65";0.65"). Note
taht it will display the second field but keep the first field in teh table.

3) I think my problem has to do with a field called Work Status included in
this query; that is the field joining the Schedule Tbl and the Labor Rate
table. The possible values for this field are "Temp" and "Full Time". If I
imntentionally change the value of this field for a record from "Temp" to
"Full Time", the combo displays the record correctly. BUT the interesting
thing is that the query itself displays the filtered list correctly whether
it is "Temp" or "Full Time" but the list in the combo only seems to show
schedules for Full Time records.

Any other advice will be appreciated. Thanks.

Pele

Is the query referring to a control on the form? if so, check that. It may
run when you type in the value but not be able to get the value from the
form. Also , check the 'bound column' property for the combo box.

Dorian

This is a perplexing problem and I can't figure it out.

I'd created a text box (name is Sch_No) on a form linked to a table; the
form will show a filtered list from that table. The textbox was able to show
data for all displayed records.

Then I decided to allow the user to change the entry in the table through
this form, so I changed the textbox to a combo box; the combo box was based
on a query called Lkup_schedule (see code below). The first field in the
query was bound to the table but it was the second field in the query that
would be displayed by the combo box.

The problem is, unlike when I'd used the textbox, the combobox was not
showing the data for certain records. It just showed a blank. I also noticed
that if you clicked on the combo box to see selection options available, the
relevant data for these problematic data were missing. It was as if the
underlying query wasn't running properly. BUT when you ran the query by
itself, it ran properly.

1) Firstly, why are some records shown correctly on the combo box while
others show a blank.
2) Secondly, why does the combo show an incomplete list from the query, yet
when I run teh query by itself, the list is correct.

Any help will be appreciated.

Pele












SELECT [Schedule tbl].sch_no AS [Number], [Schedule tbl].sch_name AS Name,
[Schedule tbl].sch_descr AS Description, [Schedule tbl].st AS [ST No Hlydy],
[Schedule tbl].ot AS [OT No Hlydy], [Schedule tbl].dt AS [DT No Hlydy],
[Schedule tbl].stH AS [ST One Hlydy], [Schedule tbl].otH AS [OT One Hlydy],
[Schedule tbl].dtH AS [DT One Hlydy], [Schedule tbl].stHH AS [ST Two Hlydy],
[Schedule tbl].otHH AS [OT Two Hlydy], [Schedule tbl].dtHH AS [DT Two Hlydy],
[Schedule tbl].[Work Status], [Labor rate tbl].labor_rate_id
FROM ([Schedule tbl] LEFT JOIN [Labor type tbl] ON [Schedule tbl].[Work
Status] = [Labor type tbl].[Work Status]) LEFT JOIN [Labor rate tbl] ON
[Labor type tbl].[Labor Type Code] = [Labor rate tbl].labor_type_cd
WHERE ((([Labor rate tbl].labor_rate_id)=[forms]![main
form]![frm_headcount]![subfrm_headcount]![labor_rate_id]));