Impoting/Connect data from a field according to the latest date

[shafara7]

I have a Form with a Subform on it and the subform is showing records in Table Component.
I just added a new field on the table, which will show the status colour of the component.
The status of the component is taken from another table called Table Component Order.
But for each component, there may exist more than one order. Refer photo.

How do I connect the Status from Table Component Order to Table Component with the following condition:
- Table Component only show the Status of the latest Order for each component.

For example, from the photo,
For Component 7001, the Status will show Green and 7002 it is Yellow.

 

[Orthodox Dave]

You could trigger the update of the Status with a procedure such as this:

Dim CompNo As Integer, OrdDate As Date, OrdStat As String

CompNo = Me.ComponentNumber
OrdDate = DMax("Date","TableComponentOrder","TableComponentOrder.ComponentNumber =" & CompNo)
OrdStat = DLookup("Status","TableComponentOrder","TableComponentOrder.ComponentNumber =" & CompNo & _
" And TableComponentOrder.Date = #" & OrdDate & "#")

Me.Status = OrdStat

You may or may not need the hash marks "#" around the date.

You'd have to decide what event you would use to trigger the update.

 

[shafara7]

Thank you Orthodox Dave.
But may I know where should I put this code? In VBA is it? If so, for which sub?

 

[Orthodox Dave]

Hi Shafara,

Yes it is VBA. You could put a command button there called "Update Status" and put the code in the On Click event. Then when anyone clicks the button, the Status colour shown will change to the latest.

I presume you have noticed I have put placeholder table names and fields (e.g. "TableComponentOrder", "ComponentNumber" etc) and you'll have to substitute these for the real names.

Good luck.