multiple form pages for same record

[polymatt]

Hello!

I apologize if my title is indecipherable; I'm (relatively) new to Access & am uncertain re: how to accurately qualify my dilemma.

I'm building a simple database at work; it contains 1 main Table (tbl_Clients) & another, (tbl_Employees) that are related by "EmployeeName" in both. My goal, ultimately, is to have a database where staff can enter client information (contact info., etc.), & which employee each client worked with.

I've built a Form "frm_ClientInfoEntry" (based on Clients) with a TabControl where the user inputs all Fields relevant to tbl_Clients on separate pages (by catergory). In the Form Header, I've included a Combo Box sourced by the tbl_Employees Table, with the "EmployeeName" being what you select. I've allowed for multiple employees to be selected, as each client can work with any number of employees.

Here's the dilemma: when I add more than 1 employee per client, let's say 2, it shows me that client twice when I'm flipping through the Form's Records.

So, like: I'm viewing a Record for Company XYZ in the Form frm_ClientInfoEntry, & say I add John C. as the employee that they worked with (in the Employee Combo Box Field). I move to the next Record as normal. Say, then, I discover that both John C. & Sarah C. worked with Company XYZ, so I add Sarah C. to the Form. When I move to the next Record, I see a 2nd copy of the Company XYZ Record after the 1st. If I add a 3rd employee, I get a 3rd, & so on. Nothing in my tbl_Clients Table is changing; just the Form adding a new viewable Record each time that I add a new employee to the Form.

I want to stop this from happening, as when I ultimately distribute the database, users will be confused.

I, preemptively, appreciate any guidance. I've been a long-time user of this forum, & have generally been able to find answers to my queries by searching through existing posts, but as I don't even know how to describe my current problem, I wasn't able to.

Thanks!

 

[Beetle]

The problem begins with your table structure. You have a many-to-many relationship here (a Client can work with many Employees and a given Employee can work with many Clients), so you need a junction table. Basic structure should look like;

tblClients
ClientID (Primary Key)
FirstName
LastName
Phone
-other attributes of Clients

tblEmployees
EmployeeID (PK)
FirstName
LastName
HireDate
-other attributes of Employees

tblEmployeeClients (this is the junction table)
ClientID (Foreign Key to tblClients)
EmployeeID (FK to tblEmployees)
-other attributes that are specific to the relationship between Client/Employee

Then, when it comes to form set up, you would have a main form/sub form scenario where the main form would be based on tblClients - for example - and the sub form would be based on the junction table. In the sub form you would use a combo box bound to the EmployeeID field in the junction table to select the Employees that are related to the Client that is displayed in the main form.

Conversely, you could also have a main form based on Employees, with a subform based on the junction table, where the main form would display the Employee and the sub form would display all the Clients that the Employee works with.