How to execute a select statement inside an Event Procedure?

G

Guest

Dears,

Good afternoon.
Could someone help me on this?
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

Thanks and regards.

PH.
 
G

Guest

There are a couple of issues here to determine how to do this. I can't tell
from your select statement whether you are expecting more that one row to be
returned by this query. Based on this line:
Form_PROCESS_COMPLIANCE.FullM15 = qryTest
it appears that is what you are doing. A query would not be the best way to
do this. I would recommend a DLookup function:

Form_PROCESS_COMPLIANCE.FullM15 = DLookup("[NonCompliance]", _
"Form-Process-Compliance", "GOVERNANCE_PATH='" _
& Lite & "' And [REVIEWED_GATE] ='" & M06 & "'")
 
G

Guest

Dear Klatuu,

Good afternoon.
Thanks for your help, however, I still couldn't execute this command.
I need to know all commands before and after to execute this command you
suggest:

Form_PROCESS_COMPLIANCE.FullM15 = DLookup("[NonCompliance]", _
"Form-Process-Compliance", "GOVERNANCE_PATH='" _
& Lite & "' And [REVIEWED_GATE] ='" & M06 & "'")

For example, is there any connection object that I need to set?
Could you please, provide me the complete command to execute it?

Thanks and regards.

PH.

Klatuu said:
There are a couple of issues here to determine how to do this. I can't tell
from your select statement whether you are expecting more that one row to be
returned by this query. Based on this line:
Form_PROCESS_COMPLIANCE.FullM15 = qryTest
it appears that is what you are doing. A query would not be the best way to
do this. I would recommend a DLookup function:

Form_PROCESS_COMPLIANCE.FullM15 = DLookup("[NonCompliance]", _
"Form-Process-Compliance", "GOVERNANCE_PATH='" _
& Lite & "' And [REVIEWED_GATE] ='" & M06 & "'")

PH said:
Dears,

Good afternoon.
Could someone help me on this?
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

Thanks and regards.

PH.
Click to expand...
Click to expand...
 
G

Guest

That is the complete statement.
Form_PROCESS_COMPLIANCE.FullM15 = DLookup("[NonCompliance]", _
"Form-Process-Compliance", "GOVERNANCE_PATH='" _
& Lite & "' And [REVIEWED_GATE] ='" & M06 & "'")

Let me break it down for you and see if it makes more sense, or perhaps I
did not copy from the original code correctly.

[NonCompliance] is the name of the field that DLookup will return a value
from based on the Criteria which is the third argument.

Form-Process-Compliance is the name of the table containing the field
[NonCompliance]. Looking at it again, it may be the form name as I copied it
from your code. In any case, it needs to be the name of the table

"GOVERNANCE_PATH='" _
& Lite & "' And [REVIEWED_GATE] ='" & M06 & "'")

GOVERNANCE_PATH is another field in your table. It will find a row where
the value in GOVERNANCE_PATH is equal to something name Lite. I suggest in
be enclosed in brackets like the other field names.

[REVIEWED_GATE] is another field in your table. It will find a row where
the value in [REVIEWED_GATE] is equal to something named M06.

The DLookup will return the value in the [NonCompliance] field where both
the above Criteria match.

Let me know if there is more you need.

PH said:
Dear Klatuu,

Good afternoon.
Thanks for your help, however, I still couldn't execute this command.
I need to know all commands before and after to execute this command you
suggest:

Form_PROCESS_COMPLIANCE.FullM15 = DLookup("[NonCompliance]", _
"Form-Process-Compliance", "GOVERNANCE_PATH='" _
& Lite & "' And [REVIEWED_GATE] ='" & M06 & "'")

For example, is there any connection object that I need to set?
Could you please, provide me the complete command to execute it?

Thanks and regards.

PH.

Klatuu said:
There are a couple of issues here to determine how to do this. I can't tell
from your select statement whether you are expecting more that one row to be
returned by this query. Based on this line:
Form_PROCESS_COMPLIANCE.FullM15 = qryTest
it appears that is what you are doing. A query would not be the best way to
do this. I would recommend a DLookup function:

Form_PROCESS_COMPLIANCE.FullM15 = DLookup("[NonCompliance]", _
"Form-Process-Compliance", "GOVERNANCE_PATH='" _
& Lite & "' And [REVIEWED_GATE] ='" & M06 & "'")

