Update table from another table.

[wizardRahl]

Hello all,

I have a form that allows a user to add employees. I have written the code that uses the "on click" event from a button, attempting to add the employee's info into 2 tables, tblEmployees and tblDxx. The Dxx refers to the department number that the user selects from a drop down box.

I believe I almost have it. I'm trying to store most of the info into tblEmployees and then just use the Emp_ID for all of the tblDxx, so as not to keep storing the names and other info over and over.

Basically, I want to copy the Emp_ID-autonumber (that was just generated from tblEmployees) and insert it into tblDxx (whatever the user selects).

What I've done is just tell tblEmployees to go to the last record and get that value. Then, insert that value (just generated EMP_ID) into the chosen table.
Here's the code I have:

 
Private Sub btnAddEmp_Click()
On Error GoTo Err_btnAddEmp_Click
 
Dim rs As ADODB.Recordset
Dim LastValue As String
 
Set rs = New ADODB.Recordset
 
 
    DoCmd.RunSQL "INSERT INTO tblEmployees ([FirstName], [LastName],[Shift])" & _
    "VALUES (txtFirstName, txtLastName, cboShift);"
 
    rs.Open "SELECT * FROM tblEmployees", CurrentProject.Connection, adOpenKeyset, adLockReadOnly
 
    With rs
    .MoveLast
      'Get the value of the last record
    LastValue = .Fields("Emp_ID").Value
    End With
 
    rs.Close
 
   DoCmd.RunSQL "INSERT INTO " & Me.cboDept.Value & " ([Emp_ID])" & _
    "VALUES (LastValue);"
 
Exit_btnAddEmp_Click:
    Exit Sub
 
Err_btnAddEmp_Click:
    MsgBox Err.Description
    Resume Exit_btnAddEmp_Click
 
End Sub
 

The problem I'm getting is that a dialouge box pops up asking for the "LastValue". The user shouldn't know this number because it should be the autogenereated EMP_ID.

If there is a better way to do this, I'm open to that as well.
Just tried this method because that's what made sense to me.

Any thoughts?
Thanks in advance!

[wizardRahl]

Figured out a better way. For those out there that would like to do the same or someting similar, you can do a ' DoCmd.RunSQL "INSERT INTO ... SELECT"'
statement. Breaking that down, you can tell the sql to INSERT INTO tblB (where you want the data to go) SELECT * FROM tblA (where the data resides)

My first attempt at doing this looked like:

 
rs.Open "SELECT * FROM tblEmployees", CurrentProject.Connection, adOpenKeyset, adLockReadOnly
 
    'With rs
     '   .MoveLast
        'Get the value you want off the last record (newest date)
     '   LastValue = .Fields("Emp_ID").Value
      '  End With
 
   'rs.Close
 
   'DoCmd.RunSQL "INSERT INTO " & Me.cboDept.Value & " ([Emp_ID])" & _
    '"VALUES (LastValue);"
 

The much easier way to do this is:

 
DoCmd.RunSQL "INSERT INTO " & Me.cboDept.Value & " ([Emp_ID])" & _
    "SELECT Max(Emp_ID) FROM tblEmployees;"
 

Enjoy!

This post can be closed if needed.