UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> GetRecordValues    

This function retrieves a record and returns the field name and value of each field in a delimited string. This is particularly useful when you may have a requirement to send a few values related to a particular record through an openargs parameter when opening a form or report. Use the related GetListItem function to easily retrieve the values from the string that this function creates.


' Code courtesy of UtterAccess Wiki
' http://www.utteraccess.com/wiki/index.php/Category:FunctionLibrary
' You are free to use this code in any application,
' provided this notice is left unchanged.
' REV  DATE                          DESCRIPTION
' 1.0  2010-10-03              initial release
' NAME: GetRecordValues
' Under normal circumstances, this function should be used in conjunction with
' SQL statements that return only one record.  If more than one record is
' returned by the passed SQL statement, the values of the first record in the
' list is returned.  If no records are returned from the SQL, a ZLS is returned
' Records are returned in the following syntax:
' FIELD1=Value;FIELD2=Value;FIELD3=Value
' Fields containing NULL are returned as FIELD=NULL to differentiate from
' fields containing zero-length strings
'ErrHandler V3.01
Public Function GetRecordValues( _
   strSQL As String, _
   Optional Delimiter As String = ";" _
   ) As String
On Error GoTo Error_Proc
Dim Ret As String
 Dim s As String
 Dim rs As DAO.Recordset
 Dim i As Integer 'field loop counter

 s = ""

 Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
 If rs.RecordCount <> 0 Then
   For i = 0 To rs.Fields.Count - 1
     s = s & Delimiter & rs(i).NAME & "=" & CStr(Nz(rs(i).Value, "NULL"))
 End If
 Set rs = Nothing
 'remove trailing delimiter
 If Left(s, Len(Delimiter)) = Delimiter Then s = Right(s, Len(s) - Len(Delimiter))

 Ret = s
 rs.Close: Set rs = Nothing
 GetRecordValues = Ret
 Exit Function
 Select Case Err.Number
   Case Else
     MsgBox "Error: " & Trim(str(Err.Number)) & vbCrLf & _
       "Desc: " & Err.Description & vbCrLf & vbCrLf & _
       "Module: modSQLUtil, Procedure: GetRecordValues" _
       , vbCritical, "Error!"
 End Select
 Resume Exit_Proc
End Function

Creative Commons License
GetRecordValues by UtterAccess Wiki is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.
Editing and revision of the content is freely encouraged; for details, see Expected Usage.

Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 5,127 times.  This page was last modified 08:40, 6 April 2011 by Jack Leach.   Disclaimers