[Repost] Form/Subform question

[Damien McBain]

In my Database (which I have posted details of in the post entitled
"Dcount?" below) to refresh the memory:
Like this:

Territories Customers Sales ProductHierarchy
----------- --------- ----- ----------------
AccNo 1--> AccNo
CustName PH2 <----1 PH2
Class ActTonnes Description
Terr 1-----> Terr ActSale
Description Status etc etc
SOff

I have a form based on the "Customers" table with a subform based on the
Sales table, linked by the field AccNo.
The subform displays continuous forms for each PH2 field.
I have some calculated TextBox's on the subform, for example ActSalePT has a
recordsource(=Iif(ActTonnes<>0,ActSale/ActTonnes,0).
On the calculated TexBox (which is locked) I have an OnDoubleClick event
which causes the ActSalePT to become invisible and another unbound text box
underneath it to become visible. The unbound text box has an AfterUpdate
event which changes the value of ActSale based on the new value typed into
the unbound text box:

Private Sub AdjTR_AfterUpdate()
[BudSale] = [AdjTR] * [BudTonnes]
[Text26].Visible = True
[BudSale].SetFocus
[AdjTR].Visible = False
End Sub

The problem is that when I double click the ActSalePT text box and it
disappears and the unbound text box appears, it appears on all the
continuous forms in the subform. If the users don't use the right record
(form in the subform) all hell breaks loose.

Is there a better way to do this such that only the data in the active form
in the subform can be edited in this way?

Damien

 

[Pieter Wijnen]

What about setting the field's locked property in the on Current Event of
the form
alternatively apply a filter to the form to only display the record to be
updated

HTH

Pieter

 

[Damien McBain]

Thanks for the reply Jackie. I've tried that. The problem is that the
subform uses continuous forms and any afterupdate events seem like they
operate on the same field on every form in the subform at the same time.
This confuses the really dumb users (sales reps!) who don't understand the
behavior of Access apps. I need to control the environment more so they
can't break it.
I'm thinking that when one of these calculated ratio type fields is being
editied, I might have to hide all the other forms on the subform by changing
the DefaultView propery in the editable fields.
Back to the drawing board...

 

[Damien McBain]

Thaks Pieter.

I'll try the locking. If I set the OnCurrent event to unlock the field will
that allow all the other individual forms in the subform to remain locked?

As for the filter, I really need all of the records to be visible at once.
It's a customer budgeting tool which selects a customer in the main form
then displays all related product records in the subform. Is I wrote in my
response to Jackie's post, I will try hiding all the other records only when
one of the ratio fields is being edited.

cheers

What about setting the field's locked property in the on Current
Event of the form
alternatively apply a filter to the form to only display the record
to be updated

HTH

Pieter

In my Database (which I have posted details of in the post entitled
"Dcount?" below) to refresh the memory:
Like this:

Territories Customers Sales ProductHierarchy
----------- --------- ----- ----------------
AccNo 1--> AccNo
CustName PH2 <----1 PH2
Class ActTonnes Description
Terr 1-----> Terr ActSale
Description Status etc etc
SOff

I have a form based on the "Customers" table with a subform based on
the Sales table, linked by the field AccNo.
The subform displays continuous forms for each PH2 field.
I have some calculated TextBox's on the subform, for example
ActSalePT has a recordsource(=Iif(ActTonnes<>0,ActSale/ActTonnes,0).
On the calculated TexBox (which is locked) I have an OnDoubleClick
event which causes the ActSalePT to become invisible and another
unbound text box underneath it to become visible. The unbound text
box has an AfterUpdate event which changes the value of ActSale
based on the new value typed into the unbound text box:

Private Sub AdjTR_AfterUpdate()
[BudSale] = [AdjTR] * [BudTonnes]
[Text26].Visible = True
[BudSale].SetFocus
[AdjTR].Visible = False
End Sub

The problem is that when I double click the ActSalePT text box and it
disappears and the unbound text box appears, it appears on all the
continuous forms in the subform. If the users don't use the right
record (form in the subform) all hell breaks loose.

Is there a better way to do this such that only the data in the
active form in the subform can be edited in this way?

Damien