By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,197 Members | 1,173 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,197 IT Pros & Developers. It's quick & easy.

Update table from another table.

P: 40
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:
Expand|Select|Wrap|Line Numbers
  2. Private Sub btnAddEmp_Click()
  3. On Error GoTo Err_btnAddEmp_Click
  5. Dim rs As ADODB.Recordset
  6. Dim LastValue As String
  8. Set rs = New ADODB.Recordset
  11.     DoCmd.RunSQL "INSERT INTO tblEmployees ([FirstName], [LastName],[Shift])" & _
  12.     "VALUES (txtFirstName, txtLastName, cboShift);"
  14.     rs.Open "SELECT * FROM tblEmployees", CurrentProject.Connection, adOpenKeyset, adLockReadOnly
  16.     With rs
  17.     .MoveLast
  18.       'Get the value of the last record
  19.     LastValue = .Fields("Emp_ID").Value
  20.     End With
  22.     rs.Close
  24.    DoCmd.RunSQL "INSERT INTO " & Me.cboDept.Value & " ([Emp_ID])" & _
  25.     "VALUES (LastValue);"
  27. Exit_btnAddEmp_Click:
  28.     Exit Sub
  30. Err_btnAddEmp_Click:
  31.     MsgBox Err.Description
  32.     Resume Exit_btnAddEmp_Click
  34. 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!
Sep 13 '07 #1
Share this Question
Share on Google+
1 Reply

P: 40
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:
Expand|Select|Wrap|Line Numbers
  2. rs.Open "SELECT * FROM tblEmployees", CurrentProject.Connection, adOpenKeyset, adLockReadOnly
  4.     'With rs
  5.      '   .MoveLast
  6.         'Get the value you want off the last record (newest date)
  7.      '   LastValue = .Fields("Emp_ID").Value
  8.       '  End With
  10.    'rs.Close
  12.    'DoCmd.RunSQL "INSERT INTO " & Me.cboDept.Value & " ([Emp_ID])" & _
  13.     '"VALUES (LastValue);"
The much easier way to do this is:

Expand|Select|Wrap|Line Numbers
  2. DoCmd.RunSQL "INSERT INTO " & Me.cboDept.Value & " ([Emp_ID])" & _
  3.     "SELECT Max(Emp_ID) FROM tblEmployees;"

This post can be closed if needed.
Sep 13 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.