Conversion to A2007

[JimS]

My client decided with no notice to convert to A2007 today from A2003...25
tables, 200 queries, etc....

Anyway, I've gotten in and solved a few issues, but one is being difficult.
I have a text box on the main form (in a tabbed control) that refers (in its
control source) to a subform as follows:

=IIf(sfmPOLinesTab.Form.recordset.RecordCount>0,sfmPOLinesTab.Form!tbTotalPODlrValue,0)

The idea is to reiterate the totals from the subform on the main (tabbed)
form. It worked just fine in A2003. Now it's failing. I tried using the
"build" wizard to rebuild the control source, but two problems. "recordset"
is not available at design time, so I'm forced to us it as it, and while it
builds the "sfmPOLinesTab....." part of it just fine, I can't see it even at
run time. It says "...refers to an object that has no value..." Error 2427.

I put "me." in front of the recordset reference, and that seemed to help
that reference. Putting "me." in front of the other reference did not.

Any ideas? Other gotchas I need to worry about?

 

[PJFry]

I don't if this will help, but when I upgraded from 2003 to 2007 I had to go
in and loaded in my prior reference libraries. One of the databases I was
working with used ADO there were no ADO references selected. Once I
corrected that, things went much more smoothly.

Hope that helps.

PJ

 

[NTC]

I would trouble shoot by starting simple.... have the mainform's unbound text
box
=Me.SubformName.Form!SubformControlName

and then go from there....

 

[JimS]

This problem is much harder than I thought. If the record count is zero, the
form isn't even instantiating the control, which is located on the footer of
the DATASHEET. I tried your simple troubleshooting technique.

The subform control is a text box with a control source of
=Sum(Round([cptransamount],2))

If the subform returns no data, the main form's reference to the subform's
text box returns #error.
If the subform returns data, the the main form's reference to the
subform's text box returns a reasonable answer (a currency amount).

So, the issue is: How do I return this simple number from the subform when
there is subform data, and a zero when there's not? In A2003, the expression
I included in my original question worked. Now it doesn't. I'd guess it has
to do with runtime properties not being available when the subform returns no
data.

Should I check to see if the recordset.recordcount property exists instead?

Jim

 

[Mike Painter]

Try creating the control over, even with just a cut and paste.
It solved an odd problem for me with a query.

This problem is much harder than I thought. If the record count is
zero, the form isn't even instantiating the control, which is located
on the footer of the DATASHEET. I tried your simple troubleshooting
technique.

The subform control is a text box with a control source of
=Sum(Round([cptransamount],2))

If the subform returns no data, the main form's reference to the
subform's text box returns #error.
If the subform returns data, the the main form's reference to the
subform's text box returns a reasonable answer (a currency amount).

So, the issue is: How do I return this simple number from the
subform when there is subform data, and a zero when there's not? In
A2003, the expression I included in my original question worked. Now
it doesn't. I'd guess it has to do with runtime properties not being
available when the subform returns no data.

Should I check to see if the recordset.recordcount property exists
instead?

Jim

I would trouble shoot by starting simple.... have the mainform's
unbound text box
=Me.SubformName.Form!SubformControlName

and then go from there....

 

[NTC]

well - if a subform has no data - one would expect this behavior. I don't
see how this worked in your 2003 version in this situation.

in your underlying query that is the source of the subform data - use an IIF
statement to assure that there is at least a 0 value

This problem is much harder than I thought. If the record count is zero, the
form isn't even instantiating the control, which is located on the footer of
the DATASHEET. I tried your simple troubleshooting technique.

The subform control is a text box with a control source of
=Sum(Round([cptransamount],2))

If the subform returns no data, the main form's reference to the subform's
text box returns #error.
If the subform returns data, the the main form's reference to the
subform's text box returns a reasonable answer (a currency amount).

So, the issue is: How do I return this simple number from the subform when
there is subform data, and a zero when there's not? In A2003, the expression
I included in my original question worked. Now it doesn't. I'd guess it has
to do with runtime properties not being available when the subform returns no
data.

Should I check to see if the recordset.recordcount property exists instead?

Jim

I would trouble shoot by starting simple.... have the mainform's unbound text
box
=Me.SubformName.Form!SubformControlName

and then go from there....

 

[JimS]

Found the problem. A2007 has a bug. It can't deal with
[subform].[form].[recordset].[recordcount]. As a result, it displays a #error
message when the subform has no records. The text box on the subform is
displaying the same #error.

