[AccessD] Function Error

Mike,

After reading the past few messages...I noticed you said it ran similar 
statements without an issue...so I pasted all SQL related statements and 
compared.  The only difference I could see was the statement in question had 
an orderBy clause.  The I remembered an app I had that crashed because of a 
corrupt field...you can't sort if a field is corrupt...or DivBy0.

Just a wild guess to look at...assuming the code compiles ok...just crashes 
on execution.

Good Luck,

Mark A. Matte


>From: "Jürgen Welz" <jwelz at hotmail.com>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: accessd at databaseadvisors.com
>Subject: Re: [AccessD] Function Error
>Date: Fri, 27 Jan 2006 10:26:22 -0700
>
>Try commenting out the sysCmd.  It frequently fails on the systems I work 
>on.
>
>So does it fail when you set the string value or when you OpenRecordset?
>
>If OpenRecordset:
>
>Try replacing oDB with CurrentDb.
>
>Ciao
>Jürgen Welz
>Edmonton, Alberta
>jwelz at hotmail.com
>
>
>
>
>
>>From: "Gowey Mike W" <Mike.W.Gowey at doc.state.or.us>
>
>>The original actually looks like this
>>
>>sSql = "SELECT SID FROM qryInmatesBinNumberUpdate WHERE Row = '" & j & "' 
>>ORDER BY Row"
>>Set oRS = oDB.OpenRecordset(sSql)
>>
>>So a colon won't help, thanks for the thought
>>
>>-----Original Message-----
>>From: Jürgen Welz [mailto:jwelz at hotmail.com]
>>
>>Put in a colon at the end of 'Order by Row"
>>
>>original
>>
>>sSql = "SELECT SID FROM qryInmatesBinNumberUpdate WHERE Row = '" & j & "' 
>>ORDER BY Row" Set oRS = oDB.OpenRecordset(sSql)
>>
>>revised
>>
>>sSql = "SELECT SID FROM qryInmatesBinNumberUpdate WHERE Row = '" & j & "' 
>>ORDER BY Row": Set oRS = oDB.OpenRecordset(sSql)
>>
>>
>>
>>Ciao
>>Jürgen Welz
>>Edmonton, Alberta
>>jwelz at hotmail.com
>>
>>
>>
>>
>>
>> >--------- Original Message --------
>> >
>> >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