Can't Cause Query to Execute

[Robert]

I'm trying to update the choices in a combo box based on the value of another
field field on the same form (actually a subform).

With the form open (and some record selected), the first time I open the
query, it executes correctly.

ProductMaterial is a subset of ProductName. The ProductMaterial combo box
should update any time ProductName changes. I've set the On Change event
(and tried various other events) of ProductName to =[qryDistinctMaterials].

qryDistinctMaterials is:
SELECT DISTINCT tblProducts.PrimaryMaterial
FROM tblProducts
WHERE
(((tblProducts.ProductName)=[forms]![tblInvoiceLinessubform].[ProductName]));

With the form in datasheet view, every time I select a different
ProductName, I get the information message:
The expression On xxx you entered as the event property setting produced the
following error: The object doen't contain the Automation object
"qryDistinctMaterials."

and qryDistinctMaterials fails to execute. If I open qryDistinctMaterials,
or toggle from design to datasheet views, it works OK.

Finally, when I open the main form, I get the caution:
"This expression is typed incorrectly, ir it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables."

Help - what have I failed to do?

Thanks, Robert

 

[tina]

the problem is the expression you're putting on the event line of the
ProductName control. from what i'm reading, the combobox control is in the
subform, and so is the ProductName control. so try putting the following
code in both the *subform's* Current event procedure, AND the ProductName
control's AfterUpdate event procedure, as

Me!ComboboxName.Requery

the above code goes in the event *procedures*; don't try to put it on the
event line in the Properties box.

hth

 

[Dirk Goldgar]

I'm trying to update the choices in a combo box based on the value of
another
field field on the same form (actually a subform).

With the form open (and some record selected), the first time I open the
query, it executes correctly.

ProductMaterial is a subset of ProductName. The ProductMaterial combo box
should update any time ProductName changes. I've set the On Change event
(and tried various other events) of ProductName to
=[qryDistinctMaterials].

qryDistinctMaterials is:
SELECT DISTINCT tblProducts.PrimaryMaterial
FROM tblProducts
WHERE
(((tblProducts.ProductName)=[forms]![tblInvoiceLinessubform].[ProductName]));

With the form in datasheet view, every time I select a different
ProductName, I get the information message:
The expression On xxx you entered as the event property setting produced
the
following error: The object doen't contain the Automation object
"qryDistinctMaterials."

and qryDistinctMaterials fails to execute. If I open
qryDistinctMaterials,
or toggle from design to datasheet views, it works OK.

Finally, when I open the main form, I get the caution:
"This expression is typed incorrectly, ir it is too complex to be
evaluated.
For example, a numeric expression may contain too many complicated
elements.
Try simplifying the expression by assigning parts of the expression to
variables."

Help - what have I failed to do?

You're going about this the wrong way, when you try to set any event
property to "=[qryDistinctMaterials]". You can't execute a query that way,
and even if you could, it would have no effect on your ProductMaterial combo
box.

I gather that the ProductMaterial combo box has "qryDistinctMaterials" as
its RowSource. I *think* that what you want is to make the ProductMaterial
combo box's list show the materials (from tblProduct) that correspond to the
selected ProductName. If I'm right, and if I understand what you're trying
to do, what you should do is build an event procedure for the AfterUpdate
event of ProductName, to requery the ProductMaterial combo box. It would
look something like this:

'----- start of example code -----
Private Sub ProductName_AfterUpdate()

Me!ProductMaterial.Requery

End Sub
'----- end of example code -----

However, I'm a little confused because your query is selecting from
tblProduct, and I would have though that ProductName would be unique in such
a table -- in which case, wouldn't your query always return only one
PrimaryMaterial from the table? But maybe I just don't understand your data
structures.

 

[Robert]

Thanks all,

I also figured out that the query was wrong.
For a query to use the value of a field on the same subform, the syntax is
[formname].[fieldname];
NOT
forms![formname].[fieldname] as would be required in other situations.