Search code examples
sqlms-accesscomboboxlookup-tables

MS Access: multitable form where one of the table is called twice

I have a following problem:

I want to create a database of the documents. Each document is associated with two employees (one of them prepared the doc, another checked and approved it), so I have two simple tables: Documents and Employees. Documents table contains two fields linked with Employee ID: PreparerID and CheckerID.

When I try to link both tables in one relationship, Access states that it cannot assure referential integrity: Step 1

So I put another instance of the same table: Step 2

Now I want to create a form, where user can set preparer and approver for every document: In query builder I see this, so I have to repeat procedure from relationship builder: Step 3 Step 4

Finally on the form I set both fields for employee selection to ComboBox and set row source to Employee.EmpName. Step 5

But form doesn't work as intended. When I change some approver, it also changes in all documents where he was preparer. So obviously I'm trying to solve this in a wrong way. Could you help me please, how to do it properly?

Thank you!

Solution

  • Combobox bound to wrong field. Selection is changing value of EmpName in Employees table record, not saving value in Documents table record.

    Don't include Employees table in form RecordSource. Just bind to Documents table.

    Set combobox properties:

    ControlSource: field of Documents table - PreparerID or CheckerID
    RowSource: SELECT ID, EmpName FROM Employees ORDER BY EmpName;
    BoundColumn: 1
    ColumnCount: 2
    ColumnWidths: 0";2"

    Name parts should really be in separate fields to facilitate search and sort by last name: FName, LName. So combobox RowSource could then be:
    SELECT ID, LName & ", " & FName AS FullName FROM Employees ORDER BY LName, FName;