X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Prevent Selecting Duplicates In A Join Table Using Combo Boxes, Any Version    
post Jan 24 2014, 05:49 PM

UtterAccess Editor
Posts: 10,291
Joined: 8-November 07
From: South coast, England

In this demonstration 'employees' can be selected from a list of employees to be added to a 'sports' group.
The intention is
1. to prevent an employee from being added more than once to any one selected group
2. to prevent an employee who has left from being added to a group, while at the same time showing that employee as part of the group if he/she was added before leaving.
The database shows three tables:
1. Groups (sports)
2. Employees
3. A join table recording which employees belong to each group.
4. An unbound master form (frmGroups) includes a combo box to allow the group to be selected, and a subform, the combo box is used as the Master in the Master Child link of the sub form.
5. Subform
The subform uses a combo box to allow employees to be selected for each group.
The sub form is bound to the join table in a query that is linked to the Employee form to return the name of all employees referenced in the join table, whether or not they are marked as 'Left'
The On Current event of the sub form re-queries the sub form combo box.
A text box, which displays the employee name form the subform's record source. Is sized and placed to obscure the sub form combo box (except the combo box's down arrow is not hidden),
In the 'On Enter' event of the text box the sub form combo box is given the focus (Me.ComboBoxName.SetFocus)
4. The Row source of the combo box
SELECT tblEmployees.EmployeeID, LastName & ", " & Title+" " & FirstName & IIf(Left=-1," (Left)","") AS Employee, tblEmployees.Left, QryA.EmployeeID FROM tblEmployees LEFT JOIN (SELECT tblEmpGroup .EmployeeID FROM tblEmpGroup WHERE (((tblEmpGroup.GroupID)=[Forms]![frmGroups]![GroupID]))) AS QryA ON tblEmployees.EmployeeID=QryA.EmployeeID WHERE (((tblEmployees.Left)=0) And ((QryA.EmployeeID) Is Null)) Or (((tblEmployees.EmployeeID)=Forms!frmGroups!subfrmEmpGroups.Form!EmployeeID));

returns the employees from the employees table but has has three separate criteria
a. exclude any employees who are marked as 'left' (((tblEmployees.Left)=0)
b. Include the currently selected employee in the sub form Or (((tblEmployees.EmployeeID)=Forms!frmGroups!subfrmEmpGroups.Form!EmployeeID))
c. A sub query and criteria which excludes any employees who have already been selected for the group.
(SELECT tblEmpGroup.EmployeeID FROM tblEmpGroup WHERE (((tblEmpGroup.GroupID)=[Forms]![frmGroups]![GroupID]))) AS QryA ON tblEmployees.EmployeeID=QryA.EmployeeID
with the criteria ((QryA.EmployeeID) Is Null)
The other code In the Master Form's combo box Me.subfrmEmpGroups.Form.AllowAdditions = Nz(Me.GroupID, 0) > 0 is simply to prevent users attempting to add employees if no group is selected.
A2007 and A2003 versions attached.
Attached File(s)
Attached File  ComboJoin.zip ( 45.74K )Number of downloads: 156
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    19th October 2018 - 05:39 PM