AfterUpdate

[Guest]

I asked this question a few days ago and when I entered the AfterUpdate
information it didn't deliver the results I am looking for. I am working in
Access 2003, and my database has a table with two fields, Part Number and
Part description. I have a combo box with the part numbers, and when I
select the correct part number I need the part description to automatically
pop up in the next box. I've tried using the code I was given for
AfterUpdate, and I also tried one I found in the Knowledge Base, but neither
one works. When I use the dropdown arrow I can see all the part numbers in
one column and the part description in the other column, but can't get the
description to show in the other box. I have been struggling with this for
at least a week and a half and I am getting extremely frustrated, which
doesn't help matters! I have another two boxes that will have to accomplish
the same result, so it is extremely important that I get this figured out. I
know it's no doubt something I'm doing (or not doing), but I can't figure it
out. Please walk me through step-by-step and hopefully I can get it right
this time. Do I need to create a query as well as the the event procedure in
AfterUpdate? Help!!

Owl Lover

 

[Guest]

In the AfterUpdate event procedure for your combo box, try this:

Me.OtherFieldName = Me.ComboName.Column(1)

Where '1' is the column you want to return, in this case it is the second
column since '0' would be the first (PartNo)...
If you are still having trouble, post your AfterUpdate code so we can have a
look at what you are doing...

Steve

 

[UpRider]

OL, in the After_Update event for the combobox, it should be as simple as:
txtBoxDesc = cboPN.column(x)

To see what selections are available, add these 2 lines of code just after
the above line:
debug.print cboPN.column(0)
debug.print cboPN.column(1)

These 2 lines will display the first 2 individual fields in the combobox
after a selection is made. The display is in the Immediate Window of the
Code Editor. (CTRL-G) . Use the view menu to see the immediate window. If
one of them is correct, fix the code to match it in your txtBoxDesc line of
code above. Then you can delete the debug.prints.

You will, of course, have to change this code to match your control names.

If this does not work, please post the row source for your combobox and
verify the fact that you want to display the description on the same form as
the combobox is on.

Also post any additional code you have in the combobox after_update event
and any code in the form's on current event.

HTH, UpRider

 

[John W. Vinson]

I asked this question a few days ago and when I entered the AfterUpdate
information it didn't deliver the results I am looking for. I am working in
Access 2003, and my database has a table with two fields, Part Number and
Part description. I have a combo box with the part numbers, and when I
select the correct part number I need the part description to automatically
pop up in the next box. I've tried using the code I was given for
AfterUpdate, and I also tried one I found in the Knowledge Base, but neither
one works. When I use the dropdown arrow I can see all the part numbers in
one column and the part description in the other column, but can't get the
description to show in the other box.

You don't need the AfterUpdate event, or any other code at all.

Simply set the Control Source of the textbox to

=comboboxname.Column(1)

