By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,847 Members | 2,161 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

Access 2003: Comparing field in one table to another

P: 23
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.
Dec 24 '07 #1
Share this Question
Share on Google+
2 Replies


puppydogbuddy
Expert 100+
P: 1,923
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. Dim strStatus As String
  3. ' the following syntax assumes that the linkfield is a numeric datatype. 
  4. strStatus = Dlookup("[Status]", "tbl_archive", "tbl_archive.[yourLinkfield] = " & Me!yourLinkToArchive)
  5.  
  6. If Me!txtStatus.Value <> strStatus Then
  7.     Me!txtStatus.BackColor = vbRed
  8. End If
  9.  
  10. End Sub
Dec 25 '07 #2

P: 23
Thank you for the input puppydogbuddy, sorry for the late reply.
Dec 29 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.