Relationship shows no records 1

[TBOB]

I have 2 tables. 1 for personnel and 1 for supervisors. I want to tie sups and employees together, but keep their information separate. I have related them by using the supervisors ssn in both tables(one to many). When I create a form using fields from both tables I get no records, even though I put some info in the tables. Also,I can neither add nor edit any field. Instead of putting a bunch of info that may not be necessary tell me what you need to see to help.

Thanks,

Tony

 

[willir]

TBOB

Are you doing this with a query or table design?

I will get back to this question...
Another way to do what you are doing is to have employee table reference itself for the supervisor information. This way, you only have one table to maintain instead of maintainging two.

tblEmployee
EmployeeID - primary key
LName - Last name
FName - First name
SSN - text field, unique
etc...
Plus...
SupervisorID - long interger, references EmployeeID


Sidebar...
You have to ask... Why did I use EmployeeID instead of SSN. SSN should be considered confidential information, and using the SSN as the key to the employee ID may inadvertantly distribute confidentail material to others. Don't believe me -- read up on identity theft.


Back to issue...
As you can see, the SupervisorID references the EmployeeID, or if you must, the SSN in the employee file. If you use SSN, make sure the SSN and SupervisorID use the same field type. If using the autonumber for the EmployeeID, then the SupervisorID will have to be defined as a long interger.

Some things to ponder...
- Will you track the supervisor / boss info for all employees? Your call. You can make the field mandatory or not.
- How do you reference the CEO or plant manager type of thing? You can reference the same employee meaning the employee is his or her own boss.

To ensure this join becomes part of the design schema, open up the design view. With the database table / form / report database objects visible. Select "Tools" -> "Relationships.

To add a table, right click on the design view, and select "Show Table". Add the emplyee table twice.

You should see tblEmployee and tblEmployee_1.

Select the EmployeeID from the first table and drag it to the SupervisorID in the second table. Probably a good idea to enforce referential integrity when promted to tweak the relationship.

Now that the relationship has been formally defined, Access will now know what fields to chack for valid info.


Back to your problem...
- Not being able to "see" the linked data
- Not being able to add / edit data

Adding and editing...
I suspect you are using a query to show the info, and Access can not decide how or what to edit. For example, you are using the SSN number but Access may need the SSN for the Supervisor table and not from the employee table. Another reason or related reason is that a requried field is missing info. Or something similar -- hard to be specific without more detail but with the new design, this is no longer important.

Since the new design uses the same table, you will not have the editing issue.

Displaying the supervisor...
Again, more info would be helpful since "we" know this works. Nonetheless, here is one way that should work...

I will assume changes have been made -- EmployeeID and SupervisorID.

On the employee form, add a combo box. Have the combo box wizard select SupervisorID for the control source, and have it query the last name of the employee, and then the first name. Upon completion of the wizard, it will use the EmployeeID and SupervisorID - which is what you want, but will display the employee's last name for the supervisor in the combo box field.

How...
The combo box will use a query to bind the EmployeeID to the SupervisorID. In the design view of the form...
Data tab...
Control source - SupervisorID
Row source - select distinctrow EmployeeID, EmployeeLastName, EmployeeFirstName
Bound column - 1

Format tab...
Column count - 3
Columns width - something like 0";1";1"
List width - auto or 2"

You can make the combo box more user friendly by tweaking the query to sort by the last name.


How to display supervised employees for a supervisor?
Create a simple subform embeded in the employee form. The subform using something like "select SupervisorID, EmployeeID, EmployeeLastName, EmployeeFirstName Group by SupervisorID"

When embedding the subform, link the EmployeeID on the master form to the SupervisorID in the subform.

Richard

 

[GoDawgs]

Just to add to the SSN as a primay key problem...most likely you're storing the SSN as text, try to avoid using text as a primary key, I've run into a few bugs in the past in that situation...I always have integers as primary keys now.

Kevin

 

[TBOB]

willir,

The suggestion previously made works out much better. One more thing, now that I have the appropriate sup with the employee how can I get the sups phone number into a text box on the same form. I tried the following code, but get a runtime 438 error:
Me![txtSUPPHONE] = mE![txtSUPPHONE].Columns(3)

Thanks,

Tony

 

[willir]

Tony

Good use of columns.

Three things...
- column(3) has to exist
- phone number may be null
- best to use text field for phone numbers
- there are useful tools within VBA / Access that help debug


Column(3) will refer to 4th column.

At error, in debug / immediate window (accessed by CTRL-G), type...
[blue]?mE![txtSUPPHONE].Columns(3)[/blue]

If you get a number, you are cooking.

Try
[blue]Me![txtSUPPHONE] = FORMAT(NZ(mE![txtSUPPHONE].Columns(3)))[/blue]

Richard