I have a continous form which has 1 field in which is a combo box which has a list of names. Once you select a name, the next record down is available for selection. This is because my tables PK is a autonumber. How do i filter the records to only show the names which have not been previously entered in my above records.
modify your combo rowsource by linking the rowsource to your combo with a left join to the table you are updating and using a criteria to limit only include your rowsource where the table is null
SELECT EmployeeDetails.EmployeeDetailsEmployeeID, EmployeeDetails.EmployeeDetailsForename+' '+EmployeeDetails.EmployeeDetailsSurname AS FullName
FROM EmployeeDetails LEFT JOIN CoursesAttendees ON EmployeeDetails.EmployeeDetailsEmployeeID = CoursesAttendees.nEmployeeID
I am looking to be able to select multiple people to one course, i use a subform on my course bookings form and i want to be able to quickly select people which havent been previously selected in the above record, if that makes sense.
SELECT EmployeeDetails.EmployeeDetailsEmployeeID, EmployeeDetails.EmployeeDetailsForename+' '+EmployeeDetails.EmployeeDetailsSurname AS FullName
FROM EmployeeDetails LEFT JOIN CoursesAttendees ON EmployeeDetails.EmployeeDetailsEmployeeID = CoursesAttendees.nEmployeeID [COLOR=red]WHERE EmployeeDetails.EmployeeDetailsEmployeeID is Null[/COLOR]
Note, you should use & rather than + for concatenating strings although + works it can produce unexpected errors
My EmployeeId is the PK and allows no Nulls, so it returned no records in my combo. I was thinking wont i need to have the row source something similar to this?
SELECT EmployeeDetails.EmployeeDetailsEmployeeID, EmployeeDetails.EmployeeDetailsForename & ' ' & EmployeeDetails.EmployeeDetailsSurname AS FullName,
FROM EmployeeDetails
WHERE EmployeeDetailsEmployeeID NOT IN
(SELECT *
FROM CoursesAttendees
WHERE nCourseID = nCourseId);
As this will select all of the people currently not selected in the course then it should show them.
This currently doesnt work, but this is how i would roughly do it in SQL server manager.
My EmployeeId is the PK and allows no Nulls, so it returned no records in my combo. I was thinking wont i need to have the row source something similar to this?
SELECT EmployeeDetails.EmployeeDetailsEmployeeID, EmployeeDetails.EmployeeDetailsForename & ' ' & EmployeeDetails.EmployeeDetailsSurname AS FullName,
FROM EmployeeDetails
WHERE EmployeeDetailsEmployeeID NOT IN
(SELECT *
FROM CoursesAttendees
WHERE nCourseID = nCourseId);
As this will select all of the people currently not selected in the course then it should show them.
This currently doesnt work, but this is how i would roughly do it in SQL server manager.
SELECT EmployeeDetails.EmployeeDetailsEmployeeID, EmployeeDetails.EmployeeDetailsForename & ' ' & EmployeeDetails.EmployeeDetailsSurname AS FullName
FROM EmployeeDetails LEFT JOIN CoursesAttendees ON EmployeeDetails.EmployeeDetailsEmployeeID = CoursesAttendees.nEmployeeID
[COLOR=red]WHERE CoursesAttendees.nEmployeeID is Null[/COLOR]
your code will work with a tweak
Code:
SELECT EmployeeDetails.EmployeeDetailsEmployeeID, EmployeeDetails.EmployeeDetailsForename & ' ' & EmployeeDetails.EmployeeDetailsSurname AS FullName,
FROM EmployeeDetails
WHERE EmployeeDetailsEmployeeID NOT IN
[COLOR=red](SELECT CoursesAttendees.nEmployeeID FROM CoursesAttendees)
[/COLOR]
SELECT EmployeeDetails.EmployeeDetailsEmployeeID, EmployeeDetails.EmployeeDetailsForename & ' ' & EmployeeDetails.EmployeeDetailsSurname AS FullName
FROM EmployeeDetails LEFT JOIN CoursesAttendees ON EmployeeDetails.EmployeeDetailsEmployeeID = CoursesAttendees.nEmployeeID
[COLOR=red]WHERE CoursesAttendees.nEmployeeID is Null[/COLOR]
your code will work with a tweak
Code:
SELECT EmployeeDetails.EmployeeDetailsEmployeeID, EmployeeDetails.EmployeeDetailsForename & ' ' & EmployeeDetails.EmployeeDetailsSurname AS FullName,
FROM EmployeeDetails
WHERE EmployeeDetailsEmployeeID NOT IN
[COLOR=red](SELECT CoursesAttendees.nEmployeeID FROM CoursesAttendees)
[/COLOR]
Hmm, not sure if thats exactly what i want. Ok let me break it down:
Tables and there content
EmployeeDetails = Info on the Employee. Each Employee has a unique ID Courses = The details of each course. Each Course has a unique ID CoursesAttendees = List of all of the Employees which attend a course. Linked to Courses Table via nCourseNo, and to the Employees table via nEmployeeNo
Forms
MainForm = SELECT Courses.* FROM Courses WHERE Courses.nCourseID=1
The number here is either the next new nCourseID or the current one I am viewing. This is defined in the VBA. So the details (Course, Course Date/Time etc) given in this main form is then added to the Courses Table.
SubForm = SELECT CoursesAttendees.nEmployeeID, EmployeeDetails.EmployeeDetailsForename & ' ' & EmployeeDetails.EmployeeDetailsSurname AS FullName, EmployeeDetails.EmployeeDetailsEmployeeID, CoursesAttendees.nCourseID
FROM EmployeeDetails INNER JOIN CoursesAttendees ON EmployeeDetails.EmployeeDetailsEmployeeID = CoursesAttendees.nEmployeeID
The MainForm and SubForm are linked via Courses.nCourseNo & CoursesAttendees.nCourseNo
RowSource = SELECT DISTINCT EmployeeDetails.EmployeeDetailsEmployeeID, EmployeeDetailsForename & ' ' & EmployeeDetailsSurname AS Fullname FROM EmployeeDetails LEFT JOIN CoursesAttendees ON EmployeeDetails.EmployeeDetailsEmployeeID = CoursesAttendees.nEmployeeID
Currently this is showing all of the Employees in the Dropdown which is on a Continuous Form. Once i select my first attended employee in the first dropdown, i want the second to exclude the one previous to this.
I might be going around this the totally wrong way so if i am please let me know.
