Having Trouble With Data Source aka Qry

  • Thread starter Thread starter Meilu
  • Start date Start date
M

Meilu

Hi All,

I have:
A Form/Table --> Main that has two fields: ID
(Autonumber) & Name
A Subform/Table --> PColor that connects to the above
through field ID and has a field Color
A table --> ColorIndex which has two fields: Name & Color

I want the Color field in PColor to have its value chosen
from a drop down list. This drop down list should pull
its values from the table ColorIndex.

This is the Data Source “description” I gave the Color
field for the subForm:
SELECT ColorIndex.Color
FROM ColorIndex
WHERE (((ColorIndex.Name) In (SELECT Main.Name FROM Main,
PColor WHERE Main.ID = PColor.ID)));

I have added a Requery Action to the “on enter” event for
the control field Color.

The above approach works … but not for the first record
in Subform PColor

Ie: For the first record in Subform Pcolor, the drop down
list for Color is empty. But after manually entering a
Color value, the 2nd record’s Color field works as
expected.

If anyone knows what’s going on… I’d really appreciate
some enlightenment!!!

-Meilu
 
On PColor table go to design view. On the color field
select Lookup wizard and follow the steps. If you have
already added the field to the form, you must delete and
replace it.

Jesse Avilés
(e-mail address removed)
http://home.coqui.net/monk
 
I forgot to clarify what I want.

I want the Color field in PColor to have its value
chosen from a drop down list. This drop down list should
pull its values from the table ColorIndex WHERE the
ColorIndex.Name corresponds to the Main.Name that PColor
is associated with.

In other words ... because there are so many criterias ...
I don't think I can do this through Table design.

Any ideas would be greatly appreciated!
 
Meilu-

This doesn't make any sense. What is the purpose of table "Main"? What is
the purpose of table PColor? It would appear that ColorIndex has all the
relevant information.

Let me guess: Main appears to have names of color groups that interest you.
PColor is unnecessary unless you want it to include only those colors for
the color groups of interest. In other words, you're trying to subset a
massive ColorIndex table. If that's the case, then the Row Source for
PColor should be something like:

SELECT ColorIndex.Color
FROM ColorIndex
WHERE ColorIndex.[Name] = [Forms]![Main]![Name]

What this does is filter the list of available colors to only those defined
in the current "Main" color group on the outer form. You should not have to
requery anything. Also, you should have an index on both ColorIndex.Color
and ColorIndex.[Name].

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Back
Top