Subform issue - show discrete values in Continuous Forms mode

[Guest]

Hi all.

I'm using a Subform to represent the 'many' part of a 'one-to-many'
relationship between Tables.

The 'one' side corresponds to Transaction Numbers, and the 'many' side to
Transaction Line Numbers (ie, each Transaction may have many Lines in it).

The Subform's 'Default View' Property is set to Continuous Forms.

For some fields of the Subform I want to do a Lookup into some other Table
based on the value of a Control of the Subform for the specific record that
is active on the Subform.

The Lookup works fine (i use a DLookup in VBA), the problem is that the
value returned is shown on all of the Subform's records (when I only want it
to show on the current record).

To clarify:

The user picks a Service from the Subform.
I want to perform a Lookup in order to show on the current record of the
Subform the Price per Unit for the chosen Service.
The Lookup is performed, but the returned value is shown in all the 'Price
per unit' records of the Form.
This is undesirable, since for the next Subform record the user may choose a
different Service, that will have a different Price.
Again the new price (derived from the Lookup) will occupy all the Subform
records (even though the information is stored correctly in the Tables, ie
each Service gets its individual Price correctly).
Thus this is mostly a display issue.

Any word of wisdom on how to resolve this?

Thank you,
Alex

 

[Marshall Barton]

I'm using a Subform to represent the 'many' part of a 'one-to-many'
relationship between Tables.

The 'one' side corresponds to Transaction Numbers, and the 'many' side to
Transaction Line Numbers (ie, each Transaction may have many Lines in it).

The Subform's 'Default View' Property is set to Continuous Forms.

For some fields of the Subform I want to do a Lookup into some other Table
based on the value of a Control of the Subform for the specific record that
is active on the Subform.

The Lookup works fine (i use a DLookup in VBA), the problem is that the
value returned is shown on all of the Subform's records (when I only want it
to show on the current record).

Instead of setting the value using VBA code, set the text
box's control source expression to the DLookup.

It might be possible to use a Join in the subform's record
source query instead of DLookup, which can be pretty slow.

OTOH, if you want to save the value that your DLookup
returns, then it gets more complicated. I'd rather not go
into it if it's not needed, but if you do need to do this,
post back with a more detailed explanation of the tables,
their fields and what exactly is supposed to happen.

 

[Guest]

Thanks Marsh, very helpful.

Thankfully the values that i need to save are calculated based on the
Lookups (simple multiplication of the Lookup value (Price) and the User entry
(Units)), so i get the correct Total (Price*Units) saved for each record of
the Subform.

 

[Marshall Barton]

Does that mean that you have resolved the issue of
displaying the correct value? If you are saving the result
of a calculated control, there may be a race condition
between when the code that sets the bound control's value
and when the DLookup runs. I think this entire issue can be
resolved if you save the Price and Units values, not the
Price*Units values. If Price were a bound control, you
would not gave a display problem and a text box with the
expression =Price*Units can be used to display the total
cost correctly for each record. If you need to see the
total, a form footer text box with the expression
=Sum(Price*Units) can easily do that.

 

[Guest]

Well actually i plug the calculated value (Price * Units, where Price is a
Lookup and Units is a user entry) into the Bound control (that saves the
result in a Table) with VBA 'connected' to the AfterUpdate event of the
'Units' control (which is the User entry for each record of the Subform).
This works ok for display and storage purposes.
And the thing is i need the Total for each Subform record (again for display
and storage).

Thanks very much again.
Alex

Does that mean that you have resolved the issue of
displaying the correct value? If you are saving the result
of a calculated control, there may be a race condition
between when the code that sets the bound control's value
and when the DLookup runs. I think this entire issue can be
resolved if you save the Price and Units values, not the
Price*Units values. If Price were a bound control, you
would not gave a display problem and a text box with the
expression =Price*Units can be used to display the total
cost correctly for each record. If you need to see the
total, a form footer text box with the expression
=Sum(Price*Units) can easily do that.
--
Marsh
MVP [MS Access]

Thanks Marsh, very helpful.

Thankfully the values that i need to save are calculated based on the
Lookups (simple multiplication of the Lookup value (Price) and the User entry
(Units)), so i get the correct Total (Price*Units) saved for each record of
the Subform.

 

[Marshall Barton]

As I said before, you should **NOT** store the Price*Units
value. You should store the Price value and calculate the
Price*Units when you need to total or display the combined
value.

The Sum function can total a value from fields in a
(sub)form record source table/query across all the records.
It can not operate on controls in the (sub)form.

 

[Guest]

Good point, thanks.

As I said before, you should **NOT** store the Price*Units
value. You should store the Price value and calculate the
Price*Units when you need to total or display the combined
value.

The Sum function can total a value from fields in a
(sub)form record source table/query across all the records.
It can not operate on controls in the (sub)form.
--
Marsh
MVP [MS Access]

Well actually i plug the calculated value (Price * Units, where Price is a
Lookup and Units is a user entry) into the Bound control (that saves the
result in a Table) with VBA 'connected' to the AfterUpdate event of the
'Units' control (which is the User entry for each record of the Subform).
This works ok for display and storage purposes.
And the thing is i need the Total for each Subform record (again for display
and storage).