Validate A Form Control Using Query ?

[pnsmack]

Good Evening All,

I've searched Microsoft's Technet as well as this site and can't find anything that *exactly* addresses the issue, so please bear with me.

Is there any way you can use a SQL query in the expression builder that references fields in the table as well as fields/controls in the form ???

The table is the source for my form, and I want to be able to compare the returned query results with current values in the form controls.


The MS Access standard "F1" Help for Expression Builder gives an example of :

>(SELECT AVG[UnitPrice]) FROM [Products])

As an example of using a query to validate the control on the form.

Here's an example of what I'd like to do. Say, for example, I have a control called [product_id] and I want to make sure it matches with a table entry :

=(SELECT [product_id] FROM [Products] where [customer_id] = [Me.customer_id])

(Basically, make sure that product_id is valid based on my form's "customer_id" control).

Any way to do this ?
Thanks in advance !
Paul

 

[tunsarod]

This:

=Nz(Dcount("product_id","Products","customer_id" = ' " & [customer_id]& "'" ),0)

will return the number of matching records.

Rod