Code to Update calculated fields in form

[Hilary Ostrov]

I'm not a programmer, but I am a firm believer in well thought out
user-friendly, intuitive, logical interfaces! I've often used
customized Access apps. created by others - in which such principles
seemed conspicuous by their absence!

So now I'm dabbling my feet into the waters of developing my own! I
am not particularly knowledgeable about VBA coding, syntax, naming
conventions etc. (although I'm figuring out some as I go along! And if
someone could point me to a primer on the meanings and common usage of
<Me.> <Dim> and such, I would be eternally grateful!) But that aside
....

Using Access 2000 on a Win XP system, I have a Form for entering
product data. I've successfully created combo boxes and managed to
get the results of a selection to populate other fields - primarily by
finding snippets of code elsewhere, and modifying accordingly. To
save keystrokes, I've even set Tab Stop=No in those fields which will
not require user input!

I've also successfully coded fields to do simple calculations based on
the data entered in other fields. It all works like a charm!

But I know that I need something else to ensure that any subsequent
change in one or more of the fields of the record on which a
calculation has been based will be reflected in the result(s) field(s)
- without having to re-enter unchanged data any other fields included
in the calculation!

Re-entering all relevant fields does the trick, but I'm sure there
must be a simple piece of code to bypass the need for this.

The fields in question are as follows:

Unit [selected from combo box]
U Price [text box]
Prof [text box]
S Price [text box based on calcuation]
Qty [text box]
Pcd Price [text box based on calculation]

(yes, I know ... spaces in field names is not the best practice, but
the person who will be using this .mdb is accustomed to seeing them
this way!)

Here's my relevant code so far:

====
Private Sub Prof_AfterUpdate()
'Update the S Price Field'

Me.S_Price.Value = ((U_Price / Unit) * (100 + Prof) / 100)

End Sub

Private Sub Qty_AfterUpdate()
'Update the Purchased Price field'

Me.Pcd_Price.Value = (U_Price / Unit) * Qty

End Sub

Private Sub Unit_AfterUpdate()
'Update the Unit field'

Me.Unit.Value = Me.Unit.Column(1)
End Sub
====

What am I missing?!

Incidentally the Unit combo box Column count is 3 and Row source :

SELECT [tblMeasure].[Unit], [tblMeasure].

, [tblMeasure].[Note]
FROM tblMeasure;

It is currently bound to Column 1 and it works!  But ideally, I'd like
the user to be able to do kbd alpha selection of Code (e.g. EA or C)
(rather than cycling through list with cursor key) and have this
"translate" (although I'm sure that is not the correct term!) into the
numerical Unit value (e.g. 1 or 100)

Again, I'm sure this must be doable, and I've tried a few ways, but
they don't give me the desired result!  One thing I haven't tried is
including the ID in the tbl query.  Could that be the key?!

Many thanks in advance!

hro

 

[John Vinson]

Using Access 2000 on a Win XP system, I have a Form for entering
product data. I've successfully created combo boxes and managed to
get the results of a selection to populate other fields - primarily by
finding snippets of code elsewhere, and modifying accordingly. To
save keystrokes, I've even set Tab Stop=No in those fields which will
not require user input!

Well... one thing to avoid is storing data redundantly. If you're
pulling data from one table via a combo box in order to store that
data in another table... STOP. It's not necessary (or very rarely
necessary) and should only be done if you have a GOOD reason (such as
wanting to grab the price as of the current moment to store in a
sale-price field, knowing that the item price may change tomorrow).

I've also successfully coded fields to do simple calculations based on
the data entered in other fields. It all works like a charm!

But I know that I need something else to ensure that any subsequent
change in one or more of the fields of the record on which a
calculation has been based will be reflected in the result(s) field(s)
- without having to re-enter unchanged data any other fields included
in the calculation!

Basically, DON'T store the calculated field AT ALL. Just recalculate
it in a Query or the control source of a form or report control.

Re-entering all relevant fields does the trick, but I'm sure there
must be a simple piece of code to bypass the need for this.

The fields in question are as follows:

Unit [selected from combo box]
U Price [text box]
Prof [text box]
S Price [text box based on calcuation]
Qty [text box]
Pcd Price [text box based on calculation]

