Trouble outputting a recordset value with fieldname a reserved word in VBA

Question

In vba, I've created a recordset called 'rs' that includes a field named "EmployeeName" and another named "Position". If I execute

ThisWorkbook.Worksheets("Contact").Cells(row, 1) = rs("EmployeeName")

it works fine, but if I instead do

ThisWorkbook.Worksheets("Contact").Cells(row, 2) = rs("Position")

I get an error "Item cannot be found in the collection corresponding to the requested name or ordinal." Run-Time error 3265

I'm pretty sure this is because "Position" is a reserved word, but I don't know a work-around.

Can you reference the field by index? ie something like rs[1]? — Jerry, Jun 12 '13 at 20:48
Yes, but I just figured out the solution. I needed to do rs("Employees.Position"). "Employees" is the name of my table. — Ben, Jun 12 '13 at 20:50

score 0 · Answer 1 · answered Jun 12 '13 at 20:51

0

rs("Employees.Position"), where Employees is the name of my table, did the trick.

answered Jun 12 '13 at 20:51

Ben

Is your Recordset based on a query that references more than one table? – Gord Thompson Jun 12 '13 at 21:23
No, it's just based on one table. – Ben Jun 12 '13 at 21:39

1 Answers1