Gowey Mike W
Mike.W.Gowey at doc.state.or.us
Fri Jan 27 10:20:09 CST 2006
Nope the row field is text :-(
-----Original Message-----
From: Andy Lacey [mailto:andy at minstersystems.co.uk]
Sent: Friday, January 27, 2006 9:16 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Function Error
Sorry about that Mike. Did you see my second suggestion? "The other
thing - is row numeric anyway? If so you don't want the two 's in the
SQL."
--
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:06
Well I went through and changed it to BinRowNum and unfortunately I
still get the same error, this is giving me a headache and it's Friday.
-----Original Message-----
From: Andy Lacey [mailto:andy at minstersystems.co.uk]
Sent: Friday, January 27, 2006 8:38 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Function Error
Rows could be too. Is it worth trying 'RowNo' or something really
obscure?
Could someone else with A2000 try this?
The other thing - is row numeric anyway? If so you don't want the two 's
in the SQL.
--
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 15:33
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
________________________________________________
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
________________________________________________
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