I smashed this up really quick on a test form to see if the concept works:
Code:
Private Sub List3_AfterUpdate()
If List3.ListCount > 0 Then
ListArrayRowSource = "20GP All In;40GP All In;40HC All In;"
For i = 1 To List3.ListCount - 1
ValidFromDate = SQLDate(List3.Column(6, i))
ValidToDate = SQLDate(List3.Column(7, i))
List3RowID = DLookup("[ID]", "FRT_Additionals_Table", "[Carrier] = '" & List3.Column(3, i) & "' AND " & ValidFromDate & " Between [Valid From] AND [Valid To]")
Found20FRTValue = DLookup("[20GP Cost]", "FRT_Table", "[ID] = " & List3.Column(0, i))
Found20BAFValue = DLookup("[20GP BAF]", "FRT_Additionals_Table", "[ID] = " & List3RowID)
Found20GRIValue = DLookup("[20GP GRI]", "FRT_Additionals_Table", "[ID] = " & List3RowID)
Cal20GPAllInValue = Found20FRTValue + Found20BAFValue + Found20GRIValue
Found40FRTValue = DLookup("[40GP Cost]", "FRT_Table", "[ID] = " & List3.Column(0, i))
Found40BAFValue = DLookup("[40GP BAF]", "FRT_Additionals_Table", "[ID] = " & List3RowID)
Found40GRIValue = DLookup("[40GP GRI]", "FRT_Additionals_Table", "[ID] = " & List3RowID)
Cal40GPAllInValue = Found40FRTValue + Found40BAFValue + Found40GRIValue
Found40HCFRTValue = DLookup("[40HC Cost]", "FRT_Table", "[ID] = " & List3.Column(0, i))
Found40HCBAFValue = DLookup("[40HC BAF]", "FRT_Additionals_Table", "[ID] = " & List3RowID)
Found40HCGRIValue = DLookup("[40HC GRI]", "FRT_Additionals_Table", "[ID] = " & List3RowID)
Cal40HCAllInValue = Found40HCFRTValue + Found40HCBAFValue + Found40HCGRIValue
ListArrayRowSource = ListArrayRowSource & Cal20GPAllInValue & ";" & Cal40GPAllInValue & ";" & Cal40HCAllInValue & ";"
Next i
List5.RowSource = ListArrayRowSource
Else
End If
SelectedValue = List3.ListIndex + 1
List5.Selected(SelectedValue) = True
End Sub
Seems to work, just need to add formating in etc.
But I need to working on the date selection part, to see if I can get what I want out of it.
I'm surprised that Access doesnt have a more elegant want to handle this sort of thing though, surely I'm not the first in the world to want to apply values based on valid dates.