(yes, I know ... spaces in field names is not the best practice, but
the person who will be using this .mdb is accustomed to seeing them
this way!)

Here's my relevant code so far:

====
Private Sub Prof_AfterUpdate()
'Update the S Price Field'

Me.S_Price.Value = ((U_Price / Unit) * (100 + Prof) / 100)

End Sub

You can leave off the .Value property - it's the default. Doesn't hurt
to leave it on. I'd suggest using square brackets around the control
name though, rather than underscores - if the control is named
"S Price", use Me![S Price] = ...

Private Sub Qty_AfterUpdate()
'Update the Purchased Price field'

Me.Pcd_Price.Value = (U_Price / Unit) * Qty

End Sub
ok...

Private Sub Unit_AfterUpdate()
'Update the Unit field'

Me.Unit.Value = Me.Unit.Column(1)
End Sub
====

But this makes no sense. You're updating the combo box Unit to assign
it the value of combo box Unit! If the Bound Column property of the
Unit combobox is 2 (remember, the Column() property is zero based but
the Bound Column property isn't), the Value of Me.Unit ALREADY IS
equal to Me.Unit.Column(1).

What am I missing?!

Incidentally the Unit combo box Column count is 3 and Row source :

SELECT [tblMeasure].[Unit], [tblMeasure].

, [tblMeasure].[Note]
FROM tblMeasure;[/QUOTE]

So what you're trying to do is to set the value of Unit to the value
of Code... which makes no sense to me.
[QUOTE]
It is currently bound to Column 1 and it works!  But ideally, I'd like
the user to be able to do kbd alpha selection of Code (e.g. EA or C)
(rather than cycling through list with cursor key) and have this
"translate" (although I'm sure that is not the correct term!) into the
numerical Unit value (e.g. 1 or 100)[/QUOTE]

Simply set the ColumnWidths property to something like

0;.25;1.25

This will *store* the value of Unit, but display the value of Code,
and respond to keystrokes.
[QUOTE]
Again, I'm sure this must be doable, and I've tried a few ways, but
they don't give me the desired result!  One thing I haven't tried is
including the ID in the tbl query.  Could that be the key?![/QUOTE]

I'm sorry, I don't understand this at all. "the tbl query"?? what's
that?

 

[Hilary Ostrov]

Well... one thing to avoid is storing data redundantly. If you're
pulling data from one table via a combo box in order to store that
data in another table... STOP. It's not necessary (or very rarely
necessary) and should only be done if you have a GOOD reason (such as
wanting to grab the price as of the current moment to store in a
sale-price field, knowing that the item price may change tomorrow).

I appreciate your taking the time to respond, John. And I do
understand the principle of what you're saying.

Perhaps I should have explained. The individual who will use the .mdb
wants to be able to track (and compare the purchase price of)
approximately 200+ items he regularly uses, each of which is currently
categorized according to type, description and part number - and most
of which are purchased on as needed basis. Part numbers are sometimes
the actual Mfg Code and common between vendors and sometimes not; the
description field is the common denominator, but he selects by Code.

He wants to have a record for each purchase of each item - and be able
to make comparisons over time. He'll also be using it to determine
his costs for labour and materials, calculate selling price - and to
create quotations and invoices. The volume is not heavy (< 300
records of purchases for all of last year), he doesn't want or need to
be able to import any data into his accounting software. Someone else
already does that, using a copy of the paper invoices he produces, and
he's not interested in changing that routine at this time.

But he does want to be able to have info *he* needs at his fingertips
and be able to produce an invoice that doesn't look as though it was
created by a five year-old using a paint program!

It's a small business, and the owner is not looking for a complex,
comprehensive solution - I wouldn't even attempt that! However, I
thought that a relatively simple (easy to maintain) .mdb would be more
efficient than the direction towards which he was leaning.
Particularly, after I've finished cleaning up his existing data!

His initial vision was to do all of the above in Excel - and use an
awful lot of copy and paste to create each invoice - because there's
also the Customer data to take into consideration.

The application he currently uses costs him CDN $800 per annum - and
it doesn't do what he wants it to do! So, he's asked for an .mdb he
can use at a one-time cost of considerably less than that.

Bearing all of the above in mind, I am open to your suggestion as to
how I should be proceeding.

I did consider using one of the Wizards, but my past experience with
MS Wizards has been far from enchanting! And I also hoped this would
be a good learning experience for me!

Basically, DON'T store the calculated field AT ALL. Just recalculate
it in a Query or the control source of a form or report control.

Yes, but he wants each field stored in the record. Each record will be
added to an existing Materials table containing the history of his
purchases. I did try the split table route before starting this;
however, the result was less than ideal.

As an end-user, I've had to work with several Access apps (and ERP and
accounting packages) over the years that are quite annoying - and not
very forgiving - if one needs to modify data already entered in the
record one is working on. My goal on this very small project is to
ensure that WYSIWYG applies in all aspects of the user interface, and
to anticipate and avoid any such irritations, whenever possible

