In cases where I expect an error could occur, my personal preference is to
break that line out to a separate procedure to handle the error rather than
jump aound in a routine, trying to guess or debug which line generated the
error. (I presume those line numbers are just for debugging, as they really
slow the execution down.)
Using the examples below, you can avoid the error in your main procedure
with:
If HasProperty(fld, "Caption") Then
'go ahead and refer to the caption
Else
'skip it.
End If
Alternatively, if you want to set the value of the property, creating it if
it doesn't exist, use:
Call SetPropertyDAO(fld, "Caption", dbText, "This is my caption")
Public Function HasProperty(obj As Object, strPropName As String) As Boolean
'Purpose: Return true if the object has the property.
Dim vardummy As Variant
On Error Resume Next
vardummy = obj.Properties(strPropName)
HasProperty = (Err.Number = 0)
End Function
Function SetPropertyDAO(obj As Object, strPropertyName As String, intType As
Integer, varValue As Variant, Optional strErrMsg As String) As Boolean
On Error GoTo ErrHandler
'Purpose: Set a property for an object, creating if necessary.
'Arguments: obj = the object whose property should be set.
' strPropertyName = the name of the property to set.
' intType = the type of property (needed for creating)
' varValue = the value to set this property to.
' strErrMsg = string to append any error message to.
If HasProperty(obj, strPropertyName) Then
obj.Properties(strPropertyName) = varValue
Else
obj.Properties.Append obj.CreateProperty(strPropertyName, intType,
varValue)
End If
SetPropertyDAO = True
ExitHandler:
Exit Function
ErrHandler:
strErrMsg = strErrMsg & obj.Name & "." & strPropertyName & " not set to
" & varValue & ". Error " & Err.Number & " - " & Err.Description & vbCrLf
Resume ExitHandler
End Function
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Bob Darlington" <bob@dpcmanAX.com.auwrote in message
news:457de608$0$21086$afc38c87@news.optusnet.com.a u...
Quote:
Thanks Allen. You got it in one .... again.
So I rewrote the code to the following:
PrintReports first calls the problem function (CreateTenantChanges) to
create the table on which it is built.
>
'---------------------------------------------------------------------------------------
' Procedure : PrintReports
' DateTime : 18/11/2006 09:11
' Author : Bob Darlington
' Purpose :
'---------------------------------------------------------------------------------------
'
Public Function PrintReports(vView As Byte) As Boolean
>
10 On Error GoTo PrintReports_Error
Dim strRpt As String
>
20 If Me.Dirty Then DoCmd.RunCommand acCmdSaveRecord
>
30 If Me!cbSummary Then
40 Call CreateTenantChanges(Me!TenantCounter, Me!RefID)
50 strRpt = "rTenantChangesSummary"
60 Else
70 strRpt = "rLVA"
80 End If
>
90 DoCmd.OpenReport strRpt, vView
>
CloseFunction:
100 On Error Resume Next
>
110 DoCmd.Hourglass False
120 Exit Function
>
PrintReports_Error:
130 MsgBox "Error " & Err.Number & " (" & Err.Description & ") " _
& "in procedure PrintReports in Line " & Erl & "."
140 Resume CloseFunction
End Function
>
'---------------------------------------------------------------------------------------
' Procedure : CreateTenantChanges
' DateTime : 18/11/2006 09:18
' Author : Bob Darlington
' Purpose : Creates tTenantChanges for use in PrintReports and
AcceptDraft
'---------------------------------------------------------------------------------------
'
Public Function CreateTenantChanges(vTenantCounter As Long, vRefID As
Long) As Boolean
10 On Error GoTo CreateTenantChanges_Error
>
Dim db As Database, rsOld As Recordset, rsNew As Recordset,
rsTenantChanges As Recordset
Dim fld As Field
Dim strFld As String, strCaption As String
Dim vEventID As Byte
>
20 Set db = CurrentDb
30 Set rsOld = db.OpenRecordset("SELECT * FROM tTenantDetails WHERE
TenantCounter = " & vRefID)
40 Set rsNew = db.OpenRecordset("SELECT * FROM tTenantDetails WHERE
TenantCounter = " & vTenantCounter)
50 Set rsTenantChanges = db.OpenRecordset("tTenantChanges")
>
60 With rsTenantChanges
70 Do Until .EOF
80 .Delete
90 .MoveNext
100 Loop
110 End With
>
120 With rsOld
130 For Each fld In rsOld.Fields
140 strFld = fld.Name
150 strCaption = fld.Properties("Caption")
>
210 If fld.Value <rsNew(strFld).Value Then
220 rsTenantChanges.AddNew
230 rsTenantChanges!ChangeTable = "tTenantDetails"
240 rsTenantChanges!TenantCounter = rsNew!TenantCounter
250 rsTenantChanges!FieldOldValue = rsOld(strFld).Value
260 rsTenantChanges!FieldNewValue = rsNew(strFld).Value
270 rsTenantChanges!FieldCaption = strCaption
290 rsTenantChanges.Update
300 End If
NextField:
310 Next
320 End With
>
330 CreateTenantChanges = True
>
CloseFunction:
340 On Error Resume Next
350 rsOld.Close
360 Set rsOld = Nothing
370 rsNew.Close
380 Set rsNew = Nothing
390 Set db = Nothing
400 DoCmd.Hourglass False
410 Exit Function
>
CreateTenantChanges_Error:
420 If Err = 3270 Then 'Caption not assigned
430 GoTo NextField
440 Else
450 MsgBox "Error " & Err.Number & " (" & Err.Description & ") " _
& "in procedure CreateTenantChanges in Line " & Erl & "."
460 Resume CloseFunction
470 End If
End Function
>
The first 2 fields are 'TenantID' and 'TenantCounter' (neither of which
has a caption).
When the function reaches line 150 for the first field, it encounters the
error 3270 as expected, and loops to the next field. But when it reaches
line 150 the second time, it exits the function to line 130 (Error Code)
in the calling function (PrintReports) and generates error 3270. I can't
see where or how the error number is transmitted back to the calling
function, or why the error code doesn't trigger in the second loop.
Any ideas?
>
--
Bob Darlington
Brisbane
"Allen Browne" <AllenBrowne@SeeSig.invalidwrote in message
news:457d2fba$0$2658$5a62ac22@per-qv1-newsreader-01.iinet.net.au...
Quote:
>If the property has never been used, you should get error 3270 (Property
>not found) when you try to refer to the caption.
>>
>Do you have something masking this, Bob?
>Perhaps:
> On Error Resume Next?
>>
>"Bob Darlington" <bob@dpcmanAX.com.auwrote in message
>news:457d1121$0$9773$afc38c87@news.optusnet.com.a u...
Quote:
>>>I want to use the caption property for fields in a recordset as a
>>>condition in a loop. That is, I only want to consider those fields which
>>>have captions:
>>>
>>For each fld in RecordsetName.Fields
>> If fld.Properties("Caption") <"" then
>> do something
>>>
>>The problem is that all fields are included, even those with no caption
>>set.
>>I've tried IsMissing, IsEmpty and IsNull for the test but none will
>>filter out those fields without a caption.
>>What is the default value for captions?