Allen Browne in Australia has a workaround on his web site that works
swimmingly. Thank you, Allen.

http://www.eggheadcafe.com/software/aspnet/31487601/trapping-empty-or-null-va.aspx
--
Jim

Try creating the control over, even with just a cut and paste.
It solved an odd problem for me with a query.

This problem is much harder than I thought. If the record count is
zero, the form isn't even instantiating the control, which is located
on the footer of the DATASHEET. I tried your simple troubleshooting
technique.

The subform control is a text box with a control source of
=Sum(Round([cptransamount],2))

If the subform returns no data, the main form's reference to the
subform's text box returns #error.
If the subform returns data, the the main form's reference to the
subform's text box returns a reasonable answer (a currency amount).

So, the issue is: How do I return this simple number from the
subform when there is subform data, and a zero when there's not? In
A2003, the expression I included in my original question worked. Now
it doesn't. I'd guess it has to do with runtime properties not being
available when the subform returns no data.

Should I check to see if the recordset.recordcount property exists
instead?

Jim

I would trouble shoot by starting simple.... have the mainform's
unbound text box
=Me.SubformName.Form!SubformControlName

and then go from there....

:

My client decided with no notice to convert to A2007 today from
A2003...25 tables, 200 queries, etc....

Anyway, I've gotten in and solved a few issues, but one is being
difficult. I have a text box on the main form (in a tabbed control)
that refers (in its control source) to a subform as follows:

=IIf(sfmPOLinesTab.Form.recordset.RecordCount>0,sfmPOLinesTab.Form!tbTotalPODlrValue,0)

The idea is to reiterate the totals from the subform on the main
(tabbed) form. It worked just fine in A2003. Now it's failing. I
tried using the "build" wizard to rebuild the control source, but
two problems. "recordset" is not available at design time, so I'm
forced to us it as it, and while it builds the "sfmPOLinesTab....."
part of it just fine, I can't see it even at run time. It says
"...refers to an object that has no value..." Error 2427.

I put "me." in front of the recordset reference, and that seemed to
help that reference. Putting "me." in front of the other reference
did not.

Any ideas? Other gotchas I need to worry about?

 

[JimS]

In my original post, I showed an iif statement that tested for no records in
the subform. Although syntactically correct, that test no longer works in
A2007. Allen Browne provided a workaround that works well. Thank you for
answering me!
--
Jim

well - if a subform has no data - one would expect this behavior. I don't
see how this worked in your 2003 version in this situation.

in your underlying query that is the source of the subform data - use an IIF
statement to assure that there is at least a 0 value

This problem is much harder than I thought. If the record count is zero, the
form isn't even instantiating the control, which is located on the footer of
the DATASHEET. I tried your simple troubleshooting technique.

The subform control is a text box with a control source of
=Sum(Round([cptransamount],2))

If the subform returns no data, the main form's reference to the subform's
text box returns #error.
If the subform returns data, the the main form's reference to the
subform's text box returns a reasonable answer (a currency amount).

So, the issue is: How do I return this simple number from the subform when
there is subform data, and a zero when there's not? In A2003, the expression
I included in my original question worked. Now it doesn't. I'd guess it has
to do with runtime properties not being available when the subform returns no
data.

Should I check to see if the recordset.recordcount property exists instead?

Jim

I would trouble shoot by starting simple.... have the mainform's unbound text
box
=Me.SubformName.Form!SubformControlName

and then go from there....

:

My client decided with no notice to convert to A2007 today from A2003...25
tables, 200 queries, etc....

Anyway, I've gotten in and solved a few issues, but one is being difficult.
I have a text box on the main form (in a tabbed control) that refers (in its
control source) to a subform as follows:

=IIf(sfmPOLinesTab.Form.recordset.RecordCount>0,sfmPOLinesTab.Form!tbTotalPODlrValue,0)

The idea is to reiterate the totals from the subform on the main (tabbed)
form. It worked just fine in A2003. Now it's failing. I tried using the
"build" wizard to rebuild the control source, but two problems. "recordset"
is not available at design time, so I'm forced to us it as it, and while it
builds the "sfmPOLinesTab....." part of it just fine, I can't see it even at
run time. It says "...refers to an object that has no value..." Error 2427.

I put "me." in front of the recordset reference, and that seemed to help
that reference. Putting "me." in front of the other reference did not.

Any ideas? Other gotchas I need to worry about?