Caption property default value

[Bob Darlington]

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.F ields
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?
--
Bob Darlington
Brisbane

[Allen Browne]

Re: Caption property default value

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?

--
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.c om.auwrote in message
news:457d1121$0 $9773$afc38c87@ news.optusnet.c om.au...

>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.F ields
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?
--
Bob Darlington
Brisbane
>

[Bob Darlington]

Re: Caption property default value

Thanks Allen. You got it in one .... again.
So I rewrote the code to the following:
PrintReports first calls the problem function (CreateTenantCh anges) to
create the table on which it is built.

'---------------------------------------------------------------------------------------
' Procedure : PrintReports
' DateTime : 18/11/2006 09:11
' Author : Bob Darlington
' Purpose :
'---------------------------------------------------------------------------------------
'
Public Function PrintReports(vV iew As Byte) As Boolean

10 On Error GoTo PrintReports_Er ror
Dim strRpt As String

20 If Me.Dirty Then DoCmd.RunComman d acCmdSaveRecord

30 If Me!cbSummary Then
40 Call CreateTenantCha nges(Me!TenantC ounter, Me!RefID)
50 strRpt = "rTenantChanges Summary"
60 Else
70 strRpt = "rLVA"
80 End If

90 DoCmd.OpenRepor t strRpt, vView

CloseFunction:
100 On Error Resume Next

110 DoCmd.Hourglass False
120 Exit Function

PrintReports_Er ror:
130 MsgBox "Error " & Err.Number & " (" & Err.Description & ") " _
& "in procedure PrintReports in Line " & Erl & "."
140 Resume CloseFunction
End Function

