[AccessD] Function Error

Andy Lacey andy at minstersystems.co.uk
Fri Jan 27 11:41:55 CST 2006


Yea but "ORDER BY [Row]" too.

And put a Debug.Print sSql in to check that it contains what you expect it
to. If it looks ok copy what you see, create a new Query in Access, go to
SQL view and paste it in. then run it there and see if the SQL itself is ok.


-- Andy Lacey
http://www.minstersystems.co.uk 

> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com 
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of 
> Gowey Mike W
> Sent: 27 January 2006 17:01
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Function Error
> 
> 
> 
> You mean something like this?
> 
> sSql = "SELECT [SID], [Row] FROM qryInmatesBinNumberUpdate 
> WHERE [Row] = '" & j & "' ORDER BY Row" 
> 
> -----Original Message-----
> From: Andy Lacey [mailto:andy at minstersystems.co.uk] 
> Sent: Friday, January 27, 2006 9:57 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Function Error
> 
> Clutching at straws. Tried putting [] around SID, Row (twice) 
> and the query name?
> 
> --
> Andy Lacey
> http://www.minstersystems.co.uk
> 
> 
> 
> 
> --------- Original Message --------
> From: "Access Developers discussion and problem solving" 
> <accessd at databaseadvisors.com>
> To: "Access Developers discussion and problem solving" 
> <accessd at databaseadvisors.com>
> Subject: Re: [AccessD] Function Error
> Date: 27/01/06 16:47
> 
> 
> Nope it's not numeric. I went through and changed the Row 
> field to numeric and adjusted the statement to take out the 
> single quotes but I still get the same error.
> 
> -----Original Message-----
> From: Heenan, Lambert [mailto:Lambert.Heenan at AIG.com]
> Sent: Friday, January 27, 2006 9:25 AM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Function Error
> 
> The SQL is treating Row as if it was a string value.
> 
> WHERE Row = '" & j & "' ORDER BY
> 
> It's not actually numeric by any chance?
> 
> 
> Lambert
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of 
> Gowey Mike W
> Sent: Friday, January 27, 2006 10:32 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Function Error
> 
> 
> Just for the heck of it I went through and changed Row to 
> Rows and it didn't solve the error. Dang
> 
> -----Original Message-----
> From: Gowey Mike W
> Sent: Friday, January 27, 2006 8:21 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Function Error
> 
> That's a good question, it works fine in 97, did they change 
> it in 2000?
> 
> 
> -----Original Message-----
> From: Andy Lacey [mailto:andy at minstersystems.co.uk]
> Sent: Friday, January 27, 2006 8:18 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Function Error
> 
> Is Row perchance a reserved word?
> --
> Andy Lacey
> http://www.minstersystems.co.uk
> 
> 
> 
> 
> --------- Original Message --------
> From: "Access Developers discussion and problem solving" 
> <accessd at databaseadvisors.com>
> To: "Access Developers discussion and problem solving" 
> <accessd at databaseadvisors.com>
> Subject: Re: [AccessD] Function Error
> Date: 27/01/06 14:58
> 
> 
> 
> 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
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com 
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> 




More information about the AccessD mailing list