How to update formfield on subform?

[Kebas53]

Here is the simplyfied scenario.

I have one tabel tblMain with two fields: [id] and [amount] (long integer)
Lets say the table is filled with 10 records.

id amount
-------------
1 10
2 20
3 30
4 40
5 50
6 60
7 70
8 80
9 90
10 100

What I like is the follewing:

On a form in datasheetview the user may select one record, say record 3.
After this selection a Subform should be displayed and show the difference
of the amounts for all records:

id amount Diff
------------------
1 10 -20
2 20 -10
3 30 0
4 40 10
5 50 20
6 60 30
7 70 40
8 80 50
9 90 60
10 100 70

I've tried a couple of scenario's, with a query as source en updating with a
recordset, with updating the form control...
Still no luck.

1. Should I make a field in the table for Diff? I thought an unbound
formfield should do.
2. What's the best way to get this working?

Thanks in advance,

Kindly regards,
Jacob.
The Netherlands.

 

[Tom van Stiphout]

On Mon, 25 Jan 2010 04:35:01 -0800, Kebas53

Say your subform control is named mySubform and it is on a form named
myForm.
The recordsource for the subform could be:
select id, amount,
amount - Forms!myForm!mySubform.Form!amount
from tblMain

-Tom.
Microsoft Access MVP

 

[Kebas53]

Hi Tim,

Thanks for your quick reply and solution.
It works when I run the subform and assign a value in the textbox by hand.
But which event should I use on the main form to detect which record is
selected?
I tried the Form_Current and the Form_AfterUpdate event with a
subform.requery command, but that did not work.
Any suggestions?

Best regards,
Jacob.

On Mon, 25 Jan 2010 04:35:01 -0800, Kebas53

Say your subform control is named mySubform and it is on a form named
myForm.
The recordsource for the subform could be:
select id, amount,
amount - Forms!myForm!mySubform.Form!amount
from tblMain

-Tom.
Microsoft Access MVP

Here is the simplyfied scenario.

I have one tabel tblMain with two fields: [id] and [amount] (long integer)
Lets say the table is filled with 10 records.

id amount
-------------
1 10
2 20
3 30
4 40
5 50
6 60
7 70
8 80
9 90
10 100

What I like is the follewing:

On a form in datasheetview the user may select one record, say record 3.
After this selection a Subform should be displayed and show the difference
of the amounts for all records:

id amount Diff
------------------
1 10 -20
2 20 -10
3 30 0
4 40 10
5 50 20
6 60 30
7 70 40
8 80 50
9 90 60
10 100 70

I've tried a couple of scenario's, with a query as source en updating with a
recordset, with updating the form control...
Still no luck.

1. Should I make a field in the table for Diff? I thought an unbound
formfield should do.
2. What's the best way to get this working?

Thanks in advance,

Kindly regards,
Jacob.
The Netherlands.

.