combo box value display

[SunTop]

How to display values in a combo box based on a certain criteria ? for example I want to display some values if the current date is after a certain date and display other values if the current date is before a certain date ?

[CJ_London]

you need to modify the rowsource on some event - might be the form open event, might be the after update event of the control that sets the 'certain' date. without more information - your current rowsource, what determines which records to show, how the 'certain' date is determined etc, it is not possible to be more specific

[SunTop]

Thanks for your reply Ajax

I have a form in my database that lists the details of a work order (location, type, issue date, expiry date). The combo box picks values from another table (completed, pending, late). I want the combo box to display only completed or pending when the current date is before the expiry date and when the current date is after the expiry date then I want the combo box to display all the values (pending, completed or late).

[CJ_London]

? details of your rowsource - i.e. the sql you are currently using and details of the underlying table it is querying

[SunTop]

The record source of the form is a query, the location and type fields are picked from a table and the issue date and expiry date is picked from another table. These two tables are not related directly, rather they are joined by a “mid” table.

Edit: the first table has another field (WorkOrderStatus), this field is left empty when the record is first created, this field is a combo box and it picks its values from a third table that is joined to the first table (I could have used a list of values here also), in this field I want the user to pick either completed, pending or late if the current date is after the expiry date, but if the current date is before the expiry date then I want the combo box only to display pending or completed.

[CJ_London]

hmm, don't think I can help more than my suggestion in post #2. If you cannot provide the detail requested, I don't see how I can provide a more detailed suggestion. Solution might be some vba code of some sort, or it might be in the sql, or it might be two different queries - could be anything really.

[SunTop]

Thanks for your reply Ajax

would it help if I provide a sample database ?

[CJ_London]

can if you want - remove any sensitive data, compact the db then zip it

[SunTop]

Thanks for you reply Ajax

Here is the sample database

The status combo box in the followup form has three options (pending, completed, late) if the expiry date is after the current date, I need the status combo box to display (pending or completed), if the expiry date is before the current date, I need the status combo box to display all three options

[CJ_London]

For future reference you are talking about cascading combos - and in this case on a continuous form.

1. You need an additional column in your status table to identify those records to appear after the expiry date. called say DisplayAfter as a yes/no field and set to true if required to display after
2. Once you have done that, in the Status on enter event put

status.rowsource="SELECT [Status].[ID], [Status].[Status] FROM Status WHERE ExpiryDate>Date()=DisplayAfter"

3. then in the Status on exit event put

status.rowsource="SELECT [Status].[ID], [Status].[Status] FROM Status"

[SunTop]

It worked perfectly !
Thank you Sir for your time and patience