Gowey Mike W
Mike.W.Gowey at doc.state.or.us
Fri Jan 27 10:45:14 CST 2006
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