Access 2003: Comparing field in one table to another

[JC21]

Hi All,

I would like to know how do I compare one field in one table to another in another table. I have one table which is the Archive table. It keeps a history of everything, I have another table which is updated weekly. In both table there is a field called status. If the status of the field changes from last week then on the form I wanted the status field to be bold. I was thinking of something like

If tbl_weekly.status <> tbl_archive.status then
Tbl.archive.status.FontColor = vbRed

I know that’s not exactly correct but I hope it gives the idea. On the form I don’t know how this can be coded. Any guidance, advice or example would be great. Hope everyone enjoys the holiday.

[puppydogbuddy]

Hi All,

I would like to know how do I compare one field in one table to another in another table. I have one table which is the Archive table. It keeps a history of everything, I have another table which is updated weekly. In both table there is a field called status. If the status of the field changes from last week then on the form I wanted the status field to be bold. I was thinking of something like

If tbl_weekly.status <> tbl_archive.status then
Tbl.archive.status.FontColor = vbRed

I know that’s not exactly correct but I hope it gives the idea. On the form I don’t know how this can be coded. Any guidance, advice or example would be great. Hope everyone enjoys the holiday.

Happy Holidays to you, also!

OK, let's assume that the textbox on the form that holds the weekly status is named txtStatus. Try placing this code in the current event of your form:

Private Sub Form_Current()
Dim strStatus As String
' the following syntax assumes that the linkfield is a numeric datatype. 
strStatus = Dlookup("[Status]", "tbl_archive", "tbl_archive.[yourLinkfield] = " & Me!yourLinkToArchive)
 
If Me!txtStatus.Value <> strStatus Then
    Me!txtStatus.BackColor = vbRed
End If
 
End Sub

[JC21]

Thank you for the input puppydogbuddy, sorry for the late reply.