Expression builder not working after 2007 upgrade

[pvdalen]

Hi all,

I hope this is simple. I have the following expression to populate a text
box:

=IIf(IsNull([Forms]![Customer]![servicecall]![SCallID]),0,(DSum("[priceper]*[partquantity]","Parts","serviceID
= " & [Forms]![Customer]![servicecall]![SCallID])))

It worked just fine in Access 2002, but now that the system has been
upgraded to 2007, it no longer functions. There is no error message; just
inaction.

Can anyone help me with why this might be the case?

Thanks for your help.

 

[Douglas J. Steele]

[Forms]![Customer]![servicecall]![SCallID] doesn't look valid to me.

If you're trying to address a control named SCallID on a subform named
servicecall on form Customer, that should be

[Forms]![Customer]![servicecall].Form![SCallID]

(Make sure that the name of the subform control on the Customer form is
actually named servicecall: depending on how the subform was added, the name
of the subform control may be different than the name of the form being used
as the subform)

 

[pvdalen]

Hey Doug,

Thanks for the response.

What you suggested was not successful. What I'm trying to do, and did
successfully in the older version of Access, is to populate the text box with
the sum of values from a subform ("Parts" within the DSum expression); the
subform/control reference is what binds it to the parent customer, so the
SCallID isn't really a control on the form being reference....is that
correct? What really confounds me is how this works just fine in 2002, but
not in 2007.

[Forms]![Customer]![servicecall]![SCallID] doesn't look valid to me.

If you're trying to address a control named SCallID on a subform named
servicecall on form Customer, that should be

[Forms]![Customer]![servicecall].Form![SCallID]

(Make sure that the name of the subform control on the Customer form is
actually named servicecall: depending on how the subform was added, the name
of the subform control may be different than the name of the form being used
as the subform)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi all,

I hope this is simple. I have the following expression to populate a text
box:

=IIf(IsNull([Forms]![Customer]![servicecall]![SCallID]),0,(DSum("[priceper]*[partquantity]","Parts","serviceID
= " & [Forms]![Customer]![servicecall]![SCallID])))

It worked just fine in Access 2002, but now that the system has been
upgraded to 2007, it no longer functions. There is no error message; just
inaction.

Can anyone help me with why this might be the case?

Thanks for your help.

 

[pvdalen]

Hi Doug.

Thanks for your response. Unfortunately, this was not successful. What
confounds me is how this works just fine in 2002, but not in 2007.

[Forms]![Customer]![servicecall]![SCallID] doesn't look valid to me.

If you're trying to address a control named SCallID on a subform named
servicecall on form Customer, that should be

[Forms]![Customer]![servicecall].Form![SCallID]

(Make sure that the name of the subform control on the Customer form is
actually named servicecall: depending on how the subform was added, the name
of the subform control may be different than the name of the form being used
as the subform)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi all,

I hope this is simple. I have the following expression to populate a text
box:

=IIf(IsNull([Forms]![Customer]![servicecall]![SCallID]),0,(DSum("[priceper]*[partquantity]","Parts","serviceID
= " & [Forms]![Customer]![servicecall]![SCallID])))

It worked just fine in Access 2002, but now that the system has been
upgraded to 2007, it no longer functions. There is no error message; just
inaction.

Can anyone help me with why this might be the case?

Thanks for your help.

 

[Douglas J. Steele]

What I showed is the correct syntax. Previous versions of Access sometimes
were sloppy in terms of what they allowed.

Did you remember to change both instances of the reference? What do you
currently have?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug.

Thanks for your response. Unfortunately, this was not successful. What
confounds me is how this works just fine in 2002, but not in 2007.

[Forms]![Customer]![servicecall]![SCallID] doesn't look valid to me.

If you're trying to address a control named SCallID on a subform named
servicecall on form Customer, that should be

[Forms]![Customer]![servicecall].Form![SCallID]

(Make sure that the name of the subform control on the Customer form is
actually named servicecall: depending on how the subform was added, the
name
of the subform control may be different than the name of the form being
used
as the subform)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi all,

I hope this is simple. I have the following expression to populate a
text
box:

=IIf(IsNull([Forms]![Customer]![servicecall]![SCallID]),0,(DSum("[priceper]*[partquantity]","Parts","serviceID
= " & [Forms]![Customer]![servicecall]![SCallID])))

It worked just fine in Access 2002, but now that the system has been
upgraded to 2007, it no longer functions. There is no error message;
just
inaction.

Can anyone help me with why this might be the case?

Thanks for your help.

 

[Dirk Goldgar]

What I showed is the correct syntax. Previous versions of Access sometimes
were sloppy in terms of what they allowed.

Pardon me for jumping in, Doug, but while the expanded syntax you posted is
correct, the original condensed syntax should still work in Access 2007. It
works for me. If all the form, control and field names are correct, I
wonder if this a result of either VBA being disabled or Jet sandbox mode
being enabled.

pvdalen, does it work if you replace the IsNull() function in your
expression with a use of the Is Null condition, like this:

=IIf([Forms]![Customer]![servicecall]![SCallID] Is Null,0,
DSum("[priceper]*[partquantity]","Parts",
"serviceID = " & [Forms]![Customer]![servicecall]![SCallID]))


?

 

[Douglas J. Steele]

Pardon me for jumping in, Doug, but while the expanded syntax you posted
is correct, the original condensed syntax should still work in Access
2007. It works for me. If all the form, control and field names are
correct, I wonder if this a result of either VBA being disabled or Jet
sandbox mode being enabled.

Thanks, Dirk. I'm always meticulous about using the correct syntax, but I
thought I'd read that 2007 was a little persnickety.

 

[Dirk Goldgar]

I'm always meticulous about using the correct syntax, but I thought I'd
read that 2007 was a little persnickety.

I've heard that said (with regard to subform control references), but I
haven't seen it in practice. It may be that in some situations there's a
problem, but I have used the condensed reference form many times with no
problem yet. So I don't know if there's a real issue with it under some
circumstances, or if someone has drawn a faulty conclusion. Who knows,
maybe this thread will prove me wrong.

 

[pvdalen]

Hey guys,

Sory this update is so late, but I didn't get the chance to try to figure
this out again until just now.

The syntax in the expression was fine; I had to go into the 2007 Trust
Center. Under Trusted Locations, I checked the box labeled Allow Trusted
Locations on my network. That's followed by (Not recommended), but given the
past usage that this seting was now impeding, I didn't give a crap.

I appreciate the help and input of all here. Thanks again.

 

[Dirk Goldgar]

Hey guys,

Sory this update is so late, but I didn't get the chance to try to figure
this out again until just now.

Thanks for the update.

The syntax in the expression was fine; I had to go into the 2007 Trust
Center. Under Trusted Locations, I checked the box labeled Allow Trusted
Locations on my network. That's followed by (Not recommended), but given
the
past usage that this seting was now impeding, I didn't give a crap.

That implies that you are running the database from a network share, not
from your local PC. That is also not recormmeded, as Access databases that
are run over the network are much more prone to corruption. It would be
better to split the database into front-end and back-end, leave the back-end
database on the server, and give each user a copy of the front-end. If you
search the groups or the web, you'll find lots of discussions of this
technique.