Use IF function to identify if identical values exist in another table?

[gojets1721]

Terrible title, I'm sorry.

I have a form that looks at record one at a time. For whatever record I'm on, I want to use VBA via a command to check if that record's ComplaintNumber is listed at all in a completely separate table (tblEmployees). This table also has a ComplaintNumber field.

Here's the IF function I'm using:

    If ("SELECT ComplaintNumber FROM tblEmployees WHERE [ComplaintNumber] =" & [Forms]![frmComplaints]![ComplaintNumber]) = "" Then
        MsgBox "No Values present in tblEmployees"
    Else
        MsgBox "Values present in tblEmployees"
    End If

The issue is that it's always saying that values are present even though there aren't. Any suggestions? Let me know if an example DB is needed.

 

[ebs17]

Terrible statement.
Use DLookup.

If IsNull(DLookup("ComplaintNumber", "tblEmployees", "ComplaintNumber =" & Forms.frmComplaints.ComplaintNumber)) Then ...

 

[plog]

I vote DCount:

MS Access: DCount Function

This MSAccess tutorial explains how to use the Access DCount function with syntax and examples. The Microsoft Access DCount function returns the number of records from an Access table (or domain).

www.techonthenet.com

No need to test for nulls because its guaranteed to return a value (even if value is 0)

 

[plog]

First, I stand by DCount, but I am looking at your code more closely and want to point out a few things:

    If ("SELECT ComplaintNumber FROM tblEmployees WHERE [ComplaintNumber] =" & [Forms]![frmComplaints]![ComplaintNumber]) = ""

1. You can't just declare an SQL statement in VBA and have to return results. You must use DoCmd.RunSQL to execute your SQL. Two different languages requiring two different environments to run correctly. In VBA you get into the SQL environment by DoCmd.RunSQL:

DoCmd.RunSQL method (Access)

Office VBA reference topic

learn.microsoft.com

Of course that's more for executing action queries (UPDATE/INSERT/DELETE, etc.) and not for SELECT queries. It just runs a query and doesn't do anything with the results.

2. Because of #1 your if statement is simply comparing strings to each other. "SELECT Complaint..." = "". Those two strings will never be equal because one has your SQL string in it and the other is empty. That if will always return false which means your code will always run the Else portion.[/CODE]

 

[Solo712]

Terrible title, I'm sorry.

I have a form that looks at record one at a time. For whatever record I'm on, I want to use VBA via a command to check if that record's ComplaintNumber is listed at all in a completely separate table (tblEmployees). This table also has a ComplaintNumber field.

Here's the IF function I'm using:

    If ("SELECT ComplaintNumber FROM tblEmployees WHERE [ComplaintNumber] =" & [Forms]![frmComplaints]![ComplaintNumber]) = "" Then
        MsgBox "No Values present in tblEmployees"
    Else
        MsgBox "Values present in tblEmployees"
    End If

The issue is that it's always saying that values are present even though there aren't. Any suggestions? Let me know if an example DB is needed.

I think you always get the "values" message because the statement does not protect against null string.
Try

If Nz("Select".....

 

[Pat Hartman]

I'm with @plog dCount() is just simpler because it always returns a count. 0 means false. Any other value means true.

 

[Josef P.]

Or use DExists

Public Function DExists(Byval Domain As String, optional byval Criteria As Variant) As Boolean
   DExists = DCount("1", Domain, Criteria) > 0
   ' or
   'DExists = Nz(DLookup("True", Domain, Criteria), False)
   ' or
   ' ...
End Function

Then later, depending on the season or the phase of the moon, you can choose the one that suits you at the moment.

Some variants that Colin tested:

Speed Comparison Tests 7

This article discusses 7 different methods for checking if a record exists. It is the seventh in a series of articles discussing various tests done to compare the efficiency of different approaches to coding. Example databases are provided so the tests can be done on your own workstations

www.isladogs.co.uk

 

[gojets1721]

Terrible statement.
Use DLookup.

If IsNull(DLookup("ComplaintNumber", "tblEmployees", "ComplaintNumber =" & Forms.frmComplaints.ComplaintNumber)) Then ...

That worked! Thanks so much!