Guru's: This Is Really Difficult :-(

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Alias - 19 Nov 2005 16:30 GMT

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

Reply to this Message

Ken Snell [MVP] - 19 Nov 2005 17:21 GMT

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.

Signature

Ken Snell
<MS ACCESS MVP>

> Hello to all,
>
[quoted text clipped - 21 lines]
> Dough, John
> Duncan, Tim

Reply to this Message

Alias - 20 Nov 2005 02:12 GMT

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

> 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.
[quoted text clipped - 35 lines]
> > Dough, John
> > Duncan, Tim

Reply to this Message

Ken Snell [MVP] - 20 Nov 2005 02:47 GMT

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

Signature

Ken Snell
<MS ACCESS MVP>

> Hi Ken,
>
[quoted text clipped - 54 lines]
>> > Dough, John
>> > Duncan, Tim

Reply to this Message

Alias - 20 Nov 2005 04:54 GMT

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

> Is this form also used to add new records to the database? Or is it just
> used for editing existing data?
[quoted text clipped - 64 lines]
> >> > Dough, John
> >> > Duncan, Tim

Reply to this Message

Ken Snell [MVP] - 20 Nov 2005 05:19 GMT

> Ken,
>
[quoted text clipped - 3 lines]
>
> 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!

Signature

Ken Snell
<MS ACCESS MVP>

Reply to this Message