Update Table with Form Instantantly

[Simon]

Hey,

I have a form with a recordsource of a SELECT statement involving a Query
and the WHERE part is populated by a textbox on the form. I then have an
addtional 12 texboxes, all of which are populated by the recordsouce, but 6
are locked.

When i make change to the 6 editable textboxes, calculations are pefromed
and the results saved back into the original table that the recordsource is
based on. I then display these results in the locked textboxes and to do this
I requery the form to get up to date data.

My problem is that the data I enter is not updated in to table until I close
the form, is there some way I can force the data entry in to the table upon
exiting the textbox?

Any help greatly appreciated

Simon

 

[Maurice]

If you are sure you could place me.dirty=false in the on_exit event. and then
requery the form..

 

[Simon]

Thanks Maurice,
It appears to work, but I do not understand why.

Simon

 

[Maurice]

When you set me.dirty to false you are telling Access to go out of edit mode
thus saving the record. The opposite is true that's where you are telling
Access to go into edit mode (That's when you see the little pencil
recordselector).

 

[John W. Vinson]

Hey,

I have a form with a recordsource of a SELECT statement involving a Query
and the WHERE part is populated by a textbox on the form. I then have an
addtional 12 texboxes, all of which are populated by the recordsouce, but 6
are locked.

When i make change to the 6 editable textboxes, calculations are pefromed
and the results saved back into the original table that the recordsource is
based on. I then display these results in the locked textboxes and to do this
I requery the form to get up to date data.

Ummm... Saving the calculated fields into your table is neither necessary nor
a good idea! You can set the Control Source of a textbox to

=[A] + - ([X] / [Y])

or any other expression, to instantly and dynamically display calculated
values.

My problem is that the data I enter is not updated in to table until I close
the form, is there some way I can force the data entry in to the table upon
exiting the textbox?

In the AfterUpdate event of the textbox, you can explicitly save the record by
either

DoCmd.RunCommand acCmdSaveRecord <you can also use a Macro instead>

or

If Me.Dirty Then Me.Dirty = False

Either action will save the record; you can also type Shift-Enter to force a
save. But as I say... it's probably unwise to store calculated values AT ALL.
Here's my blurb on the subject:

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.

 

[Simon]

Hey,

I have a form with a recordsource of a SELECT statement involving a Query
and the WHERE part is populated by a textbox on the form. I then have an
addtional 12 texboxes, all of which are populated by the recordsouce, but 6
are locked.

When i make change to the 6 editable textboxes, calculations are pefromed
and the results saved back into the original table that the recordsource is
based on. I then display these results in the locked textboxes and to do this
I requery the form to get up to date data.

Ummm... Saving the calculated fields into your table is neither necessary nor
a good idea! You can set the Control Source of a textbox to

=[A] + - ([X] / [Y])

or any other expression, to instantly and dynamically display calculated
values.

My problem is that the data I enter is not updated in to table until I close
the form, is there some way I can force the data entry in to the table upon
exiting the textbox?

In the AfterUpdate event of the textbox, you can explicitly save the record by
either

DoCmd.RunCommand acCmdSaveRecord <you can also use a Macro instead>

or

If Me.Dirty Then Me.Dirty = False

Either action will save the record; you can also type Shift-Enter to force a
save. But as I say... it's probably unwise to store calculated values AT ALL.
Here's my blurb on the subject:

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.

Hi John,

Thanks for your comments.
To start with I was not storing the calculated fields, but since I then
needed the same information is a lot of different places, I though storing it
would be better. Using the stored values would minimise the errors in the
complex calculations for each field.

But thinking about it as I type, I could parse the textbox names to the
function and have the output of each calculation assigned to the result. I
would then be able to use the same function for all the forms and reports
that require this data.

Can you confim my madness make sense?
Thanks

Simon

 

[John W. Vinson]

To start with I was not storing the calculated fields, but since I then
needed the same information is a lot of different places, I though storing it
would be better. Using the stored values would minimise the errors in the
complex calculations for each field.

But thinking about it as I type, I could parse the textbox names to the
function and have the output of each calculation assigned to the result. I
would then be able to use the same function for all the forms and reports
that require this data.

Can you confim my madness make sense?

If the function is in fact complex, you may well want to write a VBA function
to implement it. That common function could then be called from forms, queries
or reports as needed.

Storing the value makes it LESS reliable, not more - inasmuch as a stored
value can be (intentionally or accidentaly) edited, or the values that went
into the calculation could be changed without reflecting the change in the
stored result.

 

[Simon]

If the function is in fact complex, you may well want to write a VBA function
to implement it. That common function could then be called from forms, queries
or reports as needed.

Storing the value makes it LESS reliable, not more - inasmuch as a stored
value can be (intentionally or accidentaly) edited, or the values that went
into the calculation could be changed without reflecting the change in the
stored result.

I have already started to remove the stored values
Thanks for input and knowledge.

Simon