Help with form/subform problem?

[Ron]

Hi All,

I've got a form called frmCust that has a subform on it called sfrmBalance.
sfrmBalance's control source is a query called qryTrans4CustBa l.
sfrmBalance has one field on it called fldTransTotal where the control
source is =Sum([LineTotal]). LineTotal is derived from Quantity * Amount
in the above query.

I have a checkbox called chkActive on frmCust that I want to allow the
operators to uncheck only if the balance of the customer is zero. How can I
code that, and where do I put the code so that it keeps them from changing
that checkmark if the customer's balance is NOT zero. I thought I'd just
enable = false that check box if balance isn't zero, but no way, no how can
I get the fldTransTotal to be anything but empty (as seen by the main form
frmCust). Yet, it looks fine on the form/subform when viewed and has
either a valid balance or an actual 0.

Help! Help! Thanks in advance,
ron

[Bob Quintal]

"Ron" <ro************ *******@earthli nk.com> wrote in
news:Wi******** *********@newsr ead3.news.pas.e arthlink.net:

Hi All,

I've got a form called frmCust that has a subform on it called
sfrmBalance. sfrmBalance's control source is a query called
qryTrans4CustBa l. sfrmBalance has one field on it called
fldTransTotal where the control source is =Sum([LineTotal]).
LineTotal is derived from Quantity * Amount in the above
query.

I have a checkbox called chkActive on frmCust that I want to
allow the operators to uncheck only if the balance of the
customer is zero. How can I code that, and where do I put the
code so that it keeps them from changing that checkmark if the
customer's balance is NOT zero. I thought I'd just enable =
false that check box if balance isn't zero, but no way, no how
can I get the fldTransTotal to be anything but empty (as seen
by the main form frmCust). Yet, it looks fine on the
form/subform when viewed and has either a valid balance or an
actual 0.

Help! Help! Thanks in advance,
ron

Have you used the correct method of referring to the subform's
textbox? it must be addressed indirectly as
Forms!frmCust!s frmBalance.Form !fldTransTotal. value
sometimes me. equals Forms!frmCust but sometimes that doesn't
work.

I'd put the code in the checkbox's BeforeUpdate event so that a
non zero balance can force cancellation of the update.
But if fldTransTotal is the only field on the subform, why use a
subform at all? Just use a DSum() function call as control
source on a textbox directly on the mainform.

Then you could easily make the checkbox enabled based on the
value of the textbox.
--
Bob Quintal

PA is y I've altered my email address.

[Ron]

Thanks Bob, that took care of my problem. I removed the subform and put a
new textbox as you suggested. Works great! I think when I initially did
this main form I was all excited about forms/subforms and thought that was
the best way to do this.

Your solution works great though, so I switched everything around.

Thanks again!
ron

"Bob Quintal" <rq******@sympa tico.ca> wrote in message
news:Xn******** *************@2 07.35.177.135.. .

"Ron" <ro************ *******@earthli nk.com> wrote in
news:Wi******** *********@newsr ead3.news.pas.e arthlink.net:

Hi All,

I've got a form called frmCust that has a subform on it called
sfrmBalance. sfrmBalance's control source is a query called
qryTrans4CustBa l. sfrmBalance has one field on it called
fldTransTotal where the control source is =Sum([LineTotal]).
LineTotal is derived from Quantity * Amount in the above
query.

I have a checkbox called chkActive on frmCust that I want to
allow the operators to uncheck only if the balance of the
customer is zero. How can I code that, and where do I put the
code so that it keeps them from changing that checkmark if the
customer's balance is NOT zero. I thought I'd just enable =
false that check box if balance isn't zero, but no way, no how
can I get the fldTransTotal to be anything but empty (as seen
by the main form frmCust). Yet, it looks fine on the
form/subform when viewed and has either a valid balance or an
actual 0.

Help! Help! Thanks in advance,
ron

Have you used the correct method of referring to the subform's
textbox? it must be addressed indirectly as
Forms!frmCust!s frmBalance.Form !fldTransTotal. value
sometimes me. equals Forms!frmCust but sometimes that doesn't
work.

I'd put the code in the checkbox's BeforeUpdate event so that a
non zero balance can force cancellation of the update.
But if fldTransTotal is the only field on the subform, why use a
subform at all? Just use a DSum() function call as control
source on a textbox directly on the mainform.

Then you could easily make the checkbox enabled based on the
value of the textbox.
--
Bob Quintal

PA is y I've altered my email address.

[Bob Quintal]

"Ron" <ro************ *******@earthli nk.com> wrote in
news:1g******** **********@news read2.news.pas. earthlink.net:

Thanks Bob, that took care of my problem. I removed the
subform and put a new textbox as you suggested. Works great!
I think when I initially did this main form I was all excited
about forms/subforms and thought that was the best way to do
this.

Your solution works great though, so I switched everything
around.

Thanks again!
ron
Thank you for the positive feedback. Glad to have helped. I
sometimes look at an application I did long ago, and wonder why
I did something in such a convoluted fashion.

Then I remind myself of having learned a better way. I suppose
in five years I'll look at my current creations and say, ewwww,
that was clumsy. Life is a learning experience..

"Bob Quintal" <rq******@sympa tico.ca> wrote in message
news:Xn******** *************@2 07.35.177.135.. .

"Ron" <ro************ *******@earthli nk.com> wrote in
news:Wi******** *********@newsr ead3.news.pas.e arthlink.net:

Hi All,

