How to exclude a particular value from a combo-box list in a subform?

  • Thread starter Thread starter Amit
  • Start date Start date
A

Amit

Hi,

I have a form to enter Project information. A project
belongs to one Program. There are many Programs. A Project
can have other Programs as partners.

While filling out a form for a particular Project (and the
Program associated with it), is there a way to exclude the
current Program from the Project-Partner list? I would
like all other programs to be available for selection.

TIA.

-Amit
 
You will have to set the rowsource of the combo box 'on the fly'.
And make sure to do it somewhere where the form doesnt get in a loop,
continuously changing the rowsource as the events fire.

Maybe in the combo box Got Focus event, something like:

Me.cmbProgram.rowsource = "SELECT ProgramID,ProgramName FROM tblPrograms
WHERE ProgramID <> " & me.ProgramID & ";"

would do roughly what you want. Change the names to your own field names,
of course.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
Amit said:
Hi,

I have a form to enter Project information. A project
belongs to one Program. There are many Programs. A Project
can have other Programs as partners.

While filling out a form for a particular Project (and the
Program associated with it), is there a way to exclude the
current Program from the Project-Partner list? I would
like all other programs to be available for selection.
Click to expand...

So the main form is based on Projects, the subform is to have one record
per ProjectPartner, and the combo box on the subform is for picking the
program that is to be a partner? If that's right, you can set the combo
box's RowSource query to use the main form's ProgramID field as a
criterion, along the lines of

SELECT ProgramID, ProgramName FROM Programs
WHERE ProgramID <> [Forms]![frmProjects]![ProgramID];

The only hitch is that you have to requery this combo box in the Current
event of the main form, and also in the AfterUpdate event of the
ProgramID control on the main form. The code would be something like:

Private Sub Form_Current()
Me.sfProjectPartners.Form!ProgramID.Requery
End Sub

Private Sub ProgramID_AfterUpdate()
Me.sfProjectPartners.Form!ProgramID.Requery
End Sub

I don't know the names of your forms and controls, of course, but
something along those lines ought to do it. Note that the reference to
the subform must use the name of the *subform control* on the main form,
which isn't necessarily the name of teh form that is being displayed in
that control.
 
Back
Top