Return records from one table based on date in another

[glawson]

Hi,

Not sure if this might come under Forms or Queries, so I'll try here first.

When I add a record to table A (using a form) the first field I enter is a date. That date will then determine which records I see in my drop down list from table B (via a query).

Some records in table B have EffectiveFrom and EffectiveTo dates recorded - because they are now obsolete, other records have no dates recorded in these fields because they active.

How can I view and then select from the drop down only those records in table B which are effective/valid to the date entered in table A?

Thanks
Gareth

 

[Mr Smin]

In the form's date box, have an onUpdate event which refreshes the dropdown.
In the query which populates the dropdown, have a Where clause which uses the value from the form
=Forms!yourFormname!txtDate
You might need to have a default date for the query to fall back on as it will otherwise try to get the date when you open the form but before you enter a date and so raise an error.