Form.Recordset.RecordCount in Access2007

[Rob]

Hello,
I have a form named Myform with two subforms.
In one subform i have a field i want to fill with a value calcuted as sum in
the footer of the other subform.
With Access2003 i used this as Control Source for the field:

=IIf(Forms![Myform]![Myform
subform1].Form.Recordset.RecordCount>0;nz(Forms![Myform]![Myform
subform1]!Myfield;0);0)

and it works fine.
exactly the same database run in Access2007 gives me
#name
in the field, Does anyone know how to correct this. Is this a known feature
of Access2007 ?

(Exactly the same two subforms opened in a report preview opened from Myform
does not give this error)


Thanks for help,
Rob

 

[Dale Fye]

Rob,

I don't have or use 2007, but I think you have several syntax errors.

1. you used semi-colons to separate the components of the IIF, instead of
commas.
2. In the True portion, you don't actually mention the "Form", like you did
in expression portion.

Try:

=IIf(Forms![Myform]![Myform subform1].Form.Recordset.RecordCount>0, _
nz(Forms![Myform]![Myform subform1].Form.Myfield, 0), 0)

HTH
Dale

 

[Rob]

Hello Dale, thanks for your reply, but
No, i don't think that is the raison.
I was talking about the Control Source (not vba):
One cannot use commas in the place of semi-colons.
and
adding .form. will not help ...... i think,
maybe someone else has an idea?
I was thinking about a security problem????


Thanks anyway,
Rob

Rob,

I don't have or use 2007, but I think you have several syntax errors.

1. you used semi-colons to separate the components of the IIF, instead of
commas.
2. In the True portion, you don't actually mention the "Form", like you did
in expression portion.

Try:

=IIf(Forms![Myform]![Myform subform1].Form.Recordset.RecordCount>0, _
nz(Forms![Myform]![Myform subform1].Form.Myfield, 0), 0)

HTH
Dale

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Hello,
I have a form named Myform with two subforms.
In one subform i have a field i want to fill with a value calcuted as sum in
the footer of the other subform.
With Access2003 i used this as Control Source for the field:

=IIf(Forms![Myform]![Myform
subform1].Form.Recordset.RecordCount>0;nz(Forms![Myform]![Myform
subform1]!Myfield;0);0)

and it works fine.
exactly the same database run in Access2007 gives me
#name
in the field, Does anyone know how to correct this. Is this a known feature
of Access2007 ?

(Exactly the same two subforms opened in a report preview opened from Myform
does not give this error)


Thanks for help,
Rob

 

[Dale Fye]

Understand that you are talking about a control source. However, both the
IIF( ) and NZ( ) functions require commas as parameter separators, not
semi-colons.

in order to reference a control on a subform, the proper syntax is:

Foms![yourMainFormName].[subFormControlName].[Form].[ControlName]

where .[Form]. is literal. You have it in the expression portion of the
IIF( ), you need it in the True portion as well.

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Hello Dale, thanks for your reply, but
No, i don't think that is the raison.
I was talking about the Control Source (not vba):
One cannot use commas in the place of semi-colons.
and
adding .form. will not help ...... i think,
maybe someone else has an idea?
I was thinking about a security problem????


Thanks anyway,
Rob

Rob,

I don't have or use 2007, but I think you have several syntax errors.

1. you used semi-colons to separate the components of the IIF, instead of
commas.
2. In the True portion, you don't actually mention the "Form", like you did
in expression portion.

Try:

=IIf(Forms![Myform]![Myform subform1].Form.Recordset.RecordCount>0, _
nz(Forms![Myform]![Myform subform1].Form.Myfield, 0), 0)

HTH
Dale

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Hello,
I have a form named Myform with two subforms.
In one subform i have a field i want to fill with a value calcuted as sum in
the footer of the other subform.
With Access2003 i used this as Control Source for the field:

=IIf(Forms![Myform]![Myform
subform1].Form.Recordset.RecordCount>0;nz(Forms![Myform]![Myform
subform1]!Myfield;0);0)

and it works fine.
exactly the same database run in Access2007 gives me
#name
in the field, Does anyone know how to correct this. Is this a known feature
of Access2007 ?

(Exactly the same two subforms opened in a report preview opened from Myform
does not give this error)


Thanks for help,
Rob

 

[Rick Brandt]

Understand that you are talking about a control source. However,
both the IIF( ) and NZ( ) functions require commas as parameter
separators, not semi-colons.

Many of the international versions use semi-colons where we (in the US) use
commas.

 

[Dale Fye]

Thanks, Rick. Never thought of that.

Many of the international versions use semi-colons where we (in the US)
use commas.

 

[Paul Shapiro]

You are correct that this is a new bug in Access 2007, and its not fixed in
SP1. Allen Browne has suggested the workaround to use the expression in a
function (where it will work correctly) and use that function as the control
source.