Help with form/subform problem?

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 qryTrans4CustBal.
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

re: Help with form/subform problem?

"Ron" <ronSPAMBLOCKERwest777@earthlink.com> wrote in
news:WiHWf.6308$HW2.2800@newsread3.news.pas.earthl ink.net:
[color=blue]
> 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
> qryTrans4CustBal. 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
>[/color]
Have you used the correct method of referring to the subform's
textbox? it must be addressed indirectly as
Forms!frmCust!sfrmBalance.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.

re: Help with form/subform problem?

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" <rquintal@sympatico.ca> wrote in message
news:Xns9795DC9BE5B9BQuintal@207.35.177.135...[color=blue]
> "Ron" <ronSPAMBLOCKERwest777@earthlink.com> wrote in
> news:WiHWf.6308$HW2.2800@newsread3.news.pas.earthl ink.net:
>[color=green]
>> 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
>> qryTrans4CustBal. 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
>>[/color]
> Have you used the correct method of referring to the subform's
> textbox? it must be addressed indirectly as
> Forms!frmCust!sfrmBalance.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.[/color]

re: Help with form/subform problem?

"Ron" <ronSPAMBLOCKERwest777@earthlink.com> wrote in
news:1gZWf.10413$Bj7.8923@newsread2.news.pas.earth link.net:
[color=blue]
> 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
>[/color]
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..

[color=blue]
> "Bob Quintal" <rquintal@sympatico.ca> wrote in message
> news:Xns9795DC9BE5B9BQuintal@207.35.177.135...[color=green]
>> "Ron" <ronSPAMBLOCKERwest777@earthlink.com> wrote in
>> news:WiHWf.6308$HW2.2800@newsread3.news.pas.earthl ink.net:
>>[color=darkred]
>>> 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 qryTrans4CustBal. 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
>>>[/color]
>> Have you used the correct method of referring to the
>> subform's textbox? it must be addressed indirectly as
>> Forms!frmCust!sfrmBalance.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.[/color]
>
>
>[/color]



--
Bob Quintal

PA is y I've altered my email address.

re: Help with form/subform problem?

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" <rquintal@sympatico.ca> wrote in message
news:Xns9795DC9BE5B9BQuintal@207.35.177.135...[color=blue]
> "Ron" <ronSPAMBLOCKERwest777@earthlink.com> wrote in
> news:WiHWf.6308$HW2.2800@newsread3.news.pas.earthl ink.net:
>[color=green]
>> 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
>> qryTrans4CustBal. 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
>>[/color]
> Have you used the correct method of referring to the subform's
> textbox? it must be addressed indirectly as
> Forms!frmCust!sfrmBalance.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.[/color]

re: Help with form/subform problem?

Your kind thanks made my day. thank you!

"Ron" <ronSPAMBLOCKERwest777@earthlink.com> wrote in
news:ltyYf.464$BS2.204@newsread1.news.pas.earthlin k.net:
[color=blue]
> 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" <rquintal@sympatico.ca> wrote in message
> news:Xns9795DC9BE5B9BQuintal@207.35.177.135...[color=green]
>> "Ron" <ronSPAMBLOCKERwest777@earthlink.com> wrote in
>> news:WiHWf.6308$HW2.2800@newsread3.news.pas.earthl ink.net:
>>[color=darkred]
>>> 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 qryTrans4CustBal. 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
>>>[/color]
>> Have you used the correct method of referring to the
>> subform's textbox? it must be addressed indirectly as
>> Forms!frmCust!sfrmBalance.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.[/color]
>
>[/color]



--
Bob Quintal

PA is y I've altered my email address.