Improving reliability of controls

C

Chris

Hi there,
I posted a message here a few days back regarding
subforms with no records causing an error. The reply I got
seemed to solve the problem I had. Now, the problem is
back but in a different form. I think that I'm doing
something fundementally wrong and perhaps I have too many
controls on my form. The problem is as follows:

I have a control in a subform that calls a function to
calculate the current status of the record. The status of
each record is stored in a separate table called
tblHistory. The recordsource for my subform runs on a
query based on a table called tblPlanningApplications. The
table tblPlanningApplications has a field called
currentstatus which stores the ID of the appropriate
record in the tblHistory table. The control calls a
function to find out the current status and deals with it
if it is null (or if it is an error: this happens only
when the record count is 0). This in turn calls a function
that loops through the history table and returns the
current status. The problem is that it calculates
correctly sometimes and at apparently random intervals it
calculates as #Name?. When I loop through the code it
always comes out correctly even though it displays #Name?
as the result.

Could this be as a result of too many controls on the form?
Am I calling too much code? Could anyone tell me why the
control is behaving this way? Given the exact same
parameters everytime the result varies. Any help in
solving this problem would be appreciated greatly. I have
posted the code below in case it helps. Thanks in advance
for any help/advice/comments.

Regards,
Chris Kelly.

The control causing the problem is bound to the following
expression.
=GetStateName(GetHistoryState([Forms]![frmProjects]!
[sfSubJobs].[Form]![Combo88].[Value]))

CODE
Function getStateName(num) As String
Dim db As Database
Dim rst As Recordset
Dim qdf As QueryDef
Dim str As String

On Error GoTo getStateName_Error

If IsNull(num) Then
num = 16 ' The code for the unknown state: UNK
End If

' Select records from the PlanningState table. This
gives a 3 letter description of the state based on an index
str = "SELECT PlanningStateID, PlanningState FROM
tblPlanningStates WHERE (PlanningStateID = " & num & ");"

Set db = CurrentDb
Set qdf = db.CreateQueryDef("", str)
Set rst = qdf.OpenRecordset

If rst.RecordCount <> 0 Then ' If there are no records
return UNKnown
With rst
.MoveFirst
getStateName = !PlanningState

End With
Else
getStateName = "UNK"
End If

On Error GoTo 0
Exit Function

getStateName_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description
& ") in procedure getStateName of Module modGlobals"

End Function
Function GetHistoryState(currstat) As Long
Dim sqlstr As String
Dim db As Database
Dim rst As Recordset
Dim qdf As QueryDef
Dim bStateSet As Boolean

On Error GoTo GetHistoryState_Error

