Calculated fields not working

[hwr]

Hi,
I have a problem that every few weeks the calculated fields stop working in all forms. This is solved by rebooting the server. While this solves the problem is is really inconvenient for the users and I would like to stop it from happening, or at least understand what is going on.

We have a SQL back end and 5 servers that run the front end, full version of Access on my administration server, and runtime on the 4 other servers which are paired to load balance. The problem only ever happens on the runtime version and usually on 1 server at a time (although this morning I have 2 on the same load balance so no where to move the users to).

This is not really my area of expertise but the server guys don't seem to want to investigate further and I have googled the hell out of this problem and come up with nothing. So has anyone else come across this problem?????

Cheers
H

 

[JHB]

How do you calculate (in a query or ...) and what are you calculating?

 

[hwr]

Calculated fields are all on forms and include conditional formatting. Some fields are included in functions which then fail if we try to run them.

 

[Solo712]

Calculated fields are all on forms and include conditional formatting. Some fields are included in functions which then fail if we try to run them.

Are all the runtime servers running the same version of Access ? And is it the same version as the full-fledged instance ?

Best,
Jiri

 

[hwr]

Yes all are replicas, everything is the same on all 4 runtime versions.

The first time it happened I thought maybe the code had corrupted on the effected server so I copied in the latest version of the application, didn't solve the problem. So now we just reboot the server and it fixes. Not so much of an issue when it is the administration staff, I can get them to log off. We are a large sawmill so not so good if I have to stop production and wait until the server guys (external IT support company) can reboot a server.

 

[Solo712]

Yes all are replicas, everything is the same on all 4 runtime versions.

The first time it happened I thought maybe the code had corrupted on the effected server so I copied in the latest version of the application, didn't solve the problem. So now we just reboot the server and it fixes. Not so much of an issue when it is the administration staff, I can get them to log off. We are a large sawmill so not so good if I have to stop production and wait until the server guys (external IT support company) can reboot a server.

Which Access ? 2007, 2010, 2013 ? So these are calculated fields, you say. Like in a table-defined 'calculated fields' ? And are they sitting in the front-end replicas or on the back-end server ? IOW, which server are you rebooting ?

BTW, you may want to look at this: http://allenbrowne.com/ser-45.html

Best,
Jiri

 

[hwr]

Hi,

2010 - and to date it has only been on the servers that have runtime.
Reboot is always on the front end server.
All calculated fields on all forms are effected.

For example a text box with the following formula will just be blank
=IIf(IsNull([OrderNo]),0,GetPicked([OrderNo],[OrderItem],"Picked"))

The problem first happen 13th Jan and then every couple of weeks since.
There were no changes to the system that I know of.

Thanks
H

 

[JHB]

What is the code of GetPicked?
And do you've error handling in GetPicked?

 

[hwr]

Hi,
Thanks for taking the time to try and understand my problem with calculated fields. I will paste the code below but I really don't think it will add anything - it was just one example and the problem is on ALL calculated fields on ALL forms, a more simple example is...

=Nz(ftsPacketsSub.Form.txtTotalCube,0)

Loading a new version (that is working on the other servers) doesn't fix the problem, but rebooting the server does.

I was hoping that someone out there had had this problem before and knew what was causing it and how to fix it.

Thanks
H

Public Function GetPicked(lngOrderNo As Long, lngOrderItem As Long, strFieldName As String) As Currency
On Error GoTo ProcErr
Const strProcedureName = "GetPicked"

Dim db As Database
Dim qd As QueryDef
Dim rs As Recordset

Set db = GetDatabase()
Set qd = db.QueryDefs("qtsOrderItemPicked")
With qd
.Parameters("SalesOrderNo") = lngOrderNo
.Parameters("SalesOrderItem") = lngOrderItem
Set rs = .OpenRecordset(dbOpenSnapshot, dbSeeChanges, dbReadOnly)
End With

With rs
If .RecordCount = 0 Then
GetPicked = 0
Else
GetPicked = Nz(.Fields(strFieldName).Value, 0)
End If
End With

ProcExit:
On Error Resume Next
rs.Close
Set rs = Nothing
qd.Close
Set qd = Nothing
Set db = Nothing
Exit Function
ProcErr:
MsgBox "Error: " & Err.Number & " - " & Err.Description, vbCritical, strProcedureName
Resume ProcExit
End Function

 

[Solo712]

Hi,
Thanks for taking the time to try and understand my problem with calculated fields. I will paste the code below but I really don't think it will add anything - it was just one example and the problem is on ALL calculated fields on ALL forms, a more simple example is...

=Nz(ftsPacketsSub.Form.txtTotalCube,0)

Loading a new version (that is working on the other servers) doesn't fix the problem, but rebooting the server does.

I was hoping that someone out there had had this problem before and knew what was causing it and how to fix it.

Thanks
H

Public Function GetPicked(lngOrderNo As Long, lngOrderItem As Long, strFieldName As String) As Currency
On Error GoTo ProcErr
Const strProcedureName = "GetPicked"

Dim db As Database
Dim qd As QueryDef
Dim rs As Recordset

Set db = GetDatabase()
Set qd = db.QueryDefs("qtsOrderItemPicked")
With qd
.Parameters("SalesOrderNo") = lngOrderNo
.Parameters("SalesOrderItem") = lngOrderItem
Set rs = .OpenRecordset(dbOpenSnapshot, dbSeeChanges, dbReadOnly)
End With

With rs
If .RecordCount = 0 Then
GetPicked = 0
Else
GetPicked = Nz(.Fields(strFieldName).Value, 0)
End If
End With

ProcExit:
On Error Resume Next
rs.Close
Set rs = Nothing
qd.Close
Set qd = Nothing
Set db = Nothing
Exit Function
ProcErr:
MsgBox "Error: " & Err.Number & " - " & Err.Description, vbCritical, strProcedureName
Resume ProcExit
End Function

This looks like a tough nut to crack. It happens with runtime modules only, and you cannot get into them to track down the problem. Further, it's a transient thing that clears when you reboot (which would not likely happen if there was an issue with References - the most frequent cause of functions failing). I would recommend to the managers to get extra copies of full-fledged Access if they want to run their production on it.

I wish you the best of luck !

Jiri