Forms: Referencing a control on a form in an Access project View

[Mike]

Does anyone have any idea how you reference a form control
in a SQL search when creating a view (vs. a query in an
Access database) in an Access data project? In a query
you would have something like [MyField] = [Forms]![MyForm]!
[MyControl] (in the criteria box), but views don't like
this referencing syntax one bit and always blow up on the
exclamation mark (!). Microsoft says that views under a
project file are the equivalent of select queries under a
database file, but I beg to differ. Views are worthless
unless you can reference a value stored on a form
control. Any ideas on what I may be doing wrong?

 

[J. Clay]

You cannot reference anything in access from a SQL view. SQL is not aware
of Access. You need to do one of two things.

1 - Create a stored procedure with a parameter that you can send it

2 - Create a view with the required columns and use it as your source table
with a where clause:
SELECT Column1, Column2 FROM MyView WHERE Column1 = SomeParam

Within the forms and reports of access, you can reference the varies
controls to be part of the select statement.

As for being worthless if you cant reference a value on a form control, I,
and probabley every other developer would beg to differ. There are many
uses for Views. The key is to learn to think in a Client/Server mode vs.
straight client mode of regular Access. Working in SQL is different, but is
very rewarding and beneficial.

HTH,
Jim Clay