By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,662 Members | 1,790 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,662 IT Pros & Developers. It's quick & easy.

How to read the Xth field in a table

P: 37
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?
Feb 28 '10 #1

✓ answered by 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:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fRetrieveFieldValue(strSource As String, intFldOffset As Integer)
    2. Dim MyDB As DAO.Database
    3. Dim rst As DAO.Recordset
    4.  
    5. Set MyDB = CurrentDb
    6. Set rst = MyDB.OpenRecordset(strSource)
    7.  
    8.  
    9. If intFldOffset > rst.Fields.Count Then
    10.   MsgBox "Offset Invalid", vbExclamation, "Invalid Field Offset"
    11.     fRetrieveFieldValue = Null
    12. Else
    13.   fRetrieveFieldValue = rst.Fields(intFldOffset - 1)
    14. End If
    15.  
    16. rst.Close
    17. Set rst = Nothing
    18. End Function
  2. Sample Function Call:
    Expand|Select|Wrap|Line Numbers
    1. Dim varRetVal As Variant
    2.  
    3. varRetVal = fRetrieveFieldValue("tblEmployees",6)
  3. Another Option (shortened Version) which will work for a Table only, would be:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fRetrieveFieldValue2(strSource As String, intFldOffset As Integer)
    2.   fRetrieveFieldValue2 = DLookup(CurrentDb.TableDefs(strSource).Fields(intFldOffset - 1).Name, strSource)
    3. End Function
    4.  
  4. Function Call would be exactly the same, except Call fRetrieveFieldValue2()
  5. Any questions, please feel free to ask.

Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,607
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:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fRetrieveFieldValue(strSource As String, intFldOffset As Integer)
    2. Dim MyDB As DAO.Database
    3. Dim rst As DAO.Recordset
    4.  
    5. Set MyDB = CurrentDb
    6. Set rst = MyDB.OpenRecordset(strSource)
    7.  
    8.  
    9. If intFldOffset > rst.Fields.Count Then
    10.   MsgBox "Offset Invalid", vbExclamation, "Invalid Field Offset"
    11.     fRetrieveFieldValue = Null
    12. Else
    13.   fRetrieveFieldValue = rst.Fields(intFldOffset - 1)
    14. End If
    15.  
    16. rst.Close
    17. Set rst = Nothing
    18. End Function
  2. Sample Function Call:
    Expand|Select|Wrap|Line Numbers
    1. Dim varRetVal As Variant
    2.  
    3. varRetVal = fRetrieveFieldValue("tblEmployees",6)
  3. Another Option (shortened Version) which will work for a Table only, would be:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fRetrieveFieldValue2(strSource As String, intFldOffset As Integer)
    2.   fRetrieveFieldValue2 = DLookup(CurrentDb.TableDefs(strSource).Fields(intFldOffset - 1).Name, strSource)
    3. End Function
    4.  
  4. Function Call would be exactly the same, except Call fRetrieveFieldValue2()
  5. Any questions, please feel free to ask.
Feb 28 '10 #2

P: 37
thank you so such a detailed response so quickly!
Mar 1 '10 #3

ADezii
Expert 5K+
P: 8,607
You are quite welcome.
Mar 1 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.