Re-entering all relevant fields does the trick, but I'm sure there
must be a simple piece of code to bypass the need for this.

The fields in question are as follows:

Unit [selected from combo box]
U Price [text box]
Prof [text box]
S Price [text box based on calcuation]
Qty [text box]
Pcd Price [text box based on calculation]

(yes, I know ... spaces in field names is not the best practice, but
the person who will be using this .mdb is accustomed to seeing them
this way!)

Here's my relevant code so far:

====
Private Sub Prof_AfterUpdate()
'Update the S Price Field'

Me.S_Price.Value = ((U_Price / Unit) * (100 + Prof) / 100)

End Sub

You can leave off the .Value property - it's the default. Doesn't hurt
to leave it on. I'd suggest using square brackets around the control
name though, rather than underscores - if the control is named
"S Price", use Me![S Price] = ...

Thank you. I shall change that. But just out of curiosity ... is
this a matter of convention or does it affect performance? Does this
apply only if preceded by an "Me" or by any procedure or function? And
should I be using ![Field Name] for all controls, even if they are
only one word? Or can single words continue to stand alone?

ok...

Whew! I did something right

But this makes no sense. You're updating the combo box Unit to assign
it the value of combo box Unit! If the Bound Column property of the
Unit combobox is 2 (remember, the Column() property is zero based but
the Bound Column property isn't), the Value of Me.Unit ALREADY IS
equal to Me.Unit.Column(1).

Well, to the extent that it gives me at least part of the result I'm
looking for, it does work; but thanks for the clarification. I
*think* I understand what you're saying! As I mentioned, I'm still
grappling with the syntax - and the meaning of the terms. For
example, I have no clue what "Me" actually *does* ... my ill-informed
guess was that it "translates" into something along the lines of "make
entry"!

What am I missing?!

Incidentally the Unit combo box Column count is 3 and Row source :

SELECT [tblMeasure].[Unit], [tblMeasure].

, [tblMeasure].[Note]
FROM tblMeasure;[/QUOTE]

So what you're trying to do is to set the value of Unit to the value
of Code... which makes no sense to me.[/QUOTE]

But it did give me part of the result I couldn't otherwise seem to
accomplish - and still can't - except by cycling through the list with
the cursor key.

How else can I display and store the numeric value of the unit after
selecting the alphabetical code?  And what, if anything, can I do to
avoid having to renter unchanged data into fields if I make a change
in only one field which affects the calculation result?
[QUOTE]
Simply set the ColumnWidths property to something like

0;.25;1.25

This will *store* the value of Unit, but display the value of Code,
and respond to keystrokes.[/QUOTE]

Yes, but I want to be able to select via keystroke a code that will
match what he's reading on the vendor's packing slip, so that after he
selects that code, the actual number will both store and display. It's
not a long list; however, different vendors use different codes for
the same numeric unit (depending on what's being measured!)
[QUOTE]
I'm sorry, I don't understand this at all. "the tbl query"?? what's
that?[/QUOTE]

Sorry, I meant to say tblMeasure selection, i.e. the Row Source from
which I selected the fields for the combo box.

Thanks for your help and your patience :)
hro

 

[John Vinson]

I appreciate your taking the time to respond, John.

Answers inline.

He wants to have a record for each purchase of each item - and be able
to make comparisons over time.

So you want to capture the current price... right?

He'll also be using it to determine
his costs for labour and materials, calculate selling price - and to
create quotations and invoices.

Quotations and invoices should be Reports; much or most of the
calculation can be done in the Report's recordsource query or directly
in the Report itself, and generally should not be stored in a table.

But he does want to be able to have info *he* needs at his fingertips
and be able to produce an invoice that doesn't look as though it was
created by a five year-old using a paint program!

It's a small business, and the owner is not looking for a complex,
comprehensive solution - I wouldn't even attempt that! However, I
thought that a relatively simple (easy to maintain) .mdb would be more
efficient than the direction towards which he was leaning.
Particularly, after I've finished cleaning up his existing data!

A very good thought.

His initial vision was to do all of the above in Excel - and use an
awful lot of copy and paste to create each invoice - because there's
also the Customer data to take into consideration.

Yours was a better idea, IMHO!

The application he currently uses costs him CDN $800 per annum - and
it doesn't do what he wants it to do! So, he's asked for an .mdb he
can use at a one-time cost of considerably less than that.

Bearing all of the above in mind, I am open to your suggestion as to
how I should be proceeding.

You're very much on the right track. Normalize your data; enter it
using user-friendly forms; use Queries and Reports to calculate.

I did consider using one of the Wizards, but my past experience with
MS Wizards has been far from enchanting! And I also hoped this would
be a good learning experience for me!

Yes, but he wants each field stored in the record. Each record will be
added to an existing Materials table containing the history of his
purchases. I did try the split table route before starting this;
however, the result was less than ideal.

Use the Form's BeforeUpdate event to "push" the calculated fields into
bound controls, then: e.g.

Me!txtTotalPrice = Me!txtCalcTotalPrice

As an end-user, I've had to work with several Access apps (and ERP and
accounting packages) over the years that are quite annoying - and not
very forgiving - if one needs to modify data already entered in the
record one is working on. My goal on this very small project is to
ensure that WYSIWYG applies in all aspects of the user interface, and
to anticipate and avoid any such irritations, whenever possible

