Combo Box Control (1 Viewer)

Looking for some help solving this issue.

I have a combo box (cboContract) that has a query (Q_Contracts) for a row source. This combo box is for Contract Numbers.
cboContract is in a subform (F_Contract), which is within a the main form, that is based on projects (F_Project).
(So, parent form is F_Project and child is F_Contract.)
In F_Contract, I have cboContract and a check box (ckActive) for Active or Not Active, nothing else.

As projects and their respective contracts are closed out, I did not want to see them in the drop down for cboContract when I create a new project.

My first attempt was to place False in the criteria for the Active field in Q_Contracts. That works great for when I create a new project. When I add a contract number, I then choose from the drop down which gives me all the active contract numbers but, when I look back at closed projects (ie. other records) I no longer can see the contract numbers that have been filtered out by the query.

So my question is, how can I filter out the non-active contract numbers for new projects, but still see the non-active contract numbers in the older projects?

You can test for NewRecord in the current event and change the row source of the combo accordingly.

So how would this look?

If Me.NewRecord Then
DoCmd.OpenQuery "Q_Contract",
Else
DoCmd.OPenQuery "Q_Contract1",


I'm not sure how to do this.
I think I understand what you are saying though.
If it's a new record use this query for the row source of the combo box.
Else, use this other query for the row source of the combo box.

But how do I write that in VBA?

You don't want to open a query, you want to set the rowsource property of the combo:

Me.ComboName.RowSource = "QueryName"

or you can set it to SQL

Me.ComboName.RowSource = "SELECT...FROM...WHERE..."

Thanks Paul!

I used this code in the On Current event of the subform and works perfect.

If Me.NewRecord Then
Me.cboContract.RowSource = "Q_ContractCombchoose1"
Else: Me.cboContract.RowSource = "Q_ContractCombchoose"
End If

The more I learn VBA and the cool stuff you can do with it, the more I want to learn.

Happy to help!