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

[Allen Browne]

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" <bo*@dpcmanAX.com.auwrote in message
news:45**********************@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.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?
--
Bob Darlington
Brisbane

[Bob Darlington]

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" <Al*********@SeeSig.invalidwrote in message
news:45**********************@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" <bo*@dpcmanAX.com.auwrote in message
news:45**********************@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.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?
--
Bob Darlington
Brisbane

[Allen Browne]

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" <bo*@dpcmanAX.com.auwrote in message
news:45***********************@news.optusnet.com.a u...

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" <Al*********@SeeSig.invalidwrote in message
news:45**********************@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" <bo*@dpcmanAX.com.auwrote in message
news:45**********************@news.optusnet.com.a u...

>>>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?

[Bob Darlington]

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" <Al*********@SeeSig.invalidwrote in message
news:45**********************@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.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" <bo*@dpcmanAX.com.auwrote in message
news:45***********************@news.optusnet.com.a u...

>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" <Al*********@SeeSig.invalidwrote in message
news:45**********************@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" <bo*@dpcmanAX.com.auwrote in message
news:45**********************@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.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?

[Allen Browne]

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

CreateTenantChanges_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" <bo*@dpcmanAX.com.auwrote in message
news:45***********************@news.optusnet.com.a u...

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" <Al*********@SeeSig.invalidwrote in message
news:45**********************@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" <bo*@dpcmanAX.com.auwrote in message
news:45**********************@news.optusnet.com.a u...

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

[Bob Darlington]

Thanks Allen.

--
Bob Darlington
Brisbane
"Allen Browne" <Al*********@SeeSig.invalidwrote in message
news:45**********************@per-qv1-newsreader-01.iinet.net.au...

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

CreateTenantChanges_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" <bo*@dpcmanAX.com.auwrote in message
news:45***********************@news.optusnet.com.a u...

>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" <Al*********@SeeSig.invalidwrote in message
news:45**********************@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" <bo*@dpcmanAX.com.auwrote in message
news:45**********************@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.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?
--
Bob Darlington
Brisbane