RE: How to execute a select statement inside an Event Procedure?
- From: "PH" <PH@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 18 Jan 2006 14:32:03 -0800
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" wrote:
> 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" wrote:
>
> > 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" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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.
.
- References:
- Prev by Date: Re: Help, Error Message...Too Few parameters. Expected 3.
- Next by Date: Re: Data mismatch............can find form??
- Previous by thread: RE: How to execute a select statement inside an Event Procedure?
- Next by thread: Data mismatch............can find form??
- Index(es):
Relevant Pages
|
