Once date satisfies update Main Data

[SBBmaster09]

I have 2 tables the attendance and the main data. In the attendance table I can enter start date and end date of the status. My problem is I need to update the main data. The attendance is connected in the profile. When the date today is equal to the start date the main data should be updated as "inactive" and it will stay until the end date is equal today, then once end date has passed it will automatically update back to Active.

Is this possible in Excel? Is this a code, or a query or a macro? Kindly teach me how on how would this be possible done in MS Access.

Thanks much.

 

[Ranman256]

It can be a query. The field on the main form would lookup the item in the query...

the textbox [status] would have rowsource = Dlookup("[Status]","qsMyQuery")
The query would look at the ID on the main form, and pull the data and offer either Active or Inactive as the status.

it may have to refresh if you make changes to the sub table. (click refresh all button on toolbar)

 

[SBBmaster09]

I know the dlookup functions, but how would the form know that it is the best time(date) to change the status from inactive to active?