Requerying combo box 2 from value selected in combobox 1

[Paul Doree]

Hi,

I have a subform which has two comboboxes on it. Combobox 1 contains the
JobID, and combobox 2 contains all the addresses for jobs. After update of
combobox1, I need combobox 2 to be filtered to the addresses which belong to
the JobID in combobox 1.

After update of ComboBox 1 I am requerying Combobox 2. For the row source
for Combobox 2 I am using as criteria in the sql statement
forms!mainformname!subformname!controlname where controlname is JobID - the
name of ComboBox 1. This then shows all the addresses related to the Job
currently selected.

I've done this sort of thing before (on main forms, not sub forms) and had
no problems. This time, however, something strange is happening. When I
add a second record, or third etc. in the subform, the drop down list of
Combobox 2 ON ALL THE ROWS is always the address in the currently selected
row, and when I pick the address from Combobox 2 the address fileds in the
other rows then blank out!!

I've now been at this for nearly 2 hours and cannot see why. Can anyone
help?

Yours frustratingly

Paul

 

[martdi]

From what I read your subform is in continuous mode, which is a pain to
work with.

There is a hack you can do for the text to stop disappearing. I had to
do it recently.

What you have to do is to:

1- put a small text box over the combo box to cover the whole text
zone, but to leave the small arrow visible.

2 - On the OnChange event of the combobox, set the text property of the
text box to the text of the combobox.

3- You might want to set the focus to the combo box when the textbox
gets the focus.

4- You will also have to modify the source of your subform to make a
query to add a field that will contain the value you will want to be
displayed in the textbox, and assign it to the controlSource of the
textbox, so it will be a bound control.

this should work

good luck !!!

 

[Ron2006]

If I am reading your comments correctly, this implies that you sit on
say record 2 and in the combobox2 and then go and sit directly on
combobox2 for record 1 (or something like that). Since the requery is
on the update of combo box 1 it is NOT being done on the change of
record because you have not touched combobox one of that record.

Try adding the requery ALSO to the oncurrent event of the record. That
way it will be refreshed simply by changing records. and NOT just when
you change combobox1.

Ron