Gowey Mike W
Mike.W.Gowey at doc.state.or.us
Fri Jan 27 08:54:22 CST 2006
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