' Select all the records with the same planningAppID
as the selected record on the subform
sqlstr = "SELECT PlanningHistoryID, PlanningAppID,
State, DateEntered, EmpID From tblPlanningHistory WHERE
(PlanningAppID=" & Forms![frmProjects]![sfSubJobs].Form!
[PlanningAppID] & ");"

Set db = CurrentDb
Set qdf = db.CreateQueryDef("", sqlstr)
Set rst = qdf.OpenRecordset

If IsError(currstat) Then
GetHistoryState = 16 ' On error return code for
unknown
Else
If rst.RecordCount <> 0 Then
With rst
.MoveFirst
Do Until .EOF
If currstat = !PlanningHistoryID Then
GetHistoryState = !State
bStateSet = True
End If
.MoveNext
Loop

If bStateSet = False Then
GetHistoryState = 16
End If

End With
Else
GetHistoryState = 16
End If
End If

On Error GoTo 0
Exit Function

GetHistoryState_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description
& ") in procedure GetHistoryState of Module modGlobals"

End Function
 
D

Dirk Goldgar

Chris said:
Hi there,
I posted a message here a few days back regarding
subforms with no records causing an error. The reply I got
seemed to solve the problem I had. Now, the problem is
back but in a different form. I think that I'm doing
something fundementally wrong and perhaps I have too many
controls on my form. The problem is as follows:

I have a control in a subform that calls a function to
calculate the current status of the record. The status of
each record is stored in a separate table called
tblHistory. The recordsource for my subform runs on a
query based on a table called tblPlanningApplications. The
table tblPlanningApplications has a field called
currentstatus which stores the ID of the appropriate
record in the tblHistory table. The control calls a
function to find out the current status and deals with it
if it is null (or if it is an error: this happens only
when the record count is 0). This in turn calls a function
that loops through the history table and returns the
current status. The problem is that it calculates
correctly sometimes and at apparently random intervals it
calculates as #Name?. When I loop through the code it
always comes out correctly even though it displays #Name?
as the result.

Could this be as a result of too many controls on the form?
Am I calling too much code? Could anyone tell me why the
control is behaving this way? Given the exact same
parameters everytime the result varies. Any help in
solving this problem would be appreciated greatly. I have
posted the code below in case it helps. Thanks in advance
for any help/advice/comments.

Regards,
Chris Kelly.

The control causing the problem is bound to the following
expression.
=GetStateName(GetHistoryState([Forms]![frmProjects]!
[sfSubJobs].[Form]![Combo88].[Value]))

CODE
Function getStateName(num) As String
Dim db As Database
Dim rst As Recordset
Dim qdf As QueryDef
Dim str As String

On Error GoTo getStateName_Error

If IsNull(num) Then
num = 16 ' The code for the unknown state: UNK
End If

' Select records from the PlanningState table. This
gives a 3 letter description of the state based on an index
str = "SELECT PlanningStateID, PlanningState FROM
tblPlanningStates WHERE (PlanningStateID = " & num & ");"

Set db = CurrentDb
Set qdf = db.CreateQueryDef("", str)
Set rst = qdf.OpenRecordset

If rst.RecordCount <> 0 Then ' If there are no records
return UNKnown
With rst
.MoveFirst
getStateName = !PlanningState

End With
Else
getStateName = "UNK"
End If

On Error GoTo 0
Exit Function

getStateName_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description
& ") in procedure getStateName of Module modGlobals"

End Function
Function GetHistoryState(currstat) As Long
Dim sqlstr As String
Dim db As Database
Dim rst As Recordset
Dim qdf As QueryDef
Dim bStateSet As Boolean

On Error GoTo GetHistoryState_Error

' Select all the records with the same planningAppID
as the selected record on the subform
sqlstr = "SELECT PlanningHistoryID, PlanningAppID,
State, DateEntered, EmpID From tblPlanningHistory WHERE
(PlanningAppID=" & Forms![frmProjects]![sfSubJobs].Form!
[PlanningAppID] & ");"

Set db = CurrentDb
Set qdf = db.CreateQueryDef("", sqlstr)
Set rst = qdf.OpenRecordset

If IsError(currstat) Then
GetHistoryState = 16 ' On error return code for
unknown
Else
If rst.RecordCount <> 0 Then
With rst
.MoveFirst
Do Until .EOF
If currstat = !PlanningHistoryID Then
GetHistoryState = !State
bStateSet = True
End If
.MoveNext
Loop

If bStateSet = False Then
GetHistoryState = 16
End If

End With
Else
GetHistoryState = 16
End If
End If

On Error GoTo 0
Exit Function

GetHistoryState_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description
& ") in procedure GetHistoryState of Module modGlobals"

End Function
Click to expand...

I don't know why the code would sometimes give you the #Name? error and
other times not. However, it seems to me your functions are doing too
much work -- for example, you're creating querydef objects that you
don't need -- and maybe the erratic results are related somehow to that.
Revised versions like these might be more efficient, and *may* correct
the problem:

'----- start of (untested) revised code -----
Function GetStateName(num As Variant) As String

On Error GoTo GetStateName_Error

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim str As String

If IsNull(num) Then
' return code for the unknown state: UNK
GetStateName = "UNK"
Exit Sub
Else
' Select records from the PlanningState table. This
' gives a 3 letter description of the state based on an index
str = _
"SELECT PlanningStateID, PlanningState " & _
"FROM tblPlanningStates " & _
"WHERE (PlanningStateID = " & num & ");"

Set db = DBEngine.Workspaces(0)(0)
Set rst = db.OpenRecordset(str)

With rst
If .RecordCount = 0 Then
' If there are no records return UNKnown
GetStateName = "UNK"
Else
GetStateName = !PlanningState
End If
End With

End If

Exit_GetStateName:
On Error Resume Next
If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
End If
Set db = Nothing
Exit Function

GetStateName_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure GetStateName of Module modGlobals"

Resume Exit_GetStateName

End Function

Function GetHistoryState(currstat As Variant) As Long

On Error GoTo GetHistoryState_Error

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim sqlstr As String

If IsError(currstat) Or IsNull(currstat) Then
GetHistoryState = 16 ' On error return code for unknown
Exit Sub
Else
' Select all the records with the same planningAppID
' as the selected record on the subform
sqlstr = _
"SELECT State FROM tblPlanningHistory " & _
"WHERE PlanningAppID=" & _
Forms!frmProjects!sfSubJobs.Form!PlanningAppID & _
" AND PlanningHistoryID = " & currstat

Set db = DBEngine.Workspaces(0)(0)
Set rst = db.OpenRecordset(sqlstr)

With rst
If rst.RecordCount = 0 Then
GetHistoryState = 16
Else
GetHistoryState = !State
End If
End With

End If

Exit_GetHistoryState:
On Error Resume Next
If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
End If
Set db = Nothing
Exit Function

GetHistoryState_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure GetHistoryState of Module modGlobals"

Resume Exit_GetHistoryState

End Function

'----- end of revised code -----

I'm assuming in the above that the field PlanningHistoryID is a numeric
field, not text. I may have missed something, but I saw no reason why
you should loop through the records in your recordset, rather than just
select the one you wanted in the first place.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
AddPicturesToShapes 0
Improving Macro That Keeps First Letter 0
Working outlook via vba code with xlsx - error 0
Word macro that can call cells out of Excel for a find and replace 0
Mimic Split Form with Two Subforms 0
VBA works in orignal but fails in copy of database 1
Dynamic Naming of Form Button 3

Top