Confusing Type Mismatch

[thefonz37]

Let me preface by saying that in my database, I am storing employee
information inside of two tables - one for general information (Employee ID
#, name, hire date, supervisor name, etc), and one for login information into
our various business applications. These tables are joined on Employee ID
fields, which are text values in both tables.

I'm trying to design a form where users can edit employee data, but the
problem is that if somebody modifies an employee ID, it no longer associates
the employee properly with their logins, as this is the key that joins the
two. So I tried to write some code related to the Employee ID box on the
form to update the login table with the new data:

Dim oldEmpID As String

Private Sub EmpID_AfterUpdate()
Dim sqlQry As String
sqlQry = "UPDATE [Logins_Data] SET [Logins_Data].EmpID = [Forms]![Edit
Employee Data]![EmpID] WHERE [Logins_Data].EmpID=" & oldEmpID & ";"
DoCmd.RunSQL sqlQry

If Me.Dirty Then
Me.Dirty = False
End If
End Sub

Private Sub EmpID_Change()
oldEmpID = [Forms]![Edit Employee Data]![EmpID]
End Sub


Everything works ok if I replace "oldEmpID" with any other value in the SQL
Update statement, but if I leave it as it is above, I get a type mismatch. I
don't get it because oldEmpID and the EmpID field of the login table is text.

 

[Pendragon]

Because you have used EmpID as a text field, you need to have single quotes
before and after oldEmpID in your WHERE clause.

WHERE [Logins_Data].EmpID= '" & oldEmpID & "';"

note that there is a single quote before the first quotation mark and a
single quote after the second quotation mark and before the semicolon.

Since you are utilizing a form for users to update information, you can
always lock the EmpID field so it can't be edited.

If your EmpID is not alphanumeric, how about just using an autonumber field
as a primary key for EmpID?

 

[thefonz37]

Perfect, thank you, that's exactly what I needed to know.

Unforunately, as much as I would like to completely hide the value or lock
it, it's actually an ID number generated by our payroll system that's
generally used for most (if not all) identification purposes throughout the
company. They are numbers, but sometimes contain a leading zero, which is
not preserved in Access, but can be important for other things. I also
wanted to make it available for editing in the event that someone enters the
employee ID wrong, so that it can later be fixed.

Thanks so much for the help, though.

Because you have used EmpID as a text field, you need to have single quotes
before and after oldEmpID in your WHERE clause.

WHERE [Logins_Data].EmpID= '" & oldEmpID & "';"

note that there is a single quote before the first quotation mark and a
single quote after the second quotation mark and before the semicolon.

Since you are utilizing a form for users to update information, you can
always lock the EmpID field so it can't be edited.

If your EmpID is not alphanumeric, how about just using an autonumber field
as a primary key for EmpID?

Let me preface by saying that in my database, I am storing employee
information inside of two tables - one for general information (Employee ID
#, name, hire date, supervisor name, etc), and one for login information into
our various business applications. These tables are joined on Employee ID
fields, which are text values in both tables.

I'm trying to design a form where users can edit employee data, but the
problem is that if somebody modifies an employee ID, it no longer associates
the employee properly with their logins, as this is the key that joins the
two. So I tried to write some code related to the Employee ID box on the
form to update the login table with the new data:

Dim oldEmpID As String

Private Sub EmpID_AfterUpdate()
Dim sqlQry As String
sqlQry = "UPDATE [Logins_Data] SET [Logins_Data].EmpID = [Forms]![Edit
Employee Data]![EmpID] WHERE [Logins_Data].EmpID=" & oldEmpID & ";"
DoCmd.RunSQL sqlQry

If Me.Dirty Then
Me.Dirty = False
End If
End Sub

Private Sub EmpID_Change()
oldEmpID = [Forms]![Edit Employee Data]![EmpID]
End Sub


Everything works ok if I replace "oldEmpID" with any other value in the SQL
Update statement, but if I leave it as it is above, I get a type mismatch. I
don't get it because oldEmpID and the EmpID field of the login table is text.