Access VBA accessing element of recordset

Question

How do I select the actual variable if the recordset is JOINed from to tables?

 Dim db As DAO.Database
 Set db = CurrentDb
 Dim rs As DAO.Recordset
 Set rs = Currentdb.OpenRecordset("SELECT tableA.data, tableB.data “ & _
                                  “FROM tabelA INNER JOIN tableB ON tableA.ID = tableB.xyz”)

 Debug.Print (srs!tableA.data) ' does not work

Yes, data could be an element of tableA or B ... but how to specify?

THank you, Reinhard

Workaround like "as TabA_data" works but there must be a more elegant solution?

Answer 1

If you do indeed have two fields in different tables with the same name, then you have several options.

• Firstly, you could actually name the fields differently in the tables. For example, Address in table Customer could become CustomerAddress and Address in Supplier could be SupplierAddress. This is probably the approach that I would recommend, as it prevents ambiguity/confusion.

• Secondly, as you have mentioned, you could alias the field names in the query.

• Finally, you could refer to the fields in the recordset by their position:

Debug.Print rs(0)  ' Recordsets are 0-indexed, so this returns the first field

I'm not keen on doing this, as if extra fields are introduced into the recordset, this may no longer work.