Problems With Default Value...

[originalxavier]

I am trying to set up a form that will progressively filter records. There are two fields
Type:
Description:
When a user selects the type and leaves that field, I have the "On Exit" event that requeries the Description Field. So far I am having nothing but trouble. The error msg is "The syntax of the subquery in this expression is incorrect. Check the subquery's syntax and enclose the subquery in parentheses."

The expression:
Select [EQUIPMENT].[DESCRIPTION] FROM EQUIPMENT WHERE ((([EQUIPMENT].[Type]=[Forms]![frm_Parent]![subfrm_Equipment]!Type)) ORDER BY EQUIPMENT.DESCRIPTION

WhenI don't get this error message, it prompts me for the "[Forms]![frm_Parent]![subfrm_Equipment]!Type" value instead of using what is on the form.

Thanks in advance

Xavier

----------------------------------------
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far the Universe is winning."
Rick Cook
----------------------------------------

 

[RoyVidar]

Hi!

Two things with the subform reference
* adding the Form keyword
* type - is a reserved word and should be avoided as object name, you might get around it with using [brackets]

[tt][Forms]![frm_Parent].Form![subfrm_Equipment]![Type][/tt]

- does that work?

Roy-Vidar

 

[scriverb]

In addition to Roy-Vidar's comments which are necessary to employee, you have unmatched parenthesis.

[blue]Select [EQUIPMENT].[DESCRIPTION] FROM EQUIPMENT WHERE ((([EQUIPMENT].[Type]=[Forms]![frm_Parent]![subfrm_Equipment]!Type))[red][b])[/b][/red] ORDER BY EQUIPMENT.DESCRIPTION

[/blue]

This is probably why you are receiving the syntax error.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[originalxavier]

Neither worked. Adding the ".Form!" Changed the form to prompt me to Enter the parameter value Forms!frm_Parent.


Xavier

----------------------------------------
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far the Universe is winning."
Rick Cook
----------------------------------------

 

[scriverb]

Actually there is no need for any parens at all. They can be completely removed.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[originalxavier]

scriverb... That was my mistake in transposing the code. I have it after "((([EQUIPMENT].[Type])..."

Xavier

----------------------------------------
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far the Universe is winning."
Rick Cook
----------------------------------------

 

[RoyVidar]

Then next two questions:

* the form is open?
* the subform control name (which is what's actually referred) might differ from the name of the subform (as viewed in the database window)

Easiest would be to be in the query grid of the field, and build the expression thru the expression builder (thru forms, loaded forms, first form, subform, control).

Acutally, I also placed the Form keyword the wrong place too , should have been:

[tt][Forms]![frm_Parent]![subfrm_Equipment].Form![Type][/tt]

Roy-Vidar

 

[originalxavier]

Perhaps it is just the beauty of Microsoft products, but after pulling my hair out, I decided to start with a brand new form. This worked. I simply used a Description.Requery and

Select [Equipment].[Description] FROM Equipment WHERE ((([Equipment].[TYPE]) = ([Forms]![frm_Parent]![SubForm_Equipment]!Type)));

This works beautifully as it should and is the same code as before.

Xavier

----------------------------------------
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far the Universe is winning."
Rick Cook
----------------------------------------