Autofill field in form

[TheHarleygirl2005]

I am trying to autofill a field based on another field but am not sure the best way to accompish this. I have a database that includes an employee table which has an Employee ID Number (EIN), name, address, etc. All my employees are listed in that table (full time as well as temps).



I also have an attendance table, which i have created a form to input the information. When a full time employee is off, they are covered by a temp. So, the form includes a field for the guard who is off, how many shifts they are off and which temp employee worked for the full time employee and for how many shifts. My payroll department needs the employee ID added to each employee (full time and temp) on the payroll report.

My Report is formated by Guard Off with a start date and end date to coincide with our pay periods. Multiple people enter info and then a bi-weekly report is sent to payroll. The report lists the employee off and then sorts by date and lists the employee working that date and shifts worked. (I hope that makes sense)

Here is my problem. All the employee information is in one table. What I would like is when a name is selected on the form for Employee Off from a list box the EIN is automatically added to the EmployeeEIN field in the attendance table(the name in the list box is pulled from the Employee table). I also need the same for the Temp employee. When a name is selected from a list box for the temporary employee the TempEIN is automatically selected from the same Employee table based on the name selected.

Is this possible? I can go into relationships and link the name in the Employee table with one name in the Attendance table, but not both.

Any help would be great and I hope I provided enough info to clarify the issue.

[June7]

These listboxes are on a form bound to what data source - the Attendance table?

If the listbox is set up as multi-column then the EIN number can be in one of the columns (it can be hidden while the name is displayed) and the listbox can be bound to the field where you want the EIN saved. Set the EIN column as the BoundColumn and the EIN will be saved to the field.

[TheHarleygirl2005]

I have two list boxes on my form. The first list box gets info from a query that filters only full time employees. The second source is a query that lists all employees because a full time employee can fill in for another employee if they are on different shifts. I have approximately 300 employees so the list box is set up to display names, however I could change it to display names and EIN's. Once you select a name it is bound to the Employee field or the Employee working field.

As far as I am aware, I can't bind a text box to two seperate fields. What I need is when a name is selected from the list box it puts the name in the Employee field and the EIN in a different field. Is this possible? Obviously my supervisors are not going to know 300 different EIN's to select from a box so I am just trying to make it so they select a name from a list and the name goes in one field and the EIN is automatically added to the EIN field.

[June7]

The EIN doesn't have to even show in the listbox. User's make selection by name. The EIN is saved to field. Why save name if the EIN is saved? Saving the name is redundant.

[TheHarleygirl2005]

My payroll department needs a report identifying those people who took time off and who worked that shift over a two week period. My report is sorted by Full Time employees that took time off. In that grouping it shows what date they were off and who worked for them. All the information for the report is pulled from the Attendance table which is populated using the Attendance form. I have to have the name and the EIN entered into the form because my payroll department requires both. I previously created a form that enables the supervisor to select a name from a list box for the employee that took time off. Then they can select a name from another list box identifying who worked that shift. I created a relationship tying the employee name from my employee table to the employee off in my attendance table which pulls the EIN for my full time employees. My payroll department is now saying they need the EIN listed for the employee working as well. The EIN does not need to show on the form, it just needs to be entered into the table when a specific employee is selected from the list box.

[June7]

Relationships should be based on the EIN, not name, then retrieve the related employee name by joining tables in query. That's the power of relational database. No need to duplicate data.

If you absolutely must save both that will require code as the control can be bound to only one field. The trick is figuring out what event to put code in. Possibly the AfterUpdate event of the listbox. I use only VBA, not macros. Assuming the listbox is multi-column and EIN is in second column and since column index begins with 0:

Me!EIN = Me.listbox.Column(1)

[TheHarleygirl2005]

Thanks for your help on this. I'm going to work on a couple things and try creating a new query to accomplish what I need. I was hoping I wouldn't have to do that, but at this point I'm ready to try anything.

[TheHarleygirl2005]

I am getting closer, but still not there. I changed the list box to a combo box which binds the EIN to the attendance table. That creates a table with EIN's only (no names). I then had to create two queries. One joins the EIN from my employee table to the guard off in my attendance table (which adds the employee's name). I created a similar table to bring the Sub's name into a query. When I run the queries they work how I want them to. The EIN number matches the correct name. (see below)


The problem I am now having is when I try to create a report based on both queries, it seems to be confusing the system. I get a report that has over 5000 entries and it changes my data so the names and EIN's no longer match and there are several entries for one entry. I tried to create a third query to bring all the data into one place, but that didn't work either. I can create a report based on one query and it gives me everything I need except the names to coincide with the EIN's (see below). All I need on this report is for the Guard working name to be added with the EIN.


I'm sure I am missing something simple, but this project is driving me nuts!

[June7]

You have the ID autonumber field in Employ as the primary key but you want to save the EIN as foreign key? I suspect that the ID is getting saved by the combobox into Attendance, not the EIN. So when you join the EIN fields the data in Attendance is really ID values and so is associated with the wrong record in Employ. Need to get the PK/FK fields figured out. Which value is saved into Attendance? Join on the PK/FK fields in query.

[TheHarleygirl2005]

I ended up creating another query so I have a Guard Working Query and a Guard Off query. That info is pulled int the Bi-Weekly query using joins. Now that all the information is located in one query instead of two the report seems to work fine. I guess that's what we get for using a databaset that was orginally set up by someone who didn't really know much about Access. Thanks again for all your help on this.