Using AfterUpdate in Access

[Guest]

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

 

[Jeff Boyce]

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)
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
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

 

[Rick Brandt]

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
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.

 

[Dirk Goldgar]

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.

You shouldn't have the equipment description and manufacturer in both
tables. From your description, there seems to be a one-to-many
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.

 

[Guest]

Dirk,

I agree that I do not want to have the same information in different places,
I just wasn't sure how to go about it.

I like option number one the best. However, I tried and I don't know if I
messed something up, but the equipment name stays blank on my form now, even
if I select something from the combo box. any ideas what I did wrong?

Thanks,
Megan

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.

You shouldn't have the equipment description and manufacturer in both
tables. From your description, there seems to be a one-to-many
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)

 

[Dirk Goldgar]

Dirk,

I agree that I do not want to have the same information in different
places, I just wasn't sure how to go about it.

I like option number one the best. However, I tried and I don't
know if I messed something up, but the equipment name stays blank on
my form now, even if I select something from the combo box. any
ideas what I did wrong?

Could you post the details (field names/types, indexes) of the tables,
and the SQL of the form's recordsource query?

 

[Guest]

Dirk,

Table:Equipment_Name
Field:Equipment_Name (Text, Yes (no duplicates))
Description (Text, No)
Manufacturer (Text, No)

Table:Equipment
Field:
Equipment_Serial_Number (Text, Yes (no duplicates))
EquipmentName (Text, No)
BarCode (Text, Yes (duplicates ok))
PurchaseDate (Date/Time, No)
PONumber (Text, No)
PurchaseAmount (Currency, No)
Active (Yes/No)

Is this what you were looking for? I'm sorry, I am not sure what the SQL of
the recordsource is. Where do I find that?

 

[Dirk Goldgar]

Dirk,

Table:Equipment_Name
Field:Equipment_Name (Text, Yes (no duplicates))
Description (Text, No)
Manufacturer (Text, No)

Table:Equipment
Field:
Equipment_Serial_Number (Text, Yes (no duplicates))
EquipmentName (Text, No)
BarCode (Text, Yes (duplicates ok))
PurchaseDate (Date/Time, No)
PONumber (Text, No)
PurchaseAmount (Currency, No)
Active (Yes/No)

Is this what you were looking for?

Yes. But you're going to have to either set Equipment.EquipmentName to
be Indexed (Duplicates OK), or else go into the relationship window and
define a one-to-many relationship between table Equipment_Name and table
Equipment, linking the fields Equipment_Name.Equipment_Name and
Equipment.EquipmentName.

I'm sorry, I am not sure what the
SQL of the recordsource is. Where do I find that?

Open the form in design view. Open the property sheet of the form. On
the Data tab of the property sheet, the Record Source property is
listed. It may be the name of a table, the name of a stored query, or
an SQL statement defining an "in-line" query. For our purposes, we need
to be either a stored query or an SQL statement. In either case, it's
the SQL of the query I want to see. If you made the query the way I
said in my original post, it may work if you just index the field in the
table or create a relationship as I mentioned in the previous paragraph.
If you didn't make a query the way I said, we need to make it now.

The SQL of the query should be something like

SELECT Equipment.*, [Equipment_Name].Description,
[Equipment_Name].Manufacturer
FROM Equipment LEFT JOIN [Equipment_Name]
ON Equipment.EquipmentName = [Equipment_Name].[Equipment_Name];