Good on ya. Lot of work though!

The person should see Captions - generally not fieldnames, and they
should only look at table or query datasheets very rarely, and that
for debugging only!

Here's my relevant code so far:

====
Private Sub Prof_AfterUpdate()
'Update the S Price Field'

Me.S_Price.Value = ((U_Price / Unit) * (100 + Prof) / 100)

End Sub

You can leave off the .Value property - it's the default. Doesn't hurt
to leave it on. I'd suggest using square brackets around the control
name though, rather than underscores - if the control is named
"S Price", use Me![S Price] = ...

Thank you. I shall change that. But just out of curiosity ... is
this a matter of convention or does it affect performance? Does this
apply only if preceded by an "Me" or by any procedure or function? And
should I be using ![Field Name] for all controls, even if they are
only one word? Or can single words continue to stand alone?

It NEVER hurts to use brackets; it's not always necessary but it
doesn't do any harm, and I find that it makes the code easier to read
since it's clear that [S_Price] is an object or a field rather than a
VBA variable.

Well, to the extent that it gives me at least part of the result I'm
looking for, it does work; but thanks for the clarification. I
*think* I understand what you're saying! As I mentioned, I'm still
grappling with the syntax - and the meaning of the terms. For
example, I have no clue what "Me" actually *does* ... my ill-informed
guess was that it "translates" into something along the lines of "make
entry"!

Me! is purely and simply a shortcut for Forms!NameOfTheCurrentForm! so
you don't have to rewrite your code when you use it on another form.
It doesn't "make an entry", it's not an action, it's not a method;
it's just a handle onto which you can hang the name of a Form Control.

The way a Combo Box works is that it *pulls* data from its Rowsource -
typically a Query, though if sorting is irrelevant you can use a
Table; if the list of values is very short and static, you can use a
Value list, but ordinarily the Rowsource will be a Query. When the
user makes a selection from the combo the Bound Column of the selected
row will be stored into the Control Source field (if there is one;
unbound combos are very useful for other reasons such as finding
records).

But it did give me part of the result I couldn't otherwise seem to
accomplish - and still can't - except by cycling through the list with
the cursor key.

How else can I display and store the numeric value of the unit after
selecting the alphabetical code?

Set the Bound Column to the position of the numeric field; and use the
Combo's Column Widths property to set the width of the numeric field
to zero, so that the computer can see it but the user can't.

And what, if anything, can I do to
avoid having to renter unchanged data into fields if I make a change
in only one field which affects the calculation result?