PH said:
Dears,

Good afternoon.
Could someone help me on this?
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

Thanks and regards.

PH.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Klatuu,

Good afternoon.
Once again, thanks a lot for your help.
I understood now, however, I have another problem.
Please, let me know if you can help me.

I have a text box in a form and inside the text box we have control source
property.
I am trying to execute the follow command inside the control source property:

=DLookUp("[NonCompliance]","Month-Process-Compliance-2","[REVIEWED_GATE] =
'" & M02 & "'")

The NonCompliance is a query-field that I request the information.
The Month-Process-Compliance-2 is a query.
The REVIEWED_GATE is a query-field that has M02 information.

We don't receive any error when I execute it, however the text box are not
being populated with the NonCompliance value. It brings me an empty value. I
already check the query results and there is a row for M02 information.

I tried with another text box and it worked fine:
=DLookUp("[PROJECT_NUMBER]","PROJECTS","PROPOSAL_NUMBER=1")
The difference here is that PROJECTS is a table. However I tried with
another query and it runs ok too:
=DLookUp("[PROPOSAL_NUMBER]","IAC_Issue_Log","PROJECT_NUMBER=1")
The IAC_Issue_Log is a query. The difference is that the the WHERE clause
here is a number PROJECT_NUMBER=1.
So, I think the problem in my first statement that is not running is
something related to the WHERE statement using a string:
"[REVIEWED_GATE] = '" & M02 & "'"

Could you please, help me again?

Thanks and best regards.

PH.


Klatuu said:
That is the complete statement.
Form_PROCESS_COMPLIANCE.FullM15 = DLookup("[NonCompliance]", _
"Form-Process-Compliance", "GOVERNANCE_PATH='" _
& Lite & "' And [REVIEWED_GATE] ='" & M06 & "'")

Let me break it down for you and see if it makes more sense, or perhaps I
did not copy from the original code correctly.

[NonCompliance] is the name of the field that DLookup will return a value
from based on the Criteria which is the third argument.

Form-Process-Compliance is the name of the table containing the field
[NonCompliance]. Looking at it again, it may be the form name as I copied it
from your code. In any case, it needs to be the name of the table

"GOVERNANCE_PATH='" _
& Lite & "' And [REVIEWED_GATE] ='" & M06 & "'")

GOVERNANCE_PATH is another field in your table. It will find a row where
the value in GOVERNANCE_PATH is equal to something name Lite. I suggest in
be enclosed in brackets like the other field names.

[REVIEWED_GATE] is another field in your table. It will find a row where
the value in [REVIEWED_GATE] is equal to something named M06.

The DLookup will return the value in the [NonCompliance] field where both
the above Criteria match.

Let me know if there is more you need.

PH said:
Dear Klatuu,

Good afternoon.
Thanks for your help, however, I still couldn't execute this command.
I need to know all commands before and after to execute this command you
suggest:

Form_PROCESS_COMPLIANCE.FullM15 = DLookup("[NonCompliance]", _
"Form-Process-Compliance", "GOVERNANCE_PATH='" _
& Lite & "' And [REVIEWED_GATE] ='" & M06 & "'")

For example, is there any connection object that I need to set?
Could you please, provide me the complete command to execute it?

Thanks and regards.

PH.

Klatuu said:
There are a couple of issues here to determine how to do this. I can't tell
from your select statement whether you are expecting more that one row to be
returned by this query. Based on this line:
Form_PROCESS_COMPLIANCE.FullM15 = qryTest
it appears that is what you are doing. A query would not be the best way to
do this. I would recommend a DLookup function:

Form_PROCESS_COMPLIANCE.FullM15 = DLookup("[NonCompliance]", _
"Form-Process-Compliance", "GOVERNANCE_PATH='" _
& Lite & "' And [REVIEWED_GATE] ='" & M06 & "'")

:

Dears,

Good afternoon.
Could someone help me on this?
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

Thanks and regards.

PH.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to create a list of values using queries? 1
Using a sub procedure to change a subform property 5
How to execute SQL stored procedure from Access 8
Table uses a select statement as value to populate subform data 1
Problems getting an event procedure to run on a subform 2
Event Procedure Bug? 1
How can I create a SQL statement in the access control source? 2
Change textbox/combobox colors with a procedure 9

Top