Using AfterUpdate in Access

Hi,

I am VERY new to Access and trying to do something way beyond my skills.
Can anyone help me out with the Afterupdate event?

I have a table called equipment_name that has separate columns for the
equipment name, description, and manufacturer. I have another table called
equipment that has this list, plus other information about specific pieces of
equipment (the serial number is the unique identifier).

I would like to have a form for the equipment that when you enter in the
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/14066-using-afterupdate-in-access.html
equipment name, the description and manufacturer automatically fill in. That
way mistakes will (hopefully) be limited. If I can "fix" those so they
cannot be changed as well, that would be good.

Thanks,
Megan
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14066

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

Megan

First, you may not need two tables with the same data (equipment name,
description, manufacturer). This is what you'd do in a spreadsheet, but it
is counter-productive in an Access database.

Next, if you want folks to ONLY pick from a list of available items (e.g.,
equipment), use a combo box on your form. You can limit the combo box to
what is available in its source list, and you can use a query to find all
the items that comprise that source list.

If you want the form to display additional data (like description,
manufacturer) after a particular piece has been selected, one way to do this
is to add something like the following in the AfterUpdate event of the combo
box:

Me.txtDescription = Me.cboYourEquipmentControlName.Column(1)
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14066
Me.txtManufacturer = Me.cboYourEquipmentControlName.Column(2)

Several things to note:
1) use YOUR controls' names, not the ones I used in the example
2) the .Column(n) syntax is "zero-based", and refers to the columns in the
query that serves as the source for the combo box list ... sort of! You
have to start counting columns at "0", "1", "2", ...
3) you do not have to and SHOULD NOT bind the txtDescription and
txtManufacturer controls to those fields in the table. These two controls
are for display purposes only, to remind the user what those values are for
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14066
the Equipment they've selected. Again, as a relational database, there's no
need to store something like description and manufacturer more than one
time.

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Megan" wrote in message
news:A652F887-F485-4E97-9794-9B299781B74F@microsoft.com...
> Hi,
>
> I am VERY new to Access and trying to do something way beyond my skills.
> Can anyone help me out with the Afterupdate event?
>
> I have a table called equipment_name that has separate columns for the
> equipment name, description, and manufacturer. I have another table
> called
> equipment that has this list, plus other information about specific pieces
> of
> equipment (the serial number is the unique identifier).
>
> I would like to have a form for the equipment that when you enter in the
> equipment name, the description and manufacturer automatically fill in.
> That
> way mistakes will (hopefully) be limited. If I can "fix" those so they
> cannot be changed as well, that would be good.
>
> Thanks,
> Megan

Megan wrote:
> Hi,
>
> I am VERY new to Access and trying to do something way beyond my
> skills.
> Can anyone help me out with the Afterupdate event?
>
> I have a table called equipment_name that has separate columns for the
> equipment name, description, and manufacturer. I have another table
> called equipment that has this list, plus other information about
> specific pieces of equipment (the serial number is the unique
> identifier).
>
> I would like to have a form for the equipment that when you enter in
> the equipment name, the description and manufacturer automatically
> fill in. That way mistakes will (hopefully) be limited. If I can
> "fix" those so they cannot be changed as well, that would be good.
>
> Thanks,
> Megan

Yes you are very new. The proper way to do this is to NOT copy any data
between related tables except for the key field(s) that they share. In your
case you would ONLY copy [equipment name] and use any of various lookup
mechanisms to DISPLAY the other desired fields from that table on your
forms, reports or queries.

By having description and manufacturer stored in exactly one place entered
exactly one time you are guaranteed that it is always correct. Also if you
later change either of those values then you only change it in that one
place and the change APPEARS everywhere in your app because you are looking
it up relationally rather than copying it all over the place.

A common way to achieve what you are doing is to use a ComboBox for the
entry of the [equipment name] field on your form and have two additional
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14066
hidden columns in the ComboBox that contain the other two fields. Then on
your form you have two additional TextBoxes with ControlSources like...

=ComboBoxName.Column(1)

=ComboBoxName.Column(2)

That will cause those two TextBoxes to display the values from the two
hidden columns, but the values are not copied and not saved to the table
that the form is bound to. You should in fact remove those fields from the
second table. For queries and reports you simply build your query by
joining both tables so you can grab the other fields form the one source
table.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14066
RBrandt at Hunter dot com

"Megan" wrote in message
news:A652F887-F485-4E97-9794-9B299781B74F@microsoft.com
> Hi,
>
> I am VERY new to Access and trying to do something way beyond my
> skills.
> Can anyone help me out with the Afterupdate event?
>
> I have a table called equipment_name that has separate columns for the
> equipment name, description, and manufacturer. I have another table
> called equipment that has this list, plus other information about
> specific pieces of equipment (the serial number is the unique
> identifier).
>
> I would like to have a form for the equipment that when you enter in
> the equipment name, the description and manufacturer automatically
> fill in. That way mistakes will (hopefully) be limited. If I can
> "fix" those so they cannot be changed as well, that would be good.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14066

You shouldn't have the equipment description and manufacturer in both
tables. From your description, there seems to be a one-to-many
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14066
relationship between [equipment_name] and [equipment] -- for each record
in [equipment_name], there may be many records in [equipment] that have
that same information. So all you should store in [equipment] is the
primary key value of the related [equipment_name] record. (On a form,
you can use a combo box based on [equipment_name] to look this up.) You
don't need an AfterUpdate event to look up the various fields from the
[equipment_name] table and stuff them into corresponding fields in the
[equipment] record, because such corresponding fields don't even exist.
This is the best way to avoid mistakes and discrepancies in such
relationships.

But you probably still want to show those fields on your Equipment form,
so the user can see what they are. There are two ways to go about this:

(1) Use an "autolookup query".
Base the form on a query that joins the two tables on the key field that
relates them. Have the query include all the fields from [equipment],
and those fields from [equipment_name] that you want to show, and put
all those fields on your form. As soon as you choose a the equipment
name for a particular equipment record, the description and manufacturer
will automatically fill in.

Note that these fields will (normally) be updatable, and if you update
them on the form for one record, you're updating them for all records
with that equipment_name. That's because you're actually modifying the
equipment_name table in that case. If you don't want them to be
updatable, set the text boxes' Locked property.

(2) Pull data from the combo box.
Alternatively, you can base the form solely on the [equipment] table,
but collect the extra informatin from the [equipment_name] table in the
combo box. Let the combo box have a rowsource that includes the extra
columns from the table, and then have calculated text boxes on the form
that use controlsource expressions to pull the description and
manufacturer from the Column property of the combo box.

So, for example, if your [equipment_name] table has fields like this:

EqName (primary key)
Description
Manufacturer

.... then you'd set your combo box (maybe named "cboEQName") with these
properties:

Row Source:
SELECT EqName, Description, ManufacturerFROM [equipment_name];
Column Count: 3

.... and you'd have text boxes on your form with controlsources like
these:

=[cboEQName].[Column](1)

=[cboEQName].[Column](2)

Note that .Column(1) is actually the second column in the combo box,
because the columns are numbered starting from 0.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)