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.
"Pendragon" wrote:
> 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" wrote:
>
> > 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.
|
Similar Threads
