Inline SQL statement returning only duplicate first record in recordset

Question

I have a table named Employees with a field named EmployeeID. In a separate table, I have a separate master table of employees named Master with a field named EmployeeStatus. I am trying to validate all employees who have been terminated from the company are not listed in the Employees table.

However, current code I am using below is returning duplicate of the first record in the master table of employees. The value of record count property of the recordset object matches what I expect, the total number of terminated employees. rs.Fields(0) however only displays duplicate of the first matching record in the Master table. as seen from debug.print in the immediate window. I have already check for the following:

Trailing and leading spaces in field names
Proper quoting of strings
SQL and VBA syntax

How can I fix my code to display all matching records?

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
debug.print rs.recordcount
debug.print rs.fields(0)

You need to create a loop. Inside the loop you need to move to next record. I recommend you start with the first record (for some reason you call the moveLast method — cha
Can you provide the Table definition for [employee], [master] tables? we might help you to create the join query. — Krish

Mark Horner Mark Horner · Accepted Answer · 2015-04-28T20:22:05

You want to loop through the recordset, something like this;

        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)
with rs
if .recordcount > 0 Then 'make sure the query returns records
    .moveLast 'move last then back to first to make sure rs knows the record count
    .movefirst
    do until .eof 'loop through until the end of the recordset
       debug.print rs.recordcount 'debug print our info
       debug.print rs.fields(0)
    loop
end if
end with
rs.close 'close off
set rs = nothing

Typed the above from aircode but it should put you on the right track. Your current code doesn't loop through the records, it is just debug printing the last records value of field 0.

Inline SQL statement returning only duplicate first record in recordset

1 Answers