Getting a value from one table to another

  • Thread starter Thread starter JeffH
  • Start date Start date
J

JeffH

I have an employee table that has an employee number,
name, payrate, etc..

I have labor table that has the labor that was done for
jobs. Which contains the employee number, job number,
number of hours, wage.

Since an employee payrate can change many times a years.
When i'm entering the information in the labor table I
would like the current payrate to be placed in the wage
field.

How can this be done?


Thank You...
 
Hello Jeff

I hope you are using a form to enter this data - not typing it directly into
a table!

In this case, you would be best to use a combo box to select the employee.
Your combo box's RowSource needs three columns:
1. EmployeeNumber (to store in the Labor table record)
2. EmployeeName (to display for selection by the user)
3. PayRate (to store in the Wage field)

Set its properties as follows:
ControlSource: Employee Number (where to store the selected value)
BoundColumn: 1 (the column containing the selected value)
Columns: 3
ColumnWidths: 0;;0 (first and third columns invisible)

Finally, add an AfterUpdate event procedure to your combobox:

Private Sub ComboboxName_AfterUpdate()
Me.Wage = ComboboxName.Column(2)
End Sub

This will copy the value from the third column (PayRate) into the Wage field
when an employee is selected. Note that it is Column(2) because the Column
property starts counting from zero.
 
Thanks Graham for your help.

Yes, I'm currently entering the information in a form.

I'm getting the results I know I have something wrong but
I not sure what. I can kinda follow what you asking the
system to do. But its not happening.

I wish I could send you my form, but I know from the past
most people don't like that idea. So try to tell you
what I did and have.

The form record source is the labor per job, on this form
the data entry person enters in the following: Date,
Employee#, Job#, Operation#, Hours worked.

When this form opens it shows the last entry so they
don't have to enter duplicate information over and over.

The employee# is a combobox which has a Row Source
(Employee table with 3 items listed: Employee#,Last Name,
Regrate). I put in your information in AfterUpdate.

Private Sub EmployNo_AfterUpdate()
Me.Wages = EmployNo.Column(2)
End Sub

This Combo Box has Event On Exit which is Call
GetTotalHours, Which show the total on number of hours in
a text box for that person.

The properties I could set was the controlSource, Bound
Column and column width. Columns was what the Column
Count? If it was then if I 3 in there I would show the
employee name, I need to show Number.

I'm not sure what else I need to tell you. I currently
have the Wages on the screen so I can see if it changes,
which so far it hasn't. After typing this all in I can
see that there is alot going on before I even asked how
to this.

Thanks for you help!!!

Jeff
-----Original Message-----
Hello Jeff

I hope you are using a form to enter this data - not typing it directly into
a table!

In this case, you would be best to use a combo box to select the employee.
Your combo box's RowSource needs three columns:
1. EmployeeNumber (to store in the Labor table record)
2. EmployeeName (to display for selection by the user)
3. PayRate (to store in the Wage field)

Set its properties as follows:
ControlSource: Employee Number (where to store the selected value)
BoundColumn: 1 (the column containing the selected value)
Columns: 3
ColumnWidths: 0;;0 (first and third columns invisible)

Finally, add an AfterUpdate event procedure to your combobox:

Private Sub ComboboxName_AfterUpdate()
Me.Wage = ComboboxName.Column(2)
End Sub

This will copy the value from the third column (PayRate) into the Wage field
when an employee is selected. Note that it is Column(2) because the Column
property starts counting from zero.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

JeffH said:
I have an employee table that has an employee number,
name, payrate, etc..

I have labor table that has the labor that was done for
jobs. Which contains the employee number, job number,
number of hours, wage.

Since an employee payrate can change many times a years.
When i'm entering the information in the labor table I
would like the current payrate to be placed in the wage
field.

How can this be done?


Thank You...
Click to expand...


.
Click to expand...
 
Hi again Jeff
Private Sub EmployNo_AfterUpdate()
Me.Wages = EmployNo.Column(2)
End Sub
Click to expand...

Is this code actually running? I suggest you set a breakpoint on the "End
Sub" line above and run the form. It should stop at that line. If it
doesn't then check that the AfterUpdate property for the combo box is [Event
Procedure].

If the code does run, then examine the value of EmployNo.Column(2), to
ensure that it contains the required rate. If it doesn't, then double check
your combo RowSource to check it has three columns and the wage rate is in
the third.
The properties I could set was the controlSource, Bound
Column and column width. Columns was what the Column
Count? If it was then if I 3 in there I would show the
employee name, I need to show Number.
Click to expand...

ColumnCount (not Columns, sorry!) should be 3. The column which is
displayed when the combo is not dropped down is the first one with a
non-zero ColumnWidth.
 
Thanks for all your help.

I havn't goten it to work yet but I think I created a
larger problem. By adding that addition field in that
table, the database went from 20mb to 180mb which caused
a major delay.

So I'm looking at how to export data of jobs that have
been completed into a seperate database to make the
active database smaller.

Thanks for your help I will get back to it soon...

Thanks again!

Jeff
-----Original Message-----
Hi again Jeff
Private Sub EmployNo_AfterUpdate()
Me.Wages = EmployNo.Column(2)
End Sub
Click to expand...

Is this code actually running? I suggest you set a breakpoint on the "End
Sub" line above and run the form. It should stop at that line. If it
doesn't then check that the AfterUpdate property for the combo box is [Event
Procedure].

If the code does run, then examine the value of EmployNo.Column(2), to
ensure that it contains the required rate. If it doesn't, then double check
your combo RowSource to check it has three columns and the wage rate is in
the third.
The properties I could set was the controlSource, Bound
Column and column width. Columns was what the Column
Count? If it was then if I 3 in there I would show the
employee name, I need to show Number.
Click to expand...

ColumnCount (not Columns, sorry!) should be 3. The column which is
displayed when the combo is not dropped down is the first one with a
non-zero ColumnWidth.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


.
Click to expand...
 
Thanks for all your help.

I havn't goten it to work yet but I think I created a
larger problem. By adding that addition field in that
table, the database went from 20mb to 180mb which caused
a major delay.

So I'm looking at how to export data of jobs that have
been completed into a seperate database to make the
active database smaller.

Thanks for your help I will get back to it soon...

Thanks again!

Jeff
-----Original Message-----
Hi again Jeff
Private Sub EmployNo_AfterUpdate()
Me.Wages = EmployNo.Column(2)
End Sub
Click to expand...

Is this code actually running? I suggest you set a breakpoint on the "End
Sub" line above and run the form. It should stop at that line. If it
doesn't then check that the AfterUpdate property for the combo box is [Event
Procedure].

If the code does run, then examine the value of EmployNo.Column(2), to
ensure that it contains the required rate. If it doesn't, then double check
your combo RowSource to check it has three columns and the wage rate is in
the third.
The properties I could set was the controlSource, Bound
Column and column width. Columns was what the Column
Count? If it was then if I 3 in there I would show the
employee name, I need to show Number.
Click to expand...

ColumnCount (not Columns, sorry!) should be 3. The column which is
displayed when the combo is not dropped down is the first one with a
non-zero ColumnWidth.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


.
Click to expand...
 
Back
Top