Validating records in one table based on data in another table

Question

This question is an offshoot of this question here. Using an loop to loop through the recordset based on the Master table, I was able to get all the records returned by my inline Sql statement to show. However, when I actually put the validation code in the loop, valid employees are being reported as invalid and vice versa.

Public Function validEmployee(EmpID as String)

Dim Dbs As DAO.database
Dim rs As DAO.recordset
Dim sqlString as String
set dbs = CurrentDb

sqlString = "SELECT [EmployeeID] FROM [MASTER] WHERE [EmployeeStatus] = 'Terminated'"

set rs = dbs.OpenRecordset(sqlString)
rs.MoveLast
rs.MoveFirst // obtain accurate count of records in recordset

If Not (rs.BOF and rs.EOF) Then // Verify recordset is not empty
    Do Until rs.EOF
        If InStr(1, rs.fields("EmployeeID"), EmpID, vbTextCompare) = 0
            validEmployee = "Valid employee"
        Else
            validEmployee = "Employee" & EmpID & "is invalid"
            Exit Do
        End If
    Loop
    rs.moveNext
End If

Some of the steps I have tried include:

checking for leading or trailing spaces in field names
validating the values of EmpID and rs.fields("EmployeeID") via debug.print
Checking for syntax errors both in SQL and in VBA
Quotation / escaping of string literals in SQL statement

I feel the problem could be in the way I wrote my InStr() comparison. Comparison using just a single record without the loop works fine.

You need to exit the loop once you hit the employee id in question, but why not instead include the employee id in your SQL? Also, your MoveNext needs to be before the Loop — Tim Williams

Tim Williams Tim Williams · Accepted Answer · 2015-04-29T03:26:18

You don't need to loop over all of the records to find a match: the database can handle that.

Public Function validEmployee(EmpID as String)

    Dim rs As DAO.recordset
    Dim sqlString as String

    'add single-quotes around EmpID if it's not a numeric field
    sqlString = "SELECT count([EmployeeID]) as num FROM [MASTER] " & _
                " WHERE [EmployeeStatus] = 'Terminated' and " & _
                " [EmployeeID] = " & EmpID

    set rs = CurrentDb.OpenRecordset(sqlString)
    validEmployee = rs.Fields("num").Value>0
End If

Validating records in one table based on data in another table

1 Answers