i have table named employees field named employeeid. in separate table, have separate master table of employees named master field named employeestatus. trying validate employees have been terminated company not listed in employees table.
however, current code using below returning duplicate of first record in master table of employees. value of record count property of recordset object matches expect, total number of terminated employees. rs.fields(0) displays duplicate of first matching record in master table. seen debug.print in immediate window. have check following:
- trailing , leading spaces in field names
- proper quoting of strings
- sql , vba syntax
how can fix code display matching records?
public function validemployee(empid string) dim dbs dao.database dim rs dao.recordset dim sqlstring string set dbs = currentdb sqlstring = "select [employeeid] [master] [employeestatus] = 'terminated'" set rs = dbs.openrecordset(sqlstring) rs.movelast debug.print rs.recordcount debug.print rs.fields(0)
you want loop through recordset, this;
public function validemployee(empid string) dim dbs dao.database dim rs dao.recordset dim sqlstring string set dbs = currentdb sqlstring = "select [employeeid] [master] [employeestatus] = 'terminated'" set rs = dbs.openrecordset(sqlstring) rs if .recordcount > 0 'make sure query returns records .movelast 'move last first make sure rs knows record count .movefirst until .eof 'loop through until end of recordset debug.print rs.recordcount 'debug print our info debug.print rs.fields(0) loop end if end rs.close 'close off set rs = nothing typed above aircode should put on right track. current code doesn't loop through records, debug printing last records value of field 0.
