I have the default value of Date() in the form, else I believe you would need to use the NZ function again for your date?
Thanks for your patience!! And thanks to Minty for chirping in. I think I'm getting there but I'm not sure I've properly explained what I'm really after which has probably led to the confusion in my head. Maybe if I use an example.
In the following the prefix combo shows the combo box. The fields are item, date added to order. I say order as I started off this thread with order thinking it would simplify it. I still think it does so I shall continue with it. The items in the combo box may have a date when discontinued.
Comboboxitem1 12/12/2018
Comboboxitem2 13/12/2019
Comboboxitem3 12/12/2018
.
ComboboxitemN 15/12/2018
The above appear on a continuous form. Each Line is a record in order table.
Lets say item 2 was discontinued on the 16/12/2018.
If the user clicks on the 2nd line he will want to see Item2 in the item list.
If the user clicks on line 1 or 3 through line N then item2 shouldn't appear in the list. Nor should it appear for a new record. This way he can't select a discontinued item other than the one that is already there.
I think the behavior I get from CJ_London and Gasman's solutions is that for new records it's perfect no discontinued items. For rows with a discontinued item I obviously have the item displayed but it also appears in the combo box for all the other rows that aren't discontinued.
Now i'm pretty sure, although I've done so many iterations I probably wouldn't be able to replicate it, that with either method I'd either get item2 appearing in the list for each line including the new record OR I'd get a blank entry for where it did exist and not elsewhere so the above would look like (the underscore _ represents a space.
Comboboxitem1 12/12/2018
____________ 13/12/2019
Comboboxitem3 12/12/2018
.
ComboboxitemN 15/12/2018
Now using bit from everyone's response I've ended up with something that is nearly what I want. I'm using the current event to dynamically build the rowsource for each row. So if they click on a row that isn't a discontinued item the list doesn't have a discontinued item in. If they click on a row that is a discontinued item then the list does. The new record will not have a discontinued item in.
This works except for 2 different scenarios. When the form is first opened. I believe the current record at this stage is the first displayed so the rest of the rows will take on the characteristic of the first row. Now if this is a discontinued item then every row will include the discontinued item. This doesn't matter because when you click on subsequent rows it refreshes itself. If the first row doesn't contain a discontinued item all rows will NOT contain discontinued items which is a problem as when the form is first displayed they will contain a blank. You could argue that as soon as the combo is clicked for those rows then the item reappears but it looks rubbish. Similarly if you click on a record/row which has an item that is not discontinued then all the discontinued items disappear (blanked out) again.
So am I misunderstanding the solutions or am I asking for something that is impossible to achieve.
Thanks for all of you for persevering with me!