Table Relationship Help (1 Viewer)

[reptar]

Hi, i current have a splash form which opens up at loadup. The splash has a comboBox with a list of all the different employees who use the database. Once Joe Bloggs selects his name from the comboBox, he hits enter and the splash form hides itself and the main form (frm_main) opens up display company details. At the top of this main form is the employees name (i.e. employeeOwnerFullName).

I have two tables:
name: tbl_company
field1: companyID
field2: companyName
field3: employeeOwnerInititals (comboBox in main form)

name: tbl_employees
field1: employeeID
field2: employeeOwnerInitials
field2: employeeOwnerFullName

name: frm_splash
comboBox: unbound

name: frm_main
textField: companyName
comboBox: employeeOwnerInititals

To be able to display the employee's full name (employeeOwnerFullName) on the frm_main i have to set a relationship between the two tables. The common field is emploeeOwnerIntials. The problem is i believe this has to be an integer value. Currently it is a comboBox text list. How do i go able setting up a relationship here? Tbl_company has new records added constantly via frm_main form so i dont want to have to constantly keep inputting an integer value for employeeID in tbl_company. Is there some way i can use the comboBox for frm_main.employeeOwnerIntials to relay an integer value back to tbl_employee.emploeeOwnerIntials?

 

[Pat Hartman]

Relationships ALWAYS include a primary key on at least one side. You are trying to define a relationship between two non-key fields. tbl_company needs to be changed to include employeeID rather than employeeOwnerInitials. If you use the combo wizard to create the control to select the Owner, it will give you the option of hiding the primary key which is exactly what you want to do. The combo will show a name or initials, whichever you choose, but the employeeID is what will be stored in the table.