Limiting a Combo with an other Combo

  • Thread starter Thread starter Alain Vaillancourt
  • Start date Start date
A

Alain Vaillancourt

I want want lo limit the values of a bound Combo with the value of another
Combo in a continuous form. I tried specifying the first Combo as a criteria
in the source of the second Combo but it hasn't worked as expected, any
suggestion?

Thank You!
 
You have started off correctly, specify the first combo as criteria in the
Row Source query of the second combo. Next, in the AfterUpdate event of the
first combo, requery the second combo.

Me.cboCombo2.Requery
 
I made this but as this is a continuous form the value of the Combo is
correct only for the selected record.
 
On second thought, I don't think Stephen's routine will help in this case.
The problem is that the query feeding the combobox is getting its value from
the other combobox, which when it does this, it actually comes from the
current record.

If the comboboxes are bound to a field in the table, they should all show
the value of that bound field. When you open the drop down to make a
selection, getting the values for the drop down from the current record
shouldn't be a problem because when you click into that combobox, that
record will be the current record. I suspect there is something else going
on here.

What is the Record Source of the form, the Control Source of each combobox,
the Row Source of each combobox, the number of columns in each combobox, and
the Bound Column of each combobox?
 
I think all is correct but here is the details...

Form Record Source=SELECT Pièces.ID, Plages.Plage, Plages.Album,
Pièces.Oeuvre, Plages.Pièce, Pièces.Durée
FROM Pièces INNER JOIN Plages ON Pièces.ID = Plages.Pièce
ORDER BY Plages.Plage;

Combo Oeuvre:
Column Count=2
Bound Column=1
Control Source=Oeuvre
Row Source=SELECT Oeuvres.ID, Oeuvres.Titre FROM Oeuvres ORDER BY
Oeuvres.Titre;

Combo Pièce:
Column Count=2
Bound Column=1
Control Source=Pièce
Row Source=SELECT Pièces.ID, Pièces.Version, Pièces.Oeuvre
FROM Pièces WHERE (((Pièces.Oeuvre)=[Forms]![Pièces]![Oeuvre]))
ORDER BY Pièces.Version;
 
In Combo Pièce, I noticed that the Row Source query is returning 3 fields
but you have 2 columns. However, this probably isn't hurting anything.

Also, the bound field for each combobox is the ID field returned by the
query. This is normal. However, I can't tell what type of field this ID
value is being stored in. For Combo Oeuvre, the field Oeuvres.ID from the
Row Source is being stored in Pièces.Oeuvre from the form's Record Source
and for Combo Pièce, the field Pièces.ID from the Row Source is being stored
in Plages.Pièce from the form's Record Source. Is this what you intend?

In Combo Pièce, the criteria for the query is referring to
[Forms]![Pièces]![Oeuvre]. By your description, the name Oeuvre is used by
both the combobox and the field. Try changing the name of the combobox to
cboOeuvre and change the reference in the query to this new name. Does that
help? I tried this as a test when I typed the SQL into the Row Source
instead of using a stored query and referred to the other combobox, not the
field, and it appeared to do what you are asking.
 
All my IDs are of the Long type, I tryed renaming my combo and my Row Source
is already a direct SQL statement but I still have the same problem. If you
succeed making what I'm asking I would like to see it, maybe you can send
your sample to my eMail address...

Wayne Morgan said:
In Combo Pièce, I noticed that the Row Source query is returning 3 fields
but you have 2 columns. However, this probably isn't hurting anything.

Also, the bound field for each combobox is the ID field returned by the
query. This is normal. However, I can't tell what type of field this ID
value is being stored in. For Combo Oeuvre, the field Oeuvres.ID from the
Row Source is being stored in Pièces.Oeuvre from the form's Record Source
and for Combo Pièce, the field Pièces.ID from the Row Source is being stored
in Plages.Pièce from the form's Record Source. Is this what you intend?

In Combo Pièce, the criteria for the query is referring to
[Forms]![Pièces]![Oeuvre]. By your description, the name Oeuvre is used by
both the combobox and the field. Try changing the name of the combobox to
cboOeuvre and change the reference in the query to this new name. Does that
help? I tried this as a test when I typed the SQL into the Row Source
instead of using a stored query and referred to the other combobox, not the
field, and it appeared to do what you are asking.

--
Wayne Morgan
Microsoft Access MVP


Alain Vaillancourt said:
I think all is correct but here is the details...

Form Record Source=SELECT Pièces.ID, Plages.Plage, Plages.Album,
Pièces.Oeuvre, Plages.Pièce, Pièces.Durée
FROM Pièces INNER JOIN Plages ON Pièces.ID = Plages.Pièce
ORDER BY Plages.Plage;

Combo Oeuvre:
Column Count=2
Bound Column=1
Control Source=Oeuvre
Row Source=SELECT Oeuvres.ID, Oeuvres.Titre FROM Oeuvres ORDER BY
Oeuvres.Titre;

Combo Pièce:
Column Count=2
Bound Column=1
Control Source=Pièce
Row Source=SELECT Pièces.ID, Pièces.Version, Pièces.Oeuvre
FROM Pièces WHERE (((Pièces.Oeuvre)=[Forms]![Pièces]![Oeuvre]))
ORDER BY Pièces.Version;
Click to expand...
Click to expand...
 
Actually, I thought I had it, but I didn't. I now have it doing what you've
mentioned. I'll work on it and get back to you. It is probably going to take
a "work around".
 
Ok, I got the work around to work. I will send the file to your email
address used in the message.

What I did was place a textbox on top of the 2nd combobox. I set the Locked
property to Yes and the Enabled property to No. I sized the textbox to fit
directly over the combobox but left the button showing on the right. Since
Enabled and Locked are set as mentioned, the control can't receive the
focus, so when you try to click there the combo receives the focus and pops
to the front. Set the control source of the textbox to a DLookup statement
that will show the value that should be in the combobox.
 
Back
Top