[AccessD] Function Error

Sorry the highlight didn't show, the part it starts failing at is here:

' get all SIDs that are not assigned a bin number for the current row
    sSql = "SELECT SID FROM qryInmatesBinNumberUpdate WHERE Row = '" & j
& "' ORDER BY Row"
    Set oRS = oDB.OpenRecordset(sSql)

-----Original Message-----
From: Gowey Mike W 
Sent: Friday, January 27, 2006 7:54 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Function Error


I'm converting a database from 97 to 2000 and have run into a problem
with the below function.  Could someone please tell me why the
highlighted part is failing?  I get a " Runtime error '5' Invalid
procedure call "


Public Function UpdateSID4Bin() As Boolean
  
  Dim oDB As Database
  Dim oRS As Recordset
  Dim oRSBins As Recordset
  Dim sSql As String
  Dim j As Long
  Dim vRtn As Variant
  
  UpdateSID4Bin = False
  
  Set oDB = CurrentDb
  
  DoCmd.Hourglass True
  
  ' display message in status bar
  vRtn = SysCmd(acSysCmdInitMeter, "Updating Bin Number System With New
SIDs...", 10)
  
  For j = 0 To 9
    ' update meter
    vRtn = SysCmd(acSysCmdUpdateMeter, j + 1)
    DoEvents
    
    ' get all SIDs that are not assigned a bin number for the current
row
    sSql = "SELECT SID FROM qryInmatesBinNumberUpdate WHERE Row = '" & j
& "' ORDER BY Row"
    Set oRS = oDB.OpenRecordset(sSql)
    
    Do While Not oRS.EOF
      ' grab an empty bin for the current row
      sSql = "SELECT TOP 1 BinNum FROM BinNumSIDAssign WHERE Row = '" &
j & "' AND SID IS NULL"
      Set oRSBins = oDB.OpenRecordset(sSql)
      
      If Not oRSBins.EOF Then
        ' update empty bin with current SID
        sSql = "UPDATE BinNumSIDAssign SET SID = '" & oRS.Fields("SID")
& "' WHERE Row = '" & j & "' AND BinNum = '" & oRSBins.Fields("BinNum")
& "'"
        oDB.Execute sSql
      End If
      
      ' get next SID
      oRS.MoveNext
      
    Loop
  Next
  
  UpdateSID4Bin = True
  
ExitFunction:
  On Error Resume Next
  
  oRS.Close
  oRSBins.Close
  oDB.Close
  
  Set oRS = Nothing
  Set oRSBins = Nothing
  Set oDB = Nothing
  
  DoCmd.Hourglass False
  vRtn = SysCmd(acSysCmdRemoveMeter)
  
End Function

Public Function CheckEvent(pEvent As String) As Boolean
  
  Dim oDB As DAO.Database
  Dim oRS As DAO.Recordset
  Dim sSql As String
  
  CheckEvent = False
  
  Set oDB = CurrentDb
  
  sSql = "SELECT Complete FROM DownloadEvents " & vbCrLf
  sSql = sSql & "WHERE EventName = '" & pEvent & "'"
  Set oRS = oDB.OpenRecordset(sSql)
  
  With oRS
    If Not .EOF Then
      CheckEvent = (.Fields(0) = True)
    End If
  End With
  
ExitFunction:
  On Error Resume Next
  
  oRS.Close
  oDB.Close
  
  Set oRS = Nothing
  Set oDB = Nothing
  
End Function

Public Sub InitDownloadEvents()
  
  Dim oDB As DAO.Database
  Dim sSql As String
  
  ' init events
  Set oDB = CurrentDb
  sSql = "UPDATE DownloadEvents SET Complete = False"
  oDB.Execute sSql
  
  oDB.Close
  Set oDB = Nothing
  
End Sub

Public Function SetEvent(pEvent As String) As Boolean
  
  Dim oDB As DAO.Database
  Dim sSql As String
  
  SetEvent = False
  
  Set oDB = CurrentDb
  
  sSql = "UPDATE DownloadEvents SET Complete = TRUE " & vbCrLf
  sSql = sSql & "WHERE EventName = '" & pEvent & "'"
  oDB.Execute sSql
  
  SetEvent = True
  
ExitFunction:
  On Error Resume Next
  
  oDB.Close
  Set oDB = Nothing
  
End Function


Thanks in advance for any help provided.


Mike Gowey  MCP, MCDST, A+, LME, NET+
Team Leader - SouthEast Region
Information Systems Unit

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com