Update a field automatically

[Guest]

Hi
I have a table of employee information and a form based on this table for
user to input.
I have a field for staff grade and I want, when this is selected from a drop
down list , another field to be filled with the salary amount for that grade
automatically and to be saved to the table.
i have guessed I need a second table of two columns for grade and salary
rate but I am not sure if code is needed to perform the update or where to
put it. Beginner here so any tips to get this working would be appreciated
Thanks

 

[Rick Brandt]

Hi
I have a table of employee information and a form based on this table for
user to input.
I have a field for staff grade and I want, when this is selected from a drop
down list , another field to be filled with the salary amount for that grade
automatically and to be saved to the table.
i have guessed I need a second table of two columns for grade and salary
rate but I am not sure if code is needed to perform the update or where to
put it. Beginner here so any tips to get this working would be appreciated
Thanks

Yes, you need the table and you need some code, but not much.

Once you have your table with [StaffGrade] and [Salary] you use that as the
RowSource for your ComboBox. You set the ColumnCount of the ComboBox to (2) and
set the BoundColumn to (1). The Rowsource should list the [StaffGrade] field
first and you set your column widths to something like 1";0" so that the second
column is hidden.

Now, in the AfterUpdate event of the ComboBox you run code that puts the value
of the second column into another bound control on your form (Salary),

Me.Salary = Me.StaffGrade.Column(1)

Column numbering is zero-indexed so that Column(1) is actually the second
column.

You should see that as you make selections for StaffGrade the appropriate Salary
value automatically appears in the Salary control.

 

[Guest]

Hi, Kim.

Yes, your instinct is right. Take advantage of Access being a relational
database.

Two principles in database design are:

1. A table should consist of fields that are descriptors of one thing. For
example, Name, Address, City, State, Phone, BirthDate, NumberofChildren are
all properties of people.

2. Avoid storing data redundantly. Have the data appear once in your
database, then when you update it, it is correct everywhere. For example, in
an Orders table, don't try to store the name, address, etc. of the customer,
simply store the primary key of the Customers table. Then you can create a
query that joins the two tables in order to display all desired fields from
both on a form, or print on a report.

If all salaries for people of a given grade are the same, then a person's
salary doesn't truly relate to them particularly, it relates to their grade.
That's a clue that you need a Grade table, which you intuited. It need only
have an autonumber primary key (or a numeric key which represents the grade),
a description if you need one, and the salary associated with the grade.

On your data entry form, simply put a combo box for the grade that gets its
records from the Grade table.

HTH
Sprinks

 

[Steve Schapel]

Kim,

Yes and No.

Yes, make a table with the grade and salary rate, and use this as the
Row Source of the Grade combobox on your form.
Instead of basing your form on the table, base it on a query which
includes both the original table, and your new salary rates table,
joined on the grade field. Then, you can include the salary rate field
into the query, and put it on the form. Set the Enabled property of the
Salary rate textbox on the form to No, and its Locked property to Yes.
When you select a grade from the combobox, the corresponding salary will
be automatically shown.

No, to saving this in the table. If I understand you correctly, this is
not a valid thing to do, from the point of view of database design
principles. The salary should not be stored in the employee table. You
have the grade, and the grade is associated with a salary in the salary
rates table, so the salary for any/each employee is easily derivable via
a query.