My table called Patterns is connected to my table PatternColor in a one
to many relationship. Both tables have an autonumber as their primary keys.
The PatternColor Table has 3 fields: PatternColorID (autonumber) PatternID
(The many side of the PatternID autonumber field in the Pattern Table, and a
text field called color. The Pattern Table has 2 fields: PatternID
(autonumber) and Pattern (text)

Each of the line items on my order entry form requires selection of a
PatternColor.

It seems to me the best way for my order entry line items to work is with 2
combo
boxes: the first allows the user to select the pattern and the second is
filtered by the choice the user makes in the first combo box.
I have written a query with the following parameter to filter the
PatternColorCbx:

[forms]![orderentry]![orderLineItems]![PatternCbx]. It works ok to a point.
Here's the problem.

When I am in the data entry form, and I select my pattern from the 1st drop
down box, the second box correctly presents the filtered choices; however,
when I try to make a selection from the PatternColorCbx, I am "bleeped" and
told there is no matching PatternColor in the PatternColorTable. (there is!)

When I open the query that underlies my data entry line subform, I have the
same problem if I enter the pattern name first and then try to enter the
patternColor. However, if I change the the data entry sequence and
enter the PatternColorID first, everything is fine and the form makes the
appropriate matches, looks up and completes the pattern side of the
relationship.

I really would like the user interface should work the way I am suggesting.
My list of patterns is already large and when you add the patternFabricColor
choices it will get tough to find the appropriate entry.

I have thought about combining the PatternCbx & PatternColorCbx into a
single Cbx given that a pattern name could have 10 or more characters, the
user would have to do a lot of typing before they were presented with the
appropriate Pattern/PatternColor combination.

I am also reluctant to combine the Pattern and PatternColor Fields because
of the problems presented when a Pattern or a PatternColor is not on the
list. I need to be able to add a new PatternColor if the pattern is there
but not the PatternColor, and if the pattern has not yet been entered I want
to open a form, in response to the not on list event. This form is created
and set up
(patterns) with a subform (PatternColor) in a one to many relationship
and works fine.

I hope I have explained the problem clearly enough. Thanks to anyone who
can offer some help or ideas.


--
Lele

Lele,
You can place a breakpoint to catch the combo box event and see what value
you are entering when you select a choice from the combo box.

James

"Lele" wrote:

> My table called Patterns is connected to my table PatternColor in a one
> to many relationship. Both tables have an autonumber as their primary keys.
> The PatternColor Table has 3 fields: PatternColorID (autonumber) PatternID
> (The many side of the PatternID autonumber field in the Pattern Table, and a
> text field called color. The Pattern Table has 2 fields: PatternID
> (autonumber) and Pattern (text)
>
> Each of the line items on my order entry form requires selection of a
> PatternColor.
>
> It seems to me the best way for my order entry line items to work is with 2
> combo
> boxes: the first allows the user to select the pattern and the second is
> filtered by the choice the user makes in the first combo box.
> I have written a query with the following parameter to filter the
> PatternColorCbx:
>
> [forms]![orderentry]![orderLineItems]![PatternCbx]. It works ok to a point.
> Here's the problem.
>
> When I am in the data entry form, and I select my pattern from the 1st drop
> down box, the second box correctly presents the filtered choices; however,
> when I try to make a selection from the PatternColorCbx, I am "bleeped" and
> told there is no matching PatternColor in the PatternColorTable. (there is!)
>
> When I open the query that underlies my data entry line subform, I have the
> same problem if I enter the pattern name first and then try to enter the
> patternColor. However, if I change the the data entry sequence and
> enter the PatternColorID first, everything is fine and the form makes the
> appropriate matches, looks up and completes the pattern side of the
> relationship.
>
> I really would like the user interface should work the way I am suggesting.
> My list of patterns is already large and when you add the patternFabricColor
> choices it will get tough to find the appropriate entry.
>
> I have thought about combining the PatternCbx & PatternColorCbx into a
> single Cbx given that a pattern name could have 10 or more characters, the
> user would have to do a lot of typing before they were presented with the
> appropriate Pattern/PatternColor combination.
>
> I am also reluctant to combine the Pattern and PatternColor Fields because
> of the problems presented when a Pattern or a PatternColor is not on the
> list. I need to be able to add a new PatternColor if the pattern is there
> but not the PatternColor, and if the pattern has not yet been entered I want
> to open a form, in response to the not on list event. This form is created
> and set up
> (patterns) with a subform (PatternColor) in a one to many relationship
> and works fine.
>
> I hope I have explained the problem clearly enough. Thanks to anyone who
> can offer some help or ideas.
>
>
> --
> Lele