How can I create a SQL statement in the access control source?

[Guest]

Dears,

Good afternoon.
I am using the Access to create some forms based on some tables and queries.
I defined a query that brings me more than one line.
I have a form that is based on this query.
I have one field in this form for each query row and I need to select the
correct query value for each field and I have the right WHERE statement to
request this value for each one.
I tried to add this WHERE statement in the text box property (Control
Source) and it didn't run (I think I need a simple sample about it).
Also, I tried to execute a query in the event procedure to populate this
field, however, I didn't found any simple sample of how to run a query
(select statement) inside the event procedure.
The last thing I could try is to create one query for each field, however, I
think should be an easier way to do it.
Could you please, help me on this?

Thanks and regards.

Paulo Henrique.

 

[fredg]

On Tue, 17 Jan 2006 13:28:02 -0800, PH wrote:

See comments interspersed below...

Dears,

Good afternoon.
I am using the Access to create some forms based on some tables and queries.
I defined a query that brings me more than one line.
I have a form that is based on this query.
I have one field in this form for each query row and

A Control on a form will display one field of one record, not one
query row (whatever you mean by that).
Navigate to the next record to see the next record's data.

I need to select the
correct query value for each field and I have the right WHERE statement to
request this value for each one.
I tried to add this WHERE statement in the text box property (Control
Source) and it didn't run (I think I need a simple sample about it).

You cannot run a Select SQL in the control source of a control.

If you already have the query, and the query is the form's record
source, simply set the control source of a control to that query
field.

Also, I tried to execute a query in the event procedure to populate this
field, however, I didn't found any simple sample of how to run a query
(select statement) inside the event procedure.
The last thing I could try is to create one query for each field, however, I
think should be an easier way to do it.

What do you mean by one query for each field?

Why not tell us what your final result will be, with examples, and
why, rather than how you want to do it. You may know what it is you
want, but it's not being explained very well. So far you are
attempting to do what cannot be done. Sorry!

 

[Guest]

fredg,

Good afternoon.
First of all, thanks for help me.
I will try to explain better:

I have the event procedure bellow, and I don't know how can I execute this
select statement to returns me the value I am asking for FullM15 field.
The question is: What commands I need to insert here to execute this query
and put the value into the Form_PROCESS_COMPLIANCE.FullM15 field?

Private Sub Form_Open(Cancel As Integer)

Dim qryTest As String

qryTest = "SELECT [Form-Process-Compliance].NonCompliance FROM
[Form-Process-Compliance] WHERE [Form-Process-Compliance].GOVERNANCE_PATH='"
& Lite & "' And [Form-Process-Compliance].REVIEWED_GATE='" & M06 & "';"

Form_PROCESS_COMPLIANCE.FullM15 = qryTest

End Sub

Another point is what I tried to wrote in the last e-mail. I have another
fields like FullM15 field, so I will need to run one query for each field.
The FullM15 field is a textbox inside a form that is based on
Form-Process-Compliance query.
I would like to know if Access has in the Control Source property, some way
to run the same query I am trying to run through the Private Sub Form_Open
event procedure.

Thanks and regards.

PH.