'---------------------------------------------------------------------------------------
' Procedure : CreateTenantCha nges
' DateTime : 18/11/2006 09:18
' Author : Bob Darlington
' Purpose : Creates tTenantChanges for use in PrintReports and AcceptDraft
'---------------------------------------------------------------------------------------
'
Public Function CreateTenantCha nges(vTenantCou nter As Long, vRefID As Long)
As Boolean
10 On Error GoTo CreateTenantCha nges_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.OpenRecordse t("SELECT * FROM tTenantDetails WHERE
TenantCounter = " & vRefID)
40 Set rsNew = db.OpenRecordse t("SELECT * FROM tTenantDetails WHERE
TenantCounter = " & vTenantCounter)
50 Set rsTenantChanges = db.OpenRecordse t("tTenantChang es")

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!TenantCou nter
250 rsTenantChanges !FieldOldValue = rsOld(strFld).V alue
260 rsTenantChanges !FieldNewValue = rsNew(strFld).V alue
270 rsTenantChanges !FieldCaption = strCaption
290 rsTenantChanges .Update
300 End If
NextField:
310 Next
320 End With

330 CreateTenantCha nges = 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

CreateTenantCha nges_Error:
420 If Err = 3270 Then 'Caption not assigned
430 GoTo NextField
440 Else
450 MsgBox "Error " & Err.Number & " (" & Err.Description & ") " _
& "in procedure CreateTenantCha nges 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@Se eSig.invalidwro te in message
news:457d2fba$0 $2658$5a62ac22@ per-qv1-newsreader-01.iinet.net.au ...

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?
>
--
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.c om.auwrote in message
news:457d1121$0 $9773$afc38c87@ news.optusnet.c om.au...

>>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.F ields
> 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?
>--
>Bob Darlington
>Brisbane
>>

>
>

[Allen Browne]

Re: Caption property default value

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.CreatePrope rty(strProperty Name, 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.c om.auwrote in message
news:457de608$0 $21086$afc38c87 @news.optusnet. com.au...

Thanks Allen. You got it in one .... again.
So I rewrote the code to the following:
PrintReports first calls the problem function (CreateTenantCh anges) to
create the table on which it is built.
>
'---------------------------------------------------------------------------------------
' Procedure : PrintReports
' DateTime : 18/11/2006 09:11
' Author : Bob Darlington
' Purpose :
'---------------------------------------------------------------------------------------
'
Public Function PrintReports(vV iew As Byte) As Boolean
>
10 On Error GoTo PrintReports_Er ror
Dim strRpt As String
>
20 If Me.Dirty Then DoCmd.RunComman d acCmdSaveRecord
>
30 If Me!cbSummary Then
40 Call CreateTenantCha nges(Me!TenantC ounter, Me!RefID)
50 strRpt = "rTenantChanges Summary"
60 Else
70 strRpt = "rLVA"
80 End If
>
90 DoCmd.OpenRepor t strRpt, vView
>
CloseFunction:
100 On Error Resume Next
>
110 DoCmd.Hourglass False
120 Exit Function
>
PrintReports_Er ror:
130 MsgBox "Error " & Err.Number & " (" & Err.Description & ") " _
& "in procedure PrintReports in Line " & Erl & "."
140 Resume CloseFunction
End Function
>
'---------------------------------------------------------------------------------------
' Procedure : CreateTenantCha nges
' DateTime : 18/11/2006 09:18
' Author : Bob Darlington
' Purpose : Creates tTenantChanges for use in PrintReports and
AcceptDraft
'---------------------------------------------------------------------------------------
'
Public Function CreateTenantCha nges(vTenantCou nter As Long, vRefID As
Long) As Boolean
10 On Error GoTo CreateTenantCha nges_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.OpenRecordse t("SELECT * FROM tTenantDetails WHERE
TenantCounter = " & vRefID)
40 Set rsNew = db.OpenRecordse t("SELECT * FROM tTenantDetails WHERE
TenantCounter = " & vTenantCounter)
50 Set rsTenantChanges = db.OpenRecordse t("tTenantChang es")
>
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!TenantCou nter
250 rsTenantChanges !FieldOldValue = rsOld(strFld).V alue
260 rsTenantChanges !FieldNewValue = rsNew(strFld).V alue
270 rsTenantChanges !FieldCaption = strCaption
290 rsTenantChanges .Update
300 End If
NextField:
310 Next
320 End With
>
330 CreateTenantCha nges = 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
>
CreateTenantCha nges_Error:
420 If Err = 3270 Then 'Caption not assigned
430 GoTo NextField
440 Else
450 MsgBox "Error " & Err.Number & " (" & Err.Description & ") " _
& "in procedure CreateTenantCha nges 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@Se eSig.invalidwro te in message
news:457d2fba$0 $2658$5a62ac22@ per-qv1-newsreader-01.iinet.net.au ...

>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.c om.auwrote in message
>news:457d1121$ 0$9773$afc38c87 @news.optusnet. com.au...

>>>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.F ields
>> 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?

[Bob Darlington]

Re: Caption property default value

Thanks Allen. That worked fine.
I guess the behaviour of my code was a result of the goto, which I have
often seen discouraged in this ng.
But I still can't see how the 3270 value got passed back to the calling
function, or why the error code failed to trigger at the second attempt.
Anyway, your solution works so thanks again.
--
Bob Darlington
Brisbane
"Allen Browne" <AllenBrowne@Se eSig.invalidwro te in message
news:457e00fd$0 $2694$5a62ac22@ per-qv1-newsreader-01.iinet.net.au ...

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.CreatePrope rty(strProperty Name, 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.c om.auwrote in message
news:457de608$0 $21086$afc38c87 @news.optusnet. com.au...

>Thanks Allen. You got it in one .... again.
>So I rewrote the code to the following:
>PrintReports first calls the problem function (CreateTenantCh anges) to
>create the table on which it is built.
>>
>'---------------------------------------------------------------------------------------
>' Procedure : PrintReports
>' DateTime : 18/11/2006 09:11
>' Author : Bob Darlington
>' Purpose :
>'---------------------------------------------------------------------------------------
>'
>Public Function PrintReports(vV iew As Byte) As Boolean
>>
>10 On Error GoTo PrintReports_Er ror
> Dim strRpt As String
>>
>20 If Me.Dirty Then DoCmd.RunComman d acCmdSaveRecord
>>
>30 If Me!cbSummary Then
>40 Call CreateTenantCha nges(Me!TenantC ounter, Me!RefID)
>50 strRpt = "rTenantChanges Summary"
>60 Else
>70 strRpt = "rLVA"
>80 End If
>>
>90 DoCmd.OpenRepor t strRpt, vView
>>
>CloseFunctio n:
>100 On Error Resume Next
>>
>110 DoCmd.Hourglass False
>120 Exit Function
>>
>PrintReports_E rror:
>130 MsgBox "Error " & Err.Number & " (" & Err.Description & ") " _
> & "in procedure PrintReports in Line " & Erl & "."
>140 Resume CloseFunction
>End Function
>>
>'---------------------------------------------------------------------------------------
>' Procedure : CreateTenantCha nges
>' DateTime : 18/11/2006 09:18
>' Author : Bob Darlington
>' Purpose : Creates tTenantChanges for use in PrintReports and
>AcceptDraft
>'---------------------------------------------------------------------------------------
>'
>Public Function CreateTenantCha nges(vTenantCou nter As Long, vRefID As
>Long) As Boolean
>10 On Error GoTo CreateTenantCha nges_Error
>>
> Dim db As Database, rsOld As Recordset, rsNew As Recordset,
>rsTenantChange s 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.OpenRecordse t("SELECT * FROM tTenantDetails WHERE
>TenantCounte r = " & vRefID)
>40 Set rsNew = db.OpenRecordse t("SELECT * FROM tTenantDetails WHERE
>TenantCounte r = " & vTenantCounter)
>50 Set rsTenantChanges = db.OpenRecordse t("tTenantChang es")
>>
>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!TenantCou nter
>250 rsTenantChanges !FieldOldValue = rsOld(strFld).V alue
>260 rsTenantChanges !FieldNewValue = rsNew(strFld).V alue
>270 rsTenantChanges !FieldCaption = strCaption
>290 rsTenantChanges .Update
>300 End If
>NextField:
>310 Next
>320 End With
>>
>330 CreateTenantCha nges = True
>>
>CloseFunctio n:
>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
>>
>CreateTenantCh anges_Error:
>420 If Err = 3270 Then 'Caption not assigned
>430 GoTo NextField
>440 Else
>450 MsgBox "Error " & Err.Number & " (" & Err.Description & ") " _
> & "in procedure CreateTenantCha nges 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@Se eSig.invalidwro te in message
>news:457d2fba$ 0$2658$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...

>>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.c om.auwrote in message
>>news:457d1121 $0$9773$afc38c8 7@news.optusnet .com.au...
>>>>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.F ields
>>> 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?

>
>

[Allen Browne]

Re: Caption property default value

Perhaps you needed a Resume instead of a Goto after the error:

CreateTenantCha nges_Error:
420 If Err = 3270 Then 'Caption not assigned
430 Resume NextField


--
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.c om.auwrote in message
news:457de608$0 $21086$afc38c87 @news.optusnet. com.au...

Thanks Allen. You got it in one .... again.
So I rewrote the code to the following:
PrintReports first calls the problem function (CreateTenantCh anges) to
create the table on which it is built.
>
'---------------------------------------------------------------------------------------
' Procedure : PrintReports
' DateTime : 18/11/2006 09:11
' Author : Bob Darlington
' Purpose :
'---------------------------------------------------------------------------------------
'
Public Function PrintReports(vV iew As Byte) As Boolean
>
10 On Error GoTo PrintReports_Er ror
Dim strRpt As String
>
20 If Me.Dirty Then DoCmd.RunComman d acCmdSaveRecord
>
30 If Me!cbSummary Then
40 Call CreateTenantCha nges(Me!TenantC ounter, Me!RefID)
50 strRpt = "rTenantChanges Summary"
60 Else
70 strRpt = "rLVA"
80 End If
>
90 DoCmd.OpenRepor t strRpt, vView
>
CloseFunction:
100 On Error Resume Next
>
110 DoCmd.Hourglass False
120 Exit Function
>
PrintReports_Er ror:
130 MsgBox "Error " & Err.Number & " (" & Err.Description & ") " _
& "in procedure PrintReports in Line " & Erl & "."
140 Resume CloseFunction
End Function
>
'---------------------------------------------------------------------------------------
' Procedure : CreateTenantCha nges
' DateTime : 18/11/2006 09:18
' Author : Bob Darlington
' Purpose : Creates tTenantChanges for use in PrintReports and
AcceptDraft
'---------------------------------------------------------------------------------------
'
Public Function CreateTenantCha nges(vTenantCou nter As Long, vRefID As
Long) As Boolean
10 On Error GoTo CreateTenantCha nges_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.OpenRecordse t("SELECT * FROM tTenantDetails WHERE
TenantCounter = " & vRefID)
40 Set rsNew = db.OpenRecordse t("SELECT * FROM tTenantDetails WHERE
TenantCounter = " & vTenantCounter)
50 Set rsTenantChanges = db.OpenRecordse t("tTenantChang es")
>
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!TenantCou nter
250 rsTenantChanges !FieldOldValue = rsOld(strFld).V alue
260 rsTenantChanges !FieldNewValue = rsNew(strFld).V alue
270 rsTenantChanges !FieldCaption = strCaption
290 rsTenantChanges .Update
300 End If
NextField:
310 Next
320 End With
>
330 CreateTenantCha nges = 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
>
CreateTenantCha nges_Error:
420 If Err = 3270 Then 'Caption not assigned
430 GoTo NextField
440 Else
450 MsgBox "Error " & Err.Number & " (" & Err.Description & ") " _
& "in procedure CreateTenantCha nges 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@Se eSig.invalidwro te in message
news:457d2fba$0 $2658$5a62ac22@ per-qv1-newsreader-01.iinet.net.au ...

>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?
>>
>--
>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.c om.auwrote in message
>news:457d1121$ 0$9773$afc38c87 @news.optusnet. com.au...

>>>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.F ields
>> 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?
>>--
>>Bob Darlington
>>Brisbane
>>>

>>
>>

>
>

[Bob Darlington]

Re: Caption property default value

Thanks Allen.

--
Bob Darlington
Brisbane
"Allen Browne" <AllenBrowne@Se eSig.invalidwro te in message
news:457e0cc4$0 $2708$5a62ac22@ per-qv1-newsreader-01.iinet.net.au ...

Perhaps you needed a Resume instead of a Goto after the error:
>
CreateTenantCha nges_Error:
420 If Err = 3270 Then 'Caption not assigned
430 Resume NextField
>
>
--
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.c om.auwrote in message
news:457de608$0 $21086$afc38c87 @news.optusnet. com.au...

>Thanks Allen. You got it in one .... again.
>So I rewrote the code to the following:
>PrintReports first calls the problem function (CreateTenantCh anges) to
>create the table on which it is built.
>>
>'---------------------------------------------------------------------------------------
>' Procedure : PrintReports
>' DateTime : 18/11/2006 09:11
>' Author : Bob Darlington
>' Purpose :
>'---------------------------------------------------------------------------------------
>'
>Public Function PrintReports(vV iew As Byte) As Boolean
>>
>10 On Error GoTo PrintReports_Er ror
> Dim strRpt As String
>>
>20 If Me.Dirty Then DoCmd.RunComman d acCmdSaveRecord
>>
>30 If Me!cbSummary Then
>40 Call CreateTenantCha nges(Me!TenantC ounter, Me!RefID)
>50 strRpt = "rTenantChanges Summary"
>60 Else
>70 strRpt = "rLVA"
>80 End If
>>
>90 DoCmd.OpenRepor t strRpt, vView
>>
>CloseFunctio n:
>100 On Error Resume Next
>>
>110 DoCmd.Hourglass False
>120 Exit Function
>>
>PrintReports_E rror:
>130 MsgBox "Error " & Err.Number & " (" & Err.Description & ") " _
> & "in procedure PrintReports in Line " & Erl & "."
>140 Resume CloseFunction
>End Function
>>
>'---------------------------------------------------------------------------------------
>' Procedure : CreateTenantCha nges
>' DateTime : 18/11/2006 09:18
>' Author : Bob Darlington
>' Purpose : Creates tTenantChanges for use in PrintReports and
>AcceptDraft
>'---------------------------------------------------------------------------------------
>'
>Public Function CreateTenantCha nges(vTenantCou nter As Long, vRefID As
>Long) As Boolean
>10 On Error GoTo CreateTenantCha nges_Error
>>
> Dim db As Database, rsOld As Recordset, rsNew As Recordset,
>rsTenantChange s 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.OpenRecordse t("SELECT * FROM tTenantDetails WHERE
>TenantCounte r = " & vRefID)
>40 Set rsNew = db.OpenRecordse t("SELECT * FROM tTenantDetails WHERE
>TenantCounte r = " & vTenantCounter)
>50 Set rsTenantChanges = db.OpenRecordse t("tTenantChang es")
>>
>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!TenantCou nter
>250 rsTenantChanges !FieldOldValue = rsOld(strFld).V alue
>260 rsTenantChanges !FieldNewValue = rsNew(strFld).V alue
>270 rsTenantChanges !FieldCaption = strCaption
>290 rsTenantChanges .Update
>300 End If
>NextField:
>310 Next
>320 End With
>>
>330 CreateTenantCha nges = True
>>
>CloseFunctio n:
>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
>>
>CreateTenantCh anges_Error:
>420 If Err = 3270 Then 'Caption not assigned
>430 GoTo NextField
>440 Else
>450 MsgBox "Error " & Err.Number & " (" & Err.Description & ") " _
> & "in procedure CreateTenantCha nges 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@Se eSig.invalidwro te in message
>news:457d2fba$ 0$2658$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...

>>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?
>>>
>>--
>>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.c om.auwrote in message
>>news:457d1121 $0$9773$afc38c8 7@news.optusnet .com.au...
>>>>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.F ields
>>> 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?
>>>--
>>>Bob Darlington
>>>Brisbane
>>>>
>>>
>>>

>>
>>

>
>