Update values on Table1 depending the dates

[gstylianou]

Hello to all friends,



I need the help of the most experienced on Vba.

What i need:

• I have the attached database which includes 2 tables (Table1 and Table2) which are related One-2-Many (Table1 is the One and Table2 the Many)
• There is one form also with the name "Form1" which includes 11 unbound (ctr1,2,3...11) and are calculated with the values of fields [MainDate] , [Days] and [EndDays]

The Issue:

As you can see in the attached sample, on Table1 i can assign the "MainDate" value , the "Days" and on field "EndDays" i can have the value of the [MainDate]+[Days].

I need to update the field "RefNo" on Table1 on depending the date range of the field [EndDays] and the controls [ctr1-11]. Specifically i need : If the Date() is in the range of eg. [EndDate] and [ctr1] i need to have the value of the first record of the Table2 (RefNo) on field "RefNo" into Table1. If the Date() is the range of [EndDate] and [ctr2] i need the second record values of the field [RefNo] into Table2 and so over..

[recyan]

Till someone comes along,
A few queries :
1) By Date(), are you indicating Todays Date
2) End Date = Main Date + Days & ctr1 = Main Date + Days. So will End Date will always be same as ctr1.
3) Why is End Date (calculated) stored in Table1 ?
4) Why do you need to store RefNo (derived) in Table1 ?
5) What is the need for the 11 ctrs on the Form ?

Perhaps what you need can be achieved in a query.

Thanks