Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Using AfterUpdate in Access

Discussion in 'Information Technology' started by Megan, Jul 28, 2006.

  1. Megan

    Megan
    Expand Collapse
    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
     
  2. Loading...


  3. Jeff Boyce

    Jeff Boyce
    Expand Collapse
    Guest

    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


    "Megan" <Megan@discussions.microsoft.com> 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
     
  4. Rick Brandt

    Rick Brandt
    Expand Collapse
    Guest

    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
    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...
    RBrandt at Hunter dot com
     
  5. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "Megan" <Megan@discussions.microsoft.com> 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.


    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)
     

Share This Page