How to create a list of values using queries?

[PH]

Dears,

Good afternoon.
Could someone help me?

First of all, let me try to explain the problem:
I have a form (f1) and a sub-form (sb1).
I have a table called Deliverable with the follow fields:
Governance | Gate | Deliverable
For each Governance + Gate I can have more than one deliverable value.

In the form f1, I have a field called Governance that has the value for
Governance.
In the form sb1, I have a field called Gate that has the value for Gate.

In the form sb1, I have a list box called deliverable that I need to show
the list of deliverables.

I tried to do it, using the DLookup function:

Form_sb1.DELIVERABLE = DLookup("[DELIVERABLE]", "[DELIVERABLES]", _
"GOVERNANCE=[Form_f1.GOVERNANCE] AND GATE=[Form_sb1.Gate]")

However, as I am looking for more than one value, it doesn't work.

Also, I tried to create a query inside the OnFocus event procedure:

==============================================
Private Sub DELIVERABLE_GotFocus()

Dim XGovernance As String
Dim XGate As String
Dim qryTest As String

XGovernance = Form_f1.GOVERNANCE
XGate = Form_sb1.GATE

qryTest = "SELECT DELIVERABLE FROM DELIVERABLES WHERE GOVERNANCE='" &
XGovernance & "'And GATE='" & XGate & "';"

Form_sb1.DELIVERABLE = qryTest
Form_sb1.DELIVERABLE.Requery

End Sub

===========================================

And it doesn't work again.
Is there any other command I need to insert here?
Please, let me know how to fix it or give a new suggestion on how to do it.


Also, I tried to create a query in the Queries option, and put this query in
the RowSource property of my list box, however the values for Governance and
Gate are under the form and subform (f1 and sb1) and I don't know how to send
this parameters to the query when I execute the form.

Could you please, help me to solve this problem?


Thanks and regards.

PH.

 

[Douglas J Steele]

Form_sb1.DELIVERABLE.RowSource = qryTest

Also, make sure Form_sb1.DELIVERABLE.RowSourceType is set to "Table/Query"