This will display the contents of the second field (it's zero based) in the
combo box in the textbox.

If you're trying to *store* the description in this form's table... don't.
It's redundant. The description should be stored only in the Parts table.

John W. Vinson [MVP]

 

[Guest]

Thank you so much for your help!! Now I seem to have another problem. When
I select the part number from the dropdown list the part description textbox
is automatically populated, as it should be. However, when I tab out of the
part number to the next field, the part number and part description change to
something totally different. The Part Number and Part Description need to
stay as I selected them until I clear the form or enter another part number.
Is there something I'm supposed to put in OnExit or what? Again, thank you
for your help!

Owllvr

 

[John W. Vinson]

Thank you so much for your help!! Now I seem to have another problem. When
I select the part number from the dropdown list the part description textbox
is automatically populated, as it should be. However, when I tab out of the
part number to the next field, the part number and part description change to
something totally different. The Part Number and Part Description need to
stay as I selected them until I clear the form or enter another part number.
Is there something I'm supposed to put in OnExit or what? Again, thank you
for your help!

What are the Control Source properties of these two controls? What is the
Recordsource property of the form? Bear in mind - there is *no* data stored in
your form; it's just a window onto the table.

John W. Vinson [MVP]

 

[Guest]

Thank you so much for responding to my problem. I worked on it last night
and was able to fix it. But I do have a question about calculations. I am
working in Access 2000-2003, and I have some textboxes that require
calculations such as total quantities, total hours, total charges, etc. Can
you tell me what the easiest way would be to accomplish these totals? It's
been so many years since I've worked in Access, I feel like I'm having to
learn everything over again. Any help you can give me would be very much
appreciated.

 

[John W. Vinson]

Thank you so much for responding to my problem. I worked on it last night
and was able to fix it. But I do have a question about calculations. I am
working in Access 2000-2003, and I have some textboxes that require
calculations such as total quantities, total hours, total charges, etc. Can
you tell me what the easiest way would be to accomplish these totals?

It just depends on what you want to do with the calculated results. There are
several ways to do so.

One thing you should NOT do is to store the totals in any table, anywhere,
except in very restricted circumstances. 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.

The most common way to display totals on a Form or Report is to use the Form
Footer, Report Footer (not the page footer but the report footer), or - on a
Report - the footer section of a Sorting and Grouping group. You can put
textboxes in any of these with a control source

=Sum([fieldname])

to sum all of the values of that field in the Form (or report, or report
section).

A Totals query can also do sums for you; if you just want the sum and not the
details, you can base a (non-editable) Form or a Report on such a query.

If you've got some specific problems post back with the details.

John W. Vinson [MVP]

 

[Guest]

Thank you for the information. I wasn't sure if you would see my response,
so I posted the same question under calcluated textboxes again just a few
minutes ago. Someone else responded telling me the same thing as you. As I
just told them, unfortunately, the company I am building this database for
requires the totals to be used in various reports they do on a weekly,
monthly, quarterly and yearly basis. This whole database was originally in
Lotus Approach, and they have recently moved into Access and hired me to
recreate the Approach database in Access. Needless to say, this has been
somewhat of a nightmare for me since I knew nothing about Approach, and it
has been 5 years since I have worked with Access. I'm trying very hard to
keep everything simple, but things like this keep popping up! When I exported
the tables into Excel from Approach, they came into Excel as calculations.
Before I could import them into Access tables I had to take
out the calculations, which left the totals only, which was fine. I thought
there would be no problem setting the new tables up with the future totals in
Access. Do you have any suggestions as to what I can do to accomplish this
monumental task!!? Any help you can give me will be much appreciated.

Thank you so much for responding to my problem. I worked on it last night
and was able to fix it. But I do have a question about calculations. I am
working in Access 2000-2003, and I have some textboxes that require
calculations such as total quantities, total hours, total charges, etc. Can
you tell me what the easiest way would be to accomplish these totals?

It just depends on what you want to do with the calculated results. There are
several ways to do so.

One thing you should NOT do is to store the totals in any table, anywhere,
except in very restricted circumstances. 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.

The most common way to display totals on a Form or Report is to use the Form
Footer, Report Footer (not the page footer but the report footer), or - on a
Report - the footer section of a Sorting and Grouping group. You can put
textboxes in any of these with a control source

=Sum([fieldname])

to sum all of the values of that field in the Form (or report, or report
section).

A Totals query can also do sums for you; if you just want the sum and not the
details, you can base a (non-editable) Form or a Report on such a query.

If you've got some specific problems post back with the details.

John W. Vinson [MVP]

 

[John W. Vinson]

When I exported
the tables into Excel from Approach, they came into Excel as calculations.
Before I could import them into Access tables I had to take
out the calculations, which left the totals only, which was fine. I thought
there would be no problem setting the new tables up with the future totals in
Access. Do you have any suggestions as to what I can do to accomplish this
monumental task!!? Any help you can give me will be much appreciated.

If you can do the calculations in Excel, you can do the calculations (in a
Query) in Access. The problem with storing calculated results is - again -
that it is a REAL and MAJOR risk of having data in your table which is
*wrong*. Is that OK for your bosses - to have data on their reports which has
no guarantee of accurately reflecting the values that went to make it up?
After all, once a value is stored in a table, it can be edited; or the
underlying values in the table could be edited, deleted, or added to without
the change being reflected in the stored sum.

You can *do* it, with an update or append query, or with some code - but it's
still a Very Bad Idea and it's NOT necessary.

John W. Vinson [MVP]