What is wrong with this code?

[Noreene Patrick]

Private Sub Position_Exit(Cancel As Integer)
Dim EmployeeID, FirstName, LastName As String
Dim strsqlnew As String

EmployeeID = Me.EmployeeID
FirstName = Me.FirstName
LastName = Me.LastName

strsqlnew = "INSERT INTO tblEmployeeStatus(employeeid, firstname, lastname)VALUES ('" & EmployeeID & "','" & FirstName & "','" & LastName & "')"

End Sub

This code is in the on exit event of the last field in my form AddNewEmployee (which the source is tblemployeeinfo). I want it to copy only employeeid, firstname and lastname into another table called tblemploymentstatus. The point being that HR only has to input a person once and the person also appears in the employment status table each time a new employee is hired. The info will go into the tblemployeeinfo, but it will not appear in the tblemploymentstatus.. I cant figure why it doesnt appear...can you help me?

Thanks in advance, Noreene

 

[FoFa]

You need to setup either a DAO or ADO connection, then execute the SQL against that connection.
But it maybe easier to setup an APPEND query that pulls the data from the form, then using DOCMD to run that query instead.

 

[pbaldy]

For starters, you never execute the SQL. Add this at the end:

CurrentDb.Execute strsqlnew

Also, I'm not sure it's a problem, but add a space between the ")" and the word "VALUES".

 

[Mile-O]

Dim EmployeeID, FirstName, LastName As String

This dimensions two variables as Variants and one as a String.

If EmployeeID is a number then you should have:

Dim EmployeeID As Long, FirstName As String, LastName As String

Or, even better:

Dim lngEmployeeID As Long
Dim strFirstName As String
Dim strLastName As String

If EmployeeID is actually a string, then dimension it so.

 

[Bat17]

I'm surpised no one has shouted "Data Normalisation" at you either

You are storing the same data twice which is not good design. just add the Employee ID and join the tables for the rest of it.
With a join, if you find that a name is mis-spelt or something you only correct it once, not have to remeber where else you have stored it.

peter

 

[Noreene Patrick]

I want to thank all of you for your advice...I HAVE LEARNED A LOT !!!!!

Noreene