split database with type mismatch error

[dr.tom]

I have a 2007 Access database that is split on a network to allow multiple users to enter data at the same time. (I just used the wizard yesterday so don't assume I am an expert). This database was created in Access 2003 by someone else and I converted it to 2007. There is a form to "Add a new Employee" that once the data is filled in the form and the button is pressed it should populate a table in the backend database. The problem is that when you press the button I get "type mismatch" error. Here is the code behind the button. Let me know what you think the problem is. There are also other forms that work on this same principle and they all have problems too.
Thanks for the help.

Private Sub add_Click()
On Error GoTo add_Click
Dim db As Database
Dim rs As Recordset
Dim Directorate, Department, Last_Name, Employee_Type, Career_Path, First_Name As String

Set db = CurrentDb()
'this opens up the database the form is in. Leave as is.
Set rs = db.OpenRecordset("Employee Name")
'and this opens up the recordset you want to add the records to. It is the name of the table in "s
Directorate = Me.Combo15
Department = Me.Combo5
Last_Name = Me.Text2
Employee_Type = Me.Combo7
Career_Path = Me.Combo9
First_Name = Me.Text0
'....... do this for all the controls. This loads the values of the fields into the variables.
With rs
'This implies that there is rs before each . or !
.AddNew
!Directorate = Directorate
!Department = Department
!Last_Name = Last_Name
!Employee_Type = Employee_Type
!Career_Path = Career_Path
!First_Name = First_Name
.Update
.Close
End With
MsgBox "This Employee has been saved.", vbOKOnly
'DoCmd.Close acForm, "New_Employee", acSaveNo
add_Click:
'MsgBox "This Employee already exits in database", vbOKOnly
Set db = Nothing
Set rs = Nothing
'These close the recordset
End Sub

 

[DCrake]

Which line is it erroring on?

 

[dr.tom]

Ok, now the button does nothing when you click it. I should get a message telling me the new employee was added but now it does not work at all.

 

[DCrake]

Have you tried putting a breakpoint on the code and stepping through it?

 

[dr.tom]

Even when I go into the code and press "run" it just sits there. I am not sure if when I split the old 2003 db it messed up the code. The guy who wrote this really liked code so there is a ton of it. The button on the form should trigger an action to write to the table and return a message but it does not work. Not sure which piece of the code is keeping it from running. In the old 2003 version it works fine (not split).

 

[dr.tom]

the form he created is just a plain form in design view with a bunch of unbound fields that are updated once you press the "add new employee" button. Not sure why he didnt just link the form directly to the table.

 

[JANR]

You could try this:

Set rs = db.OpenRecordset("Employee Name", dbOpenDynaset)

The default open methode on a table is dbOpenTable and if I remember correctly won't work on a Linked Table.

JR