Copying info from table from list

gcgcit
gcgcit used Ask the Experts™
on
I have a form that describes a project.  You can select an employee from a list and their information is supposed to copy (First Name, Last Name Dept) from the tblEmployee and displayed on the Project form - its a one-on-one relationship so there is no subform.  It should be a "snapshot of the employees info at the time of the assignment" - hence the copy.

What is the most effective way to do this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Sr. System Analyst
Commented:
use insert ... into

http://office.microsoft.com/en-us/access-help/insert-into-statement-HP001032245.aspx?CTT=5&origin=HP001032266

INSERT INTO target [(field1[, field2[, ...]])] [IN externaldatabase]
SELECT [source.]field1[, field2[, ...]
FROM tableexpression

your case,

insert into tempTable
select * from orgTable where empId=&empid

where &empid is the id of employee that you work on...
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
I would normally include a link to the employee table in the Query that is the RecordSource for the form.  I would join that table to your project table on the EmployeeID field, but would include the First Name, Last Name and Department fields from the Employees table in the query.

I would include a field for the EmployeeID in the Project table and add a combo box on the form that displays the Employee name, but which is bound on the EmployeeID field.  As soon as you select a name in that field, the other info that pertains to that employee will display in other fields.  I would normally lock those Employee fields so that data cannot be changed without selecting a new employee in the Employee combo box.

Author

Commented:
I get error: object required when I try tried this:

  strsql = "INSERT INTO tblLCT ( FirstName, LastName )" _
            & "VALUES (" & Chr(34) & tblEmploye.FirstName & Chr(34) & ", " & tblEmployee.LastName & ") WHERE tblEmployee.EmpID = " & Me.cboEmployee.Value & ";"
 
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
As I mentioned in my email, I would only save the EmployeeID, not save all of that other information, in your project table.

When you save information in more than one table, you significantly increase the probablity that the information will get changed in one location but not the other.  For example, if you write the last name information for one of your single female employees into the project table, it is highly unlikely that it will get updated if she gets married and changes her last name.  That will probably occur within the Employees table, but not in the project table.

By only storing the EmployeeID in your projects table, the correct name (stored in the employees table) is the one that will show up on your past projects.

Author

Commented:
fyed: I understand and agree but in this case we actually need a screenshot of the person's informaiton at a specific point of time.  Normally I link to a junction table with just the ID of the Project and Person but this situation is different.

Author

Commented:
why would it be looking for an object?

 strSQL = "INSERT INTO tblLCT ( FirstName, LastName ) " _
            & "VALUES (" & Chr(34) & tblEmployee.FirstName & Chr(34) & ", " & Chr(34) & tblEmployee.LastName & Chr(34) & ") WHERE tblEmployee.EmpID = " & Me.cboEmployee.Value & ";"
         
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
Probably because tblEmployee is not open in your form.

If you are using a list to display the names, then what fields are you displaying in the list?  Assuming you are displaying the employees first and last names in the list, thesyntax should look something like:

strSQL = "INSERT INTO tblLCT (FirstName, LastName) " _
            & "Values (" & chr$(34) & me.lstName.column(1) & chr$(34) & ", " _
                                & chr$(34) & me.lstName.column(2) & chr$(34) & ")"

Author

Commented:
For simplicity of the example I just showed First and Last Name in the SQL but I actually need to pull a bunch of information from tblEmployee and display it (even though it's not in the list).

Should I be taking a different approach?  

Author

Commented:
I was thinkinging I might have to select the employee information from tblEmployee, store it in a recordset based on ID chosen then assign them into the tblLCT - but I'm wondering if there is a better way.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Yeah,

Something along the lines of:

strSQL = "INSERT  INTO tblLCT (field1, field2, field3, field4, field5) " _
           & "SELECT field1, field2, field3, field4, field5 " _
           & "FROM tblEmployee WHERE [EmployeeID] = " & me.lstEmployeeName

Author

Commented:
FYI - here was the solution I came up with.  I'll divy up the points for providing help - but this is the solution:

Dim strSQL As String
Dim rstEmployee As DAO.Recordset
Dim dbs As Database
Dim qdf As DAO.QueryDef
   
Set dbs = CurrentDb
               
strSQL = "SELECT FirstName, LastName FROM tblEmployee WHERE tblEmployee.EmpID = " & Me.cboEmployee.Value & ";"
         
Set qdf = dbs.CreateQueryDef("", strSQL)

Set rstEmployee = qdf.OpenRecordset

Me.FirstName = rstEmployee![FirstName]
Me.LastName = rstEmployee![LastName]

Author

Commented:
I found my own solution, but points awarded for helping.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial