GoTo a record from a combo in the form

[Nick Del Vecchio]

In an employee database, I would like to be able to go to a record
from a combobox showing the employee's supervisor

There is a table called tblEmployees with fields
EmployeeID
LastName
Supervisor

The form has the following fields
Employeeid
EmployeeName
SupervisorID is a combo box that shows the Employee Name and
EmployeeID
etc

When I am looking at an employeeid, I can pick the supervisor for that
employee and have it displayed in the combo. The combo displays the
name and id but is bound to the name.
What I would like to do is double click the name in the combo and have
the form go to that record that was in the combo box.

I've tried many variations of something like this that doesn't work
DoCmd.OpenForm "frmEmployees", , , "EmployeeID=" & Me.[Forms]!
[frmEmployees]![SupervisorID].Column(2)

this doesn't work either
DoCmd.GoToRecord "[EmployeeID] = " & [Forms]![frmEmployees]!
[SupervisorID].Column(2)

Any ideas?

 

[Jeanette Cunningham]

Do you want to open a form of supervisors that shows all employees for the
chosen supervisor?
Not sure why you would want to do this?
Would you explain a bit more about what you want to do.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Nick Del Vecchio]

Do you want to open a form of supervisors that shows all employees for the
chosen supervisor?
No

Not sure why you would want to do this?
Would you explain a bit more about what you want to do.

The supervisor is an employee and is also in the same database as the
rest of the employees.
When you are looking at a particular employee, you can see all that
employee's contact info and as well as a drop down box that allows you
to pick who that employee's supervisor.
But if you wanted contact information about that supervisor, you have
to manually go to that record.
I just wanted a way to be able to double click on the supervisor's
name that is in the combo box and have the form go to that
supervisor's contact information.
Or even better, open the identical form on top of itself and show the
supervisor's contact info.
The supervisor will also have a supervisor in their contact info, so
you should be able to double click on that field as well and have
another form open to that supervisor's supervisor.
Then closing that form will reveal the previous form that had the
previous employee's contact info.

Not sure if I made it more confusing, but I'm hoping you get the
picture.

Thanks

 

[Jeanette Cunningham]

Easier to open a new form.
You will need to grab the EmployeeID for the supervisor to open a form
showing the supervisor's details.
That involves looking up the EmployeeID once you have grabbed the
SupervisorID from the combo.

You can use DLookup for that - check vba help on dlookup.

Once you have the correct EmployeeID, use the OpenForm statement.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Do you want to open a form of supervisors that shows all employees for the
chosen supervisor?
No

Not sure why you would want to do this?
Would you explain a bit more about what you want to do.

The supervisor is an employee and is also in the same database as the
rest of the employees.
When you are looking at a particular employee, you can see all that
employee's contact info and as well as a drop down box that allows you
to pick who that employee's supervisor.
But if you wanted contact information about that supervisor, you have
to manually go to that record.
I just wanted a way to be able to double click on the supervisor's
name that is in the combo box and have the form go to that
supervisor's contact information.
Or even better, open the identical form on top of itself and show the
supervisor's contact info.
The supervisor will also have a supervisor in their contact info, so
you should be able to double click on that field as well and have
another form open to that supervisor's supervisor.
Then closing that form will reveal the previous form that had the
previous employee's contact info.

Not sure if I made it more confusing, but I'm hoping you get the
picture.

Thanks

 

[Nick Del Vecchio]

Thanks for your response
Actually, I found an easier way
(must have come to me in my sleep)

I created a hidden field on the frmEmployees called SupLookup with
this code
=Forms!frmEmployees!SupervisorID.Column(3)

Then on the Supervisor name's field, I put a double click event

DoCmd.OpenForm "frmEmployees", , , "EmployeeID=" & Me.SupLookup

This does exactly what I wanted it to do.
But I think I may now need to find a way to go back to the original
record.
Otherwise, I'd have to right down the name of the person I was calling
the supervisor for.

Thanks again...