Gowey Mike W
Mike.W.Gowey at doc.state.or.us
Fri Jan 27 11:12:43 CST 2006
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]
Sent: Friday, January 27, 2006 10:06 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Function Error
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
>
>
>
>________________________________________________
>Message sent using UebiMiau 2.7.2
>
>--
>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
>
>
>--
>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
>
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>
>________________________________________________
>Message sent using UebiMiau 2.7.2
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com