How to get a calculated field inputted into a Table?

[ageis]

Let me preface this with: Yes, I know this goes against many things, I
know the problems it could create, and I know it should probably be
accomplished with a query.

But suppose all of that didn't exist and suppose I needed to populate
my table with the results of a calculated field from a field within my
form...and suppose this was my ONLY option.

I am dealing with Beginning Inventory, Receipts & Amount Processed.

This is the formula I have entered in the Control Source of the
[EndingInventory] field of the form:

=[BeginInventory]+[Receipts]-[Processed]

I need these results to populate the EndingInventory column in my table
(MailSort).

I understand that there are a couple of different ways to do this, one
dealing with using the EventProcedure/AfterUpdate option...but I am
lost. I am beginning to know my way around Access fairly well, but in
many area I am still a newbie.

Any help would greatly be appreciated. And for you gurus out there - -
if it helps at all you can call me crazy...I don't mind

 

[Rick Brandt]

Let me preface this with: Yes, I know this goes against many things, I
know the problems it could create, and I know it should probably be
accomplished with a query.

But suppose all of that didn't exist and suppose I needed to populate
my table with the results of a calculated field from a field within my
form...and suppose this was my ONLY option.

I am dealing with Beginning Inventory, Receipts & Amount Processed.

This is the formula I have entered in the Control Source of the
[EndingInventory] field of the form:

=[BeginInventory]+[Receipts]-[Processed]

I need these results to populate the EndingInventory column in my
table (MailSort).

I understand that there are a couple of different ways to do this, one
dealing with using the EventProcedure/AfterUpdate option...but I am
lost. I am beginning to know my way around Access fairly well, but in
many area I am still a newbie.

Any help would greatly be appreciated. And for you gurus out there -
- if it helps at all you can call me crazy...I don't mind

Have a hidden TextBox bound to the field in your table. In the BeforeUpdate
event of the form copy the value from the calculated control into the hidden
one.

Me!HiddenControlName = Me!EndingInventory

(and yes it's a bad idea)

 

[ageis]

Rick,

First of all, thank you for your response!

Ok...I'm messing up somewhere.

1. I created the hidden textbox

2. I bounded it to EndingInventory (that does mean setting it as my
Control Source, right?).

3.) I then entered Me!HiddenControlName = Me!EndingInventory in the
BeforeUpdate event (I entered this directly into the source code).

But nothing is happening. What did I miss?

Thanks again for your help.
Aaron

 

[Rick Brandt]

Rick,

First of all, thank you for your response!

Ok...I'm messing up somewhere.

1. I created the hidden textbox

2. I bounded it to EndingInventory (that does mean setting it as my
Control Source, right?).

3.) I then entered Me!HiddenControlName = Me!EndingInventory in the
BeforeUpdate event (I entered this directly into the source code).

But nothing is happening. What did I miss?

Thanks again for your help.

What you enter into the BeforeUpdate event's property box is "[Event
Procedure]" which is one of the choices in the drop down list. Then you
press the builder button [...] which takes you to the VBA code editor
window. That is where you put your line of code.

Is that what you did?

 

[ageis]

Thank you Rick...it makes sense now

I greatly appreciate your help!
Aaron

Rick,

First of all, thank you for your response!

Ok...I'm messing up somewhere.

1. I created the hidden textbox

2. I bounded it to EndingInventory (that does mean setting it as my
Control Source, right?).

3.) I then entered Me!HiddenControlName = Me!EndingInventory in the
BeforeUpdate event (I entered this directly into the source code).

But nothing is happening. What did I miss?

Thanks again for your help.

What you enter into the BeforeUpdate event's property box is "[Event
Procedure]" which is one of the choices in the drop down list. Then you
press the builder button [...] which takes you to the VBA code editor
window. That is where you put your line of code.

Is that what you did?