Default Value Question

benallen002

Board Regular
Joined
Feb 16, 2005
Messages
65
Hi all...after swimming around in the deep end of Excel for a while now, I'm just starting to get my feet wet with Access and had a question for the board. Forgive me if it seems elementary:

I have an Assignments table that stores Date, EmployeeID, ClassID, PayRate, Location, and a few other pieces of info per assignment. I have built a form for this table and what I would like to do is have the default values for anything related to the Employee (PayRate, ClassID, etc) assigned automatically when the user selects an Employee for the task. In other words, I have the employee's default information in the Employees table, and I want my form to look that information up for use as the Default Values when that Employee gets selected for an Assignment. That way if his default values don't change, the user doesn't have to enter them again everytime they are given a new Assignment.

I hope my explanation of the problem makes sense, and appreciate any help on the matter.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
You really shouldn't be doing this.:)

You should have at least 2 seperate tables - employees and assignments.

The assignments table should have a field that is a foreign key that identifies the employee associated with the assignment.

If more than one employee can be involved in an assignment, and an employee can be involved in one assignment you would need a third table, say EmployeeAssignments.

This table would have 2 fields, again foreign keys, that identify the assignment and the employee.

Oops, just noticed you do have 2 tables.:oops:

Ignore my first comment, but the rest should still apply.
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
The question here is why add any of this default data to the record, if it is employee dependent? Is this data that can actually be changed with every assignment? If this is the case, then I'm assuming that when the employee is selected for this new assignment, these values will be written into the same record as the selected employee ID. The following is based on these assumptions.

These default values, I am further assuming are in the Employee table. It would be a query over the Employee table that would be used as the Row Source for the combo box that you would select the employee from. Therefore, in the query for this combo box, include all these additional default values. So you would have as fields in this query: EmplID, EmployeeName, PayRate, ClassID (etc.). The combo box would be bound to column1 (EmplID), but it would be hidden from view. The employee name would be the visible field in the combo box. The PayRate and ClassID and (etc.) would also be hidden from view. Using the AfterUpdate event for the EmplID combo box, you can update the default values that are associated with the selected employee from the additional columns in this combo box.
The properties for the combo box would be:
(Format Tab):
Column Count: 4 (plus the etc. columns)
Column Widths: 0, , 0, 0 (each 0 is for the EmplID, PayRate, and ClassID)
Each zero here means that this column would not be visible in the combo box.
The AfterUpdate event code would look something like this:
Code:
Me.PayRate = Me.EmplID.Column(2)     '(Column numbers start with 0)
Me.ClassID = Me.EmplID.Column(3)
etc.
HTH (and is understandable),
 

benallen002

Board Regular
Joined
Feb 16, 2005
Messages
65
Thanks for the quick responses guys. I probably should have clarified things a little more.

1. The AssignmentID is an Autonumber Field that is the Primary Key in the Assignments table. Only one Employee can be assigned to each assignment.

2. What I want to be able to do is this:

Employee John Doe normally works 8 hours a day at a rate of $10/hr. Most of the time when he is assigned to a task, that is what is inputed into the HoursWorked and PayRate fields of that Assignment. But sometimes John Doe works 10 hours at a different pay rate. In my Assignment form, I would like the 8 and $10 to automatically be in the TextBoxes when I select John Doe, then if I need to change them for that particular assignment, I can. If it his normal type of assignment, I don't have to re-input his normal values, they are already in there for me. And this shouldn't update his default values in the Employee table because they are only for that particular AssignmentID.

Still make sense?

Also, I could be thinking of this from the wrong vantage point; as I said, I'm an Excel buff. So please don't be shy about telling me I'm doing it all wrong.
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032

ADVERTISEMENT

Go back and re-read my reply. It is assuming just what your last reply is saying.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
I don't actually see the connection between the changing rates and the employee.

Yes an employee can have a standard/default rate but what you appear to be saying here is that the rate may change dependent on the assigment.

If that's the case then the rate is for the assignment rather than the employee.

Well that's my (mis?) interpretation anyways.:)
 

benallen002

Board Regular
Joined
Feb 16, 2005
Messages
65
You were right Vic, it just took me two times to read the reply until it made sense. I punched in the code (I had the query structure already right) in the EventHandler and it worked like a charm. I appreciate the help.

Sorry for the confusion Norie.
 

Forum statistics

Threads
1,136,517
Messages
5,676,319
Members
419,619
Latest member
jalme

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top