Not All Fields Update my Table from my Form

[Commish]

I am building a fantasy baseball draft tool for myself. Mostly just to
see if I can do it, and knowing that having the skillset will help me
elsewhere down the road.

I have most of the forms working as I want them. However...

I have a form that is not. I have the form based off of the table
"DraftTracker_All" - and the first field is a ComboBox that works
correctly bringing up fields from the DraftTracker_All table and
bringing up the correct related recordson forms on tabs within the
form. So far so good.

The fields are then populated based on the outcome of an auction of
the player selected from the combo box. I have the combo Box for tea
assignment, position, and salary working correctly. Also good.

However, I have 2 fields that track when in the auction that player
was awarded - the two fields are ROUND and PICK, there is a field
named "Round" in the tabel DraftTracker_All. The value that shows on
the form is populated by the formula
"=1+Int(DLookUp("DraftedPlayers","qry_DraftedPlayers")/13)" which is a
query that I use to count the number of players auctioned off so far.
This calculation will assign the player a round 1st, 2nd, 3rd, etc.
The calculation looks correct. But...

That field doesn't update back to the table - while all of the others
do. When I changed the field "ROUND" from a regular text field to have
that calculation, I must have changed something that I didn't want
to.... what do I need ot change to make the value be saved back to the
table?

 

[John W. Vinson]

However, I have 2 fields that track when in the auction that player
was awarded - the two fields are ROUND and PICK, there is a field
named "Round" in the tabel DraftTracker_All. The value that shows on
the form is populated by the formula
"=1+Int(DLookUp("DraftedPlayers","qry_DraftedPlayers")/13)" which is a
query that I use to count the number of players auctioned off so far.
This calculation will assign the player a round 1st, 2nd, 3rd, etc.
The calculation looks correct. But...

That field doesn't update back to the table

Of course not. It shouldn't.

Tables do not contain calculated expressions. If you have a calculation on a
form, that's all it is - a calculation on the form.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Commish]

Of course not. It shouldn't.

Tables do not contain calculated expressions. If you have a calculation on a
form, that's all it is - a calculation on the form.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

I'm not "exactly" storing a calculation.

I'm tracking progress, I need to know that order that a stream of
events occurred. And, I need to capture it as it occurs.

So, I am calculating a value that reflects a given progress step, and
I want it stored with the appropriate player. I can calculate it to
display it on the screen - but is there a straightforward way to move
that value back to the table?

 

[John W. Vinson]

I'm not "exactly" storing a calculation.

I'm tracking progress, I need to know that order that a stream of
events occurred. And, I need to capture it as it occurs.

So, I am calculating a value that reflects a given progress step, and
I want it stored with the appropriate player. I can calculate it to
display it on the screen - but is there a straightforward way to move
that value back to the table?

You can use a suitable form event - say the form's BeforeUpdate - to copy the
contents of the calculated control to a different control, one bound to a
field in the table:

Me!boundcontrol = Me!calculcatedcontrol

However, storing the knowledge that a particular event has occurred is a
different task than storing the value of the result of a calculation. Not sure
what you're getting at here!
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Commish]

You can use a suitable form event - say the form's BeforeUpdate - to copythe
contents of the calculated control to a different control, one bound to a
field in the table:

Me!boundcontrol = Me!calculcatedcontrol

However, storing the knowledge that a particular event has occurred is a
different task than storing the value of the result of a calculation. Notsure
what you're getting at here!
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

If I use the AfterUpdate event, do I have have to change the event, or
will the VB code fire After I tab away from the text box?

 

[Commish]

You can use a suitable form event - say the form's BeforeUpdate - to copythe
contents of the calculated control to a different control, one bound to a
field in the table:

Me!boundcontrol = Me!calculcatedcontrol

However, storing the knowledge that a particular event has occurred is a
different task than storing the value of the result of a calculation. Notsure
what you're getting at here!
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Hopefully, explaining it better might help up get a solution.

I am moderating a fantasy baseball draft where players will be
selected (drafted) by league members. It's important for me to keep
track of the order in which the players were drafted. The first player
drafted will be ROUND=1, PICK=1, the next player would be ROUND=1,
PICK=2, and so on... I have the calculation worked out - and I can
display the correct numbers on my form, but as I move to the next
player in the draft, I want the ROUND and PICK of the previous player
to be stored back on that player's record in the table
DraftTracker_All. And I am not gettting that working.

To move to the previous response... I had previously just added the
Fields ROUND and PICK to my form and set the control source to my
formula. It seems logical to me (wrong now, I know) that the value of
the calculation would be written back to that record as I exited that
record and moved to another one.

I removed the fields ROUND and PICK from my form, and added back 2
Text Boxes - in the Control Source, I put in my formulas. They display
fine. In the BeforeUpdate event, I added the following VB:


Private Sub Text36_BeforeUpdate(Cancel As Integer)

Me!Pick = Text36

End Sub