Access ComboBox Problem

[Guest]

I am pretty new to Access. I have battled for 3 days now on this and have
looked at other questions posted but cannot find my problem. I have a subform
attached to a main form as continuous. This form needs to get "Courses
offered" by certain criteria. It has 3 combo boxes. The first 2 are working
fine. The 3rd one is the problem. Based on the values selected from the first
two combo boxes the 3rd one must display only courses pertaining to that
criteria. That part is working. It allows me to select the relevant course
and stores the ID of that course in my table. As soon as I go to the next row
to capture a second course, the combo box in the first line loses the
description (the code in the table however is intact). I have inserted a
me.comboboxname.requery in the GOTFOCUS event of the combo box BUT it will
only display the course name of one record at a time as soon as it gets
focus. All the other course combo boxes are being blanked out. Please help!

 

[Brian]

I am pretty new to Access. I have battled for 3 days now on this and have
looked at other questions posted but cannot find my problem. I have a subform
attached to a main form as continuous. This form needs to get "Courses
offered" by certain criteria. It has 3 combo boxes. The first 2 are working
fine. The 3rd one is the problem. Based on the values selected from the first
two combo boxes the 3rd one must display only courses pertaining to that
criteria. That part is working. It allows me to select the relevant course
and stores the ID of that course in my table. As soon as I go to the next row
to capture a second course, the combo box in the first line loses the
description (the code in the table however is intact). I have inserted a
me.comboboxname.requery in the GOTFOCUS event of the combo box BUT it will
only display the course name of one record at a time as soon as it gets
focus. All the other course combo boxes are being blanked out. Please

help!

If I understand you correctly, then you have some code that changes the
RecordSource of combo 3 based on the values in combos 1 and 2. In which
case, you have run into a fairly common issue in continuous forms, namely
that a control property can only have ONE value for the form as a whole, it
can't have different values for different records in a continuous form.
When the RecordSource is changed so that it is appropriate for the current
record, the change is applicable to the form as a whole i.e. to ALL records,
hence Access is no longer able to display the description for any records
where the value is no longer in combo 3's RecordSource.

The only work-around I have ever discovered for this is to position a text
box precisely over the text portion of the combo box, and have the text box
display the description using a function e.g. Dlookup. In the Enter event
for the text box, you need to set the focus to the combo box, and in the
AfterUpdate event of the combo box, you need to requery the text box.

It's a far-from-perfect solution, but it's the best I've ever thought of.
I'd be delighted to hear if anyone else has a better answer.

 

[Jonathan Parminter]

The only work-around I have ever discovered for this is to position a text
box precisely over the text portion of the combo box, and have the text box
display the description using a function e.g. Dlookup. In the Enter event
for the text box, you need to set the focus to the combo box, and in the
AfterUpdate event of the combo box, you need to requery the text box.

It's a far-from-perfect solution, but it's the best I've ever thought of.
I'd be delighted to hear if anyone else has a better answer.

Hi Brian, an alternative is to have a command button
display a pop-up form for users to add/edit field. When
pop-up form closes it then updates the main form.

Luck
Jonathan