Forms -- Reference unbound control on Previous record

[SydneyGeek]

Hi, I'm putting together a budget system and have hit a problem.
I want to pull the value of an unbound, calculated control from, say, record 1 into a different unbound control on record 2 -- [Remainder] from record 1 becomes [CarriedOver] on record 2, and so on. The controls need to be unbound so the calculations update as changes are made.
I'm not sure whether to go for a code-based solution, or a DLOOKUP, but either way, how do I refer to the relevant controls?

Thanks

Denis

 

[Andrew Fergus]

Hi Denis, I'm not sure if I understand your problem 100%.

Do you want the value to be carried forward from the first record to the second when you exit the first record - for example, once you finish editing record 1 and then you tab or enter into record 2, do you want the carried forward value in record 2 to change at that point in time?

If so, this can be done with a macro activated under OnExit in the last field on the form, using the following functions (off the top of my head) : Goto Control Remainder, RunCommand Copy, Goto Record Next, Goto Control CarriedForward, RunCommand Paste, Goto Control [?whereever you want to end up for the next record]?, Stop Macro.

Whilst this will carry the remainder forward for record 2, it will not update the values for records 3 and later. This assumes that you have called the unbound controls [Remainder] and [CarriedForward] on your form. Please advise if this is way off track

Hope this helps, Andrew.

 

[SydneyGeek]

Andrew, thanks for your help -- it's got me thinking
I could definitely make that work, but I'm still wondering if a calculation-based approach is possible, purely because it's always updating live...

Denis

 

[bat17]

Is this a single form view or Continous? I think that you will need to calculate it either way to allow for travel through the records in both directions.

I dont think that you will refer to the controls but calculate from scratch each time.

peter

 

[Andrew Fergus]

I agree my solution is very limited and assumes one user working on one record at a time.

I'm not sure of a calculation-based solution for multiple unbound controls (i.e. for multiple records) on a continuous form - to get that to work I think you would need to store the remainder values in a table and then force an update for every record subsequent to the one you changed.

However, the remainder is going to be based on a value of some sort that is outside of the main records is it not? Lastly, what do you mean by "always updating live?"

Cheers, Andrew

 

[Andrew Fergus]

Having subconsciously thought about your problem, I believe it can be done without storing the remainders by removing my suggested macro from the OnExit property and assigning a modified version to a button (say "Recalculate Remainders") on the form header or footer. You could update whatever fields you wanted throughout the continuous form and when you are ready, you click on "Recalculate Remainders".

The macro would start by calculating the first remainder, and then use the macro commands in my previous post inside a loop that would recalculate every remainder thereafter throughout the form. You might also want to turn off the echo and warnings at the start of the macro and then turn them back on at the end of the macro.

Hope this helps, Andrew.