How to read the Xth field in a table

[garfieldsevilla]

I have a table with 14 fields and need to access a field based on an offset value.

I have named the fields <a> <b> <c01> <c02> <c03> <c04> .. <c11> <c12> and thought I could build a string to access the data using dlookup. A parameter passed to the VBA module is the offset from the third field, so a 1 means <c01>, 7 <c07>, 12<s12> etc

Is there a more elegant way to do this?

[ADezii]

I hope I am reading your request correctly, and I'm assuming that your Table consists of only a single Record. Given this, the following code will retrieve the Value of the 6th Field in the tblEmployees Table. Simply pass to the Function your Table Name and 'Absolute' Field Offset.

1. Function Definition:
   

   Public Function fRetrieveFieldValue(strSource As String, intFldOffset As Integer)
   Dim MyDB As DAO.Database
   Dim rst As DAO.Recordset
   
   Set MyDB = CurrentDb
   Set rst = MyDB.OpenRecordset(strSource)
   
   
   If intFldOffset > rst.Fields.Count Then
     MsgBox "Offset Invalid", vbExclamation, "Invalid Field Offset"
       fRetrieveFieldValue = Null
   Else
     fRetrieveFieldValue = rst.Fields(intFldOffset - 1)
   End If
   
   rst.Close
   Set rst = Nothing
   End Function

2. Sample Function Call:
   

   Dim varRetVal As Variant
   
   varRetVal = fRetrieveFieldValue("tblEmployees",6)

3. Another Option (shortened Version) which will work for a Table only, would be:
   

   Public Function fRetrieveFieldValue2(strSource As String, intFldOffset As Integer)
     fRetrieveFieldValue2 = DLookup(CurrentDb.TableDefs(strSource).Fields(intFldOffset - 1).Name, strSource)
   End Function

4. Function Call would be exactly the same, except Call fRetrieveFieldV alue2()
5. Any questions, please feel free to ask.

[garfieldsevilla]

thank you so such a detailed response so quickly!

[ADezii]

You are quite welcome.