Updating the value of a bound control on the main form from the subform

[jkoca]

I have an inventory table which contain the quantity on hand. I have another
table which contains stock transactions which among other things represent
the ins/outs of inventory. I really want to keep the on hand in total in the
inventory table since there will be a lot of stock transactions.

I have a main form for the inventory table, and have the stock transactions
as a subform. When I enter a stock transaction I want to add and subtract
from the bound control on the main form for the on hand.

I have a couple of questions...

Which event on the subform can I use to do the updating. Since the subform
is in continuous view, do I use the subforms form event after insert ?

In this event, what is the syntax I would use to refer to the bound control
on the main form ?

Thanks,

Jim Koca

 

[jkoca]

Graham

My subform displays in datasheet view, but the form does have a footer total
field. I went to the properties of the subform, and created AfterUpdate code
to take the value of the subform footer total field and move it to the
Parent textbox, but it just doesn't display correctly. I added a couple of
debug prints to show the values of both fields, and they are inaccurate. If
I set a watch point on the single line that makes the parent = the subform,
and step into the code, each debug print shows the correct results, and
before I hit the exit, I look and the form is displaying the correct values.

I am totally baffled. Is this some timing issue that the debugger slows it
down so everything can synch up ?

Can you tell me the syntax from the main form to reference the field on the
subform ?

Any help you can provide would be GRATEFULLY appreciated.

Thanks,

Jim Koca

Hi Jim

I think I just answered the second part in your other post.

The AfterInsert event should be fine. If you can also edit existing records
in your subform then use AfterUpdate instead (AfterUpdate fires just before
AfterInsert). You could have an invisible textbox in your subform footer,
maintaining the sum of all inventory transactions, and simply copy that
value to your main form record.

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

I have an inventory table which contain the quantity on hand. I have another
table which contains stock transactions which among other things represent
the ins/outs of inventory. I really want to keep the on hand in total in the
inventory table since there will be a lot of stock transactions.

I have a main form for the inventory table, and have the stock transactions
as a subform. When I enter a stock transaction I want to add and subtract
from the bound control on the main form for the on hand.

I have a couple of questions...

Which event on the subform can I use to do the updating. Since the subform
is in continuous view, do I use the subforms form event after insert ?

In this event, what is the syntax I would use to refer to the bound control
on the main form ?

Thanks,

Jim Koca

 

[Graham Mandeno]

Hi Jim

Yes - it is definitely a timing issue. I tried it with the Orders form in
the Northwind database and it behaves as you report. I added:
For i = 1 To 300: DoEvents: Next
before setting the value and that was OK, but with a loop count of 200 it
still fails.

I suggest you either use a DSum:
Me.Parent!CurrentInventory = DSum("TransQty", _
"InvTransactions", "ProductID=" & ProductID)

Or it might be faster to could write your own code to do a Sum query.

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

Graham

My subform displays in datasheet view, but the form does have a footer total
field. I went to the properties of the subform, and created AfterUpdate code
to take the value of the subform footer total field and move it to the
Parent textbox, but it just doesn't display correctly. I added a couple of
debug prints to show the values of both fields, and they are inaccurate. If
I set a watch point on the single line that makes the parent = the subform,
and step into the code, each debug print shows the correct results, and
before I hit the exit, I look and the form is displaying the correct values.

I am totally baffled. Is this some timing issue that the debugger slows it
down so everything can synch up ?

Can you tell me the syntax from the main form to reference the field on the
subform ?

Any help you can provide would be GRATEFULLY appreciated.

Thanks,

Jim Koca

Hi Jim

I think I just answered the second part in your other post.

The AfterInsert event should be fine. If you can also edit existing records
in your subform then use AfterUpdate instead (AfterUpdate fires just before
AfterInsert). You could have an invisible textbox in your subform footer,
maintaining the sum of all inventory transactions, and simply copy that
value to your main form record.

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

I have an inventory table which contain the quantity on hand. I have another
table which contains stock transactions which among other things represent
the ins/outs of inventory. I really want to keep the on hand in total

in
the

inventory table since there will be a lot of stock transactions.

I have a main form for the inventory table, and have the stock transactions
as a subform. When I enter a stock transaction I want to add and subtract
from the bound control on the main form for the on hand.

I have a couple of questions...

Which event on the subform can I use to do the updating. Since the subform
is in continuous view, do I use the subforms form event after insert ?

In this event, what is the syntax I would use to refer to the bound control
on the main form ?

Thanks,

Jim Koca