Put code in the AfterUpdate event of each such control to Requery the
calculated control.

Yes, but I want to be able to select via keystroke a code that will
match what he's reading on the vendor's packing slip, so that after he
selects that code, the actual number will both store and display. It's
not a long list; however, different vendors use different codes for
the same numeric unit (depending on what's being measured!)

You'll need two controls then - a combo responds to keystrokes in its
first visible field, and also displays its first visible field. You
can have an unbound Textbox with =comboboxname.Column(x) to display
some other column of the combo.

Sorry, I meant to say tblMeasure selection, i.e. the Row Source from
which I selected the fields for the combo box.

If you're wanting to store the ID, then of course you must include the
ID in the rowsource!

 

[Hilary Ostrov]

[...]

He wants to have a record for each purchase of each item - and be able
to make comparisons over time.

So you want to capture the current price... right?

Exactly,every time!

Quotations and invoices should be Reports; much or most of the
calculation can be done in the Report's recordsource query or directly
in the Report itself, and generally should not be stored in a table.

Oh, I do know that much! And I intend to use Reports for Quotes and
Invoices (although, from an "esthetic" perspective, I am more partial
to working in Word docs than Access Reports!) It's just that right
now, I'm working on structuring the "input" so to speak, otherwise the
"output" won't be of much use to him!

<g> Fortunately the Access Report builder has some "mature" options...

LOL I know it does ... and I've used them in the past. But I've also
seen some so-called "customized" built-in reports that should be an
embarassment to any programmer who calls him/herself a professional!

A very good thought.

Glad you agree!

Yours was a better idea, IMHO!

Thanks, I thought so, too!

[...]

You're very much on the right track. Normalize your data; enter it
using user-friendly forms; use Queries and Reports to calculate.

That was my intention, and it is where I'm going! But I appreciate
the affirmation (particularly considering the drubbing you gave me
after my first post <g>)

Since I started writing this response (yesterday morning!), I've been
busy practicing what you've been preaching ... and I'm now getting the
picture!. The learning curve for me was slow at start ... but now I'm
jumping by leaps and bounds And you're absolutely right ... this is
a very powerful program, and it *really* does work! And I really like
those unbound text fields for displaying data I don't need to store!

One thing I am curious about, though ... in the process of
"Normalizing" the data, and using "make table queries" to do so, I
noticed - after the fact (but not too late to do any damage that I
could see) - that Access did not automatically set "AutoNumber" for
the Primary ID key.

Needless to say, when I was testing the form I received a bizarre
message to the effect that the index key field could not be null. I
knew the ID field was included in the form (hidden) and couldn't
understand at first why I was getting this message ... but by
eventually going back to the table properties, I found out why!

Obviously I wasn't looking closely enough when I initially set the
properties of the table fields. I subsequently went through the
process of inserting a new field as ID and then deleting my original
row.

But what I noticed is that while the previous numbering was in
sequence - and the data had been sorted alphabetically - prior to
making the change, the autonumber ID's were not in sequence! Doesn't
seem to have affected anything. But please alert me if this is likely
to create some problems that I'm not aware of! And I'll start that
one from scratch - again!

Many thanks, John, for all your other suggestions, too - all of which
(although snipped for brevity, here) I have definitely taken to heart
....and into my little program

[...]

hro

 

[John Vinson]

But what I noticed is that while the previous numbering was in
sequence - and the data had been sorted alphabetically - prior to
making the change, the autonumber ID's were not in sequence! Doesn't
seem to have affected anything. But please alert me if this is likely
to create some problems that I'm not aware of! And I'll start that
one from scratch - again!

It's very easy to get the impression that "Primary Key" and
"Autonumber" are synonymous terms... *but they aren't!* Autonumbers
are good "surrogate keys"; but if you have a "natural key" - some
identifier that you know will be stable, unique, and short - it's
often preferable to omit the autonumber altogether and use it. For
instance, a table of US States and Canadian provinces can use the
two-byte alphabetic abbreviation maintained by those countries' postal
services as a PK.

Also, tables HAVE NO ORDER. They'll be displayed in primary key order
as a convenience for the user, but you have NO guarantee that using
the table in some other context will keep that order. You will need to
use Queries, sorted by one or more fields, to display the data in a
particular sequence.