Public Function getRandomID(tmpTblName As String, tmpFldName As String) As Long
[COLOR=Green]'*******************************************************
' Code to obtain a Random ID between a Minimum
' and Maximum number. The code uses
' DAO Recordset object, to obtain the Min & Max
' and returns a Random value.
'
'Input : tmpTblName - Table Name - to obtain value from
' tmpFldName - Field Name - The ID field
'
' USAGE Exmaple:
' ? getRandomID("tbl_Clients","clientID")
' 5109
'
'Code Courtesy of
' Paul Eugin
'*******************************************************[/COLOR]
Dim dbObj As DAO.Database, rsObj As DAO.Recordset
Dim strSQL As String, minID As Long, maxID As Long
strSQL = "SELECT Min(" & tmpFldName & ") AS MinOfID, " & _
"Max(" & tmpFldName & ") AS MaxOfID FROM " & tmpTblName
Set dbObj = CurrentDb()
Set rsObj = dbObj.OpenRecordset(strSQL)
If rsObj.RecordCount <> 0 Then
minID = rsObj!MinOfID
maxID = rsObj!MaxOfID
getRandomID = Int((maxID - minID + 1) * Rnd + 1)
End If
Set dbObj = Nothing
Set rsObj = Nothing
End Function