Guru's: This Is Really Difficult :-(

[Guest]

Hello to all,

I have two tables called "Main and Employees" and I want to stop users from
swapping employee names in existing records.

In other words: once an employee record is input into the Main table, don't
allow the EmployeeID for that record to be replaced by any other EmployeeID.
This has happened and it corrupts data integrity by getting employee data all
mixed up.

Using the example below:
If a record for Doe, Jane is already in the Main table how do I prevent a
user from switching the name "Doe, Jane" with a different person from the
Employees table.

Main Employees
--------------- --------------
MainID EmployeesID
EmployeesID Doe, Jane
Dough, John
Duncan, Tim

 

[Ken Snell [MVP]]

How are the data being viewed/edited? If you're allowing users to edit data
directly in the table, then there's little you can do.

If your users are editing data via a form, then you can do lots of things.
You can set the form's AllowEdits property to No; this prevents the changing
of any data, but it also prevents the addition of new data. You can write
code in the form's BeforeUpdate event to test for whether data changed and
reject it; but this also will prevent the addition of new data.

And so on...

So, before anyone can give you some specific suggestions, you'll need to
tell us more about the circumstances and setup.

 

[Guest]

Hi Ken,

OK, the user is editing data via a form. I want to prevent changing the
EmployeeID data only. It's ok if other data in the record is changed, but
swapping data in the name field is causing major havok. I only need to
control what happens with the name field, in this case the "EmployeeID" field.

Thanks Ken,

-A

 

[Ken Snell [MVP]]

Is this form also used to add new records to the database? Or is it just
used for editing existing data?

Either way, you could use the form's Current event to lock the control bound
to EmployeeID field when the record is not a new record:

Private Sub Form_Current()
Me.NameOfEmployeeIDControl.Enabled = Not Me.NewRecord
End Sub

 

[Guest]

Ken,

Yes, the form is also used to add new records. Now that I added your line of
code and tweaked to read like this:

Me.cboFullName.Enabled = Me.NewRecord

it works absolutely flawlessly. That one line of code deactivates the combo
until the file pointer is on a new record. It's actually better than what I
expected to achieve.

Ken, your a freakin genius. Absolutely brilliant piece of code. You rule
dude ;-)

-Alias

 

[Ken Snell [MVP]]

Ken,

Yes, the form is also used to add new records. Now that I added your line
of
code and tweaked to read like this:

Me.cboFullName.Enabled = Me.NewRecord

Oops... you're right... I was thinking backwards ...

it works absolutely flawlessly. That one line of code deactivates the
combo
until the file pointer is on a new record. It's actually better than what
I
expected to achieve.

Sometimes the "hard" things are actually very easy to do!

Ken, your a freakin genius. Absolutely brilliant piece of code. You rule
dude ;-)

Glad it's working for you. Good luck!