Check another table and return a value

ewayne99

New member
Local time
Today, 06:31
Joined
Sep 30, 2021
Messages
8
I have two tables... competitor_tbl and old_competitor_tbl. When I input data into the competitor_tbl I want the field ssn to check my old_competitor_tbl to see if the inputted ssn is in the old_competitor_tbl. If not return a value of new to a field called status in the competitor_tbl. Any thoughts would be helpful.

Thanks,
Wayne
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:31
Joined
Oct 29, 2018
Messages
21,358
Hi Wayne. Look for examples of using DCount().
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:31
Joined
Oct 29, 2018
Messages
21,358
Thanks theDBguy!!!
Okay, in front of a computer now. Here's an example:
Code:
Private Sub ssn_AfterUpdate()
If DCount("*", "old_competitor_tbl", "ssn='" & Nz(Me.ssn,"") & "'")>0 Then
    Me.Status = "New"
End If
End Sub
 

ewayne99

New member
Local time
Today, 06:31
Joined
Sep 30, 2021
Messages
8
Okay, in front of a computer now. Here's an example:
Code:
Private Sub ssn_AfterUpdate()
If DCount("*", "old_competitor_tbl", "ssn='" & Nz(Me.ssn,"") & "'")>0 Then
    Me.Status = "New"
End If
End Sub
theDBguy.... i changed >0 to =0 and it works perfectly. Thank you so much!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:31
Joined
Oct 29, 2018
Messages
21,358
theDBguy.... i changed >0 to =0 and it works perfectly. Thank you so much!
Oops, sorry, you're correct. Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom