[AccessD] Function Error

Nope that wasn't the problem, still get the same error. 

-----Original Message-----
From: papparuff at comcast.net [mailto:papparuff at comcast.net] 
Sent: Friday, January 27, 2006 8:01 AM
To: Access Developers discussion and problem solving; Access Developers
discussion and problem solving
Cc: Gowey Mike W
Subject: Re: [AccessD] Function Error

You forgot to explicitly declare the database and recordsets.

Dim oDB As Database
  Dim oRS As Recordset
  Dim oRSBins As Recordset

shhould be 

Dim oDB As DAO.Database
  Dim oRS As DAO.Recordset
  Dim oRSBins As DAO.Recordset



--
John V. Ruff - The Eternal Optimist :-) 

"Commit to the Lord whatever you do,
and your plans will succeed." Proverbs 16:3

-------------- Original message --------------
From: "Gowey Mike W" <Mike.W.Gowey at doc.state.or.us> 

> 
> 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
> 
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com