VB to Verify Update

  • Thread starter Thread starter Rick
  • Start date Start date
R

Rick

OK, this is a though one for me and I hope I can explain
it so everyone can understand.

I have a form pulling information from one table. In the
form I have a field labeled "Supervisor." I also have a
query that pulls from the same table and it joins several
fields into one; Suffix, First Name, MI, Last name and is
labeled "Supervisor." In the form, I have a combo box
connected to the query and after update, it updates
the "Supervisor" filed with the "Supervisor" data from the
query, Mr. John A. Smith. This works fine but here is my
dilemma.

I want to ensure the "Supervisor" and "Employee" aren't
the same person but the fields in the form for the
Employee are broken into three fields, suffix, First Name,
MI and Last name. I can't write

If Me.Supervisor = Me.Emplyee then
.....

because the values will never be the same. Is there a way
I can write VB to pull the last name of the Supervisor and
then run the if then statement against the Employees last
name?

This is way beyond my capabilities and I appreciate any
assistance.
 
You could use concatenation in the vba expression for the check; however,
what it you have 2 people with the same name. I would recommend that you use
a unique ID field for the record instead and compare on this. You can use
multiple columns in the combo box with the ID field being the bound (stored)
field and hidden from the user's view and the sups name being the part the
user sees and "thinks" they're selecting.

VBA Concatenation:
If Me.Supervisor = Me.EmployeeSuffix & " " & Me.EmployeeFirstName & " " &
Me.EmployeeMI & " " & Me.EmployeeLastName Then
 
Rick, you need to add a primary key to your table - something that is unique
across all the records.

The simplest way to do that is to open your table in design view, and add a
new field named (say) "EmployeeID". In the Data Type column, choose
AutoNumber.

Once you have this autonumber in your table, change the Supervisor field so
it stores the *number* of the supervisor. You will also need to change the
RowSource of the combo so that it contains the EmployeeID of the supervisor.

Once you have set this up correctly, you can insist that the SupervisorID is
not the same as the EmployeeID. And, you do not get a problem if John Smith
has the same name as his Dad.
 
-----Original Message-----
OK, this is a though one for me and I hope I can explain
it so everyone can understand.

I have a form pulling information from one table. In the
form I have a field labeled "Supervisor." I also have a
query that pulls from the same table and it joins several
fields into one; Suffix, First Name, MI, Last name and is
labeled "Supervisor." In the form, I have a combo box
connected to the query and after update, it updates
the "Supervisor" filed with the "Supervisor" data from the
query, Mr. John A. Smith. This works fine but here is my
dilemma.

I want to ensure the "Supervisor" and "Employee" aren't
the same person but the fields in the form for the
Employee are broken into three fields, suffix, First Name,
MI and Last name. I can't write

If Me.Supervisor = Me.Emplyee then
.....

because the values will never be the same. Is there a way
I can write VB to pull the last name of the Supervisor and
then run the if then statement against the Employees last
name?

This is way beyond my capabilities and I appreciate any
assistance.

.
Click to expand...

I see three options:
1) Use VB and extract the last name from the Supervisor
field (would have to write user function that starts at
right of text field, looks for first space) and then test
it against the employee's last name. Complex and
potential problems: What if last name has space in it?
What if duplicate last names, like Smith and Smith?
2) Easier perhaps: when you set up your query to find the
Supervisor, put in criteria so that it cannot be the same
as the employee (e.g. for the last name field set the
criteria to say "<> [Forms].[MyForm].[Employee_Last_Name]")
3) Assuming your supervisor and employee come from the
same "employees" table, store the primary key in a hidden
(Visible=False) text box on your form and either use a
verification rule or an event procedure to check that they
are not equal.
 
OK, this is getting crazy. Here is what I tried. I took
the suggestion of having the combo box pull the
information previously mentioned and my primary key,
EmployeeID. Here is what I did:

The combo box updates my Supervisor feild with the
Supervisors EmployeeID. I have a hidden text field
already in the form for EmployeeID and I created my VB
using this:

If me.Supervisor = Me.EmployeeID then
......

Well, it isn't working and I don't understand what's going
on. I unhid the EmployeeID to verify the information in
the EmployeeID and Supervisor fields matched. They do,
both show 35 but I don't get a message box I asked VB to
return if both fields returned the same value.

Is it possible the value of the Supervisor contains more
than just the Supervisors EmployeeID of 35? My drop box
has two colums but I only show one. Can the value of both
colums be dumped into the Supervisor field but only show
the Supervisor EmployeeID? (The other information in the
drop box is what I mentioned in my first posting.)
 
It depends what you specify as the "Bound Column for the
combobox control. That is what becomes its value. If you
are using the name instead of the ID, it will not match
the EmployeeID. Make sure the ID is the bound column or
that you store the ID in some other control, and then use
that value to restrict your query.

One thing that can help you figure all these things out is
to use the "Locals" window and/or "Immediate" window in
VBA to see what your fields' values are. Check out VBA
help for a full description of how to use them; however in
the Immediate window you can type lines of VBA code, for
example:

print Forms("SUPERVISOR_FORM").Supervisor.Value

will show the current value of the Supervisor field in
your form exactly the way VBA sees it. By the way, don't
use "Me" here since the immediate window can't figure out
what Me refers to.

Hope it helps...
 
Back
Top