I've got a form called frmCust that has a subform on it
called sfrmBalance. sfrmBalance's control source is a query
called qryTrans4CustBa l. sfrmBalance has one field on it
called fldTransTotal where the control source is
=Sum([LineTotal]). LineTotal is derived from Quantity *
Amount in the above query.

I have a checkbox called chkActive on frmCust that I want to
allow the operators to uncheck only if the balance of the
customer is zero. How can I code that, and where do I put
the code so that it keeps them from changing that checkmark
if the customer's balance is NOT zero. I thought I'd just
enable = false that check box if balance isn't zero, but no
way, no how can I get the fldTransTotal to be anything but
empty (as seen by the main form frmCust). Yet, it looks
fine on the form/subform when viewed and has either a valid
balance or an actual 0.

Help! Help! Thanks in advance,
ron

Have you used the correct method of referring to the
subform's textbox? it must be addressed indirectly as
Forms!frmCust!s frmBalance.Form !fldTransTotal. value
sometimes me. equals Forms!frmCust but sometimes that doesn't
work.

I'd put the code in the checkbox's BeforeUpdate event so that
a non zero balance can force cancellation of the update.
But if fldTransTotal is the only field on the subform, why
use a subform at all? Just use a DSum() function call as
control source on a textbox directly on the mainform.

Then you could easily make the checkbox enabled based on the
value of the textbox.
--
Bob Quintal

PA is y I've altered my email address.

--
Bob Quintal

PA is y I've altered my email address.

[Ron]

I did what you suggested and did away with the subform for this control.
It's all working much better now. Without the subforms, the screen builds
much faster each time it's loaded and switches from record to record MUCH
faster.

Thanks. I appreciate the knowledgeable response!
ron

"Bob Quintal" <rq******@sympa tico.ca> wrote in message
news:Xn******** *************@2 07.35.177.135.. .

"Ron" <ro************ *******@earthli nk.com> wrote in
news:Wi******** *********@newsr ead3.news.pas.e arthlink.net:

Hi All,

I've got a form called frmCust that has a subform on it called
sfrmBalance. sfrmBalance's control source is a query called
qryTrans4CustBa l. sfrmBalance has one field on it called
fldTransTotal where the control source is =Sum([LineTotal]).
LineTotal is derived from Quantity * Amount in the above
query.

I have a checkbox called chkActive on frmCust that I want to
allow the operators to uncheck only if the balance of the
customer is zero. How can I code that, and where do I put the
code so that it keeps them from changing that checkmark if the
customer's balance is NOT zero. I thought I'd just enable =
false that check box if balance isn't zero, but no way, no how
can I get the fldTransTotal to be anything but empty (as seen
by the main form frmCust). Yet, it looks fine on the
form/subform when viewed and has either a valid balance or an
actual 0.

Help! Help! Thanks in advance,
ron

Have you used the correct method of referring to the subform's
textbox? it must be addressed indirectly as
Forms!frmCust!s frmBalance.Form !fldTransTotal. value
sometimes me. equals Forms!frmCust but sometimes that doesn't
work.

I'd put the code in the checkbox's BeforeUpdate event so that a
non zero balance can force cancellation of the update.
But if fldTransTotal is the only field on the subform, why use a
subform at all? Just use a DSum() function call as control
source on a textbox directly on the mainform.

Then you could easily make the checkbox enabled based on the
value of the textbox.
--
Bob Quintal

PA is y I've altered my email address.

[Bob Quintal]

Your kind thanks made my day. thank you!

"Ron" <ro************ *******@earthli nk.com> wrote in
news:lt******** *******@newsrea d1.news.pas.ear thlink.net:

I did what you suggested and did away with the subform for
this control. It's all working much better now. Without the
subforms, the screen builds much faster each time it's loaded
and switches from record to record MUCH faster.

Thanks. I appreciate the knowledgeable response!
ron

"Bob Quintal" <rq******@sympa tico.ca> wrote in message
news:Xn******** *************@2 07.35.177.135.. .

"Ron" <ro************ *******@earthli nk.com> wrote in
news:Wi******** *********@newsr ead3.news.pas.e arthlink.net:

Hi All,

I've got a form called frmCust that has a subform on it
called sfrmBalance. sfrmBalance's control source is a query
called qryTrans4CustBa l. sfrmBalance has one field on it
called fldTransTotal where the control source is
=Sum([LineTotal]). LineTotal is derived from Quantity *
Amount in the above query.

I have a checkbox called chkActive on frmCust that I want to
allow the operators to uncheck only if the balance of the
customer is zero. How can I code that, and where do I put
the code so that it keeps them from changing that checkmark
if the customer's balance is NOT zero. I thought I'd just
enable = false that check box if balance isn't zero, but no
way, no how can I get the fldTransTotal to be anything but
empty (as seen by the main form frmCust). Yet, it looks
fine on the form/subform when viewed and has either a valid
balance or an actual 0.

Help! Help! Thanks in advance,
ron

Have you used the correct method of referring to the
subform's textbox? it must be addressed indirectly as
Forms!frmCust!s frmBalance.Form !fldTransTotal. value
sometimes me. equals Forms!frmCust but sometimes that doesn't
work.

I'd put the code in the checkbox's BeforeUpdate event so that
a non zero balance can force cancellation of the update.
But if fldTransTotal is the only field on the subform, why
use a subform at all? Just use a DSum() function call as
control source on a textbox directly on the mainform.

Then you could easily make the checkbox enabled based on the
value of the textbox.
--
Bob Quintal

PA is y I've altered my email address.

--
Bob Quintal

PA is y I've altered my email address.