Base Combobox values on another form control

[jhrBanker]

I have a Combobox control (StatCD) on a form (frmProjectMaint)for Project Status based on a “Status” table. I also have a field on the form which contains the Project End Date (“EndDT”). If the project has an end date (ie; Is Not Null), I want the Combobox values to only display the table values “On-Hold, Completed, Killed, No GL Impact or RGFO Completed”(TermDtReq=Y). However, if there is no EndDT, I want Combobox values to only display the table values “Active or Not Started”(TermDtReq Is Null).

Can I accomplish this by modifying the Combobox Row Source SQL?

Row Source: SELECT [Status].[StatCd], [Status].[Status] FROM [Status] ORDER BY [Status];

Form: frmProjectMaint (based on qryProjectsMaint)
Field: EndDT
CB: StatCD

Table: Status

StatCD	Status	TermDtReq
1	Active
2	On-Hold	Y
3	Completed	Y
4	Killed	Y
5	Not Started
6	No GL Impact	Y
7	RGFO Completed

Thanks

Using Access 2007 with mdb db

[orange]

Have you found a solution to this?
I think you should be able to do so with adjusting the row source and a requery.
Just looking at your sql ---Im surprised that Access isn't confused with the various usages ofstatus.

[jhrBanker]

No, I have not gotten an answer yet. I don't know how to modify the SQL query in the row source to accomplish my goal.

[orange]

In theory/conceptually you need some sql to populate the row source of the combo, and depending on the value of Project End Date (“EndDT”) in the current record. (---------------------untested air code--------------------------- )

     If  IsDate(endDt) = True then
  me.comboname.rowsource = "SELECT  [Status].[StatCd], [Status].[Status] FROM [Status] " _                                            & "WHERE trmdtReq = 'T' ORDER BY [Status];" 
 else 
me.comboname.rowsource = "SELECT  [Status].[StatCd], [Status].[Status] FROM [Status] "                                             & "WHERE trmdtReq  'T' ORDER BY [Status];"  
End If

Note: I don't know if it's just me or the forum, but I can not edit properly; doesn't respect crlf can not edit the code within code boxes and have lost all the editing tools!!!!

[jhrBanker]

Thanks. I'll give it a try