We have already covered this topic without ever having given it a thought. On an open recordset, you return a field value by simply referring to it. There are, of course, several ways to do this.
The first method is to refer to the field by name, as in the code below.
Set rst = dbs.OpenRecordset("tblMyTable") MsgBox rst!CustomerNo 'or
Don't forget that the field name you use depends entirely on the table or query on which the recordset is based. For example, if the customer number is contained in the CustomerNo field, and the recordset gets its data directly from tblCustomers, then rs!CustomerNo would suffice.
However, if the recordset gets its data from a query in which the CustomerNo field is renamed (using the As keyword) to CustNo:
SELECT CustomerID, CustomerNo As CustNo, CustName FROM tblCustomers then you would use rs!CustNo.
You can also refer to a field by the recordset's Field object, as in the following example:
MsgBox rst.Fields!CustomerNo MsgBox rst.Fields("CustomerNo") MsgBox rst.Fields(2)
In Appendix C, you will find a section entitled Undocumented Tools and Resources. Within this appendix, a subsection entitled Recordset.Collect shows the comparative speed of these value retrieval methods, which you might find both interesting and useful.
Was this article helpful?