Having trouble programming the combo box

[Amit]

Hi,

I have a form to enter Project information for a Program.
A Program can have other Programs as partners on a
Project. The Program (actually Program ID) is selected
using an unbound combo box, based on a query that gets the
Program names and ID.

I have a subform to select other Program partners. I would
like to display all programs EXCEPT the current Program
for which the information is being entered. I have an
unbound combo box, with the following code in its 'Row
Source':

SELECT [qlkpProgram].[ProgramID], [qlkpProgram].
[ProgramName] FROM qlkpProgram WHERE [qlkpProgram].
[ProgramID]<> Me!ProgramID;

This is not working. When I open the form, I get a box
that asks me to enter a value for 'Me!ProgramID'.

Any help on how to solve this problem will be appreciated.

Thanks!

-Amit

 

[Kevin McCartney]

Hi, Your row source looks like this to SQL "SELECT
[qlkpProgram].[ProgramID], [qlkpProgram].
[ProgramName] FROM qlkpProgram WHERE [qlkpProgram].
[ProgramID]<> Me!ProgramID"

Thus your Me!ProgramID is not the ID that you want to
pass. You'll need to place this in code eg on form_load
form_open and then requery the combo box.

Me!cboProgram.RowSource = "SELECT [qlkpProgram].
[ProgramID], [qlkpProgram].
[ProgramName] FROM qlkpProgram WHERE [qlkpProgram].
[ProgramID]<> " & Me!ProgramID
Me!cboProgram.Requery

Alternatively you can reference the form and object and
place this in your RowSource of the combo box, like:
SELECT [qlkpProgram].[ProgramID], [qlkpProgram].
[ProgramName] FROM qlkpProgram WHERE [qlkpProgram].
[ProgramID]<> Forms!frmProjects!txtProgramID

Best regards
KM

 

[John Smith]

'Me!ProgramID' is only valid in code. To refer to the current form in a row
source you have to use 'Form!ProgramID'. You will also need to requery the
combo in the form current event, otherwise it will always refer to the first
ProgramID visited.