Restricting a the values dialogbox list based on the values in a form

[Fendi Baba]

I am creating a simple project information database. In the project
information form 1 have two subforms. one contains organization
involved - many organzation can be involved - and another list of
people; they are from the organization choosen, To help me select the
people, I created a query to the "projectorganization", this narrowed
down the list to pple who belongs to the organization which are
involved in one project or the other. so in this query, I have the
projectID, the organizationID and the people's id.

The problem I have not is that the dialogbox I created based on this
query does not limit the return to based on the projectID alone but in
fact the whole query. How do I "restrict" the dialogbox list?

Thanks for any suggestion.

[Larry Linson]

Use criteria with the Query. If you are opening a Form or report, you can
supply that information through the Filter, or the WhereCondition argument
of the DoCmd.OpenForm or DoCmd.OpenReport. If you are just running a Query
which displays a datasheet view (I'd recommend against doing it that way,
however) you will have to insert the Criteria in the Query. You can use VBA
code to modify the SQL, or you can have the Criteria refer to a Control on
an open Form.

Larry Linson
Microsoft Access MVP

"Fendi Baba" <ef*****@epitome.com.sg> wrote in message
news:11**********************@e56g2000cwe.googlegr oups.com...

I am creating a simple project information database. In the project
information form 1 have two subforms. one contains organization
involved - many organzation can be involved - and another list of
people; they are from the organization choosen, To help me select the
people, I created a query to the "projectorganization", this narrowed
down the list to pple who belongs to the organization which are
involved in one project or the other. so in this query, I have the
projectID, the organizationID and the people's id.

The problem I have not is that the dialogbox I created based on this
query does not limit the return to based on the projectID alone but in
fact the whole query. How do I "restrict" the dialogbox list?

Thanks for any suggestion.

[Fendi Baba]

Larry, my apologies, how do I insert the Do.Cmd criteria on the
openform command?

[Larry Linson]

"Fendi Baba" <ef*****@epitome.com.sg> wrote

Larry, my apologies, how do I insert the Do.Cmd
criteria on the openform command?

Best I can do is an example:

The Command Button Wizard will create code for you, in the Command Button's
Click Event that contains the DoCmd.OpenForm... it will look something like
this

Private Sub cmdOpenForm_Click()
On Error GoTo Err_cmdOpenForm_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmEmployeesFull"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdOpenForm_Click:
Exit Sub

Err_cmdOpenForm_Click:
MsgBox Err.Description
Resume Exit_cmdOpenForm_Click

End Sub

You add VBA code to create a "WHERE Condition without a WHERE" between the
line
Dim stLinkCriteria As String
and the line
stDocName = "frmEmployeesFull".

If, for example, you have a ComboBox on the same Form as the Command Button
that opens the form, and its name is cboEmployee, with its Bound Column
being EmployeeID, and that is the column used as the unique record ID in the
Employees Table or Query that is RecordSource for the Form frmEmployeesFull,
then you would add

stLinkCriteria = "[EmployeeID] = " & Me!cboEmployee

That's just about as detailed as I can get without trying to give a complete
course in VBA and the Access Object Model, which cannot be done in a
newsgroup response.

Larry Linson
Microsoft Access MVP

[Fendi Baba]

Thank you very much. You have given more than I expected.